Automatically insert a foreign key into a table with a one-to-manyrelationship

C

Craig

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!
 
J

Jerry Whittle

The Parent needs to be the form and the children need to be in the subform.
If the relationship is defined in the Relationships window with referiential
integrity enabled, then the form/subform combo created with the wizard, the
child table should get the parent ID.

However children usually more than one parent. Then there are step parents,
adopted children, children without known parents (AKA orphans), and many
other permutations as the nuclear family has exploded. You may need a joining
or bridging table to break up the Many-to-Many relationships.
 
K

KARL DEWEY

If the relationship is defined in the Relationships window with
referiential integrity enabled,
I also set Cascade Update.
 
J

Jerry Whittle

Allow me to butt in. I seldom use Cascade Update as most of my primary keys
are autonumbers. With no meaning, no need to update the links. There are
possible exceptions of course such as when a new boss takes over.
 
K

KARL DEWEY

I occasionaly use key that is not autonumber and have gotten in the habit.
I do not select Cascade Delete.
 
T

Tony Toews [MVP]

Craig said:
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.
Click to expand...

That is automatically done for you if you've setup the Link Child
Fields and Link Master Fields properly. This means that the Link
Child Fields contains the field name of the ParentID foreign key on
the child table while the Link Parent Fields contains the field name
of the ParentID primary key on the parent table.

It's always a bit tricky getting this going the first few times. I
find that it helps to drag and drop the ParentID foreign key from the
child table and ensure it's visible when testing. You will see Access
automatically filling in that value as soon as you type the first
character into a field on the subform. Once it's working smoothly
then turn the field visibility off.

Tony
 
T

Tony Toews [MVP]

Jerry Whittle said:
However children usually more than one parent. Then there are step parents,
adopted children, children without known parents (AKA orphans), and many
other permutations as the nuclear family has exploded.
Click to expand...

My understanding is that the nuclear family never really existed
except in TV shows of the 50s and 60s. Sociologists have gone back to
the New York city census from the late 1800 or early 1900s in which
the detail data including names has become available due to privacy
concerns. By comparing last names of individuals with clear male and
female first names they've discovered that the number of couples not
married to each other was about 50%. Of course back then divorce was
next to impossible to get as, for example in Canada, and IIRC it
required an act of Parliament. So folks just split up and moved in
together.

And what is todays rate of couples who are divorced? Roughly 50%.

Tony
 
R

Rick Brandt

I never use Cascade Delete... made that mistake once, long time ago.
Lesson learned!
Click to expand...

What was the "mistake"? It's a perfectly legitimate setting in a Master/
Detail scenario. If I delete an Invoice main record under what
circumstances would I NOT want to delete the line-items associated with
that invoice?

The only mistake is in thinking that cascades makes sense in every
relationship (they don't). In many though they are perfectly suited to
what the database needs to do.
 
G

Gina Whipp

Rick,

My Client deleted the record from the table (don't ask but suffice it to say
I will always have work form this Client). POOF, all gone! Had it been
unchecked the Client would have gotten the typical related records message
thereby saving the record. Of course it was my first big database and what
did I know about Error Handling! Another lesson learned! Thank goodness
for back-ups!

I since no longer even bother with that setting and handle the related
records with my own message.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
T

Tony Toews [MVP]

Gina Whipp said:
Link went nowhere... Came up page cannot be displayed!
Click to expand...

Server was down for a short while then as the UPS was beeping.
Reckon it's time for a new UPS or new batteries.

Tony
 
C

Craig

The problem ended up being that the query I used had multiple tables
and multiple references of the PK I was using to link tables. I ended
up editing the query and renaming that field to something new using
Whatever:FieldName and it worked fine. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automatically insert a foreign key into a table with a one-to-many relationship 5
Select Query With Parents And Child's In one row 11
Expression for combining multiple children with one parent in a qu 4
Trying to create a report that will list unmatched records by Ticket Primary Key 0
how to handle children of parents in contacts table 2
Query to display all related data from 4-level tree 4
Implementing a Hierarchy in MS Access 5
Combo Box in a Datasheet Form 2

Top