How can I show a progress bar/meter 1

[puforee]

I have a data base that uses external data to produce useful information. I have one Form in the DB and it shows when the data base is opened. The form (Main_frm) has two buttons: Start Computations and Quit DB.

When the Start Computations button is clicked I use the On Click event to start running 15 queries using VBA. This process can take a bit of time to run. I would like to use a progress bar to show the progress through all queries, not just one at a time. The status bar only shows the progress of each query. So, on my Main_frm I want a progress bar control that will start when the Start Computations button is depressed and stop when the computations are complete.

Can I get some help on this please. I have seen WEB pages that talk about showing something when starting Macros but I need it to run from the Click Event from the button..all VBA.

Thank you in advance.

 

[Andrzejek]

running 15 queries" and "The status bar only shows the progress of each [one?] query"
In my opinion you are almost there.

Why not 2 Status Bars at the same time?
Upper one shows over-all progress - divide it into 15 pieces and progress to another 'piece' when each query is completed.
Lower one you already have

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy - thanks for the hint. Now, how do I create my own status bars to do what your are suggesting?

Thanks,

 

[Andrzejek]

Ooops.
What I wanted to say (what I meant) was "2 Progress Bars" (Sorry about it)
You can have just one Status Bar on your Form (I think), and in it you can have a Progress Bar. (or 2 Progress Bars side-by-side)

But you can have many just Progress Bars on your Form and not use any Status Bar

Or, if you share your code how you increment your Progress Bar right now, it may be modified to show over-all progress for your 15 queries.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy,

I do not have any progress bar code. Sorry for the confusion. I would like to create a progress bar based on my VB code. My "Start...." button On Click event code is shown below. I would like the progress bar to start at the beginning of the code and end as the MsgBox "Updates Complete" code is active. Is this doable? I always wondered hope progress bars compute the progress increments. Maybe I just need a barber poll that starts when I want it and ends (disappears) when my message bar shows up. I don't care if it shows % of completion. I just want something to indicate the process is running.

Option Compare Database

Private Sub Command2_Click()
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "1_ToR_Create_Add_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "2_ToR_Create_Remove_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "3_ToR_Output_Add", acViewNormal, acEdit
    DoCmd.OpenQuery "3_ToR_Output_Removal", acViewNormal, acEdit
    DoCmd.OpenQuery "4_013Breakout qry", acViewNormal, acEdit
    DoCmd.OpenQuery "5_013_Delete_Names", acViewNormal, acEdit
    DoCmd.OpenQuery "6_013_Delete_Model", acViewNormal, acEdit
    DoCmd.OpenQuery "7_013_AF_Delete_AF", acViewNormal, acEdit
    DoCmd.OpenQuery "8_013_Delete_AV", acViewNormal, acEdit
    DoCmd.OpenQuery "9_013_Delete_PP", acViewNormal, acEdit
    DoCmd.OpenQuery "10_013_Names_Append_tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "11_013_Models_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "12_013AV_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "13_013_AF_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "14_013_PP_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "15_013_FinalCreate_Tbl", acViewNormal, acEdit
    DoCmd.SetWarnings (WarningsOn)
    MsgBox "Updates Complete"
    DoCmd.OpenReport "Final Report", acViewPreview
    DoCmd.Maximize
    MsgBox "This is the report for GL-MT-013. From the Print Preview Ribbon select Data-More. Click on the Word icon. Browse to the desired location and click OK."
End Sub

 

[Andrzejek]

One way I do this is to place a Label on the Form, let's say [tt]lblInfo[/tt], and I do this:
(Also, I would rename your command button to something more meaningful, like cmdStart)

Option Compare Database

