Combo Box for lookup. Want to store KEY value but not display

[Rich]

I have a form with a combo box on it. The combo box's source is a
access lookup table with two columns, EmployeeName & EmployeeNumber.
I want to display the EmployeeName from the dropdown but store the
EmployeeNumber. I was able to get this work (for the most part) by
setting the Bound Column to 2 and setting the column widths property
to 0.5";1"

The problem is after I click the drop down to select the record that I
want the EmployeeNumber is displayed in the box on the form. How can
I get the EmployeeName to display there instead?

 

[AlCamp]

Rich,
Try 2 columns, EmployeeNumber and EmployeeName (switched around), with
columns of 0";1", and bound column =1
The field should be bound to the EmployeeNumber.
With this arrangement, you select an EmployeeName, the EmployeeName is
displayed, but the EmployeeNumber is stored in the field.
hth
Al Camp

 

[Rich]

That worked great. I was wondering also if it was possible to display
both columns in box after the drop down is checked. It is most important
for this form that the name is displayed but I have some other forms where I
want to display two columns of data after the dropdown is selected, just
like the day it displays in the list when you set the width for two columns

Is that possible too?

 

[Rich]

This worked great.. The only other thing I would like to do is to
display the EmployeeName in the box after I select from the dropdown.
Currently the bound field(the number) is being displayed.

 

[Jeff Conrad]

If you would like to display both the EmployeeNumber and the EmployeeName in the combo box even
after making a selection and store only the EmployeeNumber then this is what you need to do.

1. Create a new query of the lookup table.
2. Drop the EmployeeNumber and the EmployeeName into the query design grid.
3. Add a third column called FullDisplay and set the Field line to this:
FullDisplay: [EmployeeNumber] & " " & [EmployeeName]
4. Save the query as qryEmployees or something similar.
5. Run the query by itself to see what it looks like. The FullDisplay field will display both the
EmployeeNumber and EmployeeName with a space in between.
6. Open the form in Design View and go to the Properties area for the combo box.
7. Make the following changes:
- Change the Row Source to qryEmployees (or whatever you named the query)
- Set the Column Count to 3
- Set the Column Widths to 0";0";1" (or a little more on the last one of needed)
- Bound Column should be 1
8. Close and save the form.
9. Open the form in normal view and click on the combo box. You should see the EmployeeNumber and
EmployeeName with a space in between. Select an employee and the combo box will still display both
fields after moving away. Only the EmployeeNumber will be saved back to whatever table the form is
based on.

Hope that helps,