Solved Progress meter for reports & queries in MS Access (1 Viewer)

[nector]

I'm trying to develop a progress meter to help users have an idea of how long their report or open record set is taking running, this way users will have full ideas of what is going on in the system rather than just waiting.

The small, attached database has a VBA module below working in conjunction with a small form called frprogress:

Private Sub CmdReports_Click()
Dim Cancel As Integer
Const REPORTCANCELLED = 2501
On Error Resume Next
DoCmd.OpenReport "rptPurchasesDetails", acViewPreview & ProgressMtr
DoCmd.Close acForm, "frmProgress"
Select Case Err.Number
    Case 0
    ' no error
    Case REPORTCANCELLED
     ' anticipated error, so ignore
    Case Else
    ' unknown error, so inform user
    MsgBox Err.Number & Err.Description, vbExclamation, "Error"
End Select

End Sub

(1) I have attached code to the report and running simultaneously which I think is not correct, the idea here is that the progress meter should start when the report is opening and automatically close when the report is visible.


(2) If the above works well I want to attach it also to the code below, but I'm not sure where exactly to put it?????????/

Public Sub CmdCwrite_Click()
Call CmdTotalClasses_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim Company As New Dictionary
Dim strData As String
Dim Json As Object
Dim data As New Dictionary
Dim transactions As Collection
Dim itemCount As Long
Dim i As Long
Dim n As Integer
Dim z As Integer
Dim item As New Dictionary
Dim items As New Collection
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("QryJson")

For Each prm In qdf.Parameters
     prm = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
    Set qdf = Nothing
    rs.MoveFirst
    Do While Not rs.EOF
