Need Help with Combo Box

[EdRobey]

I have a Form which is used to enter Transactions into a Transaction Table and contains a Combo Box used to look up Vendors from a Vendor Table.
The Combo Box is bound to the Transaction Table Vendor Number field, but the lookup is done by a Vendor Name field in the Vendor File.
There is no Vendor Name field in the Transaction Table.

The Combo Box works fine except in one situation. If there are multiple Vendors with the same Vendor Number but different Vendor Names the Combo Box does look up the Vendor by Vendor Name, and does save the transaction data with the Vendor Number correctly in the Transaction Table, but when the focus moves to the next field on the form, the Vendor Name reverts to the first name in sort order on the form. It seems to do a fresh lookup after saving the data and finds the first record by name.

As an example, assume that I have 3 vendors with Vendor Number 99999, named Donald Duck, Minnie Mouse and Porky Pig. If I enter a transaction for Minnie Mouse all works fine until the cursor moves out of the Vendor field and then the name Donald Duck appears in the field replacing Minnie Mouse, although Minnie Mouse data has been saved correctly in the Transaction Table.

It seems to me that "Minnie Mouse" should stay in the combo box field on the form, but don't know how to fix this problem.

 

[theDBguy]

Hi. To fix this problem, you'll have to bind the combobox to the primary key field of the source table, so that each saved value is unique per the selected item. Using a column with duplicate values will not be able to keep track of which record was actually selected and simply display the first matched record. Hope that helps...

 

[The_Doc_Man]

Your basic problem is a data problem. As you already see, you can pick whatever NAME you want, but if the code is not unique, then when you leave the control, it refreshes the form and takes the first item in sequence with that matching code. The combo cannot show three items, so the one it will show is the first one it sees in the table with that code.

The data problem is that the bound column on the combo isn't unique but if the name IS unique, IT should be the actual key field.

 

[Gasman]

I would be using an autonumber for the Vendor record. Then it does not matter how many verndors have the same number, which seems strange to me anyway?

 

[Pat Hartman]

As you have discovered, a combo REQUIRES a unique identifier. It will never work with duplicate Vendor Numbers. You need to rethink your data. WHY are there duplicate Vendor Numbers? That makes no sense. Start there to fix the problem. Usually, you can't force the name to be unique so most applications use a "number" that is unique. In old applications, the "number" is usually the PK but in your Access app, you might want to use an autonumber as the VendorID and make it the PK. Then you can also add a unique index for VendorNumber to ensure that two vendors do not have the same number.