VBA Code to close Find dialog needed, or custom find control - Update or CancelUpdate Without AddEdit or New error

[Marshall Brooks]

Reference https://stackoverflow.com/questions/4228705/ms-access-update-or-cancelupdate-error-using-find-dialog

If you open the find dialog, leave the dialog open, change a field on the record, and then run the find command again, you get error 3020.

The thread mentions an AutoIt script - which I could write, but I don't want to have to deploy with my database, or creating a custom form and disabling the binoculars and Ctrl-F, but that is more than I would prefer to get involved with.

The issue is I don't know how the Find and Replace dialog is referred to in VBA. The dialog is invoked via:

DoCmd.FindRecord FindWhat:=" ", Match:=acAnywhere, MatchCase:=False, _
Search:=acSearchAll, SearchAsFormatted:=False, _
OnlyCurrentField:=acAll, FindFirst:=True

With possibly other parameters.

I'm looking for something like:

DoCmd.Close acForm, "Find and Replace"

or

Application.Dialogs(acFindReplace).Close

Not sure if this helps, but AutoHotKey WindowSpy shows the window as Title Find and Replace, ahk_class #32770, ahk_exe MSACCESS.EXE, ahk_Pid 684.

That gives me enough information to write an AHK script to close the window when the record is "Dirty", but I can't have an AHK script running in a deployed environment ...

Thanks in advance!!!

I also didn't find anything in a search for a custom Find & Replace replacement - I found this https://www.devhut.net/access-new-find-and-replace-for-developers/ - but it is designed for searching and replacing objects in VBA code windows.

 

[Marshall Brooks]

Very cool samples, but not what I am looking for.

First off, I understand about the replace option. I'm not too worried about our users doing anything with that - although maybe I should be. I'd be okay with a substitute without it.

I don't really need or want to filter the database. I just want to, for example, search stop at all records with "Brooks" in the Last Name field.

I have Search Field and Search All button on the Form, but CTRL-F and the binoculars still work. Button code is somewhat complex b/c I wanted the code to stay on the current record if the search item was not found.

Search Field code looks like this:

Private Sub Search_Field_Click()
Dim CurrentRecord As Long
On Error GoTo Search_Field_Click_Err
'  https://stackoverflow.com/questions/17118000/id-like-to-make-any-part-of-field-default-access-vba
' http://codevba.com/msaccess/docmd_find_record.htm
' Match:acAnywhere, acEntire (default), acStart; OnlyCurrentField: acCurrent, acAll
CurrentRecord = Me.PrimaryKey
    On Error Resume Next
    DoCmd.GoToControl Screen.PreviousControl.Name
    Err.Clear
DoCmd.FindRecord FindWhat:=" ", Match:=acAnywhere, MatchCase:=False, _
                Search:=acSearchAll, SearchAsFormatted:=False, _
                OnlyCurrentField:=acCurrent, FindFirst:=True
With Me.RecordsetClone
    .FindFirst "PrimaryKey = " & CurrentRecord
    If .NoMatch Then 'just in case another user deleted it in the interim
        MsgBox "Record not found!", vbCritical
        Exit Sub
    Else 'go to that record
        Me.Bookmark = .Bookmark
    End If
End With
    DoCmd.RunCommand acCmdFind
    If (MacroError <> 0) Then
        Box MacroError.DESCRIPTION, vbOKOnly, ""
    End If
Search_Field_Click_Exit:
    Exit Sub
Search_Field_Click_Err:
    Box Error$
    Resume Search_Field_Click_Exit

End Sub

Search All looks like this:

Private Sub Search_All_Click()
Dim CurrentRecord As Long
On Error GoTo Search_All_Click_Err
CurrentRecord = Me.PrimaryKey
Pause (0.5)
Me.Field1.SetFocus
Pause (0.5)
'    On Error Resume Next
'    DoCmd.GoToControl Screen.PreviousControl.Name
'    Err.Clear
'  https://stackoverflow.com/questions/17118000/id-like-to-make-any-part-of-field-default-access-vba
' http://codevba.com/msaccess/docmd_find_record.htm
' Match:acAnywhere, acEntire (default), acStart; OnlyCurrentField: acCurrent, acAll
DoCmd.FindRecord FindWhat:=" ", Match:=acAnywhere, MatchCase:=False, _
                Search:=acSearchAll, SearchAsFormatted:=False, _
                OnlyCurrentField:=acAll, FindFirst:=True
