Progress Bar in MS Access (1 Viewer)

GregoryWest

Registered User.
Local time
Today, 14:20
Joined
Apr 13, 2014
Messages
161
I have to run through a large (read several hundred thousand records) import file. What I was trying to do is just show the user a progress bar, so they know something is happening, while they wait. I am using this code:


Set rs_datain = DBEngine(0)(0).OpenRecordset(strsqlin)
rs_datain.MoveLast
xx = SysCmd(acSysCmdInitMeter, "working...", rs_datain.RecordCount)
MsgBox rs_datain.RecordCount
rs_datain.MoveFirst
curr_rec = 0
Do While Not rs_datain.EOF
tempstr = rs_datain!InputText
rectype = Mid((rs_datain!InputText), 14, 2)
curr_rec = curr_rec + 1
xx = SysCmd(acSysCmdUpdateMeter, curr_rec)
Select Case rectype



But when it get to the xx = SysCmd(acSysCmdUpdateMeter, curr_rec) command I get a 7952 'You made an illegal function call' error. What am I doing wrong???
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:20
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a wild guess but maybe try it this way?
Code:
xx = SysCmd(acSysCmdUpdateMeter, CStr(curr_rec))
 

GregoryWest

Registered User.
Local time
Today, 14:20
Joined
Apr 13, 2014
Messages
161
Not sure if this will help anyone else. I did find my problem. Seems I closed the status bar inside the loop I was counting through. Explains why worked for for iteration 1 but failed on 2.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:20
Joined
Oct 29, 2018
Messages
21,358
Not sure if this will help anyone else. I did find my problem. Seems I closed the status bar inside the loop I was counting through. Explains why worked for for iteration 1 but failed on 2.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:20
Joined
Sep 12, 2006
Messages
15,614
if you have over 100000 records I would update the progress bar every 1000 of so, maybe every 5000. Definitely not after every record.

Code:
if curr_rec mod 1000 = 0 then
    update progress bar
end if
 

GregoryWest

Registered User.
Local time
Today, 14:20
Joined
Apr 13, 2014
Messages
161
Interesting. Why would you not do it after every record? Is there a performance issue. or just a visual one?


if you have over 100000 records I would update the progress bar every 1000 of so, maybe every 5000. Definitely not after every record.

Code:
if curr_rec mod 1000 = 0 then
    update progress bar
end if
 

isladogs

MVP / VIP
Local time
Today, 19:20
Joined
Jan 14, 2017
Messages
18,186
Whilst progress bars are very useful, the time taken to redraw this will increase the time taken to run your procedure.
For that reason, you might find updating after a specified number of records give sufficient info for the end user without adversely affecting performance too much
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:20
Joined
Sep 12, 2006
Messages
15,614
The progress bar generally has 100% steps, so if you are processing 200K records, there is no point refreshing it more frequently than every 2000 records.

The other thing is to show an hourglass

docmd.hourglass true turns it on
docmd.hourglass false turns it off
 

Micron

AWF VIP
Local time
Today, 15:20
Joined
Oct 20, 2018
Messages
3,476
so what does the fix look like? I thought the initialization was supposed to be something like

SysCmd acSysCmdInitMeter, "working...", rs_datain.RecordCount

but you are assigning initialization to a variable?
 

smig

Registered User.
Local time
Today, 21:20
Joined
Nov 25, 2009
Messages
2,209
I did not read all replays

I made my own progress bar.
it's a simple text box, with Blue background and no text that I change it's width based on the number of steps and current step.
I repaint the form every time I want to update the progress bar.

If I have to read 1,000,000 records I won't repaint every record but every 10,000 or so.

to make it look more beautiful I put it inside an immersed empty long fixed size text box.
 

Micron

AWF VIP
Local time
Today, 15:20
Joined
Oct 20, 2018
Messages
3,476
I have done that; colored label over a textbox, altering it's width. As a fluid meter, I find the repainting causes a lot of screen flicker regardless of what else I've tried to control that. You should be ok with less frequent repaints.
 

apr pillai

AWF VIP
Local time
Tomorrow, 00:50
Joined
Jan 20, 2005
Messages
735
Here is some sample Code, may be helpful to correct yours:
Code:
DoCmd.Hourglass True

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
rst.MoveLast
TotalRecords = rst.RecordCount

rst.MoveFirst
Do While Not rst.EOF
  With rst
    Quantity = ![Quantity]
    UnitRate = ![UnitPrice]
    Discount = ![Discount]
    ExtendedValue = Quantity * (UnitRate * (1 - Discount))

    .Edit
    ![ExtendedPrice] = ExtendedValue
    .Update

    If .AbsolutePosition + 1 = 1 Then
       x = SysCmd(acSysCmdInitMeter, "process:", TotalRecords)
    Else
      'a delay loop to slow down the program       
      'to view the Progress Meter in action.      
      'you may remove it.      
'=================================================
      xtimer = Timer
      Do While Timer < xtimer + 0.02
        Doevents
      Loop
'=================================================

      x = SysCmd(acSysCmdUpdateMeter, .AbsolutePosition + 1)
    End If

   .MoveNext
  End with
Loop
rst.Close
x = SysCmd(acSysCmdRemoveMeter)
DoCmd.Hourglass False

You may find more details on this link: Progress Meter
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:20
Joined
Feb 28, 2001
Messages
26,999
Actually, I put one rectangle over another rectangle (as in, same Top, Left, and Height) and use BringToTop on the top rectangle. The bottom rectangle has a transparent background and is the full width of the form (or as much of that width as I want it to be.) The top rectangle has a colored background that contrasts with the form's background. The top rectangle starts at .Width = 1 and I scale it by determining percent completion and making the top rectangle's .Width that percentage of the bottom rectangle's .Width - and there IS the issue that repainting that too often for a long list of things to track that way can cause flickering due to frequent redraw/repaint. So I usually had a little "marker" in the subroutine that would compute where the progress bar was in TWIPS and if that didn't change, I didn't do a repaint.
 

Micron

AWF VIP
Local time
Today, 15:20
Joined
Oct 20, 2018
Messages
3,476
An experienced code writer has to be very confident indeed that there are records to move last to without ever checking if the recordset has any records...
I would not do so, but we all code differently sometimes I guess.
 

apr pillai

AWF VIP
Local time
Tomorrow, 00:50
Joined
Jan 20, 2005
Messages
735
An experienced code writer has to be very confident indeed that there are records to move last to without ever checking if the recordset has any records...
I would not do so, but we all code differently sometimes I guess.



Thanks for the tip Micron. The code was written for demo purposes only. Without some data in the table the demo will not work. The database is provided for download as well in the page: www.msaccesstips.com/2007/12/progress-meter.html



Sent from my iPhone using Tapatalk
 

Micron

AWF VIP
Local time
Today, 15:20
Joined
Oct 20, 2018
Messages
3,476
Without some data in the table the demo will not work.
With respect to your code I'd say that is sort of my point. The code shouldn't balk regardless if the reason is that there's no table data, or there is but the recordset is empty. A recordset may get populated every time for umpteen times that it's set - it only takes 1 anomaly for it to be empty, regardless of how many tables contain data.

I got in the habit of

If Not (rs.EOF And rs.BOF) Then

while others say if either is True, then there are no records thus one only needs to check for one or the other.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:20
Joined
Sep 12, 2006
Messages
15,614
With any operation that may error, you need to include error handling.

I tend to count the records with a dcount("*","queryname") first. If there are no records, then I never open the recordset.


It may be more efficient to open the recordset, and do a movelast instead. I don't know, to be honest.

Code:
on error goto fail  'always!

recordcount = dcount("*","queryname")
if recordcount=0 then
   suitable message
   exit code
end if

set rs= openrecordset()
while not rs.eof
    ,,,
    ,,,
    rs.movenext
wend
rs.close
 

Micron

AWF VIP
Local time
Today, 15:20
Joined
Oct 20, 2018
Messages
3,476
It may be more efficient to open the recordset, and do a movelast instead. I don't know, to be honest.
The potential problem with recordset count is that it can behave in one fashion for DAO and in anoither for ADO. An ADO recordset count can return a value of -1 Thus > 0 is not a foolproof test (or so Allen Browne says, and whatever he says is pretty much good enough for me). So, rather than code one way for ADO and another for DAO, you can't go wrong with testing If Not (rs.BOF And rs.EOF) Then because if both are true, there are no records regardless if ADO or DAO.

Then Albert Kalli (sp?) who is right up there with AB says you don't need to test for both, i.e. if one is true, the other is also. That's a new one for me and probably only applies when the rs has just been initialized as it is possible to MoveNext into EOF. I'll probably stick with my current habit, which is that if BOF and EOF are true, there are no records, thus don't move first, last, or do anything with that rs.

In the end, we all do what we think is right - until it isn't.
 

axsprog

Registered User.
Local time
Today, 12:20
Joined
Jun 11, 2019
Messages
15
Has any of you used just a textbox on a form and used conditional formatting (it has an option for progress bar) and let that be the engine that drives the meter? Now I do not know how well it would work counting a hundred thousand records, but for value and progression representations for dashboards, and such , it's awesome.
 

Micron

AWF VIP
Local time
Today, 15:20
Joined
Oct 20, 2018
Messages
3,476
You're not saying you have done this are you? I'm trying to envision how you make a textbox information bar progress during some execution because the control has to be bound AFAIK, and what would you bind it to that can reflect an ever increasing number? Sounds intriguing but also sounds improbable.
 

Users who are viewing this thread

Top Bottom