Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

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

    <pre>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</pre>

    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

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

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

  9. #9
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    LOL

    You're right, Mark. No one would care what the status bar says in the middle on of the night. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    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

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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:

    <pre>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</pre>


    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

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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:
    <pre>Public Declare Function apiGetTickCount Lib "kernel32" _
    Alias "GetTickCount" () As Long</pre>

    Update function:
    <pre>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</pre>


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •