Need help with tables and forms

[MChancellor]

Background information:<br>
I have 2 tables (Package Information & Personnel Information) that I have taken the primary keys from and put into what the Access Help screen calls a &quot;junction&quot; table. This &quot;junction table&quot; contains only the primary keys of the Package Information table (Control ID) and of the Personnel table (Employee ID). Each table (Package and Personnel) has a 1 to many relationship with the junction table.<br>
<br>
I have also created a form based on the Package table that contains a subform based on the junction table which contains a subform based on an autoloookup query of the Personnel table. <br>
<br>
The autolookup query allows the user to select an ID (they can also see the name) for the employee and then fills in the rest of the information automatically. This query places the employee ID that is selected in the employee ID field of the junction table.<br>
<br>
I would like to be able to do a similar process with the Control ID, but I'm not entirely sure how to go about it. The Control ID field on the Package table is an Autonumber. The Control # field on the Junction table is a Number - Long Integer, so that it will match the Autonumber type, but at the same time allow for duplicates if needed (as there is the possibility of there being more than one employee receiving items from a package). <br>
<br>
In the form I would like for the number that is automatically generated in the Package.Control ID field to be duplicated in the JunctionTable.Control ID field, and stored in the underlying table. I have been able to duplicate the information on the form by setting the default value of the control to &quot;=Package.Control ID&quot;. This, however, does not record the information in the underlying table.<br>
<br>
The other thing that I have tried is to just have the user type in the information in that control. Doing it by this method DOES store it in the table, but in a different record from the information stored by the autolookup query in the subform. <br>
<br>
If anyone has suggestions on how to fix these problems, I will be VERY appreciative for whatever solutions can be offered. <br>
<br>
Many thanks,<br>
M. Chancellor<br>

 

[elizabeth]

You should be storing only Employee info in the Employee table, and only Package info in the Package table, and (as you say) only the primary keys for each table in the junction table. If this is what you have, create your form based on the junction table. Use two combo boxes, one that looks up employees, the other for packages. Then add one subform for each of the primary tables if you want more info to show. An alternative to the subforms is to make your comboboxes mult-column to display the information you're interested in.