Set data = New Dictionary
Set transactions = New Collection
Set Company = New Dictionary
        Company.Add "tpin", rs!suptpin.Value
        Company.Add "bhfId", rs!bhfId.Value
        Company.Add "invcNo", rs!InvoiceID.Value
        Company.Add "orgInvcNo", Nz(rs!OrignalInvoiceNumber.Value, 0)
        Company.Add "custTpin", rs!TPIN.Value
        Company.Add "prcOrdCd", IIf((rs!NewprcOrdCd.Value = "0"), 0, "")
        Company.Add "custNm", rs!Company.Value
        Company.Add "salesTyCd", rs!SalesType.Value
        Company.Add "rcptTyCd", rs!DocCodes.Value
        Company.Add "pmtTyCd", rs!PaymentIDs.Value
        Company.Add "salesSttsCd", "02"
        Company.Add "cfmDt", rs!ActualDate.Value
        Company.Add "salesDt", Format((rs!ShipDate), "yyyymmdd")
        Company.Add "stockRlsDt", IIf(Len(rs!stockreleasing), rs!stockreleasing, Null)
        Company.Add "cnclReqDt", Null
        Company.Add "cnclDt", Null
        Company.Add "rfdDt", Null
        Company.Add "rfdRsnCd", rs!rfdRsnCding.Value
        Company.Add "totItemCnt", Me.txtinternalaudit
        Company.Add "taxblAmtA", Round(Nz(Me.txtclassA, 0), 2)
        Company.Add "taxblAmtB", Round(Nz(Me.txtclassB, 0), 2)
        Company.Add "taxblAmtC1", Round(Nz(Me.txtclassC1, 0), 2)
        Company.Add "taxblAmtC2", Round(Nz(Me.txtclassC2, 0), 2)
        Company.Add "taxblAmtC3", Round(Nz(Me.txtclassC3, 0), 2)
        Company.Add "taxblAmtD", Round(Nz(Me.txtclassD, 0), 2)
        Company.Add "taxblAmtRvat", 0
        Company.Add "taxblAmtE", 0
        Company.Add "taxblAmtF", 0
        Company.Add "taxblAmtIpl1", 0
        Company.Add "taxblAmtIpl2", 0
        Company.Add "taxblAmtTl", 0
        Company.Add "taxblAmtEcm", 0
        Company.Add "taxblAmtExeeg", 0
        Company.Add "taxblAmtTot", 0
        Company.Add "taxRtA", Nz((rs!Tax.Value) * 100, 0)
        Company.Add "taxRtB", Nz((rs!Tax.Value) * 100, 0)
        Company.Add "taxRtC1", 0
        Company.Add "taxRtC2", 0
        Company.Add "taxRtC3", 0
        Company.Add "taxRtD", 0
        Company.Add "taxRtE", 0
        Company.Add "taxRtF", 0
        Company.Add "taxRtIpl1", 0
        Company.Add "taxRtIpl2", 0
        Company.Add "taxRtTl", 0
        Company.Add "taxRtEcm", 0
        Company.Add "taxRtExeeg", 0
        Company.Add "taxRtTot", 0
        Company.Add "taxRtRvat", 0
        Company.Add "taxAmtA", Round(Nz(Me.txtttaxclassA, 0), 2)
        Company.Add "taxAmtB", Round(Nz(Me.txtttaxclassb, 0), 2)
        Company.Add "taxAmtC1", 0
        Company.Add "taxAmtC2", 0
        Company.Add "taxAmtC3", 0
        Company.Add "taxAmtD", 0
        Company.Add "taxAmtE", 0
        Company.Add "taxAmtF", 0
        Company.Add "taxAmtIpl1", 0
        Company.Add "taxAmtIpl2", 0
        Company.Add "taxAmtTl", 0
        Company.Add "taxAmtEcm", 0
        Company.Add "taxAmtExeeg", 0
        Company.Add "taxAmtTot", 0
        Company.Add "taxAmtRvat", 0
        Company.Add "totTaxblAmt", Round(Nz(Me.txttotaxableAB, 0), 2)
        Company.Add "totTaxAmt", Round(Nz(Me.txttotaltaxAll, 0), 2)
        Company.Add "totAmt", Round(Nz(Me.txtGrandtotal, 0), 2)
        Company.Add "prchrAcptcYn", rs!prchrAcptcYn.Value
        Company.Add "remark", rs!TheNotes.Value
        Company.Add "regrId", "11999"
        Company.Add "regrNm", rs!CreatedBy.Value
        Company.Add "modrId", "45678"
        Company.Add "modrNm", rs!CreatedBy.Value
        Company.Add "receipt", data
        data.Add "custTpin", rs!TPIN.Value
        data.Add "custMblNo", rs!Phone.Value
        data.Add "rptNo", 0
        data.Add "trdeNm", rs!Company.Value
        data.Add "adrs", rs!Address.Value
        data.Add "topMsg", ""
        data.Add "btmMsg", "Thank you for choosing us"
        data.Add "prchrAcptcYn", rs!prchrAcptcYn.Value
        Company.Add "itemList", transactions
             
    '--- loop over all the items
        itemCount = Me.txtinternalaudit
       
        For i = 1 To itemCount
            Set item = New Dictionary
            transactions.Add item
            item.Add "itemSeq", i
            item.Add "itemCd", rs!itemCd.Value
            item.Add "itemClsCd", rs!itemClsCd.Value
            item.Add "itemNm", rs!ProductName.Value
            item.Add "bcd", Null
            item.Add "pkgUnitCd", "NT"
            item.Add "pkg", 1
            item.Add "qtyUnitCd", "U"
            item.Add "qty", rs!Qty.Value
            item.Add "prc", rs!UnitPrice.Value
            item.Add "splyAmt", rs!UnitPrice.Value
            item.Add "dcRt", 0
            item.Add "dcAmt", 0
            item.Add "isrccCd", Null
            item.Add "isrccNm", Null
            item.Add "isrcRt", Null
            item.Add "isrcAmt", Null
            item.Add "vatCatCd", rs!TaxClassA.Value
            item.Add "iplCatCd", "IPL1"
            item.Add "tlCatCd", "TL"
            item.Add "exciseCatCd", "EXEEG"
            item.Add "taxblAmt", Round(rs!SupplierAmount.Value, 2)
            item.Add "vatAmt", Round(rs!FinalTax.Value, 2)
            item.Add "iplAmt", Null
            item.Add "tlAmt", Null
            item.Add "exciseAmt", Null
            item.Add "totAmt", Round(rs!TotalAmount.Value, 2)

strData = JsonConverter.ConvertToJson(Company, Whitespace:=3)
rs.MoveNext
Next
Loop
Dim Request As Object
Dim stUrl As String
Dim Response As String
Dim requestBody As String
Dim Details As Variant
stUrl = "http://localhost:9090/churchelder/paster/senior/local"
Set Request = CreateObject("MSXML2.XMLHTTP")
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .send requestBody
        Response = .responsetext
    End With
