Form shows additional primary record for each record in linked table

[Guest_imported]

I'm just learning Access 2000.&nbsp;&nbsp;Created a table with 100 personnel records.&nbsp;&nbsp;Primary key is autonumber.&nbsp;&nbsp;Created second table with 150 records for &quot;equipment assignments&quot;. Again, the primary key is autonumber.&nbsp;&nbsp;The indexed field used to join &quot;Equipment to Personnel is &quot;Personnel ID&quot;.&nbsp;&nbsp;I created the initial 100 records in the equipment table by copying the autonumber column from the personnel table and pasting it into the equipment table.&nbsp;&nbsp;I then added equipment records for those people who were assigned equipment.&nbsp;&nbsp;Some people have more than one piece of equipment.<br><br>Created a form showing the personnel records (100) with a subform (continuous) that shows all of the equipment assigned to an individual.&nbsp;&nbsp;The join is one to many with Personnel being the primary table and its primary key (the autonumber field) being used to initiate the join.&nbsp;&nbsp;The problem is that if the first record in the primary table is #1 and if there are 3 records in the secondary table for that #1, then the Access form creates 3 complete records for #1.&nbsp;&nbsp;That is, press the &quot;next page&quot; key twice and you will see the same page 2 more times.<br><br>Can someone tell me how to prevent Access from showing the second and third repetitions of what is really only one record in the primary table?&nbsp;&nbsp;Your assistance will be very much appreciated.&nbsp;&nbsp;I'm sorry for the length of this but I didn't really know how to explain the problem.<br><br>John

 

[elizabeth]

You didn't mention the recordsource of your forms. Is it the Personnel table for the main form and the Equipment form for the subform? If not, create a form for the Personell table and another for the Equipment table. Use the subform wizard to add the Personnel table form (as a subform) to the main form.<br><br>Having said all that, I wonder if you'd like to poke at your table structure a bit more, before you go into ths. Unless each piece of equipment is unique, you'd probably be better off to create a structure like this:<br><br>tblPersonnel<br>tblModel (includes Model Number/ModelID as PK and all descriptive info about that model<br>tblEquipment (includes serial number or other ID number as PK and model ID)<br>tblPersonnelEquip (includes EquipID and PersonnelID).<br><br>This structure may seem a bit more complex but it would also give you more flexibility as your needs grow (which they normally do). Another tip since you're new to Access, be sure to check out the Lookup Wizard feature in table design mode. You can use this to assign a combobox of valid values to many fields that would otherwise allow users to input inconsistant values. Table design changes need to be made <i>before</i> forms or queries are built.<br><br>If you decide to go with a structure like this, the main form would have the same recordsource but you would build a query that contained all the releveant fields and use that query as the recordsource of your subform.

 

[Guest_imported]

Hi Elizabeth and thanks for your help.&nbsp;&nbsp;Unfortunately I'm as stuck as before.&nbsp;&nbsp;Here is some additional info that may shed some light on what's going on.<br><br>The main form already includes the subform as you suggested (put there by the subform wizard).&nbsp;&nbsp;The datasource for the mainform is the table &quot;Personnel&quot;.&nbsp;&nbsp;The datasource for the subform is the table &quot;Equipment&quot;.&nbsp;&nbsp;There are no queries.<br><br>Form &quot;Personnel&quot; is linked to subform &quot;Equipment&quot; through the &quot;Personnel&quot; field &quot;EmpID&quot; and an equavlent &quot;Equipment&quot; field &quot;EmpID&quot;.&nbsp;&nbsp;In the &quot;relationships&quot; window, the personnel end of the link shows &quot;1&quot; and the Equipment end of the link shows &quot;infinity&quot;.&nbsp;&nbsp;There are no other links.<br><br>I know that somewhere there is a window with something about &quot;Set MasterLink&quot; and &quot;Set ChildLink&quot; (something like that anyway) but I can't seem to figure out how to get there to check it.&nbsp;&nbsp;Could that be the source of the problem?<br><br>Sure do appreciate your time and effort in trying to help me sort this out.&nbsp;&nbsp;I'll save your dB construction suggestions which sound very good for an update of this dB later on.&nbsp;&nbsp;Right now I just have to get it up and running.<br><br>Thanks,<br>John

 

[RoyVidar]

Linking; Open the main form in design view, open the properties boks, select the tab &quot;Data&quot;.<br><br>Click once on the subform, and you should get the &quot;link something-properties&quot; (I'm using non-english version at the moment, and can't remember the right &quot;lyrics&quot;;-)<br><br>Note, in A-2000 there is a difference between clicking once and more an a sub, so if you don't se the right properties, try clicking somewhere in the main form again and try again.

 

[elizabeth]

Go into design view of your form. In the leftmost box of the form design toolbar, there is a combobox. Select the name of the subform, then select the properties, data tab. That's where the Child/Master Link properties are found. BTW, a quicker way is to right-click on the small square that is the upper-left-most element in the subform.

 

[Guest_imported]

Thanks to you both.&nbsp;&nbsp;I got there.&nbsp;&nbsp;Problem is, everything looks fine.&nbsp;&nbsp;The links are set correctly.&nbsp;&nbsp;Can either of you think of anything else that might be making Access think it has to display a separate page for each record in the subform even though all the subform records are already being displayed on the first page?<br><br>Right now I only have 50 records in the

 

[elizabeth]

Maybe you'd like to start trouble-shooting by testing each component:<br>1)&nbsp;&nbsp;Remove (delete) the subform from the main form and see if the main form works properly.<br>2)&nbsp;&nbsp;If it does, separately test the subform (open it directly from the database's form window) and see if it works correctly.<br>3)&nbsp;&nbsp;If it does, use the subform wizard again to add the subform to the main form again.<br>4)&nbsp;&nbsp;If the tables are linked with PersonelID as the Primary Key in the Personel table and PersonelID as the Foreign Key in the Equipment table, Access should correctly guess which option is appropriate for the link. Make a note of the option to report to us in case it doesn't work when yu recreate them.