vba 使用“查找”对话框的 MS Access“更新或取消更新”错误

MS Access “Update or CancelUpdate” error using Find dialog

提问by Dean Hill

We have an MS Access 2007 database with a simple form displaying table data. We use the Find dialog (click the binoculars on the Home ribbon) to locate records we want. This can cause an error under specific circumstances.

我们有一个 MS Access 2007 数据库，其中包含一个显示表格数据的简单表单。我们使用“查找”对话框（单击“主页”功能区上的双筒望远镜）来定位我们想要的记录。在特定情况下，这可能会导致错误。

Steps to reproduce the problem:

重现问题的步骤：

1. Open the form.
2. Open the find dialog.
3. Edit some field within the record. The record is now in update mode (you'll see the pencil in row's "gutter" area).
4. Without saving the record, click on the ALREADY open Find dialog.
5. Search for a record that can't be found.
6. Click on the form again. The record is still in edit mode (i.e. the pencil still shows). Attempt a save or edit some other field.
7. This message box will display "Update or CancelUpdate without AddNew or Edit." You can click OK or Help buttons.

1. 打开表格。
2. 打开查找对话框。
3. 编辑记录中的某个字段。该记录现在处于更新模式（您将在行的“装订线”区域中看到铅笔）。
4. 不保存记录，单击已打开的查找对话框。
5. 搜索无法找到的记录。
6. 再次点击表格。记录仍处于编辑模式（即铅笔仍显示）。尝试保存或编辑其他字段。
7. 此消息框将显示“更新或取消更新而不添加新的或编辑”。您可以单击“确定”或“帮助”按钮。

Clicking the Help button shows:

单击帮助按钮显示：

You tried to call Update or CancelUpdate or attempted to update a Field in a recordset without first calling AddNew or Edit. (Error 3020)

On a Microsoft Access database engine database, you called the Update or CancelUpdate method but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data to a record without first calling AddNew or Edit.

您尝试调用 Update 或 CancelUpdate 或尝试在未先调用 AddNew 或 Edit 的情况下更新记录集中的字段。（错误 3020）

在 Microsoft Access 数据库引擎数据库上，您在将数据写入记录之前调用了 Update 或 CancelUpdate 方法，但未使用 AddNew 或 Edit 方法。

在 ODBCDirect 数据库上，当您尝试将数据写入记录而不先调用 AddNew 或 Edit 时，会发生此错误。

We've reproduced this in a new database where there is no VBA code. So the problem is solely within MS Access, and you should be able to reproduce it easily.

我们在一个没有 VBA 代码的新数据库中复制了这一点。所以问题仅在 MS Access 中，您应该能够轻松重现它。

If you save the record before doing the find, the problem doesn't happen. Unfortunately, we have users doing a find while the record is still in edit mode.

如果在查找之前保存记录，则不会发生问题。不幸的是，我们有用户在记录仍处于编辑模式时进行查找。

We've tried setting up form-level, data-field-level, and Access application level events and error handling. Nothing can detect or catch this situation. There is no way within VBA to detect if the Find dialog is active.

我们已经尝试设置表单级、数据字段级和 Access 应用程序级事件和错误处理。没有什么可以检测或捕捉到这种情况。VBA 中无法检测“查找”对话框是否处于活动状态。

Does anyone have any ideas for preventing the error or a way to save the record before the find occurs? Our best thought right now is to create an AutoHotkey or AutoIt script that waits for the Find dialog to have focus. We'll then send a Ctrl+S to save the current record to force a save.

有没有人有任何想法可以防止错误或在查找发生之前保存记录的方法？我们现在最好的想法是创建一个 AutoHotkey 或 AutoIt 脚本，等待 Find 对话框获得焦点。然后我们将发送一个 Ctrl+S 来保存当前记录以强制保存。

采纳答案by Dean Hill

The hack, work-around we came up with was to write an AutoItscript which can watch for when the Find dialog gains focus and save the record if it has changed.

