Display Progess in From??

[davidhall80]

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.

 

[tinkythomas]

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,

 

[davidhall80]

It is the Latter...What ya got...Thank you very much

 

[tinkythomas]

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

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

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]

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.

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]

You could also just have one label and change it's caption.

 

[tinkythomas]

You could also just have one label and change it's caption.

Thanks for the suggestion, I never thought of that I will give it a go, certainly reduces the effort!

 

[davidhall80]

That you all very much for your assistance. Much Appreciated....