I am using this code:
Private Sub Form_Timer()
Dim varRetVal
varRetVal = DLookup("SystemUp", "Qry_Sys_Status_T")
If Nz(varRetVal, 0) = 0 Then
DoCmd.OpenForm "frmSystemExit", acNormal
End If
End Sub
This is set up on the On Timer Event when the database opens and displays the main menu.
I run this code (and 15 other users are running this on their front end) to look at "SystemUp" from "Qry_Sys_Status_T".
The Table Sys_Status_T only has 1 record on it. SystemUp is a Yes/No field I use to shutdown the front end on all users PCs if I need to do maintenance on one of the backend databases.
The code is running every 20 seconds to see if I updated the SystemUp Field. If I updated it, a form pops up warning the user I am shutting down their front end. There is a timer event on the pop up form, which closes Access after 1 minute to allow them to finish their work.
I then let my users know, manually, they are able to reopen their front end once I have completed work in the back end. This is typically just me repairing and compiling the backend.
Every once in a while, at least 2-10 times a day, the users are receiving the run time error 3734 and have to click end to stop the code from running.
I am not sure what is creating the issue, as it is so erratic. I am not a VBA expert by any means, and am assuming there is an update I can do to my variable, but have not been able to locate it.
Once you close the debugger by ending the code, the database works just fine for a period, at times, several hours, before the debugger pops up again. I have 15 backend users and a typical day, there are 8 active users working at one time in the database.
Any assistance would be greatly appreciated!