Solved

Generic progress bar

Posted on 2004-09-13
7
607 Views
Last Modified: 2006-11-17
Can someone give me a quick & easy generic piece of code that initiates a progress bar upon the start of a macro, updates whilst the macro is performing steps and then removes itself at the end (erm, obviously)!!!.

I am aware of the code Application.SysCmd acSysCmdInitMeter

0
Comment
Question by:MidlandR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12045038
The easiest way to do what you describe is to convert your macro to VBA, create a custom form to display the status bar, and alter the properties during the code execution.  What you want to do is not readily available with macros, so you would have to use VBA.  Since you're using VBA, no need to have a macro, which converts to VBA on execution anyways.
0
 
LVL 13

Expert Comment

by:lucas911
ID: 12045396
Here is some code that might help you.  I created an empty form with a progressbar control and a text box that displays the actual percentage and save it as frmProgressBar.

Properties for this form are:
Record Selectors = no
Scroll bars = neither
navigation buttons = no
auto resize = no
border style = none
control box = no
min max = no
close = no
whats this button = no

Pop up = YES
---------------------------------------------------

Public Sub ImportMembersBasicCI()
'This sub routine will import all members who have CI Basic...assuming it's enforced

Dim rs As DAO.Recordset
Dim rsRBC As DAO.Recordset
Dim sql As String

On Error GoTo errHandler

sql = "Select * from qry_ALL_DATA where member_benefit_type = 'CI Basic' and member_coverage_type = 'Member'"

Set rs = CurrentDb.OpenRecordset(sql)

If rs.EOF Then
    MsgBox "There is no data to import.", vbInformation, "NO IMPORT DATA"
    Exit Sub
Else
    'OPEN rsRBC table using the recordset and use the ADDNEW method to add a new record
    Set rsRBC = CurrentDb.OpenRecordset("RBC_Report_Data")

'-------PROGRESS BAR APPEARS HERE------------------------------------
    Form_frmProgressBar.Visible = True
    Form_frmProgressBar.prgBar.Value = 0

    Do Until rs.EOF
'--------PROGRESS BAR INCREMENTS THE BAR AND TELLS YOU WHAT PERCENTAGE---------------
    Form_frmProgressBar.prgBar.Value = rs.PercentPosition
    Form_frmProgressBar.txtInformation = Format(rs.PercentPosition, "##.##")
'-----------------------------------------------------------------------------------------------
        With rsRBC
            .AddNew
            .Fields(1).Value = rs!member_id
            .Fields(2).Value = rs!member_last_name
            .Fields(3).Value = rs!member_first_name
            .Fields(6).Value = "Employee"
            .Fields(8).Value = rs!member_date_app_recvd
            .Fields(11).Value = 25000
            If rs!member_smoker_status = "N" Then
                .Fields(15).Value = "NS"
            Else
                .Fields(15).Value = "S"
            End If
            .Fields(16).Value = rs!member_gender
            .Update
        End With
        rs.MoveNext
    Loop

rs.Close
Set rs = Nothing

rsRBC.Close
Set rsRBC = Nothing
   
End If
                   
errHandler:

If Err.Number <> 0 Then
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
Else
    Exit Sub
End If

End Sub
--------------------

You can modify the layout of your progress bar if you'd like and change the form design as well.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12046852
MidlandR: You'll notice that Lucas911's code takes a property from a recordset to determine the value of the progress bar's display:

'--------PROGRESS BAR INCREMENTS THE BAR AND TELLS YOU WHAT PERCENTAGE---------------
    Form_frmProgressBar.prgBar.Value = rs.PercentPosition
    Form_frmProgressBar.txtInformation = Format(rs.PercentPosition, "##.##")
'-----------------------------------------------------------------------------------------------

If you are not running through a recordset in the macro, and only have to do a series of unrelated steps, you would have to manually set the values for the progress bar at several points in the sub.  An example might look like this:

Public Sub MyProgressBarMacro()

MyProgressBar.Visible = True
MyProgressBar.Value = 0

' Do, say, steps 1-2 out of 10 here

MyProgressBar.Value = 20    ' 2/10 = 1/5 = 20% done

' Steps 3 and 4 here

MyProgressBar.Value = 40

' etc...

End Sub
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:MidlandR
ID: 12048641
Hi and thanks so far for the comments.

This progress bar will primarily be used for opening a query, performing it, closing it then opening a report so therefore steps would be deemed as unrelated.

I currently have a macro that performs all these functions.

In relation to MyProgressBar.Visible etc. have you set a MyProgressBar variable initially or will Access already know that this warrants a bar and therefore by stating .Visible = True it would perform the same initialisation function as the one I mentioned at first?

Thanks again.

0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 400 total points
ID: 12055522
The sub assumes you have created a progress bar control on the form, and called it MyProgressBar.  The .Visible property only sets the bar to visible or not.  That way you can have a form with other interactive elements without the progress bar being on top of everything, in the way, or tucked in a corner.  The ActiveX reference I have listed in my own version is "Microsoft Progress Bar Control, Version 6.0".
0
 
LVL 1

Author Comment

by:MidlandR
ID: 12088982
I am running Access 2000, can I install "Microsoft Progress Bar Control, Version 6.0" from Tools/References?

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12092214
bingo  :)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question