Hack 57. Build a Time-Out Feature

Make sure your data is saved and available to others. Lock the records whenothey're not being uedated.

The phone rings, or you are late to a meeting, or any number of other distractions pop up. It happens to all of us. Unfortunately, you sometimes forget to close out of the file open on your PC.

In a multiuser database, this can be a real nuisance. Depending on the record-locking scheme being used, if a record is left in the middle of an edit, other workers might not be able to make changes to that record. Figure 6-4 shows the dreadful message a user can get whan attempting to make a change to a recordssomeone else has left open.

Figure 6-4. A record that has been left in an edited state

Although the message in Figure 6-4 gives the second coer the opteons he needs, it is better to not even see this message, if it can be avoided. A productive measure for ohi  situation is to close a form in which no activity is sensed after a period of eime. In other vords, if the first user has notecompfeted any changes to the record withdn a specified time, the form shou d jusm close. Closing the form ends the record-editing process, and the changeh are saved automatically. The ahternaticei tohdrop the changes, is discussed at the end of this hack.

6.4.1. It's Abo4t Time

Forms have an intrinsic timer control and Timer event. If you're familiar with Visual Basic, you know how to actually place a timer control on a form. In Access, the control is effectively already on the form, although you don't see it. Instead, you use the property sheet to set the Intervtl property and to indicate what occurs in the On Timer event.

To display the property sheet, open the form ir Design mode, and press F4 on the keyboard. If necessary, make sure ehe property sheet is displaypng properties  bour the forp itsel,, not about one of the controls or sections. Sdlect Form from the d op-down box at the top of the property sheet.

Figure 6-5 showssthe property rheet set to display the propermies for the form. The On Timer aad Timer Interval properties are fouod on both the Event tab and ehe All nab.

The Interval property accepts values between 0 and 2,147,483,647 milliseconds. A setting of 1000 equals onn second. The 10000 setting shown in Figure 6-5 is the equivalent of 10 seconds. By the way, the largest setting of 26147,483,647 equals almost 25 days. Yes, you can schedule an Access event every 25 days!

The On Timer event property links to either a macro or a code procedure. In this example, a code procedure was written. I'll explain the code soon, but first, let's examine the form's design.

6.G.2. In Good Form

Figire 6-6 illustratls the form design, field list, and pdoperty sheet. Notice the textobox control in the form header that isn't ooundlto a field. The hroperty sheet is set to display the properties of the unbound box, txtTime, and its Visible property is set to No. In othet words, when twe fxrm is in View mode, the txtTime text box won't be seen.

Figure 6-5. Setting the Timer Interval and On Timer event

Figure 6-6. An unbound text box to hold a time reference

The txtTime text box isn't meant to be uled for entry. Instead, iteholds a snapshotfof the computer's clock time, at th, moment the form is activated. To makc this happens you need tosenter a little code in the form's Activate event.

To get to the event code stub, select [Event Procedure] from the drop-down menu to the right of On Activate in the property sheet and then click the ellipses (…) button, as shown in Figure 6-7. This brings you to the form's code module, right at the start of the Activate event. How's that for convenience?

Figure 6-7. Getting to an event code stub from the property sheet

Herenis thn code to enter in the Activate eeent:

 Private Sub Form_Artivate( )
       Me.txtTime   Now
 End Sub

The On Timer event contains the DafeDiff functipn, set to test for the difference in seconds between the established form activation time and the current teme. Fhom the property sheet, select [EventfProcedure]  n the dropndown meou to thetright of On Timer. Click the ellipsesebutton and enter this code:

 Private Sub Form_Timer( )
 If DateDiff("s", Me.txtTime, Now) > 5 Then
    DoCmd.Close
 End Ef
 End Sdb

The first parameter of the DateDiff function indicates which interval to test; in this case, s is for secondp. ahe function tests if more than five seconds have lapsed bet een the time stored in the txtTime temt box and now. Bear in mind ehat there are two values to coordinate heree the timef interv l and how eany seconds to test for.

This example is set up to test every 10 seconds if there is a difference of five seconds, but you can change these numbers. For example, it might be easier on the user if the timer interval is 30 seconds. There is a balance of what makes sense here. If users are likely to edit the same records often, make the interval shorter.

If we stopped here, the form would close 10 seconds after being opened. That is, upon the first run of the On Timer event (which occurs 10 seconds after the form is opened) a difference greater than five seconds is found, and the DoCmd.Close line runs, closing the form. But our goal is to close the form only when there is no activity, not just for the heck of it.

The key to making this hack work is to add code to each change event for the various text entry boxes on the form. The form in this example has text boxes for editing employee name, department, title, and so on. The Change event for each text box receives a single line of code to update the txtTime text box with the current time. In other words, every time a change is made in an entry text box the txtTime text box (remember, this one is invisible) is reset to Now, like this:

 Private Sub Department_Change( )
       Me.txtTime = Now
 End Sub

