Syntax error (missing operator) in expression.

[Indigo]

I am using Access 2010 and for the life of me can't see why I am getting this error message. I am using the following to filter a subform from my main form:

    Dim rs As DAO.Recordset
    Dim frm As Form
    Set frm = subfrmCharityDetail.Form
    Set rs = frm.RecordsetClone
    
    rs.FindFirst "[CharityName] = '" & Me.CharityName & "'"
    If Not rs.NoMatch Then frm.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing

Could I please ask for another pair of eyes to look at this for me and see where I am going wrong? Thank you.

 

[arnelgp]

if your are executing this from within your subform:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "[CharityName] = '" & Me.CharityName & "'"
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing

 

[Indigo]

I am executing from my main form. The field, "CharityName" exists on the main form.

 

[arnelgp]

on main form:

Dim rs As DAO.Recordset
Set rs = Me.subfrmCharityDetail.Form.RecordsetClone

rs.FindFirst "[CharityName] = '" & Me.CharityName & "'"
If Not rs.NoMatch Then Me.subfrmCharityDetail.Form.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing

 

[Indigo]

If I use what you suggested, I get a Type MisMatch error... and I'm not sure how your's is different from mine...?

I have:

Dim frm As Form
Set frm = subfrmCharityDetail.Form
Set rs = frm.RecordsetClone

and you have:

Set rs = Me.subfrmCharityDetail.Form.RecordsetClone

Granted you have in one line what I have in 3, but what's the difference?

 

[arnelgp]

no difference i supposed, have you identified which line is causing the error.

 

[Indigo]

This line causes the errror:

rs.FindFirst "[CharityName] = '" & Me.CharityName & "'"

but not always..... I'm wondering if I have a corrupt form....?

 

[Cronk]

Do you have any records where CharityName contains a single quote?

Try running a query with the criteria is 'YourCharityName'

 

[Indigo]

Yes, I have several with a single quote.... many of the names are possessive or in French.

Should I be removing the single quotes or is there a work around? I have over 86,000 records in the table....

 

[arnelgp]

paste the following code in the module, and on your rs.FindFirst:

rs.FindFirst "[CharityName] = '" & fnFixApostrophe(Me.CharityName & "") & "'"

Public Function fnFixApostrophe(ByVal strIn As String) As String
On Error Resume Next
Dim lngPos As Long
Dim strTmp As String
If Len(strIn) = 0 Then Exit Function
lngPos = InStr(strIn, Chr(39))
Do While lngPos <> 0
strTmp = strTmp & left(strIn, lngPos) & Chr(39)
strIn = Mid(strIn, lngPos + 1)
lngPos = InStr(strIn, Chr(39))
Loop
fnFixApostrophe = strTmp & strIn
End Function

if that doesnt solve it, then your CharityName field must be numeric, and has a lookup table.

 

[Indigo]

Sorry doesn't fix it. The error no longer comes up, but the subform does not populate with the selected Charity data. My Charity field is an indexed text field.

 

[arnelgp]

place a breakpoint on your code. test the value of rs.NoMatch. if it returns false then check your table if the name does exists.

 

[gemma-the-husky]

is this really a subform?

if so, why not just set master field/linked field and let access sort it out?

 

[Indigo]

Hi Gemma/Dave & arnelgp,

Yes, it is a subform. Let me explain a bit further. The mainform is unbound and has a text box (CharityName) and a list box (CharityList) on it. I am using it as a search form with the following code:

Private Sub CharityList_AfterUpdate()
    UpdateSearch Me.CharityName, Me.CharityList
End Sub
 
Private Sub CharityName_Change()
    Dim varRetval As Variant
 
    varRetval = SearchTable(Me.CharityName, _
    Me.CharityList, "CharityName", "CharityName")
 
End Sub
 
Private Sub CharityName_Exit(Cancel As Integer)
    UpdateSearch Me.CharityName, Me.CharityList
End Sub

What I want to have happen is once the user finds the charity he is looking for, for the subform to populate with all of the other details about the charity, i.e. address, contact, phone number, email, website...

It works fine for all of the charities except for those with an apostrophe... when the charity has an apostrophe (i.e. St. John's Church) I get the syntax error.

With arnelgp's function, I no longer get the syntax error, but the subform does not populate with the charity information.

 

[gemma-the-husky]

well you can just link the master/subform together can't you? look at the data tab for the subform object in the main form. you can enter the details manually even though the master form is unbound.

 

[Indigo]

Thank you Gemma/Dave.... sometimes the simplest solution is the best!