Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Strange Situation With Requerying

Posted by: ngins Oct 30 2019, 06:23 PM

I have a strange situation I could use some feedback on.

Have an Access 2016 database that's running on Windows 10. Database is several years old. It's tab-based, with a main tab form and subforms on most tabs.

The first two tabs are synchronized. The 2nd tab's subform record source query is set to return records matching a field on the 1st tab's subform. The tab control's On Change event triggers a requery of the 2nd tab's subform when the user clicks on the 2nd tab.

Everything has worked fine until yesterday. One of the users said that when they go to the 2nd tab, it's not matching the 1st tab's data. The requery isn't being performed.

They said if they close the database and reopen it, then it works fine again. For a while. Then something happens and it starts malfunctioning. This has never happened before.

So I figured it was problem with that user's installation of Access and recommended reinstalling Access. Company owner wanted to wait with that, though.

So I put some code in after the requery to check the values from the two tabs, to see if they match, which they should if the requery was successful.

In the new code, if the two tabs aren't in sync after the requery, then it performs a second requery and checks again. If they're still out of sync, then the user gets a warning message and is told to close and reopen the db.

In either case, whether the 2nd requery was successful or not, the situation is logged with the result -- 2nd requery resolved the issue, or 2nd requery didn't resolve the issue.

Today that new code was put into place, and found something strange.

Though the users are in and out of these tabs all day, and there are about 10 users total, throughout the day more and more people had this problem, according to the log.

The original user had the problem once in the morning. Then a different user had the problem about 4 times over the next few hours. Then a different user had it once. And so on.

Each time the problem occurred, though, the second requery resolved the problem. Without exception.

At first I thought, maybe this has been happening all along, but we just weren't tracking it? But that wouldn't have been the case. The users would have said something if they couldn't see the correct values when they went to the second tab. And this code has been in place a long time.

Also, the number of times it happened today is small compared to the number of times the users go in and out of those tabs. Still, it shouldn't be happening at all. And it only started (apparently) yesterday.

Any thoughts on this and what might be causing it?


Posted by: GroverParkGeorge Oct 31 2019, 08:54 AM

Unfortunately, diagnosing a performance problem for an active process is hard to do from a description of the symptoms. Because it involves a multi-step process actively executing on one specific computer, or possibly multiple computers in the same environment, no one outside that environment can really follow step-by-step what might be happening, especially if it is a random problem.

Perhaps, if you could share a working demo of the Access Relational Database Application in question, someone could invest some time investigating the process.

Posted by: ngins Oct 31 2019, 09:04 AM

There's only a single step involved: requerying the subform when the user clicks on the tab. Nothing more.

And yes, it's happening on multiple computers. Yesterday three or four people were affected randomly by it, though it's never happened before .


Posted by: GroverParkGeorge Oct 31 2019, 09:36 AM

Again, it's a Click event that happens in real time in your environment. The reason such things are hard to diagnose from a distance is that it is an event and it does happen during normal processing. Without something to test and evaluate, it's a blind guess.

I might suspect a network problem, actually. If this is happening across several networked computers, each of which runs its own copy of the Front End accdb, and all of which are linked to a single Back End accdb holding your data, then I'd start there. It may be network problems. It might be a performance issue where the data being requeried is not being retrieved from the BE efficiently. It is possible, for example, that your data has reached a large enough size to be implicated.

If you changed the FEs and saw this problem only after that change, then perhaps you can identify those changes and roll them back for testing.

Posted by: ngins Oct 31 2019, 09:43 AM

Ok, thanks, George. I was just looking for ideas, not a diagnosis. So, thank you for sharing a few ideas regarding this.

The fact that this is always resolved with the second requery that I put in place yesterday when this started happening tells me it's not the database size. Could be a network issue, like you said. But something is causing the first requery to not work sometimes -- though the second requery, which takes places immediately afterwards if needed, always works.

Posted by: ngins Nov 1 2019, 02:36 AM

Just following up here as an FYI. The first day this problem surfaced, as I noted above, adding a second requery for the tab2 subform if the first requery failed resolved the problem in all cases.

Interestingly enough, on Day 2 it didn't. On day 2 every instance of a requery not showing the correct data afterwards (where tab2 data matched a field on tab1) failed to resolve the problem.

So that's fascinating, that on Day 1 the second requery resolved the issue every time; yet on Day 2 the second requery failed to resolve the problem every time. Fascinating.

According the log I created to track this, there were a total of 251 times that users went to Tab2 during the day yesterday. Out of those 251 times, 15 of them failed to show the correct data in Tab2 after the requery and after the 2nd requery.

Of those 15 fails, one user (out of a total of 12 users) had 8 of the fails, and all within a 2 hour period in the afternoon. And only 4 users out of the 12 had fails at all.

All of this is just an FYI, as I found it interesting.


Anyway, I think I have an avenue I'm going to pursue.

The Tab2 subform query uses a direct reference to the field on the Tab1 subform that it is supposed to filter against. I think I'm going to try storing the Tab1 field value either in a TempVar or a global variable, and update it every time that field in Tab1 changes. And then replace the field reference in the Tab2 subform query with either the TempVar value or a global function call that returns the global variable. So I'm going to try that and see if it produces better results.

Posted by: ngins Nov 7 2019, 06:05 PM


So, I tweaked the form query a bit and recreated the form from scratch (copying and pasting the controls), and that seems to have resolved the problem in general. The users aren't experiencing this anymore. However, a problem still remains.

The users have a separate Access database, which was created using the Contacts Database template. This was created before I arrived, and they've been using it separately from my database. There is no connection between the two.

However, at least two users are having a problem where: a) they'll be in my database in a record on Tab1 and the corresponding child records on Tab2; b) they'll open the Contacts database, look something up, and close it; and then c) when they return to my database, Tab2 is showing data from the first record in the table, rather than filtering for the current record in Tab1.

Not only that, but once this happens, Tab2 continues to not function properly. Tab1 is switched to a different record, but Tab2 still shows the first record, rather than the correct record.

Then, once they close and reopen Access and my database, everything is working fine again.

This is very bizarre. Why should an unrelated database that has nothing to do with my database, be causing these issues? I've confirmed this scenario several times with the users.

The users are using Runtime Access (latest version) in case that's an issue. Also, one of the users had the Access Runtime uninstalled and reinstalled today. But that didn't make a difference.


Posted by: ngins Nov 8 2019, 09:23 PM

I looked into this a little more, and it turns out that when that old database that was built using the Access Contacts template is opened, it causes my database to requery the form. Thus, tab1 goes back to the first record, and then tab2 shows the records corresponding to the first records in tab1. So that's why tab2 was showing the wrong records.

Never seen anything like this before, where opening up a completely separate database has an effect on another already-open database. And it's only that one database, the old Contacts database. Tried it with other databases, and they don't have that effect on it. Very, very strange.

In addition to causing my database to requery its main form, when the old Contacts database is opened my database is also screwed up. Nothing works properly; errors keep popping up. Then closing and reopening the database causes it to work properly again -- as long as the old Contacts database isn't reopened.

So this is about the most bizarre thing I've ever seen. Don't expect there to be an answer here. Just sharing for the sake of sharing.

Plan is to incorporate the Contacts data into my database and eliminate the old Contacts database, which should resolve the situation, but doesn't explain what's going on.

Anyway, like I said, just thought anyone reading this might find this interesting.