Combo Boxes - Add Information to Two Tables?

[hd5al]

Hi All,

I am looking for a better way to achieve data entry to two tables from one field on a form, and I am convinced that there must be a better way to do it then how I've done it.

The Problem:
I have a form on which the user enters data, one field of which is a combo box based list of equipment affected by a particular instruction. The list of affected equipment is large, and ever changing- hence the user needs to be able to enter data to the table on which the combo box is based, as well as to the table to which the combo box is providing information. IE, the combo box pulls information from one table and gives it to another, but if the table from which data is pulled does not contain the desired information, the user can enter the data once, and pass it to both tables.

My (bodged) Solution:
I've created an append query that adds all records in the affected equipment field from the main table to the table that provides the information to the combo box, and set the properties of the combo box table so that duplicates aren't accepted. This ensures that all the information is included in both tables, but is not repeated.

This solution works fine, but I can imagine that running this query every time the add information form is opened will slow down operations, especially as the database grows and grows.

Does anyone know of a better way to achieve the same result? I am convinced that my method is far too ineffecient to be used in the full database.

Thanks in anticipation,

Dave

 

[RuralGuy]

The Problem:
I have a form on which the user enters data, one field of which is a combo box based list of equipment affected by a particular instruction.

If the "field" you are referring to is a LookUp field then I recommend reading the following link:
The Evils of Lookup Fields in Tables
If the ComboBox is on the form rather than in the table then here's a link that should help:
Mastering the Combo Box NotInList Event
Post back if these links don't offer enough help.

 

[hd5al]

Allan,
The second link is exactly what I was looking for, thank you so much!

I don't know why I didn't think of building the combo box from a query...

Thanks again!

Dave

 

[RuralGuy]

Glad I could help Dave.

 

[LadyDi]

I have a question. I am trying to use the code provided in the second link and would like to take it one step farther. The combo box that I attached the code to is a "search" field. After The new record is added to the table, I would like it to take you directly to the new record on the form. As it stands now, the new record is added, but the search field takes you back to the first entry on the form instead of to the new entry. If you try to look up the new entry and get to it that way, the database still takes you to the first record on the form.

 

[RuralGuy]

LadyDi,
You should start a new thread with your post and simply add the 2nd link to your post as a reference.