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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-14-2020, 09:35 AM   #1
pooldead
Newly Registered User
 
Join Date: Sep 2019
Location: Dallas, TX
Posts: 31
Thanks: 11
Thanked 0 Times in 0 Posts
pooldead is on a distinguished road
Override "Run Query" Status Bar Message

I am attempting to override the default "Run Query" status bar that appears with my own to show overall progress.

Code:
            Set rs1 = CurrentDb.OpenRecordset(sqlStr)
                If rs1.RecordCount = 0 Then
                    Resume Next
                Else
                    rs1.MoveLast
                    rs1.MoveFirst
                    recTotal = rs1.RecordCount
                    Application.SysCmd acSysCmdInitMeter, "Progress:", recTotal
                    Do While Not rs1.EOF
                                <query code>
                        recProcess = recProcess + 1
                        SysCmd acSysCmdUpdateMeter, recProcess
                        rs1.MoveNext
                    Loop
                    On Error Resume Next
                End If
SysCmd acSysCmdRemoveMeter
This does work, however the "Run Query" part still flashes behind mine. Can someone provide guidance on how to display only my progress meter?

pooldead is offline   Reply With Quote
Old 01-14-2020, 09:46 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,280
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Override "Run Query" Status Bar Message

Hi. Just a thought... If you can't remove/replace it, maybe you could consider just turning the whole thing off.
__________________
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 offline   Reply With Quote
Old 01-14-2020, 09:48 AM   #3
pooldead
Newly Registered User
 
Join Date: Sep 2019
Location: Dallas, TX
Posts: 31
Thanks: 11
Thanked 0 Times in 0 Posts
pooldead is on a distinguished road
Re: Override "Run Query" Status Bar Message

I have thought about that and could, but my query can take a little time to run, and I'd like to give my users something so that they know it didn't freeze or break.

pooldead is offline   Reply With Quote
Old 01-14-2020, 10:11 AM   #4
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,518
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Override "Run Query" Status Bar Message

The only thing I've ever been moderately happy with for this is a custom popup form. Most suggestions seem to cause updating issues regardless of updating settings I've tried. You could have a popup form and alter the message and a bar length in chunks just before every loop, as long as you repaint this form. That's the only thing I've ever done that I was satisfied with.
If I may, your use of Resume Next is unnecessary in that code. About the only time it should be used outside of an error handler like that is if testing for the existence of certain objects or properties of objects. Also, IIRC it is possible (though rare) to get a negative record count and using your approach could result in unexpected issues. If the loop count is not variable, then you don't need the record count. If it is, then you'd need not only the count, but to recalculate the status bar length if you have one, and apply that after getting the count. Based on those comments (except for status bar length) I would suggest
Code:
Set rs1 = CurrentDb.OpenRecordset(sqlStr)
If Not (rs1.BOF And rs1.EOF) Then
  rs1.MoveLast
  rs1.MoveFirst
  recTotal = rs1.RecordCount
  Application.SysCmd acSysCmdInitMeter, "Progress:", recTotal
  Do While Not rs1.EOF
     <query code>
     recProcess = recProcess + 1
     SysCmd acSysCmdUpdateMeter, recProcess
     rs1.MoveNext
  Loop
End If
EDIT - forgot to mention that you should SET any instantiated objects to Nothing. AFAIC, it's debatable as to whether or not you need to close a recordset as well. Maybe you're doing that later in the code - can't tell.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 01-14-2020, 10:12 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,280
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Override "Run Query" Status Bar Message

Quote:
Originally Posted by pooldead View Post
I have thought about that and could, but my query can take a little time to run, and I'd like to give my users something so that they know it didn't freeze or break.
You could use a popup form as an alternative. Again, just a thought...


Edit: Oops, looks like I was too slow...
__________________
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 offline   Reply With Quote
Old 01-14-2020, 10:37 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,961
Thanks: 120
Thanked 3,331 Times in 3,005 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Override "Run Query" Status Bar Message

There are plenty of examples of progress bars you can add to a form.
Here's mine as one example: http://www.mendipdatasystems.co.uk/p...bar/4594424316

Normally progress bars are used where you have several events occurring in sequence. If you are just running one very slow query, that won't work though you could base the progress bar on a timer as in my example.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
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.


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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam (Terry Jones - RIP)
isladogs is offline   Reply With Quote
Old 01-14-2020, 02:35 PM   #7
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 631
Thanks: 31
Thanked 112 Times in 101 Posts
sxschech is on a distinguished road
Re: Override "Run Query" Status Bar Message

Not sure if you already thought about highlighting text instead of progress meter? Although the method I use isn't foolproof such as if the code has to be halted before it has a chance to restore the color...what I do is either change the fore color of the (button text/ combobox/textbox) to vbred and then back to vbnormal when the time consuming section of code is complete. Sometimes also change the wording of the text if appropriate to indicate it is running/processing.

Code:
Me.cboActivity.ForeColor = vbRed
---
---
Me.cboActivity.ForeColor = vbNormal

sxschech is offline   Reply With Quote
Old 01-14-2020, 04:28 PM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,203
Thanks: 3
Thanked 475 Times in 468 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Override "Run Query" Status Bar Message

To stop the run query status message popping up, don't use

Code:
Docmd.Runquery ...

use

Code:
db.execute (SQL string)
Cronk is offline   Reply With Quote
Old 01-21-2020, 12:00 PM   #9
pooldead
Newly Registered User
 
Join Date: Sep 2019
Location: Dallas, TX
Posts: 31
Thanks: 11
Thanked 0 Times in 0 Posts
pooldead is on a distinguished road
Re: Override "Run Query" Status Bar Message

Thanks all! I decided it wasn't worth spending so much time on and just changed "Progress:" to "Run Query" to provide a bit less flashing.
pooldead is offline   Reply With Quote
Old 01-21-2020, 12:15 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,280
Thanks: 63
Thanked 1,683 Times in 1,638 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Override "Run Query" Status Bar Message

Quote:
Originally Posted by pooldead View Post
Thanks all! I decided it wasn't worth spending so much time on and just changed "Progress:" to "Run Query" to provide a bit less flashing.
Hi. 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 offline   Reply With Quote
Reply

Tags
run query , status bar

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
If "TableA.FieldA" exists in "TableB.FieldB" then return "1" else "2" shdale Queries 8 10-29-2014 05:20 AM
Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? derekbeck Queries 5 03-26-2012 03:23 PM
Showa "loading" message while a report/query is still running mab9 Modules & VBA 4 11-02-2006 10:26 AM
Remove "Run Query" status when running query? krussell Forms 1 01-14-2003 02:36 PM
Remove "Run Query" status when running query? krussell Queries 0 01-13-2003 08:27 AM




All times are GMT -8. The time now is 02:45 AM.


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 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World