If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > update records (keys) on related sister tables

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Sep 2012
Posts: 5
update records (keys) on related sister tables

In an Access database, I have a Volunteer file, and associated with it and with the same primary key value as the Volunteer table, are AdditionalInterests, Education, and other files. When I write out a record to the Volunteers file, I want to write records with the same primary key value out to the sister files, so that when the user has time to enter the extra information, the placeholder (primary key) is already there and the correct record is updated, and enforces the relational nature of the system.

what's the best way to write to the other few files when I write out a volunteers record?
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Apr 2004
Location: outside the rim
Posts: 1,001
Sounds like a classic case for using subforms, providing I understand your question.

In the related tables (the "child records"), they would each have their own primary key, and they would each have a "foreign key" (which is the primary key from the parent table). I am assuming this is what you want, so that multiple Interests can be added relating to a single volunteer, for example.

For example:
VolKey   Name
1        Tom
2        Dick
3        Harry

IntKey   VolKey   Interest
1        1        Programming
2        1        Bowling
3        2        Programming
4        3        Cooking
4        3        Soccer
have fun,

Small, custom, unique programs
Favorite message from Windows:
There was an error displaying the previous error message

Sadly, there was no error number to look up ...
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Sep 2012
Posts: 5
yes, but...

if they don't have any or all the information for the other tabs, those files don't get created, and there's no "placeholder" for them when they go back in to enter that tab's data later on.

can i not just write a primary key on a file on the AfterUpdate hook on the form properties?
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Apr 2004
Location: outside the rim
Posts: 1,001
sure, but why? in the parent child relationship model, it's ok if you don't have any children at first (or ever) for some of the records. Based on what I know of your question at the moment, it doesn't make sense to create essentially empty records to "hold a place" for potential future child records. The sub table containing the child records will have it's own unique primary key, and will happily accept the foreign key (the parent) at any time.

When you go back to add child records later, you open the form back up, select the volunteer and then start adding interests, etc.

In fact, if your child table is one-to-many (multiple interests per volunteer, for example), the primary key of the child has to be unique (and independent of the parent key) anyway.

Am I missing something in your question?
Reply With Quote

multiple table update

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On