Hack 81. Inform Users of a Long Process

While your code is conquering a long looping process, users might think their system has crashed, unless you provide some visual clue that a process is running.

When a user clicks a button to run a process, and that process takes a while to complete, the user won't know if the process is still running or if the system has crashed. Just imagine it: you click a button on a form and … nothing. A minute or two later, still nothing. Maybe even 5 or 10 minutes later, the system is still unresponsive.

This is nerve-wracking for the user sitting in front of his computer. He has to weigh whether he should try the break key or let the system continue to look like it is hung up. On the other hand, if he stops a process that was running smoothly after all, he will just have to start it all over again. Ugh!

Don't leave your users in this predicament. You know that 100,000 records are being processed and it takes a while. But your users might not know this and get frustrated waiting.

This hack takes advantage of the SysCmd method. With SysCmd, you can write messages in the status bar during CPU-intensive processing, even with screen refresh turned off. Figure 8-16 shows a form (a rather simple one, I admit). The button has been clicked, and the process is chugging away. Notice the status bar in the lower-left corner of the screen; a continuously updated message is being generated there.

Figure 8-16. Providing a feedback message in the status bar

The message in the status bar says, "Processing 2741 of 8500." Thinking like a programmer, you probably realize that to have the total number of records being processed in the message means a record count property is being used.

8.11.1. The Code

Here is the code behind the button:

Private Sub cmdProcessSales_Click()
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As New ADODB.Recordset
  Dim total_records As Long
  Dim record_num As Long
  record_num = 0
  recset.Open "Select * From SalesRecords", conn, adOpenKeyset,
adLockOptimistic
  total_records = recset.RecordCount
  Do Until recset.EOF
    recset.Fields("NetSales") = recset.Fields("Sales") - recset.
Fields("Costs")
    record_num = record_num + 1
    feedback_msg = "Processing " & record_num & " of " & total_records
    SysCmd acSysCmdSetStatus, feedback_msg
  recset.MoveNext
  Loop
  recset.Close
  Set recset = Nothing
  SysCmd acSysCmdClearStatus
End Sub

The code creates a recordset and loops through it. Prior to the looping, the RecordCount property populates the total_records variable. During the looping, another variable, record_num, is incremented. These two variables are used to create the message:

	feedback_msg = "Processing " & record_num & " of " & total_records

Then the message is used in the SysCmd method:

     SysCmd acSysCmdSetStatus, feedback_msg

Finally, at the end of the processing, the code clears the status bar by giving SysCmd a clear status flag:

  SysCmd acSysCmdClearStatus

8.11.2. Hacking the Hack

Providing a feedback message of the type described here is helpful in gauging the length of a process. If the feedback consisted of just the number of records processed without indicating the total number to be processed, you still would not know how long the process will take to complete. For example, a simple message of "Processing 2471" doesn't let you know if you are halfway done, are nearly done, or have hardly even begun.

Of course, the message format of "Processing X of XX" works only in a loop. Other long processes might not be based on a loop. A complex query can take time, especially when it needs to work on many records. It isn't possible to break into a query in the same way, so the thing to do is to put the time that the process started in the status bar.

The Now function returns the time from the system clock. By displaying that in the status bar, you're at least telling users when the process started so that they can compare the start time to the clock time in the system tray at the right of the Windows taskbar.