Progress Meters (Including Dual-Bar) (1 Viewer)

[Frothingslosh]

One of the most commonly asked questions I’ve seen on this and other MS Access sites has got to be ‘How do I show a progress bar?’ While Access has a built-in meter bar, upon which I will elaborate momentarily, I’ve also ended up having to create a couple other options that I’ll share.

EDIT: Please see addendum at the end of this post for issues regarding accde/mde files.

EDIT: Changes specific to the newest version are italicized.

First off, we have the built-in meter bar, accessed via the SysCmd object. Microsoft has an article on it HERE, but here is an overview:

OPTION 1 – BUILT-IN METER


Positives:

• Built-in function, so normally fast to execute
• Uses no extra system resources
• Fairly straightforward

Negatives:

• Hard to see (no pop-up, only shows on bottom-left of screen)
• Hides the progress meter you normally see when action queries process.
• If you’re doing thousands of iterations, this can drastically slow down processing if you’re updating the meter literally every iteration.

USE
Step 1: Initialize the meter. You do so with the command

SysCmd acSysCmdInitMeter, Arg1, Arg2

• Arg 1 is the text you want displayed on the status bar.
• Arg 2 is a long integer used to indicate the maximum progress. For example, if you are running 27 loops, you would include 27. If you wanted to track percent complete, you might set it to be 100.
• NOTE: The displayed value of the bar starts at 0, meaning the meter will show empty.

Step 2: After each iteration/action, update the meter via

SysCmd acSysCmdUpdateMeter, Arg1

• Arg1 is a long integer indicating current progress. If the max progress (Arg 2 above) is 100 and you set Arg1 here to 27, the meter will show at 27% full.
• NOTE: Values of Arg1 set higher than Arg2 was in Step 1 will show 100% complete.

Step 3: Once you’re done, remove the meter via

SysCmd acSysCmdRemoveMeter

***

Sometimes, however, this meter isn’t what you need. It can be hard to see, and sometimes you want to make absolutely certain that your user can see your progress. Sometimes one meter won’t cut it, and you need a linked pair, with one showing task progress while another shows overall progress. For those, I wound up writing a couple custom classes, which I have attached to this post.

OPTION 2: clsMeter (Single Meter Bar)


Positives:

• Uses a pop-up window, so much more visible to the user.
• Progress bar is slightly more flexible than the hash marks Option 1 uses.
• Includes a few options for a slightly more flexible display.
• Used similarly to Option 1.

Negatives:

• Uses a custom class, so uses system resources
• Technically runs slightly slower due to being a class rather than being inbuilt.
• Little overall difference from Option 1 other than visibility.
• As with Option 1, action query progress cannot be tracked with this meter.

USE
Step 1: Dim as a clsMeter, then create a new instance of the meter object; example:

 Dim Meter as clsMeter
 

Set Meter = New clsMeter

• NOTE: When this object is initialized, the code will look for ‘frmMeter’. If that form does not exist, it will create and save it.
• The remaining examples will assume that the object being referenced was named ‘Meter’.

Step 2: Initialize the meter. Format:

Meter.Initialize ActionCount, DisplayText, PauseOnUpdate, PauseDuration, BarColor, FormColor

• ActionCount (Long Integer) is the number of actions covered by this meter, similar to Arg2 in acSysCmdInitMeter above.
• DisplayText (Text) is the text to be displayed above the progress bar. The text field that displays this is only 3 inches in length, so keep that in mind when setting this argument.
• PauseOnUpdate (Y/N) is an optional Boolean field that defaults to TRUE. It indicates whether or not the code should pause once progress has reached 100% (generally for the sake of visibility).
• PauseDuration (Single) is optional and only applies if PauseOnUpdate is TRUE. This is the length of time the system pauses once complete. This can range from 1 to 10 seconds, but the default is 1. Note that this argument will accept fractions of a second.
• BarColor is an optional Long Integer field used to set the color of the progress bar.
  • Leaving it out uses the normal values in the form (normally green).
  • This is a long variable representing the color from the color pallette.
