Determine a Label (2000)

[threeieng11774]

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

[DJLansing]

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

[Francois]

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.

[threeieng11774]

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,

[patt]

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)

[threeieng11774]

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

[HansV]

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?

[threeieng11774]

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

[HansV]

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.

[threeieng11774]

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?

[HansV]

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)

[threeieng11774]

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

[HansV]

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.

[threeieng11774]

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

[HansV]

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