Access Progress Form

[TheSmileyCoder]

Usually as developers we do our best to ensure that function calls occur almost instantaneous with no discernible wait time for the user.

If a process takes more than a quarter of a second, or maybe half a second we can turn the mouse pointer into the hourglass (or rotating circle on later versions of windows) with this simple code

Docmd.HourGlass=True

and when we are done, turn it off with

Docmd.HourGlass=False

However, if we have a longer running process, especially one that takes more than 10 seconds we risk the user getting impatient, killing the application, with all the risks of a mid-process termination. To buy a bit of patience we can try a popup form that just says "Please wait", but we can actually do better than that. We can use a progress form that not tells the user what is going on, but also shows to the user that there IS progress, that the application has not died.

With that in mind, I set out to create something more advanced than a simple "Please wait..." form, and this is the result.


The downloadable sample consists of 5 objects. A progress form (TSC_ProgressFormSimple) that handles the graphics, a class module (TSC_PF_Simple) that handles all the logic, 2 utilities modules and a form (frm_Test) to try and illustrate intended usage.
You will need to import all but the frm_Test into your application.

To start up the progress form you simply dimension a new class object:

'Init progress form
  Dim oPF As TSC_PF_Simple
  Set oPF = New TSC_PF_Simple

We now want to set the title, and the initial task text, and then show the form

'Show the form
  oPF.Title = "Importing Objects" & vbNewLine & "Please wait..."
  oPF.UpdateTask 0, "Preparing Import"
  oPF.Show

Each time we have progress to report we simply call the UpdateTask method, supplying the new percentage as a number between 0 and 1 (1 being 100%) and optionally the text to display.

  oPF.UpdateTask 0.5 , "Importing"

When you are all done and wish to close the form, you can just set the object variable to nothing, and the class will take care of the rest. In this case I included it in the clean up procedure:

Exit_Sub:
  'Cleanup
  Set oPF = Nothing

The class has some built in timers to prevent flickering, and to give the user the chance to see the process finish before the form is closed.

Another option is to allow the user to cancel a process (well technically in-between processes when the form is updated). If you set the AllowCancel to true, a cancel button becomes visible and the user can now attempt to cancel. Note that click events only take place when the progress form is updated.

This progress form uses the code by Stephen Leban to autosize the text frames if needed. This means you can use long text strings without missing out.

I hope you enjoy this free sample. Remember that feedback and comments is the fuel that keeps the fire lit.

The problem remaining is that a progress form such as this is only usefull when you have a alot of opereations to run sequentially. It is not that usefull if all you have is a single big query that ages to run, since the progress form can only update graphics, in between queries or other function calls.

[Lysander]

I like this! I have several long processes in my databases, some can take 30 mins to run and use something similar, but nowhere near as sophistaced. Two questions. What happens if your process should happen to fail, i.e. does the form disappear. (In my simple version it does not, and the user is locked out of access:( Secondly, can you say what versions of Access this runs on.

[TheSmileyCoder]

Hi Lysander
Note that this only works if the process can be divided into chunks, since you need to modify your code to include the commands to update the progress monitor. So if you have a single query that takes 30 mins to run then there isn't really much to do, unless you can divide the query into smaller parts.

@Question 1: If the progress class object is declared

Dim oPF As TSC_PF_Simple

within the scope of a single procedure, then as the procedure is exited (by intent, or by error) the object will fall out of scope, and during termination it should close the form automatically.

@Question 2: The download is stored in 2002/2003 format. It was created with Access 2010, which in some cases means it can't be opened by earlier versions, but I have not used anything fancy so I believe it should work in 2002 as well. Reports of either success or failure using 2002/2003 are appreciated.

[Seth Schrock]

I just implemented this on a new database that I'm working on. It works great and I think that my boss will really enjoy having it so that there is something to look at besides a spinning wheel.

Thanks for sharing Smiley.