Proper Quotation?

J

Jay

I have a Double Click Event Procedure that launches a form with the specific
record only. Works fine unless there is a ' in the text of the link. i.e.
Susans Call works but Susan's Call creates an error.

Below if the code for the link, how would I handle passing the appostrophe
without causing an error:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ViewNoteForm"

stLinkCriteria = "[Notes]=" & "'" & Me![Notes] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
M

martdi

Maybe you could make your link by using the ID

or try to double single quotes by using replace( Me![Notes], "'", "''")

second parameter is a single quote between double quotes
third parameter is two single quotes between double quotes
 
J

Jay

martdi,

I didn't even think of putting a Unique Note ID on the record. Went ahead
and did that and all is well again.

Many thanks.

martdi said:
Maybe you could make your link by using the ID

or try to double single quotes by using replace( Me![Notes], "'", "''")

second parameter is a single quote between double quotes
third parameter is two single quotes between double quotes

I have a Double Click Event Procedure that launches a form with the specific
record only. Works fine unless there is a ' in the text of the link. i.e.
Susans Call works but Susan's Call creates an error.

Below if the code for the link, how would I handle passing the appostrophe
without causing an error:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ViewNoteForm"

stLinkCriteria = "[Notes]=" & "'" & Me![Notes] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...
Click to expand...
 
J

John Vinson

I have a Double Click Event Procedure that launches a form with the specific
record only. Works fine unless there is a ' in the text of the link. i.e.
Susans Call works but Susan's Call creates an error.

Below if the code for the link, how would I handle passing the appostrophe
without causing an error:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ViewNoteForm"

stLinkCriteria = "[Notes]=" & "'" & Me![Notes] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...

Two solutions: one would be to double up all the apostophes in the
field; the other would be to use " as the delimiter instead of '. You
can do this by using two " marks within the string:

stLinkCriteria = "[Notes]=""" & Me![Notes] & """"

One BIG question though: why on Earth would you use a free-text note
field as a link criterion? Are you storing the note redundantly in two
tables? Would it not be better to open ViewNoteForm using a unique
primary key, rather than the text of a note!?


John W. Vinson[MVP]
 
Top