我们提出的解决方法是编写一个AutoIt脚本，该脚本可以监视“查找”对话框何时获得焦点并在记录发生更改时保存记录。

We didn't want to distribute the script separately from the database, so the the script was added to a database table as a Blob. The database's AutoExec macro runs some VBA code that pulls the script out of the Blob table and starts the script.

我们不想将脚本与数据库分开分发，因此将脚本作为 Blob 添加到数据库表中。数据库的 AutoExec 宏运行一些 VBA 代码，将脚本从 Blob 表中拉出并启动脚本。

When the script detects that the Find dialog has focus, the script runs a VBA macro in the database. The macro checks to see if the current record is dirty. If dirty, the macro forces a save. Also, the AutoIt script is stopped when the database is closed.

当脚本检测到“查找”对话框具有焦点时，脚本会在数据库中运行 VBA 宏。宏检查当前记录是否脏。如果脏了，宏会强制保存。此外，AutoIt 脚本会在数据库关闭时停止。

This is all pretty awkward, but it works.

这一切都很尴尬，但它确实有效。

回答by BIBD

I'd suggest that you've found a bug that was introduced in MS-Access 2007. However, I have not been able to duplicate it on my copy. I presume we're both up to date on our patches, so perhaps there is something more subtle happening.

我建议您发现 MS-Access 2007 中引入的一个错误。但是，我无法在我的副本中复制它。我想我们的补丁都是最新的，所以也许发生了一些更微妙的事情。

If you're wanting to force the record to be saved, use one of the the following - not a CTRL-S

如果您想强制保存记录，请使用以下方法之一 - 而不是 CTRL-S

• if me.dirty then Me.Dirty = false ''(n.b. often the preferred method)

• Docmd.RunCommand acCmdSaveRecord

• DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 ''(n.b. deprecated)

• if me.dirty then Me.Dirty = false ''(n.b. often the preferred method)

• Docmd.RunCommand acCmdSaveRecord

• DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 ''(n.b. deprecated)

The problem as I understand it, is that if they edit the form after the "find" is already open and then do the "find" the get the error.

据我了解，问题在于，如果他们在“查找”已经打开后编辑表单，然后执行“查找”，则会出现错误。

I'd try one of two things:

我会尝试以下两件事之一：

1. Find a way to close the built in find form, and do so whenever you make the current record dirty (On Dirty)
2. Add your own "find" button to the form (not opening the built in find form), and hide the one on the ribbon.

1. 找到关闭内置查找表单的方法，并在您使当前记录变脏时执行此操作（On Dirty）
2. 将您自己的“查找”按钮添加到表单（而不是打开内置的查找表单），然后隐藏功能区上的按钮。

回答by David-W-Fenton

@CodeSlave's answer suggests a possibility to me:

@CodeSlave 的回答向我暗示了一种可能性：

Instead of simply removing the binoculars from the toolbar/ribbon, instead change what the binoculars do. That is, have it call code that saves the current record if it's dirty and then launches the FIND dialog.

不是简单地从工具栏/功能区中移除双筒望远镜，而是更改双筒望远镜的功能。也就是说，让它调用代码来保存当前记录，如果它是脏的，然后启动 FIND 对话框。

Now, there'd need to be some code to check that a form was open, and that it had a recordsource (testing the .Dirty property errors if there's no recordsource), and that a field has the focus, but all of those things are doable. Likely many of them (except the last) would be taken care of by showing the toolbar/ribbon only when the form is loaded, or by editing the default toolbar/ribbon when the form opens.

现在，需要一些代码来检查表单是否打开，并且它有一个记录源（如果没有记录源，则测试 .Dirty 属性错误），以及一个字段是否具有焦点，但是所有这些是可行的。可能其中许多（除了最后一个）将通过仅在加载表单时显示工具栏/功能区或在表单打开时编辑默认工具栏/功能区来处理。

But this would be much less crazy than using an out-of-process solution, and your users wouldn't know any difference.

但这比使用进程外解决方案要简单得多，而且您的用户不会知道任何区别。