MS ACCESS 2007 - Selecting Value from Combobox in Subform creates a duplicate value

Question

I am creating a sales database for a small charity in MS ACCESS which should allow a salesperson to enter a Sale and then pull reports etc.. The entry form for the sale (testsales) should create an entry in the sales table. This table has a foreign key for the agency table which in turn stores all the referral agencies we work with.

I have put the combobox for the agency name selection in a subform.

Unfortunately each time I save a sale with a value in the combobox selected, the dublicate value is added to the agency table.

Thanks in advance

Database relations

Salesentry form with subform

If there is anything I should add please let me know.

Answer 1

In your case you can use only combobox or listbox with source of rows from query instead of using subform.

You will have to query AgencyID and AgencyName, make the Bound column AgencyID (1 if you query AgencyID first), but set the width of AgencyID in combobox/listbox to 0 to not show it for users.