A combo box question

[GeorgeD]

I've got a table with customer name fields (last name, first name) and I want to use a combo box on a form to search for and retrieve a specific customer.



I have several customers with the same last name, but who are differentiated by their first names.

I set up my combo box based on a query using the last name as the bound column but displaying both last and first names. Now I can get to the first name record fine, but the control will not allow me to select any other common last name in the set. I can select a different last name and it's fine. Is there some magic here I'm missing?

Well, being pressed for a solution and not finding one I set up synchronized combo boxes, one for last name and one for first name. This seems to do the job, although in a clunky manner.

If I'm forced to use the synchronized approach is there any way to get rid of the duplicate names which show up based on the search argument and still have all the associated first names show in the second combo box?

I'm sure there is a simple solution to this problem. I can't believe that nobody has run into this before. All the examples I've managed to find use two related table such as a categories table and items table. These examples provided the synchronized combo box solution but don't solve my problem.

I could really use some help on this, even thought it's a very elementary situation. TIA.

[protean_being]

Using proper database design you should have a customer name ID field that is a primary key. The combo box's fields would then be ID, LastName, FirstName. Hide the ID column by making column widths be 0"; 1"; 1". The bound column is 1. This will link to the customer name and remove the issue of two customers with the same first and last name. The ID should be used in any other tables that the customer name is used in. Use a query to get the FirstName and LastName. This way is the customer changes their name (ie marriage) then you can change it in one location.

[GeorgeD]

Thank you for the pointer. I think my problem stemmed from the in house customer id being composed of 3 variable sections. Trying to use a multi-field primary key caused me even more problems. Using your suggestion the problem was easily solved by using an internal "customer id" to tie all the relevant information together. Thank you for the assistance.

[protean_being]

No problem. If you want to learn more about how to set up a table, look for a database book that explains Normalization. This will help you understand better table design.