Search facility problem

[scubadiver]

I have a search facility using the following in the after update event of a
combo box:

Dim rstClone As Object
Set rstClone = Me.RecordsetClone
rstClone.FindFirst "[Qry_ELS]=" & Me.QueryNo
Me.Bookmark = rstClone.Bookmark
Set rstClone = Nothing
Me.QueryNo = Null

This works perfectly fine.

I have changed the code slightly for a different form and different query
(but the set up is exactly the same):

Dim rstClone As Object
Set rstClone = Me.RecordsetClone
rstClone.FindFirst "[CustomerName]=" & Me.CName
Me.Bookmark = rstClone.Bookmark
Set rstClone = Nothing
Me.CName = Null

For one search option I getting the following message:

run-time error '3077': Syntax error (missing operator) in expression.

For the other search option I get the following:

Run-time error '3070': The Microsoft Jet database engine does not recognise
'RSSL' as a valid field name or expression.

In both instances the following line is highlighted:

rstClone.FindFirst "[CustomerName]=" & Me.CName

I don't understand why it can work perfectly well in one form but not
another.

Any ideas?

Cheers

 

[Allen Browne]

If CustomerName is a Text field (not a Number field), you need extra quotes:
rstClone.FindFirst "[CustomerName] = """ & Me.CName & """"

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

Several things can go wrong with that code anyway:
a) The value in the text box could be Null. Use IsNull() to test.
b) The value might not be found (e.g. filtered.) Test NoMatch.
c) It may not be possible to save the record in the form (e.g. if a required
field is missing), so the attempt to move to the next record will fail.

 

[scubadiver]

doh!

Slap on forehead!

:

If CustomerName is a Text field (not a Number field), you need extra quotes:

rstClone.FindFirst "[CustomerName] = """ & Me.CName & """"