Combo Box Trouble -Adds new record instead of refering to existing record

[summernightdrive]

Hey Guys and Gals. I am new to this forum and have a question that has been troubling me all day. I did plenty of searching and I couldn't find any answer, although it may be a simple one.



So I have a combo box in a form that refers to a table that has a long list of companies. When I test out the combo box and pick one of the companies, it adds that company (with a new foreign key ID) as another record in the company table. So that company is now in the table twice. Is there any way to use the combo box to refer to a record that is already there instead of creating a new one off of your selection?

I am sorry if this is a noob question... I just need it to do this so that the existing primary key for that company is carried over as a primary key for the relating table... not create a whole new record like it is. Thank you so much for your help!

[TheShabz]

You would have to use an IF statement in VBA to first see whether it exists in the table, and add to it if it doesnt. Here's some pseudoVBA to give you an idea. This would be on the AfterUpdate event of the combobox

If DCount ("companyName", "tableName", "CompanyName = " & Me.ComboboxName) = 0 Then 'this means it doesn't exist
'Code you have to add it in
Else 'means it exists
exit sub 'just exists and leaves it as is
End If

haven't tried this out but it makes sense logically.