• FormColor an optional Long Integer field used to set the background color of the form.
  • Leaving it out uses the normal background color of the form (normally white).
  • This is a long variable representing the color from the color pallette.
• NOTE: Current progress will start at 0% complete.
• NOTE: This is the step where frmMeter is displayed to the user. The form is both modal and a pop-up, so it will effectively lock the user out of doing anything else with the database until processing is completed.

Step 3: As you complete tasks or iterations, update the meter:

Meter.Update ActionCount

• ActionCount is the current value of whatever counter you’re using – iteration number, task number completed, whatever you’re tracking.
• NOTE: The system only updates the visible meter whenever at least 1% progress has been made since the last display update. If you’re only running 8 loops, the display will update every time. If you’re running 10,000 loops, however, the display will be updated every 100. This is to cut down on unnecessary screen updates that slow the system down. The system still tracks the exact progress internally, however.
• NOTE: After a screen update, the system pauses 0.002 seconds. This ensures that the progress meter moves at a speed visible to the human eye. Any value lower than 0.002 seconds is treated by Access as 0 seconds, and if your iterations or tasks finish quickly enough, the meter can well reach 100% instantly if the pause is set below 0.002.
• NOTE: Once the meter reaches 100%, it will pause for the length of time indicated by Step 2, and then close the display form. As with any other object, the object variable itself will remain allocated until you destroy it or terminate code execution.

Minimum pause length, maximum pause length, after-update pause length, and the name of the meter form are set as constants at the top of the class module, and can be easily changed there.

Optional Step: You can alter the displayed text for the task as follows:

Meter.ChangeDesc NewDescription

• NewDescription is a string that will replace the displayed description text.

OPTION 3: clsDualMeter (Dual Meter Bar)


Positives:

• High visibility due to pop-up meter display
• Uses dual meter bars, so can track tasks and overall progress simultaneously

Negatives:

• Notably more complicated than either option above, as it involves both tasks and the ‘project’ – the overall process you’re executing.
• As with clsMeter, slower and more resource-intensive than the built-in option.
• As with Options 1 and 2, this meter cannot track action query progress.

USE
Step 1: As with clsMeter, you must first Dim a variable as a clsDualMeter object, and then create it.

Dim Meter as clsDualMeter
 

Set Meter = New clsDualMeter

• NOTE: When this object is initialized, the code will look for ‘frmDualMeter’. If that form does not exist, it will create and save it.
• The remaining examples will assume that the object being referenced was named ‘Meter’.

Step 2: Initialize the project – i.e.: the overall process you’re executing, such as exporting several spreasheets.

Meter.InitializeProject NumberOfTasks, ProjectDescription, PauseOnTaskComplete, TaskCompletePauseDuration, PauseOnProjectComplete, ProjectCompletePauseDuration, BarColor, FormColor

• NumberOfTasks (Long Integer) is the total number of tasks to be completed with this project. For example, you may be exporting ten spreadsheets.
• ProjectDescription (String) is the text description for the project itself. An example might be “Exporting Employee Status Reports”. This is displayed in a 3 inch text box, so try to keep it short.
• PauseOnTaskComplete (Boolean) is optional. Set to True if you want a pause after each task is completed.
• TaskCompletePauseDuration (Single) is optional and is the length of time you want the system to pause after each task is completed. Can range from 1 to 10 seconds, and fractions of a second are allowed. In the example case, this would pause for 1 second after each spreadsheet is exported.
• PauseOnProjectComplete and ProjectCompletePauseDuration are the same as the two arguments above, but apply to the project as a whole. In the example case, these would cause the meter to pause for one second after the project is 100% complete.
• BarColor is an optional Long Integer field used to set the color of the progress bars.
  • 
    [*]Leaving it out uses the normal values in the form (normally green).
    [*]This is a long variable representing the color from the color pallette.
• FormColor is an optional Long Integer field used to set the color of the meter display form.
  • 
    [*]Leaving it out uses the normal values in the form (normally white).
    [*]This is a long variable representing the color from the color pallette.

