Subforms and update..?

[kenjoswe]

Hi all,

I have a mainform in MS-Access97 with 4 subforms on it.
The mainform is connected to a query with the maintable and the (sub)tables for the subforms.

I want to be able to have several records in all subtables for a specific record in the maintable.

My problem is that I can't get the recordset/query to be updatable.

How can I solve this?

/Kent J.

 

[RickSpr]

It sounds like the main form's record source includes the tables for the subforms. That's a common misunderstanding. The main form doesn't need these; the subforms' own record sources take care of accessing the subtables.

If the main form query has outer joins (and it probably does the way you describe it), that will prevent you from adding data in the subforms. Removing the subtables and their outer joins will correct that.

If that doesn't solve your problem, search the help file for "updatable query". There are quite a few reasons a query may not be updatable, and you'll have to figure out which one applies. When you do, if you don't know how to fix it, come back with another question. Rick Sprague

 

[kenjoswe]

Rick,
Thanks for your reply.

You were right. I have the subtables with outerjoins in the mainform query.

If I remove the table from the mainform query the recordset is updatable. But how can I then have the subforms to follow the mainform when I navigate between the mainforms records?

/Kent J.

 

[RickSpr]

When you set up the Link Master Field and Link Child field properties in the subform control, Access automatically modifies the subform's record source query to add a restriction that the child link field must match the value of the master link field. This is very much like, and generally can be thought of as, creating an outer join from the master form's table to the subform's. Whenever the record changes in the main form's recordset, each subform is requeried to find the related records in the child tables. It's all automatic.

BTW, lots of people don't expect Access to be so clever with all of this, and try to set up main/subform record sources the way you did. Rick Sprague