Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










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:

  1. 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.

  2. 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).

  3. 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


  1. 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.

  2. 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 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.


/ 232