Access Progress Bar

Routine to provide Macro Progress Bar in Access.

When you combine a number of queries and processes in an Access procedure the only sign of any real progress that you get is the 'hourglass' and the progress of each individual query. If your user isn't aware of how many queries are in the procedure then this illusion of progress isn't much use. The normal line of blue blocks that we get is 20 blocks long so by breaking your process into something that you can then display using the 20 blocks you can create and control the amount of progress you display.

Dim varPB As Variant 'Progress Bar

Dim intPBmax As Integer 'Progress bar maximum

Dim intPBinc As Integer 'Progress bar increment

Dim strPBmsg As String 'Progress bar message

'   Set progress initial bar variables

strPBmsg = "Linking and Loading Data ..."

intPBmax = 3

intPBinc = 0

'   Display progress bar

varPB = SysCmd(acSysCmdInitMeter, strPBmsg, intPBmax)

Put the folowing at points in your procedure or at the end of a loop if that's what you are doing

'   Update progress bar

intPBinc = intPBinc + 1

varPB = SysCmd(acSysCmdUpdateMeter, intPBinc)

Use this code to tidy up when the procedure has completed

'   Remove progress bar

varPB = SysCmd(acSysCmdRemoveMeter)

If you have something that loops say 50 times then you set intPMax to 50 and create a piece of arithmetic that equates your progress intPBinc to units of 20 relative to intPMax, but you realised that already didn't you.