Step 3: Initialize the first task. This is needed EVERY time a new task is to be started. In the example of exporting 10 spreadsheets, you would do this immediately before beginning each export.

Meter.InitializeTask ActionCount, TaskDescription

• ActionCount (Long Integer) is the number of actions needed in this task. For the export example, assuming you were doing a line-by-line export rather than TransferSpreadsheet, this would be the number of lines being exported.
• TaskDescription (String) is self-explanatory. It suffers from the same 3 inch display limitation as the project description.

Step 4: Update the task as you progress through it. In the export example, every time you finish exporting a row to the destination, you would run this step.

Meter.UpdateTask ActionCount

• ActionCount (Long Integer) is simply the numeric value indicating your progress. In the example, this would be the number of rows exported so far.
• NOTE: Once this step reaches 100% completion, processing will pause as indicated when the project was initialized (1 second by default).
• NOTE: After reaching 100% task completion, project progress will be incremented by 1. In the exporting ten spreadsheets example, every time an export is complete, the project (lower) progress (TaskCount) will increment, and the project meter bar will update by a 10% increment.

Step 5: If you need to change your project status text (say your procedure has multiple different actions), you can change it via this:

Meter.ChangeProjectDescription ProjectDesc

• ProjectDesc (String) is the new project description you want displayed on the project (lower) meter bar.

Step 6: If you have more tasks, loop back to Step 3.

Step 7: Once the project is 100% complete, the code will pause if indicated back in step 1, and then the meter form will be closed.


An example where you might need to change the project description might be this:

• Import spreadsheet.
• Clean data in multiple fields
• Transfer cleaned data to a permanent table
• Output reports based on newly imported data

For each of these steps, it would be a good idea to update the project description text to keep the user updated.

***
I have updated an Access 2003 database with both class modules as well as a test form so you can see it work as well as have working examples of the various methods. If you have any concerns or suggestions, please post them; I do have a few updates I want to make to these when I have the time and desire.

As per this forum’s rules, if you have questions about this, please PM me directly, and I will reply as soon as I am able.

***

ADDENDUM July 11, 2014

As CJLondon pointed out to me, this code will not be able to generate new forms in an mde/accde file. If you use my code in those types of files, you will need to have the forms pre-generated. You can certainly do that by running the test form in Meter.mdb, but just to play it safe, I'm uploading a file containing the two forms in question as well as the module.

 

[Frothingslosh]

Update:

Uploaded new versions of both databases. Old versions will be deleted after the new ones are up.

• Added the following to procedure "Initialize"
  • Optional parameter for progress bar color. Default is green.
  • Optional parameter for progress form color. Default is white.
• For the single meter class, added procedure "ChangeDesc". Implementation will be as follows:

  Meter.ChangeDesc "New Text Here"

 

[Frothingslosh]

Updated to v 1.02

Corrected Initialization functions so that you really can control the background and progress bar colors. For real this time!

Please note that the easiest way to permanently change the default color scheme is to simply modify the forms. The built-in form creator ONLY runs if frmMeter/frmDualMeter don't exist and will not overwrite your changes.

 

[Carla_B]

Hi....just wanted to say THANK YOU, THANK YOU, THANK YOU!!!

After 8 or more hours trying to get another solution I found to work in my application, I had this working perfectly and how I wanted in under 30 minutes!

You, sir, are a star!!! Again, thank you

Carla

(PS I registered just so I could thank you, but I have a feeling it will turn out to be a wise move - I find solutions on here for what I need more than on any other forum!)

 

[ianruel30]

Updated to v 1.02

As with Options 1 and 2, this meter cannot track action query progress.

Hi,

First, I would like to thank you for this amazing Progress Bar. as per your notes as Negatives,the progress cannot sync in action queries. is there any way i can put the built in progress bar in the form?

Thanks

 

[isladogs]

@ianruel30
Post #5 has now been approved. Sorry about the delay but I hadn't seen it until today.
In future, please follow the advice in this 'sticky thread': Report your own posts to sample databases

Posting this to trigger email notifications

 

