Subform occasionally doesn't update when main form moves to a different record (1 Viewer)

[cricketbird]

I have a Single Form (Products) with a subform (Components). This has been working fine for 7+ years. However, a few weeks ago, a user said they updated the components for product A, then switched to product B and the subform continued to show the components from product A. Nobody else was having the issue and neither I nor the user could reproduce it, so I dismissed it as a fluke. Yesterday, a different user said they had looked at product C, then switched to product D, entered a lot of component data for D and it looked correct, but when they closed and reopened the form, D's components were now listed on product C. Today, two more users (out of 20 active users) said they experienced this, but only intermittently and that mostly it has been working fine.

I've checked that the master/child links are working (and clearly they nearly always are). Is this a sign of corruption of some sort? Or a common problem with a known cause? Tearing my hair out over this one...

The data is in an .accdb backend on a shared network drive, while each user has their own .accde front end installed locally. We are running Office 365. Users navigate between records on the mainform by typing the product ID into a text field (After Update:

DoCmd.OpenForm "Edit Products", acNormal, , "ProductID= " & Me.ComboPickProduct, acFormEdit, acNormal

).

 

[Pat Hartman]

I would shut down the app for a half hour so no user can open it. A simple way is to rename the BE. Then rebuild the BE by creating a new, empty database and importing all the tables from the original. Make sure all the indexes have been imported and then check on all the relationships.

 

[gemma-the-husky]

@cricketbird
Personally I don't like the idea of users having to type in a productID. I would rather give than an interactive way of selecting the product.

When you save records in the subform do you leave it to the database to set the FK of the subform record, or are you setting that in code?

 

[cricketbird]

I would shut down the app for a half hour so no user can open it. A simple way is to rename the BE. Then rebuild the BE by creating a new, empty database and importing all the tables from the original. Make sure all the indexes have been imported and then check on all the relationships.

Thanks - I did this and so far...so good...fingers crossed.

 

[cricketbird]

@cricketbird
Personally I don't like the idea of users having to type in a productID. I would rather give than an interactive way of selecting the product.

When you save records in the subform do you leave it to the database to set the FK of the subform record, or are you setting that in code?

They have interactive ways as well, but nobody uses them - they prefer the speed of just entering an ID. The affected users assured me they were just typing the ID.

I'm leaving it to the database to set the FK in the subform record - it has worked for a long time without issue and still mostly works.

I also added a [subform].requery statement to the AfterUpdate event on the main form. Hopefully that will work.

 

[Pat Hartman]

The Requery in the Form's AfterUpdate event is unnecessary, especially since it has unexpected consequences such as repositioning the recordset which might be disconcerting to the user.

 

[gemma-the-husky]

They have interactive ways as well, but nobody uses them - they prefer the speed of just entering an ID. The affected users assured me they were just typing the ID.

I'm leaving it to the database to set the FK in the subform record - it has worked for a long time without issue and still mostly works.

I also added a [subform].requery statement to the AfterUpdate event on the main form. Hopefully that will work.

There must be something happening. It's hard to pin some things down though.

I have a situation where a SQL update statement should set a number of values. Occasionally one of the values only doesn't get set, although the others do The only thing I can think is that something else is changing the value back to its original setting, but I can't for the life of me understand what. It happens maybe once every few months. Very strange.