Detecting when Access, and not a specific form, has lost focus (1 Viewer)

HairyArse

Registered User.
Local time
Today, 12:59
Joined
Mar 31, 2005
Messages
92
Hello, I am using a Microsoft Access .mdb front end and a MySQL back end. I have a bound form containing a sub form.

For one of my users, sometimes when she switches from Access to Excel and then back to Access a minute or two later, she gets a #DELETED error in the subform and one or more of the rows she entered has been deleted.

I've tried forcing a save or requery on Lost Focus but I believe this only applies to the form itself and not to the whole Access application.

Is there a way to detect this and counter it?
 

HairyArse

Registered User.
Local time
Today, 12:59
Joined
Mar 31, 2005
Messages
92
Also, how the hell can I change my stupid username on here? :D
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 28, 2001
Messages
27,001
For one of my users, sometimes when she switches from Access to Excel and then back to Access a minute or two later, she gets a #DELETED error in the subform and one or more of the rows she entered has been deleted.

Is this like switching between two active windows? Because this is absolutely NOT normal Access behavior if that is what you meant. In fact, this cannot be a complete description of the problem because something else HAS to be going on here.

As long as any task window remains open, the local memory that holds a record of what Access did MUST remain intact. This memory retention is not an Access feature but a WINDOWS feature, that memory for threads will remain intact until the app exits and the window associated with the main process or task closes. Without it, no one could gainfully use WINDOWS at all because window-switching wouldn't work.

There ARE a couple of events you can use, called the Enter and Exit events, but they apply to the form. The overall app itself doesn't have separate events that I've ever seen because a quiescent app with no forms open has no code-usable context. To capture an event (at least to my understanding) you need a class module to be open and active. I would be willing to back down on that if other forum members have found exceptions, but I don't recall seeing anything like that.

If you had a setup with a "switchboard" form that never closes until you close the app, I would guess that you could use its Enter and Exit events for some things, but the truth is that if changes are being made to a form, it is the form's Enter and Exit events that would be most productive. THEY are closest to the problem and have access to all data on the form.

But let me ask this: Is it possibly the case that the database links to a spreadsheet and that your user simultaneously has the linked database and the spreadsheet open at the same time? In that case, I might expect destructive interference between the two windows. Otherwise, this makes no sense.

As to the name... beware of asking for what you want, you might get it. One of the super moderators can advise you on that one. I'm just a part-timer here.
 

HairyArse

Registered User.
Local time
Today, 12:59
Joined
Mar 31, 2005
Messages
92
Thanks for your comprehensive response The_Doc_Man. It's never quite made any sense to me, either. But I have seen this behaviour happen on numerous occasions.

Basically my user will be working on an Order or Quotation in Access and will have added items to it. She'll then switch to Excel to get further information and then a couple of minutes later when she switches back will encounter the #DELETED error.

Using a MYSQL backend is generally great, but I do encounter a lot of similar isues as well as write conflicts, even when using TIMESTAMPS and updating said timestamps on the AfterUpdate Event of all fields.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 28, 2001
Messages
27,001
Ah, I knew there was more to it than this... You said in your first post it was SQL-based, but I focused on the problem and not the configuration. My error.

If you don't commit that transaction after a certain amount of time, SQL-based back-ends will have a time-out that "unrolls" the transaction. Don't know the specifics for MySQL and it is possible that some sort of optional setting on MySQL would help you. But in the absence of actions or settings to the contrary, you are getting timed out.

If this had been a "pure" Access FE/BE situation, my description is correct and your user would not have seen this. But with an active SQL engine as a BE, you have ... not a free-form monologue, but a dialog with rules relating to "keep-alive" for transactions. And depending on how long your user stays in Excel, it appears the rule is being violated.
 

Users who are viewing this thread

Top Bottom