Solved

Generic progress bar

Posted on 2004-09-13
7
604 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

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.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

830 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