Subform Record not Updating (1 Viewer)

[Geezer]

Realise this has been asked before but I'm struggling to get a subform to update it's recordset when a new record is added.

Have one main form with sevral tabs and some of these tabs I've placed subforms.

The three recordsets are linked by a one-many ID table from each of the two other tables, a primary and secondary table.

When I add an entry to the secondary table via a subform, subforms on other tabs do not update when I navigate between tabs after adding or deleting a record. However when I change records in the main form and then navigate back to the entry changed the record has been updated.

I've tried numerous requery options but none appear to work. What happens between changing records as this seems to work rather than a requery?

Any ideas appreciated.

Thanks.

 

[RuralGuy]

Try creating a Public Procedure on the MainForm that does a Requery of all of the SubForms and call it from any SubForm and see what happens.

 

[boblarson]

Also, just a heads up in case you have done this common error:

Do NOT include the main form table in any of the subform record sources. Also, do not include one subform's table in the other subform's record source. If you have 3 tables, your main form should have one table (probably using a query of the table) as the record source, the one subform should have one table (probably using a query of the table) as the record source and the third also only has one table (different one from the first subform's table) as its record source.

If you put in any of the others into their record sources then it will cause problems for you and you do not need them to be there anyway.

 

[Geezer]

Thanks guys.

RG, I'll try calling a requery Public Procedure and see how that works.

Bob, I'm not including the main form table in any of my subform record sources. The subforms however are sharing record sources, or do you class two seperate queries based on the same table seperate?

Thanks again guys, much appreciated.

 

[boblarson]

Bob, I'm not including the main form table in any of my subform record sources. The subforms however are sharing record sources, or do you class two seperate queries based on the same table seperate?

Why would you have two subforms that share the same table? That makes no sense to me and can cause you to have problems updating because you will likely get the error message about a conflict with another user.

 

[Geezer]

Why would you have two subforms that share the same table? That makes no sense to me and can cause you to have problems updating because you will likely get the error message about a conflict with another user.

Bob,

The main form has several tabs based on certain aspects of the record. As the main form is split into tabs we have related aspects within the subforms which are placed on their relevant tabs under different subforms.

However they're all sourced from the same table but just split/grouped across several tabs.

Does that make sense? Have I gone about it the wrong way?

Perhaps I've designed things incorrectly but it seems fairly logical, to me anyway.

Thanks.

 

[boblarson]

If they are all from the same table you don't need subforms (in fact you should NOT have subforms) and you just put the controls on each tab page.

Otherwise you will suffer from the issue about trying to update a record that is already opened and been changed by a user. You only need subforms IF the source table is DIFFERENT.

 

[Geezer]

Bob,

There are 3 "main" tables, 1 primary, one secondary and they're linked via an ID link table. I have another 3 tables linked to the primary and secondary tables.

Are you suggesting I run the main form off one query which includes all records?

Ideally I'd like to have the main form show all the primary table records and within the subforms show the grouped related secondary table records (and others), some of which will share a record source but show different aspects.

Thanks again.

 

[boblarson]

Okay, let's go over this one more time.

1. Determine if your tables have a ONE-TO-ONE relationship or ONE-TO-MANY.

2. For those with a DIRECT ONE-TO-ONE Relationship you can use a single query as a form's record source.

3. For those with a ONE-TO-MANY relationship you put the ONE side as the record source on a MAIN FORM (it matters not if you use a tab control for this or for the one-to-one type because it is all one record).

4. For the MANY side you use a SUBFORM (ONE) unless there are DIFFERENT tables which are in their own subform. You do not use the same record source for multiple subforms or you will run into concurrency issues (one record in progress and on the other subform it reporting that it can't update because another user just changed data).

Does that help conceptualize what is needed here? If not, perhaps if you upload a copy of your database with bogus data we can make some suggestions based on that. But right now it is difficult as I can't see what you've actually gone and done and with what.

 

[Geezer]

Thanks Bob, I may have to send through a bogus dataset with the current table layout.

For now though the following link provides an idea of my table relationship layout:

http://www.databasedev.co.uk/many_to_many_example.html

 

[boblarson]

Okay, to use that same example, Artist would be the table for the main form and the junction table would be the subform and Album would be a subform on the subform. There would be separate forms for adding genre's and labels as those are only lookup tables essentially.

 

[Geezer]

Yip!

But then add tabs to the main form to group seperate items of the Artist and seperate subforms under those tabs to group Album items which may relate to the grouping tabs for the Artist. The Album subforms show different data but run off the same table/s.

If I shouldn't be doing things this way then I need to rethink my layout.

Thanks.

 

[boblarson]

If I shouldn't be doing things this way then I need to rethink my layout.

You shouldn't be doing things that way and you need to rethink your layout.

 

[Geezer]

You shouldn't be doing things that way and you need to rethink your layout.

I could always keep my form layout more or less as is and split the "Album" table dependant on the tab information required. That way my subforms would all be running off different tables. Would I need accompanying seperate linking tables our could I use one main linking table, I'm guessing seperate linking and subform tables based on what you've said so far.

Also, would this solve my original problem?

 

[boblarson]

I'm not understanding how you currently have your form(s). It would be more helpful to actually see the forms in the database (a copy) so I could visualize better what you are attempting to do.

 

[Geezer]

I'm going to try and explain things a little clearer as populating the current DB with bogus data will take some time. I have two main tables which have a many-to-many relationship and are linked by a junction table (field names simplified):

Tbl_Indoor
IndoorModelNumber(PK)
Tab1_Field2
Tab1_Field3
Tab2_Field4
Tab2_Field5
Tab2_Field6
Tab3_Field7
Tab3_Field8
Tab3_Field9
Tab3_Field10

Tbl_Junction
IndoorModelNumber(PK)
OutdoorModelNumber(PK)

Tbl_Outdoor
OutdoorModelNumber(PK)
Tab1_Field2
Tab1_Field3
Tab1_Field4
Tab2_Field5
Tab2_Field6
Tab2_Field7
Tab2_Field8
Tab3_Field9
Tab3_Field10

Tbl_Junction acts as a link between two many-to-many relationship tables, Tbl_Indoor and Tbl_Outdoor

The fieldnames are labelled to show what tabs (total 3 tabs) they relate to on my form, for simplicity.

The main form runs off a query based on Tbl_Indoor while each tab has a corresponding subform based on seperate queries designed around Tbl_Outdoor and Tbl_Junction, however the queries are all based on the same underlying tables.

I'm also running a subform with the Tbl_Junction as a record source where I add the related Tbl_Outdoor OutdoorModelNumber to associate it with the Tbl_Indoor.

My questions:

1) Is running different queries based on fields in the same underlying tables considering a "different" record source or the same record source?

2) If I'm wanting to maintain my form layout with subforms how is the best means to go about it?

Everything works fine except for when I add a new OutdoorModel via the Tbl_Junction subform or an existing OutdoorModel, save the record, and navigate to another tab the new OutdoorModel entry is not listed in any of the subforms. However, if I flip from one IndoorModel record and back again then the OutdoorModel entry is updated to all the subforms. Likewise if I delete an OutdoorModel via the Tbl_Junction subform and navigate to another tabe that entry shows #Deleted, but if I navigate back and forth through IndoorModels the subform entries are updated to reflect the deltetion.

Any help appreciated.

Thanks.