Access Cookbook - Ken Getz [222]
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:
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.
Figure 10-26. The event procedure attached to the Timer event will execute once every second
Add the following code to the form's event procedure:
Private Declare Function timeGetTime Lib "winmm.dll" ( ) As Long
' Record lock timeout time in seconds
Private Const conMaxLockSeconds As Integer = 60
Sub cmdClose_Click( )
DoCmd.Close
End Sub
Private Sub Form_Timer( )
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
Set ctlmsg = Me.txtMessage
If Me.Dirty Then
' Record has been modified since last save
If sblnDirty Then
' Elapsed time may be over one minute, so
' grab both the minutes and seconds portion
' of the elapsed time
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
' Timeout user and undo changes
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"
End If
Else
' Start timing the edits
slngTimerStart = timeGetTime
sblnDirty = True
End If
' Record has not been modified since last save
Else
If sblnDirty Then
' User has saved changes, so stop timer
sblnDirty = False
ctlmsg = ""
End If
End If
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.
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