Syntax Error when clicking on Form Button

[mkdrep]

I have a program that has a "GC" Button that I click on which takes me to a General Contractor Form. It works perfectly unless the Firm has an apostrophe in it's name. For example "D'Agostino". (refer to attached DAgostino1.jpg). When I click on the GC button, I get the attached Syntax error, (Syntax on DAgostiono.jpg).

The third attachment (GC Firm Button Code.jpg) shows the VBC for this button.

Any suggestions how to fix this error?

Thanks!

 

[pbaldy]

I would have cut/pasted code but can't with a picture. Instead of using the single quotes around the value, use Chr(34).

 

[Simon_MT]

It is the aposthrope in d'Agostino that creates the problem.

This works as well: ClientsCriteria = "[Client Surname] like """ & .[Field1] & """"

Simon

 

[pbaldy]

It is the aposthrope in d'Agostino that creates the problem.

This works as well: ClientsCriteria = "[Client Surname] like """ & .[Field1] & """"

Simon

It will work, though I personally dislike all the quotes (I'm easily confused). I'd have

"[Client Surname] = " & Chr(34) & [Field1] & Chr(34)

 

[mkdrep]

It will work, though I personally dislike all the quotes (I'm easily confused). I'd have

"[Client Surname] = " & Chr(34) & [Field1] & Chr(34)

Here is my original code. Would you be kind enough to edit it so I understand where the new code goes? thanks.

Private Sub GCFirmButton_Click()
On Error GoTo Err_GCFirmButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GC Names"
stLinkCriteria = "[GCName]=" & "'" & Me![GC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_GCFirmButton_Click:
Exit Sub
Err_GCFirmButton_Click:
MsgBox Err.Description
Resume Exit_GCFirmButton_Click

End Sub

 

[pbaldy]

This is the line you would change.

stLinkCriteria = "[GCName]=" & "'" & Me![GC] & "'"

 

[mkdrep]

Here is my original code. Would you be kind enough to edit it so I understand where the new code goes? thanks.

Private Sub GCFirmButton_Click()
On Error GoTo Err_GCFirmButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GC Names"
stLinkCriteria = "[GCName]=" & "'" & Me![GC] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_GCFirmButton_Click:
Exit Sub
Err_GCFirmButton_Click:
MsgBox Err.Description
Resume Exit_GCFirmButton_Click

End Sub

Code worked perfectly. Thank you very much for your help! Another quick question, I assume you picked Chr(34) because that will allow an apostrophe as a wildcard....is the a list of Chr(??) someplace that will cover allowing other special characters in a search?

thanks.............Mark

 

[pbaldy]

Jason has a tutorial here that explains a lot of that:

http://www.baldyweb.com/BuildSQL.htm