Progress bar in form

[Autoeng]

I have read the numerous postings for how to add a progress bar to a form but have yet to find one that does what I need it to do except a French example that I cannot understand. I have a popup form that starts a macro that runs four individual queries and then closes when complete. I would like to add a progress bar to the form that steps along with the queries until complete.

Query one starts running - progress bar 0%
Query one completes, query two starts - progress bar 25%
Query two completes, query three starts - progress bar 50%
Query three completes, query four starts - progress bar 75%
Query four completes - progress bar 100%, form closes

Here is the code for the OnClick event. Using labels so I can control command button color.

Private Sub Label8_Click()
On Error GoTo Label8_Click_Err

Beep
MsgBox "Press ""OK"" to begin exporting the files for ECN reporting to your C:\My Documents Folder. Please be patient as this will take approximately 1 minute. This form will close automatically when export is completed.", vbInformation, "ECN Report Export"
DoCmd.SetWarnings False
DoCmd.OutputTo acQuery, "RecToImpqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtoiavg.xls", False, ""
DoCmd.OutputTo acQuery, "RelToImpqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtoidtl.xls", False, ""
DoCmd.OutputTo acQuery, "RecToPendqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtopavg.xls", False, ""
DoCmd.OutputTo acQuery, "RelToPendingqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtopdtl.xls", False, ""
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmDates"


Label8_Click_Exit:
Exit Sub

Label8_Click_Err:
MsgBox Error$
Resume Label8_Click_Exit

End Sub


If someone would be gracious enough to assist me I would be very grateful.

Autoeng

 

[boblarson]

Make sure that the Min and the Max of the progress bar control is set to 0 and 100 respectively.


Then you can add this code in between the code that runs your queries, changing the value for each place you put it.



pbrYourProgressBarName.Value=50

Me.Repaint

And that will do the trick of what you are wanting.

 

[Autoeng]

boblarson:

Thank you for your response but I need some further help if you don't mind. I'm a vb newbie and still learning. I added the following to my code:

DoCmd.SetWarnings False
pbrActiveXCtl11.Value=0
Me.Repaint
DoCmd.OutputTo acQuery, "RecToImpqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtoiavg.xls", False, ""
pbrActiveXCtl11.Value=25
Me.Repaint
DoCmd.OutputTo acQuery, "RelToImpqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtoidtl.xls", False, ""
pbrActiveXCtl11.Value=50
Me.Repaint

and so on....

As you can tell I used the "More Controls" toolbox to create a Microsoft Progress Bar Control (is this the correct progress bar to use) and how do I declare the control?

Autoeng

 

[ghudson]

Autoeng, you should be able to copy and paste this into your db for a working meter as your outputs progress...

Private Sub bOutPutQueries_Click()
    
    Beep
    MsgBox "Press ""OK"" to begin exporting the files for ECN reporting to your C:\My Documents Folder. Please be patient as this will take approximately 1 minute. This form will close automatically when export is completed.", vbInformation, "ECN Report Export"
    
    DoCmd.SetWarnings False
    
    Application.SetOption "Show Status Bar", True
    
    Dim OutputMeter
    Dim MeterFull As Integer
    Dim MeterCount As Integer
    MeterFull = 10
    
    OutputMeter = SysCmd(acSysCmdInitMeter, "Processing Query 1, please wait...", MeterFull)
    MeterCount = MeterCount + 2
    OutputMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)
    DoCmd.OutputTo acQuery, "RecToImpqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtoiavg.xls", False, ""
    
    OutputMeter = SysCmd(acSysCmdInitMeter, "Processing Query 2, please wait...", MeterFull)
    MeterCount = MeterCount + 2
    OutputMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)
    DoCmd.OutputTo acQuery, "RelToImpqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtoidtl.xls", False, ""
    
    OutputMeter = SysCmd(acSysCmdInitMeter, "Processing Query 3, please wait...", MeterFull)
    MeterCount = MeterCount + 2
    OutputMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)
    DoCmd.OutputTo acQuery, "RecToPendqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtopavg.xls", False, ""
    
    OutputMeter = SysCmd(acSysCmdInitMeter, "Processing Query 3, please wait...", MeterFull)
    MeterCount = MeterCount + 2
    OutputMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)
    DoCmd.OutputTo acQuery, "RelToPendingqry", "MicrosoftExcel(*.xls)", "c:\my documents\rtopdtl.xls", False, ""
    
    OutputMeter = SysCmd(acSysCmdRemoveMeter)
    
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmDates"
    
    Exit Sub
    
End Sub

'HTH