Help! with using combo boxes during data entry

[Guest]

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

 

[Rick Brandt]

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.

How is the query for your form set up? It sounds like you have both tables
included in that query when you should only have the table that you want to
do insertions on.

 

[Guest]

When creating the forms, I would select the following field names: 'project number', 'project name', and 'business unit'. I think what I should have done is select the 'business unit_ID' field instead! Preliminary tests seem to show that this works! I've been going nuts over this....Thanks.