Solved Override "Run Query" Status Bar Message (1 Viewer)

pooldead

Registered User.
Local time
Yesterday, 23:57
Joined
Sep 4, 2019
Messages
108
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?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:57
Joined
Oct 29, 2018
Messages
13,678
Hi. Just a thought... If you can't remove/replace it, maybe you could consider just turning the whole thing off.
 

pooldead

Registered User.
Local time
Yesterday, 23:57
Joined
Sep 4, 2019
Messages
108
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.
 

Micron

AWF VIP
Local time
Today, 02:57
Joined
Oct 20, 2018
Messages
3,471
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:57
Joined
Oct 29, 2018
Messages
13,678
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...
 

isladogs

CID VIP
Local time
Today, 07:57
Joined
Jan 14, 2017
Messages
14,211
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/progress-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.
 

sxschech

Registered User.
Local time
Yesterday, 23:57
Joined
Mar 2, 2010
Messages
682
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
 

Cronk

Registered User.
Local time
Today, 18:57
Joined
Jul 4, 2013
Messages
2,454
To stop the run query status message popping up, don't use

Code:
Docmd.Runquery ...


use

Code:
db.execute (SQL string)
 

pooldead

Registered User.
Local time
Yesterday, 23:57
Joined
Sep 4, 2019
Messages
108
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:57
Joined
Oct 29, 2018
Messages
13,678
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.
 

Users who are viewing this thread

Top Bottom