Combo boxes on forms

[rob_andy]

Right, I've got a relational database, one of the tables is a machine table, there is a form that represents this data both view and input. This machine table is linked to a customer table by the use of customerID in both tables. Customer ID is just a number, done to reduce the amount of info stored in 2 tables. What I want to do is represent each customerID on the form combo as a customer name. So the form would show the customer name instead of the number, but the form would input the id and not name. Thanks for your help. Rob

 

[rob_andy]

I basically want the combo box to display the hospital name, i.e. so its easier to input a know name and then the combo box to lookup the ID number and insert that into the database instead of the name. Please help I'm stuck. Rob

 

[SydneyGeek]

The trick is to set the combo box up with at least 2 fields. The first (bound) column) is the ID. The second (visible) column is the name.

Make sure the Control Source for the Combo is the Customer ID field, then set up the combo as follows:

1. In the Properties for the Combo, go to Row Source and click the Build (...) button. In the query grid, select the Customers table.

2. Create the fields:
Customer ID
A calculated field -- [Last Name] & ", " & [First Name]

3. Close the query, save when prompted.

4. Change the Columns property to 2

5. Change the Column Widths property to 0;3 -- it doesn't matter what number goes after the 0, anything greater than 0 will make the name field visible.

Now when you start typing a client's name, the Combo narrows the search down to the correct name for you -- but it stores the ID