strange search behaviour 1

[cynaptic]

I have a smallish database of about 3500 records, school names. I have a 'go to and find' combo which throws an error every time you search for a school beginning with 'St'
as in abreviated 'Saint'.

Runtime error '3077';
syntax error (missing operator) in expression.

Code:-

Option Compare Database

Private Sub Command46_Click()
On Error GoTo Err_Command46_Click


DoCmd.GoToRecord , , acNewRec

Exit_Command46_Click:
Exit Sub

Err_Command46_Click:
MsgBox Err.Description
Resume Exit_Command46_Click

End Sub
Private Sub Combo51_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SchoolName] = '" & Me![Combo51] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command53_Click()
On Error GoTo Err_Command53_Click


DoCmd.Close

Exit_Command53_Click:
Exit Sub

Err_Command53_Click:
MsgBox Err.Description
Resume Exit_Command53_Click

End Sub

-:end of Code

rs.FindFirst "[SchoolName] = '" & Me![Combo51] & "'"

The line above seems to be the problem.

I am not really a VBCoder, any help appreciated.

 

[petermeachem]

It's because this line

rs.FindFirst "[SchoolName] = '" & Me![Combo51] & "'"

becomes


rs.FindFirst "[SchoolName] = 'is's'"

which is one too many ''s.

If you are sure that there are no ", you can do


rs.FindFirst "[SchoolName] = " & chr$(34) & Me![Combo51] & chr$(34)

chr$(34) is "

Or, use the replace function to replace ' with '' as in


rs.FindFirst "[SchoolName] = '" & Replace(Me![Combo51],"'","''&quot & "'"

I hope that all makes sense, there's lots of ' '' and "
Peter Meachem
peter@accuflight.com

 

[cynaptic]

Thanks it works great.