Problems with Combo Box Search Option in my Form

[Nika]

I am having problems with my combo box search option. It won’t search the
last names with apostrophe s, for example: O`Neal.
I get an error. The box popped up: Microsoft Visual Basic; Run-Time Error
“3077â€; Syntax Error (missing operator) After clicking debug, I got the
following (with highlighted rs.FindFirst "[LAST NAME] = '" & Me![Combo384]
& "'")

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[LAST NAME] = '" & Me![Combo384] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Just to mention, I have a query with all last names listed. When I search
the last name, I spell the last name exactly as it is spelled in my query.
And I have no problems searching other last names.

Can somebody help me to clear this error?

Thanks.

 

[Allen Browne]

Use double-quotes around the value you embed in the string:

Private Sub Combo384_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
strWhere = "[LAST NAME] = """ & Me![Combo384] & """"
rs.FindFirst strWhere
If rs.NoMatch then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Notes:
====
1. If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

2. You need to test NoMatch after a find: not EOF.

3. I find it better to search RecordsetClone rather than Recordset.Clone

4. Assuming the data is in Access tables, it's better to declare a DAO
recordset rather than just an object.

5. It's worth explicitly saving any edits in progress.

6. It's good practice to clean up your objects.

7. Using a separate string for the FindFirst makes it easire to debug. If
it's not working as expected, you can add the line:
Debug.Print strWhere
to see how it looks.

8. This assumes the combo is unbound, and its bound column is text.

9. There could be multiple people with the desired surname, so it might be
better to apply a filter rather than just find the first match.

 

[Klatuu]

Allen's Answer is correct and I don't intend to interfer, but to offer a tip.
For a long time I had problems trying to figure out how many doulbe qoutes
went where. I read all the rules, tried various constants suggested by
others, but I never seemed to get it right. It was really frustrating. Then
a trick dawned on me that I began using that made it really easy to
understand.

The rule is that to put a double qoute in a string, you type in two double
qoutes. Easy enough, but then there are the opening qoutes and closing qoutes
for a string and I seem to get confused on them. But then the idea came to
me that to replace one single quote with one double qoute I needed to type in
two double qoutes. So, my trick was to enter the code using the single
qoutes:

Easy enough! Then, go back and replace each singe qoute with two double
qoutes.
first single qoute ----v
rs.FindFirst "[LAST NAME] = '" & Me![Combo384] & "'")

Changed to:
two doubles ----vv
rs.FindFirst "[LAST NAME] = """ & Me![Combo384] & "'")

Second singe qoute ----v
rs.FindFirst "[LAST NAME] = """ & Me![Combo384] & "'")

Changed to:
two doubles ----vv
rs.FindFirst "[LAST NAME] = """ & Me![Combo384] & """")

Done! After a while, it become intuitive and you can enter the doubles as
easily as the singles.

 

[Nika]

Thanks so much. It worked.

I have just started learning Access. I have never taken any Visual Basic
Classes.
I just simply copied your answer, because I didn’t understand it.
Could you please let me know if you know about any online tutorial or some
other classes I could take to learn to build these types of codes?

Thanks again.

 

[Klatuu]

Try this one:
http://www.allenbrowne.com/casu-22.html

--
Dave Hargis, Microsoft Access MVP

Thanks so much. It worked.

I have just started learning Access. I have never taken any Visual Basic
Classes.
I just simply copied your answer, because I didn’t understand it.
Could you please let me know if you know about any online tutorial or some
other classes I could take to learn to build these types of codes?

Thanks again.

I am having problems with my combo box search option. It won’t search the
last names with apostrophe s, for example: O`Neal.
I get an error. The box popped up: Microsoft Visual Basic; Run-Time Error
“3077â€; Syntax Error (missing operator) After clicking debug, I got the
following (with highlighted rs.FindFirst "[LAST NAME] = '" & Me![Combo384]
& "'")

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[LAST NAME] = '" & Me![Combo384] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Just to mention, I have a query with all last names listed. When I search
the last name, I spell the last name exactly as it is spelled in my query.
And I have no problems searching other last names.

Can somebody help me to clear this error?

Thanks.

 

[Bob Larson]

Some other good free tutorials:

http://www.functionx.com

look for the Access 2003 or 2007 tutorials, depending on what you have.


--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

Thanks so much. It worked.

I have just started learning Access. I have never taken any Visual Basic
Classes.
I just simply copied your answer, because I didn’t understand it.
Could you please let me know if you know about any online tutorial or some
other classes I could take to learn to build these types of codes?

Thanks again.

I am having problems with my combo box search option. It won’t search the
last names with apostrophe s, for example: O`Neal.
I get an error. The box popped up: Microsoft Visual Basic; Run-Time Error
“3077â€; Syntax Error (missing operator) After clicking debug, I got the
following (with highlighted rs.FindFirst "[LAST NAME] = '" &
Me![Combo384]
& "'")

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[LAST NAME] = '" & Me![Combo384] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Just to mention, I have a query with all last names listed. When I search
the last name, I spell the last name exactly as it is spelled in my
query.
And I have no problems searching other last names.

Can somebody help me to clear this error?

Thanks.