If Request.Status = 200 Then
MsgBox Request.responsetext, vbCritical, "Internal Audit Manager"
Set rst = db.OpenRecordset("select vsdcRcptPbctDate,rcptNo,intrlData,rcptSign FROM [tblCustomerInvoice] WHERE [InvoiceID] = " & Me.txtJsonReceived, dbOpenDynaset)
Set Json = JsonConverter.ParseJson(Request.responsetext)
'Process data.
On Error Resume Next
rst.Edit
rst![rcptNo] = Json("data")("rcptNo")
rst![intrlData] = Json("data")("intrlData")
rst![rcptSign] = Json("data")("rcptSign")
rst![vsdcRcptPbctDate] = Json("data")("vsdcRcptPbctDate")
rst.Update
On Error Resume Next
ElseIf (Request.Status <> 200) Then
MsgBox Request.responsetext, vbCritical, "Internal Audit Manager"

rs.Close
rst.Close
Set rst = Nothing
Set rs = Nothing
Set qdf = Nothing
Set prm = Nothing
Set Json = Nothing
End If
End Sub

 

[tvanstiphout]

> DoCmd.OpenReport "rptPurchasesDetails", acViewPreview & ProgressMtr
This cannot be correct. acViewPreview is a constant (integer) with value of 2. ProgressMtr is a public function returning a variant, and upon quick inspection of your code it returns an empty string.
The only reason this even compiles is because VBA is very sloppy with its data types, but this cannot be what you meant to do. Please tell us what you did intend to have happen with this code.

 

[tvanstiphout]

Nector,
I'm sorry, but a progress meter will not work in your scenario, where you want to show progress of the report being generated. Access does not provide that level of control. If a report has a long-running query, query progress will be shown in the StatusBar, and if it takes a long time to format the report, there will be text about that in the StatusBar as well.

I'm at a loss why you are focused on such minutia, while you still have much bigger fish to fry, namely your database design. I counted 22 fields in tblPurchases related to Tax, including Repeating Groups. That is a violation of proper relational database design - the rules of Normalization.
Sme flds are unnec abbrev'ed, so it is hard to understand what they refer to, but there are likely other reasons to frown on this design.
May I refer you to the Northwind 2 Templates (especially the Developer template) for a better design? There is a lot of documentation that comes with it, and you can find several videos on YouTube discussing the templates. Highly recommended.

 

[plog]

Agree with tvan. You're focused on what to paint the third floor bathroom while the foundation of the house is a a few cinder blocks and a couple 2x4s held together with duct tape. Here's the big things I see with your tables:

1. Field names with suffixes. When you feel the need have a bunch of similar field names that are virtually the same and only differintiated with letters and numbers at the end of their names (taxAmtA, taxAmtB, taxRtC1, taxRtC2, etc.) It's time for a new table. All those tax_ fields need to go into at least one new table.

2. Calculated fields. You don't store values you can calculate with other data in your database. I see what I think are a few of these fields (splyAmt, taxblAmt, taxAmt, totAmt). If you can use math and other data, you don't store those values in a table. Instead you build a query and calculate those values in the query and use the query. Otherwise when underlying data changes the stored totals don't unless.

I have a hunch you have made a few other mistakes (storing data in multiple places, storing groups of data in the wrong table, etc.), but you've only provided 2 tables of your database for us to see.

My advice is to read up on normalization (the process of setting up tables and fields), work through a few tutorials, apply what you learn to your database and then upload a copy of it here so we can help you make sure the structure is correct before you move on.

Database normalization - Wikipedia

en.wikipedia.org

 

[nector]

Many thanks all contributors,

Kindly take note that is not the live table by just a fiction table , but the point remain the same was to get a progress meter because sometimes users feel uncomfortable when a report is taking long without any response.

 

[GPGeorge]

There's an old aphorism about the value of simplicity, usually expressed as the acronym, "KISS". Most of us know what that means.

Over the years I came to realize that many developers follow a similar career trajectory, which can be summarized as phases.

Phase 1. Early excitement and exploration.
You discover a new tool, e.g. MS Access, and want to exploit it. In this phase constant frustration appears whenever you have to figure out how
to accomplish a task for the first time. Just getting something to work at all is exciting. Being complacent about serious things like proper
"normalization" is, a common attitude here. There's a strong urge to put all that off for later. Now is the time to just "get things done".

