MS Access “Update or CancelUpdate” error using Find dialog

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 ci开发者_开发知识库rcumstances.

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.

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.

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.

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.

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.

---

@CodeSlave's answer suggests a possibility to me:

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.

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.

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

---

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.

If you're wanting to force the record to be saved, use one of the the following - not a 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)

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.

---

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

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.

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.

This is all pretty awkward, but it works.