Re: trying to use dual relationships and getting dupes

the primary key in Table1 should be linked to a corresponding *foreign key*
in Table2.
the primary key in Table2 should be linked to a corresponding *foreign key*
in Table3.

once you've set those relationships up correctly in the Relationships
window, and enforced referential integrity on each one, then you can create
a mainform/subform/subform setup that *doesn't need any code at all* to
relate child records to parent record; Access will do it automatically.

bind the mainform to Table1.
bind subform1 to Table2.
in mainform Design view, add a subform control to "hold" subform1. set the
subform control's LinkMasterFields property to the name of the primary key
in Table1; set the LinkChildFields property to the name of the corresponding
*foreign* key in Table2.
bind subform2 to Table3.
in subform1 Design view, add a subform control to "hold" subform2. set the
subform control's LinkMasterFields property to the name of the primary key
in Table2; set the LinkChildFields property to the name of the corresponding
*foreign* key in Table3.

hth


"sparks" <jstalnak@xxxxxxxxxx> wrote in message
news:2pvm13tc4qm03on7hidle0mq12shsvvckj@xxxxxxxxxx
I have a form/table with an autoid it is linked to a table/form with
and ID as a 1 to many.
Under this form/table I need another table with many records for each
on of the many in the previous table.
form1 form2 form3
table1 table2 table3

autoid -1-------------@ ID -1-----------------@- ID2

of course it won't allow this so I put in a second ID

autoid1 -1-------------@ ID1
ID2 -1-----------------@- ID3

so ID2 is not an autoid based on a record in table 2

but when a sub sub form is built using table3
when a record is created in form2 instead of id2 generating a single
new number to be used in form3 I get 4 blank records.

there is some code under form current and even when its removed I
generate 4 ID2 records that have NO ID1 values

so instead of 1--------1
2

I am getting null 1
null 2
null 3
null 4

I can't think of why I would get 4 records generated in this way since
At first I thought that the sub sub form was generating the records
but I am getting nothing in table1 or table3.
just blank records being generated by the ID2 which is an autonumber.
autonumber-----this got me thinking but what is firing it 4 times ?

I could see 1 since its linked but why 4?









.

Relevant Pages

  • Re: Can this be done?
    ... I think that you are confusing the Primary Key with the Foreign Key. ... for table2 (incidentally a Foreign Key is not necessarily part of the Primary ... > to create parent/child relationship. ...
    (microsoft.public.access.formscoding)
  • Subform refresh
    ... My invoice form, has a temporary Primary key and invoice details has a ... temporary Foreign key. ... I then get the true primary key and update it in the ... Now after the new Foreign key is updated, the form / subform lose their link ...
    (microsoft.public.access.forms)
  • Re: Fields
    ... Time to set up a separate table to hold the Date of Contact and the Comments ... That table should also have a primary key field, and a foreign key ... and the subform is the data from the new table. ...
    (microsoft.public.access.forms)
  • Re: Cant figure this out
    ... both tables where the primary key and the foreign key have the same value. ... If you want to see student discipline records and test scores in one form, ... One subform should be based on the discipline ...
    (microsoft.public.access.gettingstarted)
  • Re: Blank subforms
    ... I guess I'm not too sure about foreign key terminology. ... Tables A, B, and C all have a Primary key. ... but no fields were showing on the subform. ... employees and the main form is employees who are ...
    (microsoft.public.access.formscoding)