We help IT Professionals succeed at work.

Change Status Bar Text when running an Action Query

MacRena
MacRena asked
on
Hello Experts,

When I run an Action Query, I would like to replace the default "Run Query XXX__________" in the Status Bar with a message similar to when I use the Process Meter in an iterative loop, for instance:
          SysCmd(acSysCmdInitMeter, "Updating Records", intRecCount)
which gives me "Updating Records XXX__________" in the Status Bar.

I am using 3 queries:
          DoCmd.OpenQuery ("qryUpdateRecords")
          DoCmd.OpenQuery ("qryMarkRecordsForDelete")
          DoCmd.OpenQuery ("qryDeleteRecords")
And I would like to have a distinct message appear in the Status Bar for each Query.  

These are huge batch updates (up to 350,000 records), so I can't do it in code, and the processor gets pegged, so the system knots up so badly that the screen doesn't refresh but every 4 or 5 minutes.  I got the whole process to finish in 35 minutes, but the 3 Queries all report "Run Query XXX__________" in the Status Bar, and I would like to show which query is running.

Any ideas?

Thanks,
Mac

Comment
Watch Question

I have found that you cannot update any display WHILE the query is running.  As such, you cannot have any kind of progress meter.  You could however, change the message to indicate the "progress" of the entire operation between the queries.

I have a lengthy maketable and report sequence for which I display different Popup forms, at points along the way.  You could make 1 that says "Update Query ... processing please wait."

The next would say "Update Query   .... done.  Deleting Excess Records ... processing.. please wait."

etc.
Show or hide the forms via code BETWEEN the cmds for the queries.

Best you can do, I'm afraid.
 
btw, my forms actually include pictures of a cute little dog in different poses; I have heard the users actually refer to different stages as "he's lying down" or "it will be a while, he's still sitting up".
 
Put a

     doevents

somewhere right after you change the message in the status bar and before the query is executed.  

<code to change the status bar>
DoEvents
DoCmd.OpenQuery ("qryUpdateRecords")

<code to change the status bar>
DoEvents
DoCmd.OpenQuery ("qryMarkRecordsForDelete")

<code to change the status bar>
DoEvents
DoCmd.OpenQuery ("qryDeleteRecords")

Author

Commented:
Hello guys,

Thanks for replying.

Sorry I didn't come back sooner - I haven't gotten any of these email notifs (I read in the Lounge that the notifs aren't working so I checked back).  

So I wonder if you will get one from this Q now...

I do have DoEvents between the query calls, but the text is still "Run Query".

I think Volibrawl is correct that you can't change the Status Bar text when running a Query.

I was hoping someone knew of an undocumented syntax to call the query while passing in the Display Text (that would replace the default "Run Query" text) like maybe...

DoCmd.OpenQuery queryname[, view][, datamode][, displaytext]

I will continue to research it and come back here to award the points.

Thanks again for replying.

Mac
You can use this to change the Status Bar text.

SysCmd acSysCmdSetStatus, "Text goes here"

Then, use this to revert back to Access controlling the Status Bar text.

SysCmd acSysCmdClearStatus

You could set the Status Bar text in between each query running. That should do it.

Thanks!

Joe

Author

Commented:
Hmmm,
SysCmd acSysCmdSetStatus, "Text goes here"
It won't show status, but it will do what I need.
Thanks, Joe. I'll try that.  
What status are you looking for? Something like this in the Status Bar area:

Record(s) 1 of 350,000 Updated

If so, please let me know. I might have something that can do it for you. It would be code, but I don't think it will degrade the processor's speed very much while you are running your queries.

Thanks!

Joe
Hi Joe and Mac,

I am interested in that code and will chip in another 50 points for it.

I am assuming you are saying you may have code that displays a "pseudo progress meter" WHILE a query (ie an update, delete or maketable) is running.




Author

Commented:
joekendall,
Yes, "Record(s) 1 of 350,000 Updated" would work even better.  

Volibrawl, I'll move up the points to 100 for your 50 (you've helped me in the past so call it further payment on previous help)

Mac

