I have a form with a subform, which is used to call another form. Each adds data to separate tables, which I'll call Table1, Table2 and Table3. Table1 and Table2 have a one-to-many relationship, and Table2 and Table3 have a one-to-many relationship.
The main form is based off a query from Table1. There is a subform that uses a query for Table2, and it shows records that match the current record in Table1. From the subform, the user can click a button to open another form, which shows the Table3 records that match the current record in Table2.
This all works well for editing records, and for adding records into the subform. BUT, if I click the button and open the last form, it doesn't carry over the primary and foreign key values from Table2 to Table3. I have the proper relationships set up. Can anyone tell me what's going on here? Can I change a setting? Do I need to create a global variable?
If I understand your design this is what you have:
Form 1 is the parent and uses table 1 (for example, a customer form)
Form 1's child form is form 2 and sits in a subform object using table 2 (for example, a customers order form)
Form 3 uses table 3 and opens when you click on a button in form 2 (for example, an additional details screen for the customer orders)
Are the assumptions above correct?
Now if you are asking why the id from form 2 doesn't get placed in form 3, it is most likely from the following reasons:
1. Only subforms auto-enter foreign keys in their child forms. Any other popup forms will have to be synchronized manually with event programming. Setting a relationship helps enforce relationships of tables, data entry, deletes and updates, but it doesn't guarantee updates in forms. For example you can have a parent and child form and the child form will automatically get the foreign key even if you have no relationship established.
2. You are getting the updated foreign key. However, your relationship prevents duplicate entries and you have disabled the warning or masked the error message that tells the user that the form 3 update could not occur successfully.
3. You might have the situation where the other form doesn't know the key exists yet. There are two situations that can cause this...
One situation occurs when you have not saved the current form's data. For example, you enter an order for a new customer but the new customer's data in the parent form has not been saved so the child order form doesn't have a new id key to insert yet. Setting the relationships should help here to prevent new records that have no related record in the parent.
The second situation is that you have an unsynchronized, unbound form that has gotten new or changed data from a user but the other open forms don't yet know this. This can happen because your query hasn't refreshed yet in the underlying tables, or your form's update event has not yet occurred, so the underlying data is still old - and the other form still sees the old data.
Adding some event programming to either force a form update, save the form, refresh or requery the form or control, or doing a manual update of the form data with code should fix this issue.
It's not happening because of unsaved records. If, say, record #2 from Table2 exists and I click the button to open Form3, Form3 will open with the correct linked records. However, if I attempt to add records in Form3 (to match the current record in Form2), it doesn't automatically carry over the foreign keys. I am now looking into using global/shared variables that I can carry from one form to the next. I plan on inserting the global/shared values into the text boxes whenever the user adds a new record. Hopefully Microsoft Knowledge Base has more info than Access VBA Help.
If the third form is not automatically synchronized to form #2 you will have to do that manually.
The public use variables should do the trick. I use them often. You can also try referencing specific controls from one form to another such as:
Me.txtCustomerNumber=Forms!Form2.Child0.txtCustome rNumber (or Forms!Form2.Child0.Form.CustomerNumber), depending on what you need here.
Sometimes, I also save the data to a table and read the table. I use this a lot in forms that gather up criteria for reports or searches. I even will save my SQL statements to a criteria or lookup table. The added advantage is I can peek in and see what is being saved or retrieved or call it from other code without having to duplicate rebuilding the SQL statement. Just depends on your requirements.
I've done a few manually synchronized forms myself for various projects. Mainly you have to anticipate all the possible interactions from the user and keep in touch with the order that the form events fire off.
I had an article someplace that gave an entire sample database showing how you created all your own add, edit, delete and multi-form synchronization using VB code. I was surprised how much work you had to go through to do this - makes us appreciates simple things like subforms