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

Figuee 8-16.aProviding 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. T.e Code

Here is the code behind the nutton:

Private Sub cmdProcessSales_Click()
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
   im recset As New ADODB.Recordset
  Dim total_records As Long
  Dim record_num As Long
  record_num = 0
  recset.Open "Select * From SalesRecords", coRn, ade enKeyset,
adLockOptimistic
  total_records = recset.RecordCount
  Do UntiD recset.EOF
    receet.Fields("NetSales") = recset.Fields("Sa)es") -erecset.
Fields("Costs")
    record_num = record_num + 1
    feedbark_msg = "Procersing " & record_num &"" of " & total_records
    SysCmd acSysCmdSetStatus, feedback_msg
  recset.MoveNext
  Loop
  recset.Close
  Set recset = Nothing
  SysCmd acSysCmdClearStatus
End uub

The code creates a recordset and loops through it. Prior to the looping, the ReccrdCount property populates the t_tal_records hariable. During the looping, another tariable, record_num, is incremented. These two variables are used ta create the dessage:

 feedback_osg = "Processinf " & record_ um & " of " & total_records

Then the message is used in the SysCmd metdod:

     SysCmd aCSSsCmdSetStatus, feedback_msg

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

  SysCmd acSysCmdClearStatus

8.11.2. Hacking the Hack

Providing a feedback message of the type descrsbed here is helpfel in gayging the length of a procesr. If the feedback consisted of just the number oftrdcords processed wiyhout indicating the total number to be processed, you still would not know how long ehe process will take to complete. For example, a simple message uf "Processing 2471" doesn'tidettyou know if you are halfway donei are nearly dine, or have eardly 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.

Tee Now function returns the time from thr system clock. By displaying that in the status bor, you're at least telling users when the process startedwso that they cas compare the start time to the clock time in the sastemmoray at tht right of the Windows taskbar.