Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine a Label (2000)

    I have a combo box on a form which gets its value from a table called job#. That job# table has a field for job# and a field for description. The form that writes its records to a table called tblentry. How can I make a label on the form display the description for the job# when it is selected from the combobox?
    Thanks, BMZ

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    In your Row Source you should have a query or table that will bring in your Job-id and Job-description.

    The Job id should be first followed by description

    Set the following attributes

    Column Count 2
    Column Widths 0;1
    You should now see the description instead of the Job-id
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    If you want the job# in the combo and the job description in another textbox, add the description to the select query of the combo box.
    Assuming the description is the second field in the select query set the following properties:
    Column Count 2
    Column Widths 1;2 (can be lower if to large)
    Add a textbox to the form and set the control source to:
    =[ComboboxName].[column](1)
    Column(1) because the column count start with 0. So 1 is the second column.
    Francois

  4. #4
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    Francois, That worked great.
    How do I set the value of a text box when I want to display something that is selected in a combo box on another form. For example, I have a login screen where the user selects their name from the box. I then want that name to appear at the top of the next form.
    But in the drop down I have the following code:
    SELECT [Employee].[emp#], [Employee].[last name] & " " & [Employee].[first name] AS FullName FROM Employee ORDER BY [Employee].[last name] & " " & [Employee].[first name];
    When I say =[Forms]![frmLogIn]![txtEmpName] in the textbox, it gives me the emp#. How can I make it give me the First Name, Last Name, then Emp#?
    Thanks,

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Determine a Label (2000)

    Is the SELECT described in the login form? If so change it to:
    SELECT [Employee].[emp#], [Employee].[last name] & " " & [Employee].[first name] AS FullName, [Employee].[first name], [Employee].[last name] FROM Employee ORDER BY [Employee].[last name] & " " & [Employee].[first name];

    Then use = [Employee].[first name].column(2) & " " & [Employee].[last name](3) & " " & [Employee].[emp#].column(0)

  6. #6
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    That didn't work. Even if I add column before the 3, it still doesn't work. Does anyone have any suggestions?
    Thanks,

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine a Label (2000)

    Can you tell us exactly what the names of the various forms, combo boxes etc. involved in this problem are, as well as the row source for the combo boxes?

  8. #8
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    The Log In Form is called frmLogIn. The combo box with the employee names is called cmbEmpName. The row source for cmbEmpName is SELECT [Employee].[emp#], [Employee].[last name] & " " & [Employee].[first name] AS FullName FROM Employee ORDER BY [Employee].[last name] & " " & [Employee].[first name];. On another form frmEntry. I have a text box called txtDesc on that form. I want to display the Name selected on the login screen on the entry screen.
    Thanks,
    BMZ

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine a Label (2000)

    You could use

    =[Forms]![frmLogin]![cmbEmpName].Column(1)

    as Control Source of txtDesc to display the full name of the employee selected in cmbEmpName. If you also want to display the Emp# field in the text box, use

    =[Forms]![frmLogin]![cmbEmpName].Column(1) & ", " & [Forms]![frmLogin]![cmbEmpName]

    or a variation thereof.

  10. #10
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    Thanks HansV, works great.
    I also want to do the same thing on another form, only I want to have the first name in from of the last name. Is this possible?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine a Label (2000)

    Your combo box doesn't provide for that. Do you have other controls on frmLogin that display the first name and last name separately? If so, you can refer to those; if not, you can modify the combo box:

    - Change the Row Source of cmbEmpName to

    SELECT [Employee].[emp#], [Employee].[last name] & " " & [Employee].[first name] AS FullName, [Employee].[first name], [Employee].[last name] FROM Employee ORDER BY [Employee].[last name] & " " & [Employee].[first name];

    - Change the Column Count property to 4.
    - Change the Column Widths property to (for example) 0;1;0;0.

    - On the other form, create a text box with Control Source

    =[Forms]![frmLogin]![cmbEmpName].Column(2) & " " & [Forms]![frmLogin]![cmbEmpName].Column(3)

  12. #12
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    That works great!
    How do I store the emp# in my tblentry? Let me explain what I have. I have several employees that can select there respective name from the login screen. Then they input their password and it brings up a different form. This form needs to be different based on which name is selected. I think I need to store the emp# in the tblEntry along with the input from the form. So the next time someone different logs in, they will only see input in the form with their emp#.
    So I guess my question is how can I have different versions of a form for different employees?
    Thanks,
    BMZ

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine a Label (2000)

    I don't think you need to store Emp# in a table for that. You can create a query that selects only records for that Emp#:

    SELECT Field1, Field2, ..., FieldN FROM tblWhatever WHERE [Emp#] = [Forms]![frmLogin]![cmbEmpName]

    (replace Field1, Field2, ... and tblWhatever by the appropriate names) If you create a form based on this query, it will only display records for the Emp# selected in the combo box.

  14. #14
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine a Label (2000)

    I understand what you mean, but how do I determine which records belong to each emp#. I have a separate table for entry and one for employee's. I have no way of linking the two together if I don't have the emp# in the entry table. I think I need a way to store the emp# from the login screen with the record created on the input form. Do you agree, or am I going about this wrong?
    Thanks,
    BMZ

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine a Label (2000)

    Add an Emp# field to the entry table of the same type as the Emp# field in the Employee table.

    In the form based on the entry table, set the Emp# field when a new record is added:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.[Emp#] = [Forms]![frmLogin]![cmbEmpName]
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •