Subforms integration not working seamlessly in Ms Access 2010

0
votes

I have a form in MS Access 2010 with two sub forms in it. I've defined one-to-one relationship with the tables behind the forms. Both the sub-forms should be used to create a record in corresponding tables and then update the foreign key in the parent form. But when I fill the sub-form first, an entry is created in the corresponding table but the foreign key doesn't get updated. Shouldn't this happen automatically? Or should I add code for updating parent form's field?

Also when I start filling the main form first and then jump to any of the sub-form, I get the following error message:
"You must enter a value in the 'Activations.dbBoxID' field" (Activations is the main form here and dbBoxID is the foreign key field.)

Another issue is that I don't want sub-forms to update the corresponding tables until an entry in main table is also created (i.e. all required fields in main form are also filled).

I am new to MS Access. Any help would be really appreciated!

2
ms-accessms-access-2010
Your form is upside down. The table where you store a foreign key is the CHILD table and should be in the subform, with the table from which the value for the FK is drawn as the parent form. That's the only way the LinkChild/LinkMaster properties can operate. – David-W-Fenton
That makes sense. I didn't knew subform-parentform relationship works this way. I quickly wrote a sample database and it worked flawlessly. Thanks David! – Prashant

2 Answers

2
votes

The concept you are missing here, and what's not being explained to you is that when you build a master to child relationship in access, you can model these relationships with forms and a sub form, but you base the forms on the actual tables and not queries that are an result of joining those tables together.

In other words, your master form should be based on the main or parent table. DO NOT join in the child table. I'm going to repeat this again: do not join in the child table.

So you can base the master or main form on a query, but when you do so, do not join in the child table in that query. In fact, in most cases there is really no advantage to using and basing a form on a query anyway. Simply create the form, and base it on the table.

The SAME ADVICE applies for the child form. You base the child form on the child table. You DO NOT use a query with a join.

The reason why you're getting error messages is because you have a query for the main form that is based on two tables. With an enforced relationship, when your focus switches from the main form to the child form, a record save of the main form occurs, and thus you're getting the error message.

So your error and problem is due to you basing a form on a query that is a join of two tables when it is not needed in this case.

0
votes

A form with subforms works better if the data in the parent form are entered first. Then the subforms can be joined on the parent_key to foreign_key in each table without any code.

If you want to let users enter records in subforms first, don't update them until the parent form is completed/saved, you're going to need to do some coding. Not sure why you need/want to do that?

A client form with bills and payments subforms, for example you would enter enough client data to create a record. Then bills and/or payments can then be entered.