Error with VBA code on search

[SaraMegan]

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

 

[cpod]

Try this:

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

 

[SaraMegan]

Woohoo!

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

--Sara

 

[SaraMegan]

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

 

[cpod]

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

 

[Pat Hartman]

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

 

[SaraMegan]

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.