Error with VBA code on search

SaraMegan

Starving Artist
Local time
Today, 11:54
Joined
Jun 20, 2002
Messages
185
Please bear with me... I'm not great with Access, and I'm completely dumb with VBA. I'm completely VBA Illiterate.

That said, I'm trying to do a search thing with a combo box in a pop-up form. I got the form from hayley and have been trying to convert it for my needs. It comes up fine, and looks great, but then when I select the record from the list, that form is supposed to close and the "kardex main form" is supposed to show up with that record.

INSTEAD, when I select the record from the list, I get a missing operator error and I don't know what it means or how to fix it. I'll paste the code and see if anyone can decipher by looking at it.

Private Sub lstNames_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "kardex main form"

stLinkCriteria = "[fldName]=" & Me![lstNames]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Me![lstNames] = ""
DoCmd.Close acForm, "frm Wild Search"
End Sub

The error message says:

Syntax error (missing operator) in query expression '[fldName]=A & D Welding'.

A & D Welding is the record i clicked on when I got the error.

When I select "debug," it highlightes the line:

DoCmd.OpenForm stDocName, , , stLinkCriteria

Does anyone have a thought about this? Do I need to provide more information? I'd be happy to.

Thanks in advance, you're all wonderful. :)

--Sara
 
Last edited:
Try this:

stLinkCriteria = "[fldName]='" & Me![lstNames] & "'"
 
Woohoo!

Ooh! That works! Thank you *so* much! I just wish I understood all of that stuff... But thank you!!!

--Sara
 
uh oh...

Okay, so the search form works fine on most records.

However, on some records it gives me a Run-time error (#3075).

I've looked at the records it does this for... they seem to be the ones with an apostrophe in their title. (ie "Ernie's Gas")

Syntax error (missing operator) in query expression '[fldName]='Ernie's Gas'.

Is this because of the apostrophe, and it now thinks it's missing one because there's now a complete set? ('Ernie')

Any ideas on how to fix this?

--Sara
 
stLinkCriteria = "[fldName]=""" & Me![lstNames] & """"
 
Whenever a text field might contain embedded single quotes, you need to use the double quote as the delimiter.

stLinkCriteria = "[fldName]=" & Chr(34) & Me![lstNames] & Chr(34)

I used the Chr() function because I have a hard time reading strings that contain bunches of quotes. In most of my databases I define a constant to use for this purpose:

Public Const QUOTE As String = """"

Then your code would be:

stLinkCriteria = "[fldName]=" & QUOTE & Me![lstNames] & QUOTE
 
Last edited:
Thanks, Pat, for helping me understand. I thought that was what was going on, but I wasn't sure why or what to change.

thanks, cpod for showing me what to change.
 

Users who are viewing this thread

Back
Top Bottom