With Me.RecordsetClone
    .FindFirst "PrimaryKey = " & CurrentRecord
    If .NoMatch Then 'just in case another user deleted it in the interim
        MsgBox "Record not found!", vbCritical
        Exit Sub
    Else 'go to that record
        Me.Bookmark = .Bookmark
    End If
End With
    DoCmd.RunCommand acCmdFind
    If (MacroError <> 0) Then
        Box MacroError.DESCRIPTION, vbOKOnly, ""
    End If
 
Search_All_Click_Exit:
    Exit Sub
Search_All_Click_Err:
    Box Error$
    Resume Search_All_Click_Exit
End Sub

Pause looks like this:

Public Function Pause(NumberOfSeconds As Variant)
' https://www.access-programmers.co.uk/forums/showthread.php?t=167470
' Supports resolution at least to the the tenth of a second, maybe greater.
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = timer
    Do While timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    Box Err.Number & " - " & Err.DESCRIPTION, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

Either button or CTRL-F brings up the familiar Search&Replace Form:

The issue, which happens often to me is (and you should be able to verify in any database):

• Click Search Button or Press CTRL-F.
• Search to a record that matches the search term.
• Leave the Find & Replace dialog open.
• Modify any field on the record that is selected.
• Click on a different record in the database and/or click Find Next.
• For me, code pops up asking if I want to save changes to the record. Click Yes (which would be the default).
• Result:

Once you get that message, I haven't found a way to get rid of it, without closing the current form without saving changes, and the re-entering the changes to my record.

So I think my options are:

• Figure out some way to close the dialog whenever a field of the record is changed - but I don't know how to do the. The form is opened via: DoCmd.RunCommand acCmdFind , but I don't see a method (short of AutoHotKey) to close it.
• Find out exactly where in the code the Error is generated and add something like (air code):

If Err.Number = 3020 Then
    Err.Clear
    Resume Next
End If

• Create my own form similar to the Find dialog but without the replace tab, which I then WOULD be able to close when the record got dirty, and override the CTRL-F and Binoculars shortcuts with it, but as mentioned above on StackOverflow - that seems like a lot of work to mimic built-in functionality, unless someone else has already created it.
• Less than ideal - Since I don't know how to close the file dialog, I also don't know how to tell it is open. I could add code after "Do you want to Save?" displays to say "Please close the Find Dialog if it is open ..." But then users have to click another button everytime they want to save a record. (And I think they would get the error also if they edited the record, clicked the confirm button to save it, and then searched again - unless I add the "Close F&R" message there also).

 

[sonic8]

he issue, which happens often to me is (and you should be able to verify in any database):

• Click Search Button or Press CTRL-F.
• Search to a record that matches the search term.
• Leave the Find & Replace dialog open.
• Modify any field on the record that is selected.
• Click on a different record in the database and/or click Find Next.
• For me, code pops up asking if I want to save changes to the record. Click Yes (which would be the default).

I cannot reproduce this issue at all.
If you see "code pops up asking if I want to save changes" then this is something that you must have created, as by default Access forms will just save the record without asking.
I suspect, the root cause for your whole issue is in the code that is asking to save changes. Maybe you should revise that code first before trying to change the behavior of the search dialog.

 

[Marshall Brooks]

Interesting. I don't see the issue with a new database either. The StackOverflow page has other steps to recreate the issue, but those don't work for me in a new clean database.

Correct the save code is something I added. Without giving away any company info, here is the code (which is pretty basic):

Private Sub Form_BeforeUpdate(Cancel As Integer)

   ' This procedure is called if the data on a form is changed and the record loses focus.
   ' If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it.

   On Error GoTo Err_BeforeUpdate
