Proper Quotation?

[Guest]

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

 

[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

 

[Guest]

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.

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

 

[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

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]