Search facility problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scubadiver - 20 Mar 2007 12:47 GMT

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

Reply to this Message

Allen Browne - 20 Mar 2007 13:55 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I have a search facility using the following in the after update event of
> a
[quoted text clipped - 39 lines]
>
> Cheers

Reply to this Message

scubadiver - 20 Mar 2007 14:54 GMT

doh!

Slap on forehead!

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

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

Reply to this Message