Data Entry Using Form - How to Automatically update a Primary Key?

[Elizabeth Mitte]

I have a database with multiple tables, all interlinked via referentially enforced relationships between pks and fks. I am attempting to produce forms which will allow the user to enter data that will end up in multiple tables. This part I can do successfully!

My problem lies with the primary keys. I have arranged the form with a subform that also contains a subform. In order for the primary keys for the various tables to be filled out I have put these fields within the different forms...simply put either the 'date' doesn't go into the primary key without the primary key field being present and what is filled in, OR it does but creates duplicates within the primary key field, depending upon the way that I arrange the forms.

[ADezii]

Are your Link Child and Link master Fields properly set?

[Elizabeth Mitte]

I assumed they were because of the relationships from the tables being correct, however, I am not sure how to check this after the form has been made?

I am also confused how it relates to the issue of either not filling the primary key field or producing duplicates in the primary key field?

[ADezii]

@Elizabeth, here is a Demo (Attachment) that I ofter use whenever I deal with Sub-Forms greater than 1 Level deep. The Demo shows how you can create Independent, Sub-Sub-Sub Forms without nesting Sub-Form Controls. Pay specific attention to:

1. The Current() Event of the Cities and Schools Sub-Forms.
2. The Link Child/Master Fields of all the Sub-Forms.
3. The Relationships Window which graphically displays how the Tables are interrelated:
   

   State{1} ==> {MANY}Cities{1} ==> {MANY}Schools{1} ==> {MANY}Students

4. How the Primary Key Values of the Sub-Forms are automatically populated.

P.S. - Any questions, feel free to ask.

[Elizabeth Mitte]

There doesn't appear to be any events in the properties of the Cities and Schools subforms?

How do I view the link child/master fields for the whole subform? do i have to click and look at the properties of all the fields until i find them?

The relationship is very simple and I guess makes this easier, my relationships 'tend' (so not always) to have a one pk -> 1+ fks <- another single pk and I am wondering if that makes a difference to the way the forms become set out?

The form is 'read-only' so I cannot edit, but I imagine you mean that fks are auto-filled when a pk is filled in? This I can do, however, I can't then use this field as it is a pk to enter data everytime for a different subform say, different data that happened to be on the same id or on the same date.

Thank you very much for helping :o)

[ADezii]

There doesn't appear to be any events in the properties of the Cities and Schools subforms?

The Cities and Schools Sub-Forms have Code in their Current() Events. Look at the Form itself, and NOT the Sub-Form Control.

How do I view the link child/master fields for the whole subform? do i have to click and look at the properties of all the fields until i find them?

Select the Sub-Form Control ==> Right Click ==> Properties ==> Data Tab

[Elizabeth Mitte]

I am afraid I cannot find the Current() Events code, in all of the properties under 'events' there is nothing...