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.

 

[Pat Hartman]

Using the Find dialog is dangerous since it opens up Replace and that can affect multiple rows and you have no control over what strange things user try to do.

There are lots of references here to search forms. There is also a search by form method which may be closer to what you are looking for. I'll post a link to a sample that uses it but the button code is just this if you don't need the sample:

Private Sub cmdFilterOn_Click()
On Error GoTo Err_cmdFilterOn_Click

    DoCmd.RunCommand acCmdFilterByForm

Exit_cmdFilterOn_Click:
    Exit Sub

Err_cmdFilterOn_Click:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_cmdFilterOn_Click
End Sub

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

 

[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?

 

[Pat Hartman]

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.

You should be very afraid about using the find/replace dialog and you should make multiple backups of the BE each day to protect against an accident.

Did you try the first option? It is a no code solution and lets the users construct complex filters. And YES you do want to filter the table/query the form is bound to. This sample doesn't filter the "database" whatever that means.

Text fields sort like text fields which is character by character, left to right which is why 2 comes after 100. Numeric values sort by magnitude. So, the simple solution is to convert the DocNumber to a number data type. If it can contain text then you can still solve the problem if the placement of the alpha characters is consistent by making two fields.

 

[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.

 

[Pat Hartman]

The find/replace wasn't intended to be used this way which is why there are no events where you can add your own code to automate it.

If you have validation code in the form's BeforeUpdate event, you can ensure that only valid records get saved. You don't need to do it the way you are doing it. Access will attempt to save the current record as soon as you leave it and that will run the BeforeUpdate event. You might be able to add a prompt there to ask for confirmation before saving. However, that will rapidly annoy the users and cause them to ignore all messages and just click OK or whatever in order to close the prompt.

 

[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!

 

[Pat Hartman]

You have to use Cancel = True in the BeforeUpdate event to clear the save.

 

[Marshall Brooks]

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

 

[Pat Hartman]

put it before or after the Me.Undo

 

[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.

 

[Pat Hartman]

Without the Cancel = True, the form remains dirty.

 

[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.

 

[Pat Hartman]

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 ...

The form's AfterUpdate event runs AFTER THE RECORD HAS BEEN SAVED. That means, if you dirty the record in this event, you are causing an infinite loop. However, Access is smarter now than it used to be. In the past, this action would put the form into a hard freeze as it flickered. You needed a hard reboot to get out of the freeze up. Current versions recognize the recursion and exit gracefully after a certain number of loops.

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.

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.