' DontPromptUser is set by the Confirm Button
If DontPromptUser = True Then Exit Sub
' Prompt to confirm the save operation. The default action is save, so for Yes, only the date modified field must be updated.
If Box("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
         Me.Undo
         DocNumberChanged = False
Else
    Me![Date Modified].value = Now()
End If
Call ThisForm_Reset_Colors
Me.btnUNDO.Enabled = False
Me.btnUNDO.BackColor = RGB(216, 216, 216)
Me.btnUNDO.ForeColor = RGB(135, 135, 135)
Me.UPDATE.Enabled = False
Me.UPDATE.BackColor = RGB(216, 216, 216)
Me.UPDATE.ForeColor = RGB(135, 135, 135)

Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   Box Err.Number & " " & Err.DESCRIPTION
   Resume Exit_BeforeUpdate
End Sub

Explanations:

• There is a confirm button on the form which saves the record (named Update, but the text on the button says Confirm). The DontPromptUser Public Variable gives you from getting the "Do You Want To Save" message when you have manually saved the record already.
• If you click no to saving, changes are undone.
• If you click Yes, the Date Modified Value is changed (The default is to save, so no action is necessary.)
• The Undo and Confirm buttons are disabled unless the fields on the form have been changed without saving the record.
• Each textbox on the form has code that turns the background yellow when changed and there is a subroutine that changes them back to the default background.

There is also:

Private Sub Form_AfterUpdate()
If DocNumberChanged = True Then
    DocNumberChanged = False
    Dim CurrentRecord As Long
    CurrentRecord = Me.PrimaryKey
    Call UpdateTableSortKeyField("[THIS_TABLE]", "[DOCNUMBER]", "SortKey", CurrentRecord)
'    MsgBox ("SortKey")
End If
End Sub

Which calls:

Public Sub UpdateTableSortKeyField(ByVal tableName As String, ByVal baseColumnName As String, ByVal sortKeyColumnName As String, ByVal CurrentRecord As String)
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT " & baseColumnName & ", " & sortKeyColumnName & ", PrimaryKey  FROM " & tableName)
        rs.FindFirst "PrimaryKey = " & CurrentRecord
'        If rs.NoMatch Then
'            MsgBox "Not Found"
'        Else
'            MsgBox "Found"
'        End If
        rs.Edit
        rs.Fields(sortKeyColumnName).value = GetSortKey(Nz(rs.Fields(baseColumnName).value, ""))
        rs.UPDATE
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub

In:

'' **************************************************************************
'' Module:      modNumeralsSorting
'' Author:      Philipp Stiefel (phil@codekabinett.com)
'' Purpose:     Functions for natural numerals sorting in Microsoft Access
'' Version:             1.0.1   - 2019-10-31 - Bugfix in GetHexString
''                              1.0             - 2016-08-22 - Initial release
'' Further info: http://codekabinett.com/rdumps.php?Lang=2&targetDoc=logical-numerals-sorting-access-vba
'' **************************************************************************

Too much to explain here, but what I needed was DocNumber to be 1, 2, 10, 25, 100, instead of 1, 10, 100, 2, 25, etc. so there is a hidden field named SortKey that the form sorts on before displaying the data.

I possibly see the source of the error. In the search fields, I don't have a Me.RecordSetClone.Close statement and in the code above, I am opening another recordset.

Does that seem like possibly the error source to you?

 

[Marshall Brooks]

I tried changing the Search functions to add .Edit after the With Statement and .Close before the End With statement, but I still get the error.

I figured out logically what is happening. I have the Find dialog open. Then I change the record. Then I try to search for a new record, but since the record is not saved, it gives me the error message. But I can't add something like Me.Dirty = False b/c the change occurs after the find dialog is opened and it is not closed - and I don't want to save every change to the database without a validity check that the users intended to make the change.

 

[Marshall Brooks]

I've isolated and somewhat resolved the issue ...

The error occurs with my BeforeUpdate code, specifically this line in Red:
If Box("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
Me.Undo
DocNumberChanged = False
Else
Me![Date Modified].value = Now()
End If

If I comment that line out, the error doesn't occur. The error occurs when you try to use the find command to go to a different record and the form is dirty.

I can live with that line commented out, but I liked having the date Modified field updated when I changed a record and went to a new record as well as on implicit saves.

What I'm not clear on is implicit saves don't clear the error, i.e.:

If Box("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
Me.Undo
DocNumberChanged = False
Else
Me![Date Modified].value = Now()
Me.Dirty = False
End If

Still generates the error, as does:

If Box("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
Me.Undo
DocNumberChanged = False
Else
DontPromptUser = True
Me![Date Modified].value = Now()
DoCmd.RunCommand acCmdSaveRecord
DontPromptUser = False
End If

???

 

[Marshall Brooks]

I thought I could move the Me![Date Modified].value = Now() line to the Form_AfterUpdate() Event, but that didn't work and gave me odd errors ...

If I just place it there, the "Do You Want to Save?" from the Before Update event is stuck in a loop - i.e. even if nothing has changed, I still get prompted. If I add DontPromptUser = True to the Form_AfterUpdate() Event, I don't get the prompt, but I can't go to a different record - probably b/c of the BeforeUpdate() being stuck in a loop.

 

[Marshall Brooks]

It works the way I want (and I don't get the error) if I add the Me![Date Modified].value = Now() line to the Form_Dirty() Event procedure.

Thanks to everyone for the assistance!

 

[Marshall Brooks]

Would Cancel = True replace Exit Sub above, or where would I put it in the existing code (Reply #5)? Thanks!

 

[Marshall Brooks]

Got it - thanks!!! (Somewhat works out the same. Cancel=True it doesn't save the record. Without it, it undoes the changes and saves the record.)

Best analogy would be if I open a Word file and add a space and close without saving, as opposed to adding a space, removing the space I just added and then saving.

 

[Marshall Brooks]

One more question ...

Technically, in the search code in Reply #3, should I have .edit after the With clause and .close just before the End With Statements.

I tried it with and without them and couldn't see a difference - but that doesn't mean they don't belong there.

 

[Marshall Brooks]

Changed the thread title since what I did was solve the error message, not find a way to close the find dialog or create a custom one.

 

[Marshall Brooks]

Interesting - maybe it is something other than the dirty property that is affecting the behavior - it worked out marginally better for me without Cancel = True ...

Basically, once I moved Me![Date Modified].value = Now() from BeforeUpdate to Form_Dirty, it works like I want it to.

Without Cancel=True, if I change the record and move to a new record, it prompts me to save or not and then goes to the new record.

With Cancel = True, if I change the record and move to a new record, it prompts me to save and stays on the current record and then I have to click the arrow or find again to go to the next record.

 

[Marshall Brooks]

Cancel = True cancels the save. It does NOT stop the form from being dirty . You need to use
Me.Undo
Cancel = True
to perform both actions.

I don't claim to be the expert and I'm not even saying you are wrong. What you wrote is correct, but it isn't what I am doing.

Cancel = True by itself would probably leave the error message - not sure.

What I have is Me.Undo without Cancel=True.

Technically what that does (I think) is undoes all the changes (including the date modified field) and then SAVES the record.

Like the Word analogy, it's the difference between opening a Word file, adding a space and clicking Close and don't save changes as opposed to removing the space and closing and saving. The file date is different, but the file is essentially the same.

Could you see my question in Reply #16?

 

[Marshall Brooks]

No point except that it saves a mouse-click as I said above. (If I change the record and click Find Next, without Cancel = True, I am prompted to save or not and then it goes to the next record. If I change the record and click Find Next with Cancel = True, I am prompted to save or not and then it stays on the current record and I have to click find again.)

And it wasn't my choice, but by default (with BeforeUpdate() code added) Access saves every change and updates the database file date every time it is opened.

 

[Marshall Brooks]

Thanks to all who replied for their assistance!!!

Understood. I'm also fortunate the database isn't really setup where we routinely have to change the same value to some other value - so there aren't a lot of examples where replace would be useful.