Generic progress bar

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

LVL 1
MidlandRAsked:
Who is Participating?
 
Steve BinkConnect With a Mentor Commented:
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
 
Steve BinkCommented:
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
 
LucasMS Dynamics DeveloperCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Steve BinkCommented:
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
 
MidlandRAuthor Commented:
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
 
MidlandRAuthor Commented:
I am running Access 2000, can I install "Microsoft Progress Bar Control, Version 6.0" from Tools/References?

0
 
Steve BinkCommented:
bingo  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.