[Frothingslosh]

Hi,

First, I would like to thank you for this amazing Progress Bar. as per your notes as Negatives,the progress cannot sync in action queries. is there any way i can put the built in progress bar in the form?

Thanks

Not that I've ever heard of.

You'd probably be better off asking that one in the VBA subforum so that some of the heavyweights will see it. If it's remotely possible to move UI elements, then it's likely to be Galaxiom, Ridders, or Doc who knows how to do it.

 

[isladogs]

@ianruel30
You are asking whether a progress bar can be used with an action query.
I believe the answer is NO.
This is because the query is a single process and whilst running it stops all other processes from taking place.
On both counts, a progress bar can't work in this instance.

I actually tried to do this myself some time ago using a timer event. I failed

However, there is a possible work-round suggested some years ago by ChrisO in this thread: Indivisible process animation
This includes an example MDB database with the wonderful name LipstckOnAPig
Whether its worth the effort, even Chris himself was dubious

 

[CJ_London]

I don't have time to investigate, but if there is an intrepid explorer out there who wants to give it a go.....or someone who tried and determined not possible

A window is made up of smaller windows for individual components - scrollbars, navigation bars, controls etc.

I suspect not possible but just wondering if the progress bar which appears bottom right of the access window when a query runs can be identified as a window in its own right - and be persuaded to appear somewhere else.

You are asking whether a progress bar can be used with an action query.
I believe the answer is NO.

I try to persuade clients that they don't want a progress bar for long running action queries but when they insist I break the query down into smaller chunks using TOP 10 PERCENT or similar, run in a loop which gives me a break to update a suitable progress bar. Other slight benefit is that after the first 10% you can determine how long it took so you can then provide an estimated end time.

 

[isladogs]

I don't have time to investigate, but if there is an intrepid explorer out there who wants to give it a go.....or someone who tried and determined not possible

A window is made up of smaller windows for individual components - scrollbars, navigation bars, controls etc.

I suspect not possible but just wondering if the progress bar which appears bottom right of the access window when a query runs can be identified as a window in its own right - and be persuaded to appear somewhere else..

I'd also considered that but made no progress with that either - sorry about the pun
For anyone interested, the code needed uses commands acSysCmdInitMeter, acSysCmdUpdateMeter, acSysCmdRemoveMeter

I try to persuade clients that they don't want a progress bar for long running action queries but when they insist I break the query down into smaller chunks using TOP 10 PERCENT or similar, run in a loop which gives me a break to update a suitable progress bar. Other slight benefit is that after the first 10% you can determine how long it took so you can then provide an estimated end time.

That works but obviously slows down the lengthy query even more .... though perhaps not by much???

 

[CJ_London]

generally not by much - depends what it is doing, but a query that takes 15 mins might now take say 17 mins - who cares? the client has a progress meter so they are happy and 2 mins is not a lot in the scheme of things.

For retrieving data (i.e. select query) that can be a different problem and comes back to proper indexing and minimising number of records returned. Still amazes me that (some) users want to see a form populated with maybe 1m records - and then they filter. Suspect that is to do with the excel mentality

 

[Stormin]

I use FrothingSlosh's progress meter in one of my in-house applications. I coded it in about 18 months ago, with some of my own modifications. It's really great. I've thanked FS before, but I'll take the opportunity to say my thanks again

I had a query that extracted data from a gigantic external database and loaded it to a local table for further processing. This took about 3 minutes - while not that long, it stood out from the rest of the queries in the chain and made Access look like it had crashed/hung up, especially when the progress bar was stuck at 0%.

After some experimenting, I found a way to create pseudo-progress based on estimated time to completion. This was possible since the query took around the same length each time it was run.

What I do is create a new ADO object and open the same database through that. Then I run the SQL asynchronously while updating the progress meter every second. If the query finishes before the meter reaches 100%, it just jumps to 100%. If the query takes longer than the estimated time, the meter stays stuck at 99% until it finishes, then jumps to 100%. It is better to over- than under-estimate for the sake of the user. Not perfect, but it can be argued that Microsoft's own progress meters can be much worse than this!

