Recipe 10.10 Set a Maximum Locking Interval for a Record

10.10.1 Problem

You've employed pessimistic locking on your application's forms to prevent two users from making changes to the same record at the same time. Sometimes, a user will lock a record for an excessive period of time; for example, he might start to edit a record and then get a long phone call or leave for lunch without saving or canceling his edits. Is there any way to limit how long a user can lock a record and time out the user when the locking time limit has been exceeded?

10.10.2 Solution

There's no built-in database or form option for "maximum record lock interval," but you can create your own record lock timeout feature by making use of the form's Timer event. This solution shows you how to create such a facility using an event procedure attached to the form's Timer event.

To add a record lock timeout feature to your own application, follow these steps for each form for which you wish to enable this feature:

Figure 10-26. The event procedure attached to the Timer event will execute once every second

Now load the 10-10.MDB database. Open the frmEmployees sample form to test out the record lock timeout feature. Make a change to an existing record and leave the record in an unsaved state. After a brief delay, a message appears in the form's footer informing you how many seconds of edit time remain (see Figure 10-27). The number counts down second by second; the message color changes to red when only a few seconds remain.

Figure 10-27. The changes to this record will be timed out unless they are saved

Finally, if you haven't either saved or undone your changes during the specified time interval, your edits will be undone and a confirming dialog will inform you of the event (see Figure 10-28).

Figure 10-28. The changes to the record have timed out

10.10.3 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 to the event procedure. Because this code could be used in multiple forms within the application, we chose to encapsulate it within each form's event procedure. You may wish to split the code into two parts: one part that maintains the static variables in the form's Timer event procedure, and a second common component that lives in a global module. To accomplish this, you'd have to pass three variables (by reference) to the common function: a form variable referencing the form, and the two static variables, sblnDirty and slngTimerStart.