Solved

Generic progress bar

Posted on 2004-09-13
7
601 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
  • 4
  • 2
7 Comments
 
LVL 50

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 50

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 50

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 50

Expert Comment

by:Steve Bink
ID: 12092214
bingo  :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now