Phase 2. Serious study and discipline.
You realize that this is going to be harder than you hoped, and that there are many, many things you have to learn to do, or to do better
in order to be successful. That means rethinking everything you first did and asking yourself if there might be a better way. You can't overlook
the fundamental role of good table design, not if you ever want to be more than a dabbler. You can't ignore efficient interface design,
not if you want others to use your product.

Phase 3. Showing off.
Yes, many of us reach this phase, sooner or later. We've learned a great deal and begin to feel like we can incorporate a lot of features and
functions beyond the simple stuff. And we do so. On the coding side, we learn about recordsets and begin to implement them everywhere,
often in place of simple queries, for example. Or maybe we stick variables everywhere, whether they're useful or not. I'm sure most developers
can look at their earlier work and find examples of exactly what I'm talking about.
On the interface side, we begin to use bright colors, flashing icons and labels and all the other bells and whistles. That's where touches like
progress bars and custom navigation functions to replace the simplistic built-in form navigation appear.

Phase 4. Getting serious.
Finally, most developers eventually get serious about "doing it right". They begin to revisit and re-study concepts like Normalization,
in depth and with an eye to passing muster with their peers who have little tolerance for sloppy design. Writing good code takes on
a different character. Things get both more complex and simpler. More complex, in that features like consistent, principled variable naming
and declaration take priority over neat coding tricks and unnecessary thrills. More complex, in that structures like Dictionaries, classes and
global error handling begin to dominate your thinking.
But simpler in that unnecessary code gets eliminated without regret or mercy.

I may be minimizing or missing some significant aspects of the process, one of them is certainly the part about getting ahead of yourself that previous posters have pointed out. That is actually one of the more common threads in the careers of developers on the path from neophyte to pro. It's tempting to move on to the sexy stuff, but pros resist that urge, at least most of the time.

The point is that simplicity is achieved, ironically, at the end of a long process of learning how to include everything that your application does need and nothing that it doesn't need.

 

[GPGeorge]

Many thanks all contributors,

Kindly take note that is not the live table by just a fiction table ,

Spoiler

but the point remain the same was to get a progress meter because sometimes users feel uncomfortable when a report is taking long without any response.

Good grief. You MIGHT have advantageously revealed that earlier in the process.

It's pretty fully developed to be "a fiction table", though, so I suspect it actually does represent something in an existing application.

 

[MajP]

but the point remain the same was to get a progress meter because sometimes users feel uncomfortable when a report is taking long without any response.

There are lots of examples of progress meters in Access. If you search the forum you can find many.

Progress Bar

Attached is an updated version of my progress bar app. The example app contains 4 forms with different variations of the progress bar: 1. using a solid bar with progress based on a series of events (update queries). 2. using a solid bar with progress done using a timer event. 3...

www.access-programmers.co.uk

Add "Progress Meter" to a working VBA routine

