Suppress "Can't Use Find and Replace Now"

[sjw]

Long-time reader, first-time poster... I am stumped!
I have a form our staff use for record management (dynaset), and another form that other users input data into that goes into our main form. In order to search the most recent records, we are refreshing the form's dynaset before bringing up the Find and Replace dialog with the following code:

Private Sub Cmd_FindRecord_Click()
On Error GoTo Err_Cmd_FindRecord_Click
     Application.Echo False
    
    Dim ctlPrevious As Control
    Set ctlPrevious = Screen.PreviousControl
    
    If IsNull(Me.ClmID) Then
    
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
    
    Else
    
    Dim recordIDNumber As Long
    recordIDNumber = Me.ClmID
    
        Me.Requery
        
        With Me.Recordset
        .FindFirst "ClmID=" + CStr(recordIDNumber)
        
        End With
        
    End If
    
    Application.Echo True
    
    ctlPrevious.SetFocus
    DoCmd.RunCommand acCmdFind
 
 Exit_Cmd_FindRecord_Click:
    Exit Sub
 Err_Cmd_FindRecord_Click:
    Resume Exit_Cmd_FindRecord_Click
    
End Sub

The refresh works as expected, and the Find dialog works. However, since implementing this change, our admins have been encountering a "Can't Use Find and Replace Now" error message when they click out of MS Access into MS word or Outlook, and click back to Access.

Is there a way I can suppress this error message, or change my code to prevent this?

I've tested, and without the new code, the Find and Replace dialog box stays open and does not create the error message when MS Access loses focus.

 

[Ranman256]

you dont need vb to do this. You can just run a query using the REPLACE function.

 

[sjw]

you dont need vb to do this. You can just run a query using the REPLACE function.

I'm not sure that I understand - do you mean the Replace() function that swaps out string values?

We're not replacing any values; the Find and Replace menu is being used to navigate through our large record set by searching for record IDs, names, etc.

 

[Ranman256]

Don't use find, find next.
Just filter ALL records to your criteria. Then ALL records you want are visible.
No find needed.

 

[sjw]

Don't use find, find next.
Just filter ALL records to your criteria. Then ALL records you want are visible.
No find needed.

Not sure how to filter a Dynaset Form, if you would like to fill me in on that.
I would prefer to stick with the Find and Replace dialogue if possible, as this is used by lots of front-end staff, and they are very used to the standard interface.

 

[JHB]

Are you sure the control which get the focus can be used in conjunction with the "Find & Replace"? (Like focus on a button or something equal to that)

ctlPrevious.SetFocus

 

[sjw]

Yes, it's functioning correctly- staff click into a text field, then click the "Find" button, and the focus is brought back to the text field once the "Find and Replace" dialog is open.

I'm attaching my db (anonymized) so that folks can replicate the issue.

Try using the find function, then clicking out of the window (to copy text, for example), and clicking back into Access. You will see the "Can't Use Find and Replace Now" error.

 

[JHB]

Set the form's "Pop Up" property to "No".

 

[sjw]

You are absolutely right, that's the issue! Thanks JHB!

 

[JHB]

You're welcome, good luck.