Automatically insert a foreign key into a table with a one-to-many relationship

[cjohns38 via AccessMonster.com]

I have two tables one called parents and one called children. The table
structure is as follows:

Parents:
ParentID (PK)
First
Last
DOB

Children
ChildID (PK)
ParentID(FK)
First
Last
DOB

I created a subform for parents to be able to fill in information about their
children. The problem is that I need the ParentID in the children table to
default to the ParentID associated with the subform. IE if I open up the
form for ParentID 1000 I want the ParentID in the children table to default
to PARENTID 1000 so I don’t have to type them in. I’m sure there has to be a
way to do this but I haven’t found it. Any help would be greatly appreciated.


Thanks!

 

[Dorian]

If you set the subform up using the wizard this will be done automatically.
Just make sure you respond to the prompts with what you want to achieve.
To invoke the wizard, just drag the subform icon to your form.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dorian]

Forgot to say, you can make the Parent id a hidden field (visible = false) in
the subform.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Jeff Boyce]

The common approach to handling this involves two forms. The first form is
considered the "main" form, and holds your 'parent table' information.

The second form holds the "child" information, including the field for
ParentID, and is used as a subform, embedded within the main form. The
control in which that subform is shown can be 'connected' to the mainform by
indicating which field(s) connect them ... i.e., the ParentID.

Note that this approach means you NEVER have to add the parentID to the
child record -- Access handles that for you if you set it up this way.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[cjohns38 via AccessMonster.com]

Thanks for each of your responses. I really appreciate it. Let me explain
my setup little further. This is a database designed to track study
participants. As it stands I have linked together five separate
tables/queries into a single query so I don’t have to build nine million
queries and subforms. This means that some PK/FK’s are sort of repeated
(keep this in mind for later). IE the PK ParentID shows up in more than one
table. I have used the fields in this query to build a form with a tab
control.

Tab control 1: Address
Connects to address subform which allows me to track addresses over time. I
linked the subform to the masterform on ParentID. This is working properly.


Tab control 2: Scheduling
Connects to scheduling subform so we can track when they are scheduled to
visit. Subform linked to the master form on childid. Currently, I cannot
not add time points 1,2,3 without typing in the ParentID.

Tab control 3: Parent Procedures
All info comes from the full query and it’s working properly.

Tab control 4: Child procedures
All info comes from full query and it’s working properly.

Tab control 5: Siblings
Connects to a siblings subform so we can add information about siblings. I
linked the subform to the masterform on ParentID. This is not working
properly and I cannot add siblings. In fact, I’m battling through another
error “The LinkMasterFields property setting has produced this error: ‘The
object doesn’t contain the Automation Object ‘Contacts.â€

At this point, I’ve re-examining my relationships between tables. I’m making
sure the one-to-one and the one-to-many relationships are correct. I’m
enforcing referential integrity and using cascading updates to related fields.


So………..basically. I do have the mastertable and child tables set up.
Currently, I’m just bypassing the mastertable in favor of a query to link
tables together and then build the form based on the query. I’m then linking
to the child table from the master form. It’s just not working………….my best
guess is that it has something to do with relationships between the tables
and using a complex query.

Thoughts?

 

[cjohns38 via AccessMonster.com]

I figured it out. Turns out that having more than one variable with exactly
the same name was freaking out the linking. I changed the tables I was
linking on to have a 1 and all the sudden it works perfectly.