Good afternoon: In a different post (https://www.access-programmers.co.uk/forums/threads/vba-modification-to-allow-listboxs-multi-select-extended.318906/), I received some fantastic help from expert **MajP**. In his final solution, MajP included a progress meter. I find this progress meter so...

www.access-programmers.co.uk

But every example I have seen is based on knowing the number of steps involved. So the meter is upated for every step or groups of steps in an process of known steps. There is no way to determine how long a query or report will take to load. You can present a custom hourglass with fictitious or continuous duration if that makes you fell better.

 

[The_Doc_Man]

Agree with tvan and plog...

While an external database such as SQL Server (and there are others) can give you a "partial completion status" for a given query, a report is a different story altogether. Access is single-threaded at the user level so while it is "driving" your report, there is no hook you can easily activate to drive a progress bar. I.e. you don't have control while that is going on, so your code might not be ABLE to run. If I recall correctly, there is not even a hook you can exploit access to even DETERMINE progress.

With single-threaded code, that report is an "all or nothing at all" type of setup. You start it by opening the report - and that is ALL YOU CAN DO until the report is finished. You didn't say explicitly, but if your DB is a native Access BE file shared somewhere, that is accessed by a single-threaded internal DB engine (Jet/Ace) as well. In which case you have no condition where you can even evaluate completion. Or, more precisely, for a native Access DB, by the time you COULD evaluate your progress, it would be 100%.

Among other things, you might say "well, let me put code in one of the report sections to drive a progress meter." OK, that might work - except that by the time you deal with that section's progress code, the section is already displayed, which means that displaying the meter is already too late. I.e. by the time you want to show progress, at least part of the report is already visible. Which one do you want to show? I'm guessing that the users would want to see the report. AND there is the issue of where you would put that progress meter unless you attempt to use the general low-resolution meter that is normally at the bottom of the report frame. It is accessible via SysCmd functions. Here's a link to get you started if that's the way you wanted to go.

Application.SysCmd method (Access)

Office VBA reference topic

learn.microsoft.com

Because Access was built for a small-business environment, it lacks a lot of amenities that bigger database systems might offer. Therefore, don't be surprised if it won't do exactly what you want.

EDIT: Their posts crossed with mine, so I add that I agree with MajP and GPGeorge's viewpoints as well.

 

[nector]

But I will not give up on this one no matter what, a solution will be found soon, I had the combo boxes problem before where 3 million records are on a table but the combo box can only load 65500 record, by trying the in initialized code on the combo box now all my forms load on a maximum of 3 seconds while the POS one takes 4 seconds.

I think its time to think outside the box again otherwise If I just accept , then the world not see any new things all

Many thanks to you all

 

[MajP]

Among other things, you might say "well, let me put code in one of the report sections to drive a progress meter." OK, that might work - except that by the time you deal with that section's progress code, the section is already displayed, which means that displaying the meter is already too late. I.e. by the time you want to show progress, at least part of the report is already visible.

Maybe you could do this. Prior to running the report count the number of records. Then update the progress meter on the on paint event. A report paints each line in sequence with some rollback for formatting.

 

[The_Doc_Man]

I think its time to think outside the box again otherwise If I just accept , then the world not see any new things all

I appreciate your attitude, but the question remains as to whether a progress meter is a wasted extra effort since by the time you get to a point where you COULD show/drive such a meter, you are already looking at part of the report. At which point, does the meter represent a true added value or a true added nuisance?

It would help if you could identify whether the tables reside in a monolithic file with the reports, or are they split into native Access front-end and back-end, or is it an Access front-end and an SQL engine as the back end. Because the potential approach paths to solve your problem will depend on the type and location of the BE portion of the database file(s).

 

[ebs17]

The primary concern would be that the processes are sufficiently fast so that a progress display is only perceived as an annoying flickering and is therefore unnecessary.

Proper queries do not provide entry points for tracking progress. Of course you can divide a loop of 100,000 rounds into 100 progresses, but that makes it even slower.

First I would always look for the critical points of slowness and strive for improvements there.
So that a user is informed about an upcoming wait, a pop-up form with a message could be displayed at the beginning and then removed again at the end. If you know the weaknesses of your program and can estimate the length of the wait, you can pass on such information to the user. If he knows that there is enough time to get a new coffee, the time would be better used than looking at a bar that is growing.

 

[The_Doc_Man]

If he knows that there is enough time to get a new coffee, the time would be better used than looking at a bar that is growing.

You reminded me of a moment where I "optimized" another program that took a predictable 17 minutes to finish its processing, but I got it down to eight seconds by finding the bottleneck and changing the way it did things. So after I fixed the delay, one of the members of the project said, "I heard about how you fixed the conversion process. Thanks for nothin." To which I had to ask, "B., what did I ever do to you?" And his reply, "You took away a guaranteed 17 minute coffee break." Proof that no good deed goes unpunished.

 

[ebs17]

Adding an additional break is easier than shortening the runtime in a process.
You can also simply install a progress bar there and you could let the user decide beforehand how long the break should be.

 

[isladogs]

Although not the standard method, you can run a progress meter on a timer.
See my example app: Progress Bar (isladogs.co.uk)

I really wouldn't recommend you try to do so for this purpose. Instead show a message to indicate something is happening
e.g. Loading Report . . .

Do bear in mind that running a progress bar takes a small but measurable time so slightly increases the time for other events to complete

 

[tvanstiphout]

There is no way to determine how long a query or report will take to load

And even if you did: there is no way to have Access call you or yield to you so you can update your progress bar.