MS Access throwing error because of parentheses

Question

I have some navigation buttons that do not work when the text has parentheses in it. The text comes from a combo box, I take a value from it and perform a findfirst . This was working great until I hit values with parenthesis. For example 3D-1(A)-461 will throw the 2423 error.

Originally I was gonna use a recordset.movenext, but I have three subforms that have to requery and thought this was the best method.

I appreciate all your help.

  If TCTOfilt.ListIndex <> TCTOfilt.ListIndex - 1 Then
    TCTOfilt.ListIndex = TCTOfilt.ListIndex + 1

    Forms!frmMasterDashboard.Requery
    Forms!frmMasterDashboard!subFrmApplicNotes.Form.Requery

    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset(Name:="ACbyTCTO", Type:=RecordsetTypeEnum.dbOpenDynaset)
    rst.FindFirst Criteria:=BuildCriteria(Field:="[TCTO Number]", FieldType:=dbText, Expression:=Me.TCTOfilt)
    If Not rst.NoMatch Then
        Me!subFrmOEMNOI.Form.Requery
    Else
        Exit Sub
    End If
Else
    TCTOfilt.ListIndex = TCTOfilt.ListCount + 1

    Forms!frmMasterDashboard.Requery
    Forms!frmMasterDashboard!subFrmApplicNotes.Form.Requery
    Set rst = CurrentDb.OpenRecordset(Name:="ACbyTCTO", Type:=RecordsetTypeEnum.dbOpenDynaset)
    rst.FindFirst Criteria:=BuildCriteria(Field:="[TCTO Number]", FieldType:=dbText, Expression:=Me.TCTOfilt)
    If Not rst.NoMatch Then
        Me!subFrmOEMNOI.Form.Requery
    Else
        Exit Sub
    End If
End If

That's odd. Parentheses don't have specific meaning within Access fields. Which line of code is causing the error? — Robert Harvey, Nov 26 '18 at 18:03
It's not giving me a line number, but I believe it is Expression:=Me.TCTOfilt. when that variable has normal text in it the button works find. But the parentheses break it — mightymax, Nov 26 '18 at 18:07
OH BuildCriteria is the Criteria i'm sending to rst.FindFirst. It gives the Field to look in, Field Type and finally Expression to search for. rst.FindFirst Criteria:=BuildCriteria(Field:="[TCTO Number]", FieldType:=dbText, Expression:=Me.TCTOfilt) — mightymax, Nov 26 '18 at 18:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184279/discussion-between-maxine-hammett-and-robert-harvey). — mightymax, Nov 26 '18 at 18:18
I haven't the slightest idea what's causing this. Maybe someone else knows. — Robert Harvey, Nov 26 '18 at 18:19
[BuildCriteria](https://learn.microsoft.com/en-us/office/vba/api/access.application.buildcriteria) — Robert Harvey, Nov 26 '18 at 18:20

score 3 · Accepted Answer · answered Nov 26 '18 at 18:33

You're misusing BuildCriteria. It's not meant to sanitize fields, it's meant to replicate the behaviour of the query builder, where parentheses do have meaning (they invoke functions).

If you want to sanitize strings, you either have to write your own string sanitation function or use a pre-existing one (such as Gustav's CSql).

Generally, I recommend using parameters. However, these are not available for .FindFirst. They are available for querydefs, however, and using a querydef to directly query what you want generally is way faster than opening a recordset and then searching that recordset. See here how to use parameters in Access.

Thank you I'll look into it. – mightymax Nov 26 '18 at 22:09 — mightymax, Nov 26 '18 at 22:09

MS Access throwing error because of parentheses

1 Answers1