Linking a Sub-Form to a Main Form using a Unique Key

[michael_m_jones]

I have been using access for about a year so be gentle. The issues is the following



1. I have the following Tables:

Sub_tbl
Main_tbl

2. I also have the following Forms that correspond with the above tables respectively:

Sub_form
Main_form

3. Both tables have a field that is hidden on the forms called LinkMaintoSub_Key
I have created a, relate, (Main_tbl)one-to-many(Sub_tbl) using the field LinkMaintoSub_Key
The Main_tbl field LinkMaintoSub_Key is "Validate; no to multiples"
The Sub_tbl field LinkMaintoSub_Key is "Validate; yes to multiples"

4. What I have done is added the Sub_form to the Main_form
The Sub_form has Buttons "Save Record", "Delete Record", Next, etc.
What I want to happen is to enter data on the main form which creates a unique value in the LinkMaintoSub_Key field
Then I want to enter data into a record on the Sub_form and be able to click save and add as many records as I want to the Sub_form, while maintaining the same value in the Main_tbl LinkMaintoSub_Key field as in the Sub_tbl LinkMaintoSub_Key field

5. I have created the forms but when I fill them out and then try and save the Sub_Form I get the message "Microsoft Access database engine cannot find a record in the table "Main_tbl" with key matching field(s) "LinkMaintoSub_Key"

Any input would be much appreciated!

[rpeare]

You've done everything correctly as far as I can tell, but you don't need a 'save' record. ANything you type in the subform is automatically saved and assuming you have linked your tables correctly (the FK of the subform should be the PK of the main form) the link will be established automatically as well.

In other words if you have a field LinkMainToSub_Key on both tables on your Main_tbl LinkMaintoSub_Key should be an autonumber (unless you are generating a PK yourself) and on your Sub_tbl LinkMaintoSub_Key should be a number (long integer).

As long as that's the case you do not need the 'save' button and you do not need to go through any contortions in creating the link fields, it will happen automatically.

[michael_m_jones]

You've done everything correctly as far as I can tell, but you don't need a 'save' record. ANything you type in the subform is automatically saved and assuming you have linked your tables correctly (the FK of the subform should be the PK of the main form) the link will be established automatically as well.

In other words if you have a field LinkMainToSub_Key on both tables on your Main_tbl LinkMaintoSub_Key should be an autonumber (unless you are generating a PK yourself) and on your Sub_tbl LinkMaintoSub_Key should be a number (long integer).

As long as that's the case you do not need the 'save' button and you do not need to go through any contortions in creating the link fields, it will happen automatically.

The issue is that I want to generate multiple records in the Sub_form one after another while still maintaining the same data in the main form.


Exp: Main_form has the date and a file number on it. I fill those out then I move down to the Sub_form with fields are Name, Age, Reason for visit. So with the main part filled out I then am free to leave the form open and enter information for the whole day because all the records that I generate will be linked to the Main_form with the date and file name on it. Does that make sense?

[rpeare]

You do NOT want to save the File Name and Date on the records of the subform (on the table Sub_tbl), you only want to save the FK (PK of the table Main_tbl) on your subtable, then in any query, report or anything else you run you can look up the file name and date by linking back to your 'main' record.

In a normalized structure you keep as little information as possible stored in your tables and by duplicating the file name/date in the records of your subform/subtable you're keeping more information than is necessary.

[michael_m_jones]

I think I am doing a poor job of communicating. I am not keeping multiple values of the date and the file name, those are in the Main_tbl in the sub table is just the Name, Age, and Reason for visit. They are linked by the LinkMaintoSub_Key. That issue is that I need to add multiple Sub_tbl records for each of the main records.

[rpeare]

Your sample database does not have any tables and your form does not function because those tables do not exist. Can you create a sample that has tables in it that functions as you have it now. From what I'm looking at it looks like your MAIN FORM has no record source (it's unbound) but your subform is a bound form which you are trying to link to the combo box on the main form but I can't verify that without an example that works (has tables).

[michael_m_jones]

I have posted an example of what I am taking about. Only open the Main_tbl the Sub_tbl is not meant to be used other than as a sub form.

[rpeare]

So many problems I just modified yours. You were close, but when linking a form and a subform the link criteria should ALWAYS be the PK from the master record.

Example.zip

As stated previously you do not need buttons to do the saving, deleting or updating, they are automatic when you correctly set up your tables and links.

[michael_m_jones]

This is great! Thanks for the help.