I wrote this last year as a relative newbie so excuse any sloppy coding. It's part of the daily import routine so has been used 5 days a week for 12 months now, and so far nothing has blown up

Hopefully, this can be modified and will help someone somewhere.

Function SalesExtracts_DataDump( _
    Optional booSilent As Boolean, _
    Optional booVerySilent As Boolean, _
    Optional FullRebuild As Boolean)

' Author        :   <Stormin>
' Date Created  :   June 2017
' Purpose       :   Creates a static data dump from SalesExtracts
' Notes         :   A.K.A. Cache Builder
    
    On Error GoTo Err_Gen
    
    Dim rc_bef          As Long
    Dim rc_aft          As Long
    Dim tm_bef          As Single
    Dim tm_aft          As Single
    Dim strAbortSource  As String
    Dim strSQL          As String
    Dim strDesc         As String
    Dim DualMeterActive As Boolean
    Dim DisplayMeters   As Boolean
    Dim DbWasLocked     As Boolean
    Dim t               As Single
    Dim EstMins         As Single
    Dim TotalActions    As Long
    Dim SecondsPassed   As Long
    Dim i               As Integer
    Dim strMsg          As String
    
    Dim db As DAO.Database
    Set db = CurrentDb()
    
    Const strDDTableName As String = "Main Data - SE Data Dump"
    Const strDDQueryName As String = "Get SE Data for Source Records"
    Const strCLQueryName As String = "Delete Mismatched SE Dump Data"
       
    If booVerySilent Then booSilent = True
    
    If Not booSilent Then
        Select Case MsgBox( _
                "Do you wish to force a full rebuild of the SalesExtracts Cache?" & vbNewLine & _
                vbNewLine & _
                "Click 'Yes' to force a full rebuild (slow)." & vbNewLine & _
                "Click 'No' to perform a standard update (faster)." & vbNewLine & _
                "Click 'Cancel' to abort the procedure.", _
                vbYesNoCancel + vbDefaultButton2 + vbQuestion, _
                "SalesExtracts Cache Builder")
            Case vbYes: FullRebuild = True
            Case vbNo:  FullRebuild = False
            Case Else:  GoTo Err_MsgBox_Cancel
        End Select
    End If
    
'Initialise progress meter

    'Meter options
    EstMins = IIf(FullRebuild, 3.5, 0.3) 'estimated minutes for completion (for progress meters)
    strDesc = "Building Cache... " 'description prefix (for progress meters)
    DisplayMeters = True
    
    If DisplayMeters Then
        
        'Determine total action count
        TotalActions = Int(EstMins * 60)
        
        'Determine which meter to use
        If Not DualMeter Is Nothing Then DualMeterActive = True
        
        'Initialise the correct meter
        If DualMeterActive Then
            'Initialise a new dual meter task
            DualMeter.InitialiseTask _
                NumberOfActions:=TotalActions, _
                TaskDescription:=strDesc & "Please wait", _
                TaskPauseDuration:=0.75, _
                PauseOnTaskUpdate:=False
        Else
            'Initialise a new single meter form
            Set Meter = New clsMeter
            Meter.Initialise _
                NumberOfActions:=TotalActions, _
                Description:=strDesc & "Please wait", _
                FormTitle:="SalesExtracts Cache Builder: Estimated Progress", _
                TaskPauseDuration:=0.75, _
                PauseOnUpdate:=False
        End If
        
    End If
    
    'Hourglass on, warnings off
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    
    'Set timer and record count
    tm_bef = Timer
    rc_bef = CountAllRecords(strDDTableName)
    
    'Clear cache table
    If FullRebuild Then
        'Delete everything in the table
        strSQL = "DELETE * FROM [" & strDDTableName & "]"
    Else
        'Only delete mismatched IDs (from current month, not using VSID)
        strSQL = GetQuerySQL(strCLQueryName)
    End If
    db.Execute strSQL, dbFailOnError
    rc_aft = CountAllRecords(strDDTableName)
    If FullRebuild And rc_aft <> 0 Then GoTo Err_TableNotEmpty
    
