Solved

Generic progress bar

Posted on 2004-09-13
7
602 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

13 Experts available now in Live!

Get 1:1 Help Now