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?
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:
Open the form in design mode, and add to the form an unbound text box named txtMessage that will be used to display the countdown message. This control should be at least 3.45” wide and 0.1667” high. On the sample form, we have placed txtMessage in the form’s footer, but you can place it anywhere you’d like.
Change the form’s TimerInterval property to 1000. This will cause any code attached to the form’s Timer event to be executed every 1,000 ms (or 1 second).
Create an event procedure attached to the form’s Timer event. Figure 10-26 shows how the properties sheet for the form should look after completing these steps.
Add the following code to the form’s event procedure:
' Record lock timeout time in seconds Private Const conMaxLockSeconds = 60 Private Sub Form_Timer( ) Dim intElapsed As Integer Dim strMsg As String Dim ctlmsg As Control Static sfDirty As Boolean Static sdatTimerStart As Date Set ctlmsg = Me!txtMessage If Me.NewRecord Then Exit Sub End If Select Case Me.Dirty ' Record has been modified since last save. Case True If sfDirty Then ' Elapsed time may be over one minute, so grab both ' the minutes and seconds portions of the elapsed time. intElapsed = Minute(Now( ) - sdatTimerStart) * 60 _ + Second(Now( ) - sdatTimerStart) 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 ' Time out user and undo changes. ctlmsg = "" ctlmsg.ForeColor = vbBlack ' Undo changes. One of these DoCmd statements ' might generate an error, so ignore errors. On Error Resume Next DoCmd.DoMenuItem acFormBar, acEditMenu, _ acUndo, 0, acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, _ acUndo, 0, acMenuVer70 On Error GoTo 0 sfDirty = False MsgBox "You have exceeded the maximum record " _ & "lock period (" & conMaxLockSeconds _ & " seconds). " & vbCrLf & vbCrLf _ & "Your changes have been discarded!", _ vbCritical + vbOKOnly, "Record Timeout" End If Else ' Start timing the edits. sdatTimerStart = Now( ) sfDirty = True End If ' Record has not been modified since last save. Case False If sfDirty Then ' User has saved changes, so stop timer. sfDirty = False ctlmsg = "" End If End Select End Sub
Alternately, you can import the frmEmployees sample form from
10-10.MDB
, open frmEmployees in design mode,
pull up the Timer event procedure code, and copy all the lines
between Private
Sub
Form_Timer( )
and End
Sub
to the clipboard. Close the sample form, open
your own form’s Timer event procedure, and paste the code from
the sample form into your event procedure. Now delete frmEmployees
from your database.
Save your form, and open and test it.
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.
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).
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:
sfDirty
Saves a Boolean variable that notes if the form was dirty (i.e., has unsaved changes)
sdatTimerStart
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 sfDirty As Boolean Static sdatTimerStart As Date Set ctlmsg = Me!txtMessage If Me.NewRecord Then Exit Sub End If
The remainder of the event procedure
uses a Select
Case...End
Select
statement to branch on the value of the
form’s Dirty property and compare it against
sfDirty
(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 sfDirty |
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 sfDirty to True and sdatTimerStart to Now( ). |
False |
True |
User has saved changes, so set sfDirty to False. |
False |
False |
No action needed. |
If the form is currently dirty (Me.Dirty
=
True
) or was previously dirty
(sfDirty
=
True
), and the elapsed time is less than
conMaxLockSeconds
, the following piece of code is
executed:
' Elapsed time may be over one minute, so grab both ' the minutes and seconds portions of the elapsed time. intElapsed = Minute(Now( ) - sdatTimerStart) * 60 _ + Second(Now( ) - sdatTimerStart) 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
(sfDirty
=
True
), and the elapsed time is greater than or
equal to conMaxLockSeconds
, the following piece of
code is executed:
' Time out user and undo changes. ctlmsg = "" ctlmsg.ForeColor = vbBlack ' Undo changes. One of these DoCmd statements ' might generate an error, so ignore errors. On Error Resume Next DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, 0, acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, 0, acMenuVer70 On Error GoTo 0 sfDirty = False MsgBox "You have exceeded the maximum record lock period (" _ & acbMaxLockSeconds & " seconds). " & vbCrLf & vbCrLf _ & "Your changes have been discarded!", _ vbCritical + vbOKOnly, "Record Timeout"
The edits to the record are undone by using DoCmd.DoMenuItem to simulate the user selecting Edit → Undo using the menus. Access has a two-level undo buffer (one for the current control and one for the current record), so you must issue the undo command twice, ignoring any errors that occur if only one undo was necessary. Next, the code puts up a message box to inform the user that he or she has lost the edits.
If the form is
currently dirty (Me.Dirty
=
True
) but wasn’t previously dirty
(sfDirty
=
False
), sfDirty
is set
to True
and the starting time is stored away in
sdatTimerStart
, as the following code
shows:
' Start timing the edits. sdatTimerStart = Now( ) sfDirty = True
If the form is not currently dirty (Me.Dirty
=
True
) but was previously
dirty (sfDirty
=
True
), the code stops the timer by setting
sfDirty
to False
and
clearing txtMessage:
' User has saved changes, so stop timer. sfDirty = False ctlmsg = ""
Finally, if the form is not currently dirty
(Me.Dirty
=
True
) and wasn’t previously dirty
(sfDirty
=
False
), nothing needs to be done.
While 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. Since 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, sfDirty
and
sdatTimerStart
.