How to set progress report meter in Ms Access

If you are iterating a recordset, that is the most appropriate place to fire off some user feedback.  Rather than updating the status bar for every row, I usually update the status bar every 50, 100, or 500 rows.

If you have a long-running query, there isn't a good way to show that progress.  I don't think that ADODB supports async queries when connecting to Access databases.

My query is that is there any VBA code that can be used for this?

You can take the simple approach and update the statusbar via the Application.Echo method.

Look here for description of the method and example VB code:
https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-echo-method-access

You can take the simple approach and update the statusbar via the Application.Echo method.

Look here for description of the method and example VB code:
https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-echo-method-access

You can't do this. Access can only do one thing at the time, so while it is preparing and finishing a report, that's it.
Educate your users about patience.

/gustav

Hi Experts

After continuing hunting for solution of the progress bar in ms access , I have seen a code in Microsoft site but it is not fully explained, see  below :

What I want are the follow:

(1) Where do I type the code below in my Income statement report , example which EVENT?
(2) What should I replace with , example ( "Customer "  & Mytable)


Sub ProgressMeter()
   Dim MyDB As DAO.Database, MyTable As DAO.Recordset
   Dim Count As Long
   Dim Progress_Amount As Integer

   Set MyDB = CurrentDb()
   Set MyTable = MyDB.OpenRecordset("Customers")

   ' Move to last record of the table to get the total number of records.
   MyTable.MoveLast
   Count = MyTable.RecordCount

   ' Move back to first record.
   MyTable.MoveFirst

   ' Initialize the progress meter.
    SysCmd acSysCmdInitMeter, "Reading Data...", Count

   ' Enumerate through all the records.
   For Progress_Amount = 1 To Count
     ' Update the progress meter.
      SysCmd acSysCmdUpdateMeter, Progress_Amount

     'Print the contact name and number of orders in the Immediate window.
      Debug.Print MyTable![ContactName]; _
                  DCount("[OrderID]", "Orders", "[CustomerID]='" &; MyTable![CustomerID] &; "'")

     ' Go to the next record.
      MyTable.MoveNext
   Next Progress_Amount

   ' Remove the progress meter.
   SysCmd acSysCmdRemoveMeter

End Sub

Regards

Chris

Sure, you can loop through a recordset and set a progress bar.

But that's not what you asked for - except if you for each record wish to run the report, and neither is that what you intend to do.

/gustav

That answers one of my questions (what are you trying to show progress on)

I want to show progress on a report called rptBalancesheet

Regard


Chris

Again: You can't. There is no event or indication to catch for the progress of rendering the report.

/gustav

A report object has format and print events.  You could potentially place code in one of these events that would display the progress or current report subject.

Ok

I have found a free code out there in the internet , I think the issue of creating a form is not a problem for me here ,I have only one issue not fully covered see code 2 below :
The code below goes behind the new form which will be created again no issue here:
Option Compare Database
Option Explicit
 
' ***************************************************************
' * Class: Form_ProgressBar *
' * Popup progress bar *
' * *
' * Author: Christopher J. McClellan *
' * Published under Creative Commons Attribution-Share Alike *
' * http://creativecommons.org/licenses/by-sa/3.0/ *
' * You are free to change, distribute, and pretty much do *
' * whatever you like with the code, but you must give credit *
' * to the original author and publish any derivitive of this *
' * code under the same license. *
' ***************************************************************
Private Const MaxBoxWidth As Long = 7200 'maximun boxProgress width
 
Public Enum ePBarModeType
 PBarMode_Percent = 0
 PBarMode_Executing = 1
End Enum
 
Private mMode As ePBarModeType
Private mCurrentProgress As Long
Private mSteps As Long
 
Public Property Get PercentComplete() As Double
'read only
 PercentComplete = mCurrentProgress / mSteps * 100
End Property
 
Public Property Let Mode(PBarMode As ePBarModeType)
 mMode = PBarMode
End Property
 
Public Property Get Mode() As ePBarModeType
 Mode = mMode
End Property
 
Public Property Let CurrentProgress(lng As Long)
' Updating the CurrentProgress property updates the status of the Progress Bar
 
mCurrentProgress = lng
 ' format #0 makes a 1 or 2 digit number without decimals
 If mMode = PBarMode_Percent Then
 'format "#0" gives a 1 or 2 digit integer
 Me.txtStatus = Format(Me.PercentComplete, "#0") & " % Complete"
 ElseIf mMode = PBarMode_Executing Then
 Me.txtStatus = "Executing..."
 End If
 ' boxProgress.Width = a percentage of maximum box width
 Me.boxProgress.Width = (mCurrentProgress / mSteps) * MaxBoxWidth
 Me.Repaint
 DoEvents
End Property
 
Public Property Get CurrentProgress() As Long
' current step of process
 CurrentProgress = mCurrentProgress
End Property
 
Property Let steps(lng As Long)
' total number of steps to process
 mSteps = lng
End Property
 
Public Sub init(steps As Long, Mode As ePBarModeType, Optional strCaption As String = "Loading...")
' initializes values for progress bar
 
Me.Mode = Mode
 Me.Caption = strCaption
 mCurrentProgress = 0
 mSteps = steps
 Me.txtStatus = "Ready"
 Me.boxProgress.Width = 0
 Me.Visible = True
End Sub


Problem:

Code two

</pre>
Private Sub exampleCall1()
' example call for using progress bar with a looping process
 Dim pbar As Form_ProgressBar
 Dim i As Long
 Dim steps As Long
 steps = 100000
 
 ' create new instance of Progress Bar
 Set pbar = New Form_ProgressBar
 With pbar
 ' #of steps, Mode, Caption
 .init steps, PBarMode_Percent, "Hey, I'm working here!"
 For i = 1 To steps
 ' do something in a loop
 
 ' update progress
 .CurrentProgress = i
 Next i
 End With
 Set pbar = Nothing
End Sub

Where should I press this code Two ????????? if it is still on the new form then how do I call this function for example from my splash form?????????????

Many thanks to the Author: Christopher J. McClellan *

Regards

Chris

Maybe you should ask Christopher J. McClellan

Ok finally sorted out , but it is only suitable for those who have reports taking too much time to load as for me it is not required because my reports takes a maximum of 3 seconds. It works ok for sure as long as the names changes and calling is done correctly otherwise it gives an an error , however , good work from  Christopher J. McClellan


Regards

Chris

@Member_2_7948725

good work from  Christopher J. McClellan

Are you Christopher J. McClellan?

No I'm not !