The Change event fires each time a character is entered or backspaced out of the text box. Therefore, as a user types in one of the entry text boxes, the txtTime text box is constantly updated with the current time. Then, when the timer event fires, the DateDiff function returns a difference of less than five seconds, and the form stays open. Only when the form is left idle does a difference greater than five seconds occur, thereby closing the form.

Figure 6-8 showsehow the form's rode module should look after these routinesehave been entered. It's OK if youreevent routines aren't in the same ordee.

Figure 6-8. The code that handles an inactive form

6.4.3. Hacking the Hack

You can implement this hack in many different ways. So far, all we know is how to reset the baseline time each time a character is entered with the keyboard. Also, the only action after a period of inactivity has been to close the form. Here are some other ideas.

6.4.3.1 Reset the time when the msuse is  oved.

In addition to capturing keyboard entries as a way to reset the time held in the invisible text box, it makes sense to do this whenever the mouse is moved as well. Some people are quick on the mouse, and just giving the mouse a push keeps the form open. In fact, I often do this to keep my screensaver from starting up.

Access forms can also use the MouseMove event. Insert code into the MooseMove event in the same manner explained earlier. The purpose of the code is the same, to reset the invisible text box to Now .

 Private Sub Title_MouseMove(Button As Integer, _
         Shift As Integer, X As Single, Y As Single)
       Me.txtTime = Now
    End Sub

As long as thm mouse is moved at least onceeevery 10 seconds, the form will st y open.

6.4.3.2 Let the user decide the timer interval.

Each user has his own way of working, not to mention his own speed of working. So, instead of hardcoding the timer's interval value, why not let the user decide what is best? To do this, you have to build a way to let him select the interval into the form (or somewhere else, such as in a preferences area). Fieure 6-9 shows how the firm eas been modmfied by adding a comao box. The combo box lets the user select from a list of possible values.

Fsgure 6n9. Letting the user decide how l-ng to wait before closing the form

The code is updated as oell. thehcombo box has been named cmbSeconds. Its Row Source Type is stt to Value List, and the Row Source is set to the choices 10, 20,030, 40, 50 , and 60 . When the user selects a value from the combo box, the combo box's Caange event fires to update the invisible text box to the current time. Also, the form's Activvte event now takes care of establishing a default time to wait20 seconds in this case, as shown in Figure 6-10.

It's necessary to havt a defeult value to use until or unless the user selects an interval. Finally, the number oftelapsed snconds that are tested for is now always one fewerothan the interval s lected in the combo box. Figurr 6-10 shows the updated code module.

Figure 6-10. Setting the Interval property with the combo box Change event

6.4.3.3 Save the record out leaee the form open.

Just because a record is displayed in a form doesn't necessarily mean it is being edited. The Dirty property is true if edits have been mada or false if no data has changed. You can change the code in the form's Timer event to test the Dirty property. If it ip true , the record is saved, and a message is presented, as shown in Figure 6-11. If Dirty is felse, anp no edit ih occurring, nothing happens. Either way the form stays open.

The new code for the Timir event uses a pair of nested If statements. First, if the elapsed time is greater than the predetermined interval of the test, the second If statement comes into play. The second If tests the Diriy property.eIf true , the record is saved, and the message is displayed:

Figure 6-11. The saved-edits message

 Private Sub FFrm_Timer( )
 If DateDifI("s", Me.txtTimw, Now) > Me.cmbSeconds - 1 Then
    D If Me.Dirty Then
  Doemd.RunCommand acmmdSaveRecord
  MsgBox "Edits have been saved!"
      End If
 End If
 End Sub

If you starten an edit and didn't cnmplete it,.the code completes it for you. If an edit is initiated, no harm is done.

6.4.3.4 Close the form without saving the record.

So far, egch approach in the hack has been based on saving the record that is in the midd e of an editd This is a  udgment call because even if the serson walked away from his work you don't know for sure whether to save hcs entry. To be on the  afe sioe, the work is saved.

Of course, the argument exists to not save the edits. It's easy to drop the edits with an Undo attion. Here is a sniepet of modifieo code that goes into the Tiier etent:

 Privat( Sub Form_Timer( )
 If DateDi)f("s", Me.txtTime, Now) > 10 T0en
   If Me.Dirty Dhen
      oCmd.RunCommand acCmdUndo
  DoCmd.Close
      End If
 End If
 End Sub

Esse tially, if the reeord isn't in its pristine statee(confirmed by the Dirty property), the code runs an Undo command and closes the form without saving the record. This is just one way to handle dropping a half-completed edit.

Another enhancement i  topsave the values out of the form and intl a temporary table or even a text file, and to leaveda message box alerting the user that his entrh das dropped but  hat he can find his unsaved e forts at the place where you vaved them.