Access Cookbook - Ken Getz [223]
Discussion
The technique in this solution makes use of the form's Timer event, the form's Dirty property, and a couple of static variables to repeatedly check to see if the form has had unsaved changes for an extended period of time.
The timer procedure begins by declaring several variables, including the following static variables:
sblnDirty
Saves a Boolean variable that notes if the form was dirty (i.e., has unsaved changes)
slngTimerStart
Saves the date/time the record was first dirtied
In addition, the code uses the NewRecord property to determine if the user is working with a new record and exits if this is the case. Since a user adding a new record can't lock the records of other users and likely will need additional time to complete a new record, we decided not to subject record additions to the timeout process. Here's the initial code of the event procedure:
Dim intElapsed As Integer
Dim strMsg As String
Dim ctlmsg As Control
Static slngTimerStart As Long
Static sblnDirty As Boolean
If Me.NewRecord Then
Exit Sub
End If
The remainder of the event procedure uses an If...Then statement to branch on the value of the form's Dirty property and compare it against sblnDirty (the value of the form's Dirty property the last time we checked). The process is summarized in Table 10-17.
Table 10-17. The state table for the Form_Timer event procedure
Current Dirty value
Value of sblnDirty
Action needed
True
True
Form remains dirty. Check if time limit has been exceeded and undo edits if so.
True
False
Form has just been dirtied, so set sblnDirty to True and slngTimerStart to the current number of milliseconds since Windows started, using the TimeGetTime API function.
False
True
User has saved changes, so set sblnDirty to False.
False
False
No action needed.
If the form is currently dirty (Me.Dirty = True) or was previously dirty (sblnDirty = True), and the elapsed time is less than conMaxLockSeconds, the following piece of code is executed:
intElapsed = (timeGetTime - slngTimerStart) \ 1000
If intElapsed < conMaxLockSeconds Then
' Update message control with remaining time
strMsg = "Edit time remaining: " _
& (conMaxLockSeconds - intElapsed) & " seconds."
ctlmsg = strMsg
If intElapsed > (0.9 * conMaxLockSeconds) Then
ctlmsg.ForeColor = vbRed
End If
Else
' ... See below ...
End If
The code updates the txtMessage control with the countdown message, changing the color of the text to red if the elapsed time is greater than 90% of conMaxLockSeconds to call extra attention to an impending timeout.
If the form is currently dirty (Me.Dirty = True) or was previously dirty (sblnDirty = True), and the elapsed time is greater than or equal to conMaxLockSeconds, the following piece of code is executed:
ctlmsg = ""
ctlmsg.ForeColor = vbBlack
Me.Undo
sblnDirty = False
MsgBox "You have exceeded the maximum record lock period (" & _
conMaxLockSeconds & " seconds). " & vbCrLf & vbCrLf & _
"Your changes have been discarded!", _
vbCritical + vbOKOnly, "Record Timeout"
The edits to the record are undone by using the Undo method of the form. Next, the code puts up a message box to inform the user that the edits have been discarded.
If the form is currently dirty (Me.Dirty = True) but wasn't previously dirty (sblnDirty = False), sblnDirty is set to True and the starting time is stored away in slngTimerStart, as the following code shows:
' Start timing the edits.
slngTimerStart = timeGetTime
sblnDirty = True
If the form is not currently dirty (Me.Dirty = True) but was previously dirty (sblnDirty = True), the code stops the timer by setting sblnDirty to False and clearing txtMessage:
' User has saved changes, so stop timer.
sblnDirty = False
ctlmsg = ""
Finally, if the form is not currently dirty (Me.Dirty = True) and wasn't previously dirty (sblnDirty = False), nothing needs to be done.
Although the code for this solution could have been placed in a global module, we chose not to, since its two static variables must be maintained between calls