INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How can I show a progress bar/meter

How can I show a progress bar/meter

(OP)
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.

RE: How can I show a progress bar/meter

"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 smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: How can I show a progress bar/meter

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

Thanks,

RE: How can I show a progress bar/meter

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

RE: How can I show a progress bar/meter

(OP)
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.



CODE -->

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 

RE: How can I show a progress bar/meter

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

CODE

Option Compare Database

Private Sub cmdStart_Click()
    cmdStart.Enabled = False    
    DoCmd.SetWarnings (WarningsOff)
    lblInfo.Caption = "Step 1 of 15"
    lblInfo.Refresh
    DoCmd.OpenQuery "1_ToR_Create_Add_Tbl", acViewNormal, acEdit
    lblInfo.Caption = "Step 2 of 15"
    DoCmd.OpenQuery "2_ToR_Create_Remove_Tbl", acViewNormal, acEdit
    lblInfo.Caption = "Step 3 of 15"
    DoCmd.OpenQuery "3_ToR_Output_Add", acViewNormal, acEdit
    lblInfo.Caption = "Step 4 of 15"
    DoCmd.OpenQuery "3_ToR_Output_Removal", acViewNormal, acEdit
    lblInfo.Caption = "Step 5 of 15"
    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
    lblInfo.Caption = "I am DONE."
    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 

You may need to do lblInfo.Refresh 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.

RE: How can I show a progress bar/meter

(OP)
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,

RE: How can I show a progress bar/meter

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: How can I show a progress bar/meter

(OP)
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:

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,

RE: How can I show a progress bar/meter

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

Eliminate Me.lblInfo.Requery 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) txtInfo = "Step X of Y" and see if your text box will display the progress.

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close