Private Sub [red]cmdStart[/red]_Click()
    [blue]cmdStart.Enabled = False    [/blue]
    DoCmd.SetWarnings (WarningsOff)
    [blue]lblInfo.Caption = "Step 1 of 15"
    lblInfo.Refresh[/blue]
    DoCmd.OpenQuery "1_ToR_Create_Add_Tbl", acViewNormal, acEdit
    [blue]lblInfo.Caption = "Step 2 of 15"[/blue]
    DoCmd.OpenQuery "2_ToR_Create_Remove_Tbl", acViewNormal, acEdit
    [blue]lblInfo.Caption = "Step 3 of 15"[/blue]
    DoCmd.OpenQuery "3_ToR_Output_Add", acViewNormal, acEdit
    [blue]lblInfo.Caption = "Step 4 of 15"[/blue]
    DoCmd.OpenQuery "3_ToR_Output_Removal", acViewNormal, acEdit
    [blue]lblInfo.Caption = "Step 5 of 15"[/blue]
    DoCmd.OpenQuery "4_013Breakout qry", acViewNormal, acEdit
    [blue]...[/blue]
    DoCmd.OpenQuery "5_013_Delete_Names", acViewNormal, acEdit
    DoCmd.OpenQuery "6_013_Delete_Model", acViewNormal, acEdit
    DoCmd.OpenQuery "7_013_AF_Delete_AF", acViewNormal, acEdit
    DoCmd.OpenQuery "8_013_Delete_AV", acViewNormal, acEdit
    DoCmd.OpenQuery "9_013_Delete_PP", acViewNormal, acEdit
    DoCmd.OpenQuery "10_013_Names_Append_tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "11_013_Models_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "12_013AV_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "13_013_AF_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "14_013_PP_Append_Tbl", acViewNormal, acEdit
    DoCmd.OpenQuery "15_013_FinalCreate_Tbl", acViewNormal, acEdit
    [blue]lblInfo.Caption = "I am DONE."[/blue]
    DoCmd.SetWarnings (WarningsOn)
    [green]'MsgBox "Updates Complete"[/green]
    DoCmd.OpenReport "Final Report", acViewPreview
    DoCmd.Maximize
    MsgBox "This is the report for GL-MT-013. From the Print Preview Ribbon select Data-More. Click on the Word icon. Browse to the desired location and click OK."
End Sub

You may need to do [tt]lblInfo.Refresh[/tt] if repainting of the Form is delayed due to the process of your query.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

OOPS. I placed a Label object on the form. I named it lblInfo. I then installed the code. When I ran the code I get "Compile error: Method or data member not found. And it highlights the word refresh in the lblInfo.Refresh line of the code. Is it possible you wanted me to insert a Text object rather than a Label object? I also changed the code to Me.lblInfo.Refresh. It had the same failure. When I typed this in the list following the Me.lblInfo. does not show a Refresh selection. I am using MS Access 2013

Thanks,

 

[Andrzejek]

Skip (eliminate) the [tt]lblInfo.Refresh[/tt] and see if it will work OK for you.
Test object (text box) would work, too. Try it and see

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

OK. Using a text box named lblInfo now. This is on the same form as the start button. Previous failure no longer occurs but lblInfo text box does not update. So, I placed a msgbox after 1st and 8th refresh. Program ran and displayed "Step 1 of 16". Closed msgbox and program continued but..Start button and lblInfo controls became not visible. At the end of the program "Step 8 of 16" showed followed by "I am Done".

Are my queries causing this? Or, maybe I have to put DoCmd.SetWarnings (WarningsOff)and DoCmd.SetWarnings (WarningsOn)in between each query.

Current code:

Private Sub cmdStart_Click()
    cmdStart.Enabled = False
    DoCmd.SetWarnings (WarningsOff)
    Me.lblInfo = "Step 1 of 16"
    Me.lblInfo.Requery
    MsgBox "Step one complete"
    DoCmd.OpenQuery "1_ToR_Create_Add_Tbl", acViewNormal, acEdit
    Me.lblInfo = "Step 2 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "2_ToR_Create_Remove_Tbl", acViewNormal, acEdit
    Me.lblInfo = "Step 3 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "3_ToR_Output_Add", acViewNormal, acEdit
    Me.lblInfo = "Step 4 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "3_ToR_Output_Removal", acViewNormal, acEdit
    Me.lblInfo = "Step 5 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "4_013Breakout qry", acViewNormal, acEdit
    Me.lblInfo = "Step 6 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "5_013_Delete_Names", acViewNormal, acEdit
    Me.lblInfo = "Step 7 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "6_013_Delete_Model", acViewNormal, acEdit
    Me.lblInfo = "Step 8 of 16"
        Me.lblInfo.Requery
        MsgBox "Step one complete"
    DoCmd.OpenQuery "7_013_AF_Delete_AF", acViewNormal, acEdit
    Me.lblInfo = "Step 9 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "8_013_Delete_AV", acViewNormal, acEdit
    Me.lblInfo = "Step 10 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "9_013_Delete_PP", acViewNormal, acEdit
    Me.lblInfo = "Step 11 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "10_013_Names_Append_tbl", acViewNormal, acEdit
    Me.lblInfo = "Step 12 of 16"
        Me.lblInfo.Requery
    DoCmd.OpenQuery "11_013_Models_Append_Tbl", acViewNormal, acEdit
    Me.lblInfo = "Step 13 of 16"

Thanks,

 

[Andrzejek]

First, I would not name a text box with the [tt]lbl[/tt] prefix.
Rename it to something like [tt]txtInfo[/tt]

Eliminate [tt]Me.lblInfo.Requery[/tt] line, it does not do anything anyway.

About not displaying your progress -
You may want to play with DoEvents function.
Try placing DoEvents before (or after) [tt]txtInfo = "Step X of Y" [/tt] and see if your text box will display the progress.

Have fun.

---- Andy

There is a great need for a sarcasm font.