(well, I only have 49, so I'll change to 99 today and to 100 tomorrow)
I do have code, but it did not work properly. The query runs on a very high priority. Therefore, my code will not run until the query is done.

I would have to use some API calls to give my code a higher priority (therefore, the Windows messages would be processed before the Query's messages are) so it would run while the query is running. I haven't done that, and I'm not sure how bad the performance would be if I did so. I believe it would degrade the performance, but I don't know.

If you are still interested, I can try it. Then, I can report back with the results.

One question, is your database shared on a network?

Thanks!

Joe

Author

Commented:
Joe,

I was wondering how that would work: interrupting a query to refresh the status bar.  I'll look forward to it, if you have time to do it for me.

No, it is for a stand-alone machine.

Mac
Let me try to explain as best as I can. Windows runs off of messages. You have messages to paint the screen, close a window, etc.

These messages have a priority. Low priority messages aren't processed until all of the high priority messages are processed.

It seems (key word here is seems) that when Access runs a query, the query has a higher priority than some of the Windows' messages. For instance, I was trying to create a timer that would fire every minute and update the Status Bar with the number of records processed at the time. The timer would not start until the queries had ran.

I will see what I can do, but I can't promise anything. Even if it does work, I would not consider this a solution to use often. Nor would I consider it the best solution, but it may be what you need. If it can be done, you will want to test it before incorporating it into your processes.

Thanks!

Joe

Author

Commented:
upgraded points to 100

Author

Commented:

Author

Commented:
Joe,
If the task becomes greater than your schedule will allow, then please don't feel obligated.  But if you would also like to have the results then, by all means, procede with my thanks.
Mac
Mac:

I tried to get it to work. Could not. Sorry!

It is as if there are 2 processes running. One seems to be the query and the other seems to be my code to update the Status Bar.

I can get either one to work but not both at the same time. I am not sure why. I'm not saying it is impossible to do it. I'm just not 100% sure how it needs to be done.

If changing the name of the Query each time in the Status Bar will work, I would stick with that. Feel free to reduce the points back to what you had.

Sorry for the expectations being lifted with no result.

Thanks!

Joe
First of all, I am not sure I like this answer, although it does work.

Since you can call a Public Function from an SQL, and since it has to be evaluated for every record, you can use the public function to update your status display.  

This example updates a "Used In Test" field in a Words Table used in a learn Spanish program, and it updates a label rather than a status bar.  What I do not like is the use of the globals, but I could not think my around it.

Private Sub Whatever
    Dim SSQL As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * From Words")
   
    lngCount = rs.RecordCount
    lngCounter = 0
    SSQL = "UPDATE Words SET Words.UsedInTest = True " & _
           "WHERE ShowProg(" & _
           lngCounter & _
           ")"
    DoCmd.RunSQL SSQL
end sub

In a .bas module:
Global lngCount as long
Global lngCounter as long

Public Function ShowProg() As Boolean
    Dim x As Long
    ShowProg = True
    lngCounter = lngCounter + 1
    Forms.form1.lblStatus.Caption = "Processing " & lngCounter & " of " & lngCount
    DoEvents
End Function


My apologies.  I sent code from an earlier test project which still had a problem.  Do not pass lngCounter as a parameter.  To do so means it will remain at it's initial value.  

                     Private Sub Whatever
                        Dim SSQL As String
                        Dim rs As DAO.Recordset
                        Dim db As DAO.Database
                        Set db = CurrentDb
                        Set rs = db.OpenRecordset("Select * From Words")
                       
                        lngCount = rs.RecordCount
                        lngCounter = 0
                        SSQL = "UPDATE Words SET Words.UsedInTest = True " & _
                               "WHERE ShowProg()"
                        DoCmd.RunSQL SSQL
                     end sub

                     In a .bas module:
                     Global lngCount as long
                     Global lngCounter as long

                     Public Function ShowProg() As Boolean
                        Dim x As Long
                        ShowProg = True
                        lngCounter = lngCounter + 1
                        Forms.form1.lblStatus.Caption = "Processing " & lngCounter & " of " & lngCount
                        DoEvents
                     End Function
I should also add that in executing some queries Access will hit the public function more than the anticipated number of times.  It takes some tinkering with, and, like I said, I'm not sure I even like the answer.  But I have used it once or twice when it was essential to show some kind of progress bar.  I would much rather handle something like this in code updating the status bar or some kind of status label with Doevents.  But you said code is not an option.

In fact, having posted all this, I wish I hadn't.  You *can* update a status from a public function during execution of a query, but I'm telling you it takes a lot of tinkering to make it work and is probably not worth the trouble.

Also, I should have closed my recordset after getting the reccount in Sub Whatever and never used variable X in function showprog().  Just not my day for answers...
DreamingEagle:

I couldn't get a label on a form to update when running an update query. It may work with other types of queries.

You mentioned doing this in code. Do you have some code that will update the Status Bar while running an Action query?

Thanks!

Joe
DreamingEagle:

I did get your code to work with an Update query. I have 655,000+ records to update, and it only said "Processing 2 of 655,000". It never incremented past 2.

Do you have any ideas?

Thanks!

Joe

Author

Commented:
Thanks, DreamingEagle.  I'll give that a try (your warning is noted).
Mac
The whole thing is to use a public function in the Where clause of the SQL (one which always returns True, of course) and to put a DoEvents in the public function that updates the label or status bar remotely using the forms collection.  The public function must be evaluated, and while it is in control you are free to update labels, status bars, or whatever.  But when I think of all of the complexities of SQL expressions involving joins and whatnot--- exactly when that public function is evaluated, and how many times, it makes this answer as big a can of worms as the problem.  

Author

Commented:
yea, but i'm gonna learn something!
Yeah, I do.  The counter has to be incremented in the public function, and the variable, unfortunately, has to be a global one since the public function has to be in a .bas file.  Also, sometimes, it will come out reading something like Processing 500 or 250, or 1000 of 250 because of unanticipated evaluations of the public function.  I used this in one of my first Access using an append query, but, if you'll think about it, any function in a where clause has to be evaulated.  

But, you know, this is an old xBase trick to show progress in indexing huge files.  In Clipper and Foxpro when you issued an index command, it just went away until the index was finished.  So if you made the index conditional upon the function returning true you could manipulate the screen inside of the function.  Another advantage was that the data file could never be opened with a browse utility because the function was not present.  Only the application containing the function could access the data file.

I used this once when making the transition to Windows programming in Access, but it was messy because of the multiple unexpected hits on the public function and I remember having to increment the counter and divide by three to get the progress bar to come out right.  

But I'm really not sure how else you are going to interrupt the execution of an SQL statement to update anything.  It's kind of like the old xBase index commands.  No one expects you to do anythg but wait until it's finished.  

But this is about a klutzy a workaround as I could frankly imagine and I'm sorry I got us all down this track (unless of course by some wierdness it works out).

Hopefully, someone more knowledgable than I am will provide the *correct* answer here.
DreamingEagle:

Good comments!

Mac:

I tried spawning a seperate thread to do you updating of the Status Bar, but it did not work.

I also tried automating Access through VB, but it did not work either.

I believe there is a way, but I just can't figure it out yet.

Thanks!

Joe

Author

Commented:

Thanks to all.
I ended up using Joe's answer, but the "Text goes here" disappears when the "Run Query" appears.
It does well enough, though.
Thanks, Joe and Volibrawl.


DreamingEagle - I will post points for your excellent effort.


see my question

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20242440

Mac