Display Progess in From??

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
I have a macro that runs a series of queries. I'd like to know the progress of the macro. Is it possible for something to pop up on my screen to let me know which query is currently running in the background?? Thanks in advance.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
Are you just after a progress bar style dialog or do you just want to know which query is executing by means of text?

If it 's the latter I can show you a method I use by using a form with hidden labels.

Regards,
 

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
Sorry for the delay. Right the first thing to do is create a form with however many labels you need. Name the labels after your queries e.g "Running query QueryName".

Place the labels on top of each other so that you end up with what looks like 1 label. You can make the form as pretty as you like but I just keep things simple.

Now place the following code in the forms vba module......
Code:
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

    Dim ctl As Control
    'loop through controls on form and hide
    For Each ctl In Me.Controls
        With ctl
            Select Case .ControlType
            Case acLabel
                .Visible = False    'hide label
            End Select
        End With
    Next

End Sub
This will hide the labels when the form is opened. Next you need to add code to unhide/hide each label depending which query is executing, place the code in a standard module......
Code:
Option Compare Database
Option Explicit

'------------------------------------------------------------
' Run queries
' Chris Coombes 03/03/2009
'------------------------------------------------------------
Function RunQueries()

    Dim strFrm As String

    strFrm = "Form1"    'form name

    On Error GoTo Progress_Err

    DoCmd.SetWarnings False

    DoCmd.OpenForm strFrm    'open form to show progress
    Forms!Form1.Label0.Visible = True    'show the label
    Forms!Form1.Repaint    'refresh form
    DoCmd.OpenQuery "BA1"    'run query
    Forms!Form1.Label0.Visible = False    'hide the label ready to show next label
    Forms!Form1.Label1.Visible = True    'show the label
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BB1"
    Forms!Form1.Label1.Visible = False
    Forms!Form1.Label2.Visible = True
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BD1"
    Forms!Form1.Label2.Visible = False
    Forms!Form1.Label3.Visible = True
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BF1"
    Forms!Form1.Label3.Visible = False
    Forms!Form1.Label4.Visible = True
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BG1"
    Forms!Form1.Label4.Visible = False
    Forms!Form1.Label5.Visible = True
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BH1"
    Forms!Form1.Label5.Visible = False
    Forms!Form1.Label6.Visible = True
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BI1"
    Forms!Form1.Label6.Visible = False
    Forms!Form1.Label7.Visible = True
    Forms!Form1.Repaint
    DoCmd.OpenQuery "BJ1"

    DoCmd.Close acForm, strFrm    'close the form

    DoCmd.SetWarnings True

    Beep
    MsgBox "Complete", vbOKOnly, "Testing"    'message to show complete


Progress_Exit:
    Exit Function

Progress_Err:
    MsgBox Error$
    Resume Progress_Exit

End Function
The code comments I think are self explanatory. The function can either be called from a macro or a button on a form.

Hope that helps.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355

ADVERTISEMENT

Hi David & Chris, nothing wrong with Chris's solution but as with lots of things in Access theres often multiple ways of doing things. Something you may not be aware of is that Access has a built-in progress meter that can appear in the status bar.

The following code has 3 examples of how to use the status bar to show progress for the user. In a blank form add three command buttons - I just left the default names Command0, Command1 and Command2. The Command0 code below shows the progress meter with static text that doesn't change. The Command1 code below doesnt show the progress meter but simply updates the status bar with new text as the code progresses through the loop. The final code for Command2 combines both by showing text that updates after each loop plus the progress meter increasing.

Code:
Private Sub Command0_Click()
Dim i As Long, y As Long, n As Long, varReturn As Variant

'Initialise the Status Bar Meter. The last argument (10) represents the maximum value expected to reach 100%
varReturn = SysCmd(acSysCmdInitMeter, "Updating Database...", 10)


'Dummy code to count a large number ten times.
For i = 1 To 10
    
    For y = 1 To 10000000
        n = i + y
    Next y
    
    'Update the progress meter bar
    varReturn = SysCmd(acSysCmdUpdateMeter, i)
Next i

'Close the Status Bar Meter
varReturn = SysCmd(acSysCmdRemoveMeter)
End Sub

Private Sub Command1_Click()
Dim i As Long, y As Long, n As Long, varReturn As Variant


'Dummy code to count a large number ten times.
For i = 1 To 10
    
    For y = 1 To 10000000
        n = i + y
    Next y
    
    varReturn = SysCmd(acSysCmdSetStatus, "Updating Outer Loop # " & i & " of 10")
    

Next i

'Clear Status
varReturn = SysCmd(acSysCmdClearStatus)
End Sub

Private Sub Command2_Click()
Dim i As Long, y As Long, n As Long, varReturn As Variant
'Because you cannot amend the text of the status bar progress meter you instead initilise and
'then remove it for each action so it appears as though only one status bar is used.

'Initialise the Status Bar Meter. The last argument (10) represents the maximum value expected to reach 100%
varReturn = SysCmd(acSysCmdInitMeter, "Updating Outer Loop #1 of 10", 10)


'Dummy code to count a large number ten times.
For i = 1 To 10

    If i > 1 Then
        'Close the Status Bar Meter
        varReturn = SysCmd(acSysCmdRemoveMeter)
        'Initialise Meter again with new text
        varReturn = SysCmd(acSysCmdInitMeter, "Updating Outer Loop # " & i & " of 10", 10)
        'Set the progress to what has been done so far
        varReturn = SysCmd(acSysCmdUpdateMeter, i)
    End If

    
    For y = 1 To 10000000
        n = i + y
    Next y
    
Next i

'Close the Status Bar Meter
varReturn = SysCmd(acSysCmdRemoveMeter)

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
You could also just have one label and change it's caption.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top