Previous Page
Next Page

Hack 57. Build a Time-Out Feature

Make sure your data is saved and available to others. Lock the records when they're not being updated.

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 when attempting to make a change to a record someone 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 user the options he needs, it is better to not even see this message, if it can be avoided. A productive measure for this situation is to close a form in which no activity is sensed after a period of time. In other words, if the first user has not completed any changes to the record within a specified time, the form should just close. Closing the form ends the record-editing process, and the changes are saved automatically. The alternative, to drop the changes, is discussed at the end of this hack.

6.4.1. It's About 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 Interval property and to indicate what occurs in the On Timer event.

To display the property sheet, open the form in Design mode, and press F4 on the keyboard. If necessary, make sure the property sheet is displaying properties about the form itself, not about one of the controls or sections. Select Form from the drop-down box at the top of the property sheet.

Figure 6-5 shows the property sheet set to display the properties for the form. The On Timer and Timer Interval properties are found on both the Event tab and the All tab.

You can display the property sheet in a few ways. You can press F4, or you can press Alt-Enter. You can also use the View Properties menu, or just click the Properties button on the Form Design toolbar.


The Interval property accepts values between 0 and 2,147,483,647 milliseconds. A setting of 1000 equals one second. The 10000 setting shown in Figure 6-5 is the equivalent of 10 seconds. By the way, the largest setting of 2,147,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.4.2. In Good Form

Figure 6-6 illustrates the form design, field list, and property sheet. Notice the text box control in the form header that isn't bound to a field. The property sheet is set to display the properties of the unbound box, txtTime, and its Visible property is set to No. In other words, when the form 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 used for entry. Instead, it holds a snapshot of the computer's clock time, at the moment the form is activated. To make this happen, you need to enter 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


Here is the code to enter in the Activate event:

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

The On Timer event contains the DateDiff function, set to test for the difference in seconds between the established form activation time and the current time. From the property sheet, select [Event Procedure] in the drop-down menu to the right of On Timer. Click the ellipses button and enter this code:

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

The first parameter of the DateDiff function indicates which interval to test; in this case, s is for seconds. The function tests if more than five seconds have lapsed between the time stored in the txtTime text box and now. Bear in mind that there are two values to coordinate here: the timer interval and how many 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.

The Now() function returns the system time. Every computer keeps an internal clock running. When timing events, it's necessary to start with a baseline time. The Now() function takes a snapshot of the time, which is then compared to a later time (effectively another snapshot, but later in time). Subtracting the first snapshot from the second snapshot equals the elapsed time. Incidentally, the computer clock is also used in programs that allow you to enter "today's date." Sometimes, the clock needs to be reset.


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 shows how the form's code module should look after these routines have been entered. It's OK if your event routines aren't in the same order.

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 mouse is moved.

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 MouseMove 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 the mouse is moved at least once every 10 seconds, the form will stay 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). Figure 6-9 shows how the form has been modified by adding a combo box. The combo box lets the user select from a list of possible values.

Figure 6-9. Letting the user decide how long to wait before closing the form


The code is updated as well. The combo box has been named cmbSeconds. Its Row Source Type is set to Value List, and the Row Source is set to the choices 10, 20, 30, 40, 50 , and 60 . When the user selects a value from the combo box, the combo box's Change event fires to update the invisible text box to the current time. Also, the form's Activate 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 have a default value to use until or unless the user selects an interval. Finally, the number of elapsed seconds that are tested for is now always one fewer than the interval selected in the combo box. Figure 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 but leave 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 made 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 is true , the record is saved, and a message is presented, as shown in Figure 6-11. If Dirty is false, and no edit is occurring, nothing happens. Either way the form stays open.

The new code for the Timer 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 Dirty property. If true , the record is saved, and the message is displayed:

Figure 6-11. The saved-edits message


	Private Sub Form_Timer( )
	If DateDiff("s", Me.txtTime, Now) > Me.cmbSeconds - 1 Then
      If Me.Dirty Then
		DoCmd.RunCommand acCmdSaveRecord
		MsgBox "Edits have been saved!"
      End If
	End If
	End Sub

If you started an edit and didn't complete 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, each approach in the hack has been based on saving the record that is in the middle of an edit. This is a judgment call because even if the person walked away from his work you don't know for sure whether to save his entry. To be on the safe side, the work is saved.

Of course, the argument exists to not save the edits. It's easy to drop the edits with an Undo action. Here is a snippet of modified code that goes into the Timer event:

	Private Sub Form_Timer( )
	If DateDiff("s", Me.txtTime, Now) > 10 Then
	  If Me.Dirty Then
	    DoCmd.RunCommand acCmdUndo
		DoCmd.Close
      End If
	End If
	End Sub

Essentially, if the record isn't in its pristine state (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 is to save the values out of the form and into a temporary table or even a text file, and to leave a message box alerting the user that his entry was dropped but that he can find his unsaved efforts at the place where you saved them.

    Previous Page
    Next Page