access 2010 vba dealing with values with apostrophes in them

[gtjoeckel]

Hello,

I am new to the forum and am looking for some guidance on how to setup my access 2010 VBA code to handle customer name values with an apostrophe in them.
The function works perfectly for customers without apostrophes.
My function is a combo box search that displays all of the entries that match the selection from the combo box search.
Here is the VBA code:

Private Sub cboCustomerSearch_AfterUpdate()
myCustomer = "Select * from tblCustomerMaster where ([Customer] = '" & Me.cboCustomerSearch & "')"
Me.Form.RecordSource = myCustomer
Me.Form.Requery
End Sub

I think I need to convert the value of [Customer] in my tblCustomerMaster to a string value?

Thanks,
Garrett

 

[MarkK]

Welcome to the forum!!!

You can just use a parameterized QueryDef to open the recordset, like...

Const SQL_SELECT As String = _
    "SELECT * " & _
    "FROM tblCustomerMaster " & _
    "WHERE Customer = p0 "

Private Sub cboCustomerSearch_AfterUpdate()
    With CurrentDb.CreateQueryDef("", SQL_SELECT)
        .Parameters(0) = Me.cboCustomerSearch
        Set Me.Recordset = .OpenRecordset
    End With
End Sub

See what happens there? And the QueryDef handles delimiters automatically, so it doesn't matter if there are delimiters embedded in your data.
hth
Mark

 

[jdraw]

You need to do what is called "escaping the quote" or it could be any other character in certain situations.
What this means is that for names like O'Mally or O'Halloran,
you have to "double up the single quote"/escape the single quote.

If you have a single quote in the name, then replace the single quote with 2 single quotes, for your search.


Here is a sample illustrating the concept

DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'")

Ooooops: I see MarkK has responded. I like his response better.

 

[Cronk]

Alternatives

"Select * from tblCustomerMaster where ([Customer] = " & """"Me.cboCustomerSearch """" & ")"

"Select * from tblCustomerMaster where ([Customer] = " & chr(34) & Me.cboCustomerSearch & chr(34) & ")"

I use the second one and always on names and addresses

 

[Minty]

I'd go with the parameter query every time, as it doesn't break on any strange characters.

I thought I had caught all of these until we had a customer called 6" Nail's, which breaks a number of the workarounds.