Change The Status Bar text when running a query (Access 2000)

Topic

New Reply

I have a macro that runs 4 queries. I want the status bar to display the query name that’s currently running. How can I do this? If it can’t dynamically be changed, I’d like to have the status bar be statically changed, like setting that property in the macro.

Any ideas?

Thanks.

Reply | Quote

Replies

Use the Echo action before each query is called in your macro to put the name of that query in the statusbar.

Reply | Quote

Thanks, Charlotte.

That works, I knew there was something because I’d seen other databases do this before.

How can I get the status bar text to remain as I set it while the query is running? It changes and then when the query runs, the status bar text changes to Run Query and a progress bar.

Reply | Quote

As far as I know, you can’t. I generally pop up a form and manipulate a label on it for that purpose, but you pretty much have to do that from code.

Reply | Quote

I’d recommend using code rather than macro. You can use Access Application SysCmd function to set and then clear status bar text. Example:

Public Sub RunQuery()

    Dim strQry(1 To 4) As String
    Dim n As Integer
    Dim intReturn As Integer
    
    strQry(1) = "Alphabetical List of Products"
    strQry(2) = "Invoices"
    strQry(3) = "Products by Category"
    strQry(4) = "Order Details Extended"
    
    For n = 1 To 4
        intReturn = SysCmd(acSysCmdSetStatus, strQry(n))
        DoCmd.OpenQuery strQry(n)
        intReturn = SysCmd(acSysCmdClearStatus)
    Next n

End Sub

Above example uses some queries in Northwind database. They run too fast to really see status bar text, but tested with a breakpoint & worked correctly. I assume these queries you are running take a while to open or execute (if action queries). I don’t see any obvious way to use the SysCmd function in a macro, therefore recommend you ditch the macro in favor of a sub procedure. If you are calling the macro from a toolbar then you’d have to change procedure from a sub to a function.

HTH

Reply | Quote

Mark,

Yeah that sounds like it will work.

Yes, my 4 queries take 1 1/2 hours to complete and I’m going to be adding 2 more. These queries are updating fields in a table. I want these queries to run automatically overnight so that’s why I put them in a macro. Will call the macro Autoexec and then have a scheduled task run the database that contains the autoexec.

If I were to create this subprocedure, how can I call this so it will run during the night? I could put it on a form in a button, but then someone has to press the button to make it run. Can you call subprocedures from macro’s? Or I suppose I’d change it to a function, then call the function from the autoexec macro.

Is that right?

Sarah

Reply | Quote

Yep, you can create a function in a module, say

Function fncStart()
…
End Function

Then you can call it from you AutoExec macro. Use fncStart() as argument to the RunProcedure action.

Another possibility is to have a form opened automatically when the database opens (set it in Tools/Startup…)
Forms have an OnTimer event and a TimerInterval property. The OnTimer event fires automatically every TimerInterval milliseconds (unless TimerInterval=0, then it’s inactive). In the OnTimer event handler, you can check the current time (and date) and decide whether to do something.

Reply | Quote

Hans answered your question on how to call a procedure from a macro (have to change it from a sub to a function). My only question is, if this macro is going to run automatically, in the middle of the night, who cares what the status bar says?? Who’s gonna be around to see it?? Just wondering….

Reply | Quote

LOL

You’re right, Mark. No one would care what the status bar says in the middle on of the night.

But until I get everything working correctly, seeing the status bar text change is just for me during the day. Since it takes so long for these queries to run, I want to be able to start the queries on a test pc and then come back and see how far along it is, what query it’s working on. That’s all.

And by the way, running the queries from the function and setting the status bar text works good, but the status bar text only stays for a short bit, and then changes to Run Query.

Sarah

Reply | Quote

That’s what I was talking about. The statusbar text is useful for notifications when you’re looping through a process in code, but if you just run a query like that, you can’t suppress the run query progress bar as far as I know.

Reply | Quote

I tried this with update queries and you’re correct, the “Run Query” still appeared in the status bar. As an alternative to running action queries directly, you can use the DAO Database Execute method, as shown in this example:

Public Function RunUpdateQuery()
On Error GoTo Err_Handler

    Dim strQry(1 To 4) As String
    Dim db As DAO.Database
    Dim n As Integer
    Dim intReturn As Integer
    
    Set db = CurrentDb
    
    strQry(1) = "qryTest1"
    strQry(2) = "qryTest2"
    strQry(3) = "qryTest3"
    strQry(4) = "qryTest4"
    
    For n = 1 To 4
        intReturn = SysCmd(acSysCmdSetStatus, strQry(n))
        db.Execute strQry(n), dbFailOnError
        intReturn = SysCmd(acSysCmdClearStatus)
    Next n

Exit_Function:
    Set db = Nothing
    intReturn = SysCmd(acSysCmdClearStatus)
    Exit Function
Err_Handler:
    Resume Next
    
End Function

NOTE: You have to make sure a reference is set to the DAO 3.6 Object Library (Tools menu>References in VB Editor). When you use Execute method, you do not get any of the standard warnings you get when you run action query directly. So you may want to test your update queries thoroughly before “automating” update procedure.

HTH

Reply | Quote

To reply further, an alternative to running action query is to update recordset in code. The SysCmd function can then be used to display customized progress meter in status bar in addition to text. This method allows you to identify and track progress of update procedure. Example:

This should go in Declarations section of code module:

Public Declare Function apiGetTickCount Lib "kernel32" _
 Alias "GetTickCount" () As Long

Update function:

Public Function UpdateTable()
On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSql As String
    Dim strTbl As String
    Dim strStatusTxt As String
    Dim lngCount As Long
    Dim intReturn As Integer
    Dim lngStartTime As Long
    Dim lngEndTime As Long
    Dim dblElapsedTime As Double
    Dim strMsg As String
    
    Set db = CurrentDb
    strTbl = "TABLENAME"
    strSql = "SELECT * FROM " & strTbl & " ;"
    strStatusTxt = "Updating " & strTbl & " table...."
    lngCount = 1
    lngStartTime = apiGetTickCount()
    
    Set rst = db.OpenRecordset(strSql)
    With rst
        .MoveLast
        .MoveFirst
        intReturn = SysCmd(acSysCmdInitMeter, strStatusTxt, .RecordCount)
        Do Until .EOF
            .Edit
            !FLD1 = "Text 1"
            !FLD2 = "Text 2"
            .UPDATE
            intReturn = SysCmd(acSysCmdUpdateMeter, lngCount)
            lngCount = lngCount + 1
            .MoveNext
        Loop
        .Close
        intReturn = SysCmd(acSysCmdRemoveMeter)
    End With
    
    'Timer & MsgBox for test purposes only:
    lngEndTime = apiGetTickCount()  'milliseconds:
    dblElapsedTime = (lngEndTime - lngStartTime) / 1000
    strMsg = strTbl & " table has been updated." & vbCrLf & vbCrLf & _
             "Elapsed Time: " & dblElapsedTime & " seconds."
    MsgBox strMsg, vbInformation, "TABLE UPDATED"

Exit_Function:
    Set db = Nothing
    Set rst = Nothing
    Exit Function
Err_Handler:
    Resume Next

End Function

You would use a separate function in place of each update query, modified as necessary. You can then use macro to run functions in sequence. Note the use of Windows GetTickCount function to time procedure and MsgBox are for test purposes only, you can comment these out when running code on autopilot. You can use same function to time action queries.

While this method has advantage of displaying name of table being updated (or other text) in addition to progress meter, there

Reply | Quote