Using FindRecord with records containing double quotes

Question

I'm trying to use OpenArgs to locate a record when opening a form. There are no duplicates of the record. The code works until a record contains double quotes.

OpenArgs is set when closing one form using

Private Sub EditBtn_Click()
    Dim CurrentProd As String
    CurrentProd = Me.ProductName
    DoCmd.Close acForm, "ProductsFrm"
    DoCmd.OpenForm "ProductEditFrm", OpenArgs:=CurrentProd
End Sub

When the new form opens the load event contains

Private Sub Form_load()
    Dim ProdSrch As String
    If Nz(Me.OpenArgs, 0) <> 0 Then
        ProdSrch = Replace(Me.OpenArgs, """", """""")
        Me.ProductName.SetFocus
        DoCmd.FindRecord ProdSrch
    End If
End Sub

The replace function alters a string such as "Chocolate Torte 8" Rich with Chocolate Ganache" to Chocolate Torte 8"" Rich with Chocolate Ganache". Other help I found suggests this should then be interpreted as an instruction to treat the two double quotes as a single quote and not as a delimiter, however, Access cannot find the record.

Even if I type the exact text and make it equal a string variable, as below, Access will not find the record

Private Sub Form_load()
    Dim ProdSrch As String
    If Nz(Me.OpenArgs, 0) <> 0 Then
        ProdSrch = "Chocolate Torte 8"" Rich with Chocolate Ganache"
        Me.ProductName.SetFocus
        DoCmd.FindRecord ProdSrch
    End If
End Sub

or this

Private Sub Form_load()
    Dim ProdSrch As String
    If Nz(Me.OpenArgs, 0) <> 0 Then
        ProdSrch = "Chocolate Torte 8" & Chr(34) & " Rich with Chocolate Ganache"
        Me.ProductName.SetFocus
        DoCmd.FindRecord ProdSrch
    End If
End Sub

There is no error, Access takes me to the first record.

I solved the problem another way using recordsetclone.
Changing the Openargs to the primary key unique number rather than a text string. The text box containing the primary key in the second form has 'enabled' set to 'no' so setfocus doesn't work.

Private Sub Form_load()
    Dim Rst As Recordset
    Dim ProdSrch As String
    If Nz(Me.OpenArgs, "") <> "" Then
        ProdSrch = "[ProductID] = " & Me.OpenArgs
        Set Rst = Me.RecordsetClone
        Rst.FindFirst ProdSrch
        Me.Bookmark = Rst.Bookmark
        Rst.Close
    End If
End Sub

I should have used the primary key initially to find the records rather than the name, changing the text box from enabled = true to false in VBA. That should be much simpler. But having tried this method, I'd like to know what I'm missing

Incidentally, if I step through the code I get

Run-time error number 2046 pop up 'The command or action 'FindRecord' isn't available now.

This does not pop up if I'm not stepping through.

user_7607190 · Answer

Apostrophes and quotes in data are an issue in SQL statements or anything that mimics WHERE clause of SQL (WHERE argument of OpenForm/OpenReport and domain aggregate functions). FindRecord does not have that issue as it is emulating Find/Replace dialog. Remove the Replace() operation and your original code should work. It does for me. Example:
DoCmd.OpenForm "Main", , , , , , Me.cbxProd

Private Sub Form_Load()
Me.tbxName.SetFocus
DoCmd.FindRecord Me.OpenArgs
End Sub

Could eliminate issue by searching numeric ID field instead.

Using FindRecord with records containing double quotes

1 Answers