Help with data entry using a combo box

[Guest]

I cannot get my message posted in the 'Form' newsgroup so I am also duplicating it here

Hello All, below is simple scenario to illustrate the problem I'm having

I have 2 tables with a 1-many relationship. The first contains two fields, ID (the autonumber key) and business units The second table has four fields- ID (the autonumber key), project number, project name, and business unit table_ID (the lookup to the business unit table)

I have a form that I would like for users to enter new project numbers, names, and assign to a business unit (using a combo box). Also, if the business name does not exist, the user should be able to add it by typing it into the combo box (Limit to List is No)

Here is my problem, when the user types in a new project number, project name, and selects a business unit, the Business Unit table adds a NEW record which essential is a duplicate of an earlier record. The business unit combo box then displays the additional duplicate business unit name on the next data entry form. Why is this happening

I want the new record in the project table (specifically the business unit_ID lookup field) to link to an EXISTING business unit record. The only time a new record should be added to the business unit table is if the user needs to add a new business name.

Does anyone have any suggestions

Thanks for any help
-rjkulesa

 

[Ted]

It's hard to say what the problem is without knowing more
details such as the rowsource for the combo box and which
column you are binding to, and which column is being
displayed. Please post back with this info.