如何在MS Access运行查询序列时，显示在状态栏进度 [英] How to show progress on status bar when running a sequence of queries in MS Access

问题描述

我在Windows 7中运行的相当缓慢生成表的顺序和更新的查询在MS Access 2010的宏。我想让它显示上查询其运行状态栏，因为通常的消息运行查询不给查询名称。

I have a macro in MS Access 2010 in Windows 7 which runs a sequence of quite slow Make Table and Update queries. I want it to show on the status bar which query it is running, as the usual message "Run query" does not give the query name.

我写了下面的VBA：

Function RunQueryAndReportStatusWithMsgBox(QueryName As String)
Dim RetVal As Variant
On Error GoTo ErrHandler

PutStatusBarBack
MsgBox "About to run query"
Application.Echo False, "Executing " & QueryName & " ..."
DoCmd.OpenQuery QueryName, acViewNormal, acEdit
On Error GoTo 0
Exit Function

ErrHandler:
Select Case Err
   Case 2501:    ' OpenQuery cancelled by the user pressing escape
      MsgBox "The OpenQuery action for query " & QueryName & " was cancelled by the user."
   Case Else:    ' Another error has occurred.
      ' Display the error number and the error text.
      MsgBox "Error # " & Err & " : " & Error(Err)
   End Select

' Put status bar back to normal.
PutStatusBarBack

End Function

Function PutStatusBarBack()

Dim RetVal As Variant

On Error GoTo ErrHandler

' Put status bar back to normal.
RetVal = SysCmd(5) ' not sure if I need this.
Application.Echo True, ""

On Error GoTo 0
Exit Function

ErrHandler:

' Display the error number and the error text.
MsgBox "Error # " & Err & " : " & Error(Err)

' Put status bar back to normal.
RetVal = SysCmd(5) ' not sure if I need this.
Application.Echo True, ""

End Function

我已经写了宏调用 RunQueryAndReportStatusWithMsgBox 与又将作为参数每个查询，然后我叫 PutStatusBarBack 在宏观的末端。我把警告关闭在开始和结尾。这个作品真的很好 - 就像我希望它

I have written a macro to call RunQueryAndReportStatusWithMsgBox with each query in turn as an argument, and then I call PutStatusBarBack at the end of the macro. I turn warnings off at the start and on at the end. This works really well - just as I want it to.

不过，我不希望每一个查询开始的时间是pressing确定上一个消息框。如果我注释掉 MSGBOX 语句，这是行不通的了。结果是可变的。有时，它显示了一些在状态栏中，有时没有。当我跑了就在刚才，我刚刚得到了准备就绪的消息在整个但有时我有出庭所需的信息的一些疑问，但不是全部。

However, I don't want to be pressing OK on a message box every time a query starts. If I comment out the MsgBox statement, it doesn't work any more. The results are variable. Sometimes it shows something in the status bar, sometimes not. When I ran it just now, I just got the "Ready" message throughout but sometimes I have got the desired message appearing for some but not all of the queries.

我用试过 RefreshDatabaseWindow 而不是 MSGBOX ，但这并没有什么区别。

I have tried using RefreshDatabaseWindow instead of MsgBox, but that doesn't make any difference.

推荐答案

由于我从HansUp在回答类似的问题得到的帮助（<一href="http://stackoverflow.com/questions/27844653/how-to-show-progress-on-status-bar-when-running-$c$c-not-queries">How运行code（不查询）时，显示在状态栏进度），我后来公布，现在我可以回答这个问题我自己。

Thanks to the help I received from HansUp in answering a similar question (How to show progress on status bar when running code (not queries)) which I posted afterwards, I can now answer this question myself.

为了使code的工作，而不调用MSGBOX，你需要把两行调用Application.Echo之前：

To make the code work without the call to MsgBox, you need to put two lines before the call to Application.Echo:

RetVal = SysCmd(4, "Executing " & QueryName & " ...")
DoEvents

现在，这不正是我想要的。

This now does exactly what I want.

这篇关于如何在MS Access运行查询序列时，显示在状态栏进度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！