Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-05-2019, 10:45 AM   #1
GregoryWest
Newly Registered User
 
Join Date: Apr 2014
Location: Winnipeg Manitoba Canada
Posts: 149
Thanks: 11
Thanked 0 Times in 0 Posts
GregoryWest is on a distinguished road
Progress Bar in MS Access

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???

GregoryWest is offline   Reply With Quote
Old 06-05-2019, 10:57 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,980
Thanks: 34
Thanked 714 Times in 697 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Progress Bar in MS Access

Hi. Just a wild guess but maybe try it this way?
Code:
xx = SysCmd(acSysCmdUpdateMeter, CStr(curr_rec))
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-07-2019, 09:53 AM   #3
GregoryWest
Newly Registered User
 
Join Date: Apr 2014
Location: Winnipeg Manitoba Canada
Posts: 149
Thanks: 11
Thanked 0 Times in 0 Posts
GregoryWest is on a distinguished road
Re: Progress Bar in MS Access

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.

GregoryWest is offline   Reply With Quote
Old 06-07-2019, 09:55 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,980
Thanks: 34
Thanked 714 Times in 697 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Progress Bar in MS Access

Quote:
Originally Posted by GregoryWest View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-07-2019, 10:16 AM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,764
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Progress Bar in MS Access

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
Tera (06-07-2019)
Old 06-07-2019, 10:22 AM   #6
GregoryWest
Newly Registered User
 
Join Date: Apr 2014
Location: Winnipeg Manitoba Canada
Posts: 149
Thanks: 11
Thanked 0 Times in 0 Posts
GregoryWest is on a distinguished road
Re: Progress Bar in MS Access

Interesting. Why would you not do it after every record? Is there a performance issue. or just a visual one?


Quote:
Originally Posted by gemma-the-husky View Post
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 is offline   Reply With Quote
Old 06-07-2019, 11:01 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,391
Thanks: 106
Thanked 2,517 Times in 2,311 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Progress Bar in MS Access

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-07-2019, 01:15 PM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,764
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Progress Bar in MS Access

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-07-2019, 06:46 PM   #9
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 711
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Progress Bar in MS Access

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?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-12-2019, 07:10 AM   #10
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 1,942
Thanks: 52
Thanked 131 Times in 122 Posts
smig is on a distinguished road
Re: Progress Bar in MS Access

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.
smig is offline   Reply With Quote
Old 06-12-2019, 09:40 AM   #11
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 711
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Progress Bar in MS Access

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.
Micron is offline   Reply With Quote
Old 06-13-2019, 09:34 AM   #12
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 674
Thanks: 1
Thanked 107 Times in 95 Posts
apr pillai is on a distinguished road
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Progress Bar in MS Access

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
Old 06-13-2019, 09:55 AM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,931
Thanks: 79
Thanked 1,564 Times in 1,452 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Progress Bar in MS Access

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2019, 01:31 PM   #14
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 711
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Progress Bar in MS Access

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.
Micron is offline   Reply With Quote
Old 06-14-2019, 10:38 AM   #15
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 674
Thanks: 1
Thanked 107 Times in 95 Posts
apr pillai is on a distinguished road
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Progress Bar in MS Access

Quote:
Originally Posted by Micron View Post
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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Progress Bar In Ms Access 2003 cheer Forms 1 07-18-2017 01:01 AM
Progress Indicator using Access/VBA aman Modules & VBA 8 07-08-2014 03:51 AM
Access Progress bar for VBA processing? jonathanchye General 3 06-22-2011 06:56 AM
[SOLVED] MS Access Progress Bar drjez General 4 02-10-2005 09:01 AM
The progress bar in ms access benheaven1 Forms 3 12-23-2003 01:53 AM




All times are GMT -8. The time now is 12:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World