vba 如何在查询运行时插入请稍候表单

How to insert a Please Wait form while a query is running

提问by PowerUser

I want to display a "Please Wait" form while a 60+ second query is running. I have the query and the form made (easy enough), except the macro won't bring them together. Here is my autoexec macro:

我想在 60 秒以上的查询运行时显示“请稍候”表单。我已经制作了查询和表单（足够简单），除了宏不会将它们组合在一起。这是我的 autoexec 宏：

1. SetWarnings=False
2. Open Wait Form
3. Run the query & Display Results
4. Close Wait Form

1. 设置警告=假
2. 打开等待表格
3. 运行查询并显示结果
4. 关闭等待表格

The problem is that I can only see the outline of my wait form while the maketable query is running. I can only guess that the macro doesn't wait for the form to completely load before going to the next step.

问题是我只能在可生成查询运行时看到我的等待表单的轮廓。我只能猜测宏在进入下一步之前不会等待表单完全加载。

And just for fun:

只是为了好玩：

The Wait form has a Timer that shows the number of seconds elapsed. How do I run the query while still allowing the form to update itself at the same time?

等待表单有一个计时器，显示经过的秒数。如何在运行查询的同时仍允许表单自行更新？

-------Update----------

- - - -更新 - - - - -

Repaintonly updates the background color before moving on to the query. I also tried 6 repaints in a row with no difference (I thought that might get around the bug you people mentioned). The form is very simple. A single text box that uses "loading" for the control source and a timer function (disabled for the moment). Here is my macro in slightly greater detail:

Repaint仅在继续查询之前更新背景颜色。我还连续尝试了 6 次重绘，没有任何区别（我认为这可能会解决您提到的错误）。表格非常简单。使用“加载”作为控制源和计时器功能（暂时禁用）的单个文本框。这是我的宏稍微详细一点：

1. SetWarnings=False
2. Open Wait Form (modal=yes, popup=no)
3. Repaint the form
4. Open a maketable query (this query saves alot of time)
5. Close Wait Form
6. Open the display query

1. 设置警告=假
2. 打开等待表单（modal=yes，popup=no）
3. 重新绘制表格
4. 打开一个 maketable 查询（这个查询可以节省大量时间）
5. 关闭等待表格
6. 打开显示查询

采纳答案by Kevin Ross

The first problem is easy to solve, try putting me.Repaint before the query runs, sometimes access can get a bit lazy with screen painting and you have to force it.

第一个问题很容易解决，尝试在查询运行之前放入 me.Repaint ，有时访问屏幕绘制会变得有点懒惰，您必须强制它。

As for the other thing that is a bit more complicated. You would have to execute the query asynchronously. You can do this in ADO by adding the adAsyncExecute option when executing the query. You would then catch the finishing event to tell the users the query is done

至于另一件稍微复杂一点的事情。您必须异步执行查询。您可以通过在执行查询时添加 adAsyncExecute 选项在 ADO 中执行此操作。然后，您将捕获完成事件以告诉用户查询已完成

Here is a link on the Microsoft knowledge base http://support.microsoft.com/kb/262311

这是 Microsoft 知识库上的链接http://support.microsoft.com/kb/262311

回答by Lunatik

Firstly, you just need to Repaintthe wait form after it is shown for it to show correctly, it's just a bug in VBA that sometimes causes forms not to be displayed properly even if they are drawn at the correct point in the code.

首先，您只需要Repaint等待表单显示后才能正确显示，这只是 VBA 中的一个错误，有时会导致表单无法正确显示，即使它们是在代码中的正确位置绘制的。

Secondly, if you set the wait form's ShowModalproperty to false then you will be able to update it through your routine. I use a public function that takes a 0-100 argument to update a progress bar. This works best in a loop where you can normally calculate the number of loops remaining and provide an accurate guide to the elapsed progress, but even when performing a series of operations you can time and pass suitable values through to the progress form so that the user is kept informed. This works better than most Windows progress bars!

其次，如果您将等待表单的ShowModal属性设置为 false，那么您将能够通过您的例程更新它。我使用一个带有 0-100 参数的公共函数来更新进度条。这在循环中效果最好，您通常可以计算剩余的循环次数并为经过的进度提供准确的指导，但即使在执行一系列操作时，您也可以计时并将合适的值传递给进度表，以便用户被告知。这比大多数 Windows 进度条更有效！

回答by ViggoV

If you are using an Action Query you can track the number of executed rows by using a vba-function which returns the same value that was passed to it. Of course you have to know the total number of rows, to know when the query is done or display progress (This can often be achieved relatively fast using a lightweight counting query*).

如果您使用的是操作查询，您可以使用返回与传递给它的相同值的 vba 函数来跟踪已执行的行数。当然，您必须知道总行数，才能知道查询何时完成或显示进度（这通常可以使用轻量级计数查询相对快速地实现*）。

'first some global vars to keep track of the number
Dim lngCurrentRow as Long
Dim lngTotalRows as Long 'We assume lngTotalRows have been set elsewhere

'Here is the function
Public Function UpdateProgress(FieldValue as Variant) as Variant

    lngCurrentRow = Nz(lngCurrentRow, 0)

    If (lngCurrentRow >= lngTotalRows) then
        MsgBox "Query Done!"
    Else
        'Do stuff to show progress
    End If

    lngCurrentRow = lngCurrentRow + 1

End Function

For displaying the progress I have used an Unbound form, with an empty textfield with transparent background (progressFrame) and a label (progressLabel) which has a blue background and is placed behind the textbox to the left. For each call to UpdateProgress you can then set the width of the label:

为了显示进度，我使用了一个未绑定的表单，其中有一个带有透明背景 (progressFrame) 的空文本字段和一个蓝色背景并位于左侧文本框后面的标签 (progressLabel)。对于每次调用 UpdateProgress，您可以设置标签的宽度：

progressLabel.Width = progressFrame.Width * (lngCurrentRow / lngTotalRows)

*: F.ex. if the query is based on one table, with some added data, so that the number of records the query will produce is equal to that of the table, a count can be done on the table alone so that it will be faster..

*：F.ex。如果查询是基于一张表，添加一些数据，这样查询将产生的记录数等于该表的记录数，则可以单独对表进行计数，这样会更快。

I hope this was helpful, or at least inspiring :)

我希望这是有帮助的，或者至少是鼓舞人心的 :)

回答by Snowflake68

I had the same issue with a pop up form which took a few seconds before it displayed the text which informs the user that something is processing so please wait. I added the line of code below after the code which opens the form and before the next bit of code which runs the update queries (which takes a while hence the need to inform the user that something was processing)

我遇到了同样的问题，弹出窗体花了几秒钟才显示文本，通知用户正在处理某些内容，因此请稍候。我在打开表单的代码之后和运行更新查询的下一段代码之前添加了下面的代码行（这需要一段时间，因此需要通知用户正在处理某些内容）

DoCmd.RepaintObject acForm, "frmMyForm"

DoCmd.RepaintObject acForm, "frmMyForm"

Works perfectly for me.

非常适合我。