RunQuery:
    
    If (Not DisplayMeters) Or (Meter Is Nothing And DualMeter Is Nothing) Or DbWasLocked Then
        'Run the query normally (either no meter, or meter stays at 0% for duration)
        MsgBox _
            "Building the SalesExracts cache in linear mode." & vbNewLine & _
            "Any progress bars (if displayed) will not move." & vbNewLine & _
            "Click OK and then wait for approximately " & Round(EstMins + 0.9, 0) & " minute(s)."
        DoCmd.OpenQuery strDDQueryName
    Else
        'Self-contained ADO run of the query SQL
        ' in asynchronous mode so that we can update
        ' the progress meter with pseudo progress
        ' (better than it sitting at 0% for 3 minutes)
        On Error GoTo Err_ADO
        Dim ADOcmd As Object, ADOcon As Object
        Set ADOcmd = CreateObject("ADODB.Command")
        Set ADOcon = CreateObject("ADODB.Connection")
        With ADOcon
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .Open db.Name
        End With
        With ADOcmd
            Set .ActiveConnection = ADOcon
            .CommandType = 1 'adCmdText
            .CommandText = GetQuerySQL(strDDQueryName)
            .Execute Options:=16 + 128 'adAsyncExecute + adExecuteNoRecords
            Do While .State > 1 ' connecting, executing, or fetching
                'Update the meter every second
                Sleep 1000
                SecondsPassed = SecondsPassed + 1
                'Increment the meter without filling it completely
                If SecondsPassed < TotalActions Then
                    i = Round(FormatTimer(SecondsPassed, TotalActions, fMinutes), 0)
                    If i = 0 Then
                        strMsg = "Please wait"
                    Else
                        strMsg = "Approx. " & i & " min(s) remaining"
                    End If
                    MeterUpdateTask DualMeterActive, SecondsPassed
                    MeterChangeDesc DualMeterActive, strDesc & strMsg
                    MeterRefresh DualMeterActive
                Else
                    MeterChangeDesc DualMeterActive, strDesc & "Please wait"
                End If
            Loop
        End With
        ADOcon.Close
        Set ADOcon = Nothing
        Set ADOcmd = Nothing
        On Error GoTo Err_Gen
        'end of self-contained ADO run
    End If
    
    rc_aft = CountAllRecords(strDDTableName)
    tm_aft = Timer
    
    TableImportDate strDDTableName, rc_aft - rc_bef
    
    If DisplayMeters Then MeterFill DualMeterActive, "Complete"
    
    DoCmd.Hourglass False
    
    If FullRebuild And Not booVerySilent Then
        Beep
        MsgBox _
            "SalesExtracts Cache was built in " & _
            FormatTimer(tm_bef, tm_aft, fMinsAndSecs) & "." & vbNewLine & _
            "Thank you for your patience.", _
            vbOKOnly, _
            "Success!"
    End If
    
    RunAll_Success_Public = True
    RunAll_RecsAdded_Public = rc_aft - rc_bef
    
Exit_Proc:
    Set db = Nothing
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    If DisplayMeters And Not DualMeterActive Then Reset Meter
    Exit Function
    
Err_Gen:
    MsgBox _
        "SalesExtracts_DataDump()" & vbNewLine & _
        vbNewLine & _
        Err.Description, _
        vbOKOnly, _
        "Error " & Err.Number
    GoTo Err_MsgBox_Cancel
    
Err_TableNotEmpty:
    MsgBox _
        "The table """ & strDDTableName & """ could not be cleared." & vbNewLine & _
        rc_aft & " records remain.", _
        vbOKOnly, _
        "Failure"
    On Error Resume Next
    Err.Raise vbObjectError
    GoTo Err_MsgBox_Cancel
    
Err_MsgBox_Cancel:
    If Err Then strAbortSource = "System" Else strAbortSource = "User"
    MsgBox "SalesExtracts Cache builder aborted.", vbOKOnly + vbCritical, "Cancelled by " & strAbortSource
    GoTo Exit_Proc
    
Err_ADO:
    Select Case Err.Number
        Case -2147467259
            'The database was edited since being opened,
            ' and so the ADO connection cannot have
            ' exclusive access
            DbWasLocked = True
            Set ADOcmd = Nothing
            Set ADOcon = Nothing
            Resume RunQuery
        Case Else
            GoTo Err_Gen
    End Select
    
End Function

 

[isladogs]

Hi Stormin
I haven't tried your code but, from your description, it is in some ways similar to the LipstickOnAPig example by ChrisO. See link in post #8

 

[Stormin]

Kinda.
The pig db runs the operation locally and displays an infinite spinner by opening a separate lipstick db.
My db updates the current (dual)meter class locally and runs the operation by opening a duplicate of itself.

 

[kb44]

Good morning everyone...

I would like to implement this progress meter (Option 2 - Single Meter Bar) to a series of queries which I run from a form by clicking a button. I am a newbie to Access so I inserted the code in the way I interpreted the original post. Here is what I have:

Private Sub RunQueries_Click()

MsgBox "You are about to trim spaces and display staffing differences."
Dim Meter As clsMeter
Set Meter = New clsMeter
Meter.Initialize ActionCount(8), DisplayText("Queries Progress…"), PauseOnUpdate, PauseDuration, BarColor, FormColor
DoCmd.SetWarnings False
DoCmd.OpenQuery "1-A Monthly_Staffing Data Trim Spaces"
Meter.Update ActionCount
DoCmd.OpenQuery "1-B Monthly_Staffing_Data_No_OPEN_or_blanc_ID"
Meter.Update ActionCount
DoCmd.OpenQuery "1-BB Monthy Staffing_Data_OPEN_BLANK_LOA_Pending"
Meter.Update ActionCount
DoCmd.OpenQuery "1-BB-2_tbl_Select_Curr_Month_OPEN_BLANK_LOA_Pending"
Meter.Update ActionCount
DoCmd.OpenQuery "1-C Staffing_Difference_new_v_old"
Meter.Update ActionCount
DoCmd.OpenQuery "1C-2_Totals_Changes_per_ID"
Meter.Update ActionCount
DoCmd.OpenQuery "1-D Staffing_Display_Changed_fields"
Meter.Update ActionCount
DoCmd.OpenTable "1-d Staffing_Display_Changed_Fields_NO_OPEN"
Meter.Update ActionCount
DoCmd.SetWarnings True

MsgBox "Done. Make changes here if needed and save this table."

End Sub

I am getting the following error: "Compile Error: User-defined type not defined"

Then: Meter As clsMeter is highlighted

I searched for answers to this error and read that my database does not have a reference set to the Microsoft DAO Object Library. So I then tried to add the Microsoft DAO 3.6 Library but got the error:

"Name conflicts with existing module, project, or object library"

It's probably a very simple mistake, however I don't know how to proceed.

Can anyone shed light on this? thanks.

PS. I don't like the spaces in some of the query names but management refuses to change them.

 

[isladogs]

Assuming you are using any version of Office from 2007 onwards, the Microsoft DAO 3.6 Library has been superceded by the Microsoft Office xx.0 Access database engine library where xx is a number like 12, 14,15,16 depending on your Access version. The newer reference is installed by default

You can't have both and should use the newer reference

 

[Frothingslosh]

After checking on Colin's suggestion, let me ask you this: did you actually import the clsMeter module from my files into your database? If you copied the code from it instead and just pasted it, then you need to make certain that you saved it in a class module, not a regular one.

I bring this up because that error is telling you that the class module 'clsMeter' can't be found.

 

[kb44]

After checking on Colin's suggestion, let me ask you this: did you actually import the clsMeter module from my files into your database? If you copied the code from it instead and just pasted it, then you need to make certain that you saved it in a class module, not a regular one.

I bring this up because that error is telling you that the class module 'clsMeter' can't be found.

This was the answer guys. Thank you!