Runtime error '3075' VBA

[Neilster]

Does anyone know how to allow apostrophes in a text field with this code running on the after update event?

Answer = DLookup("[CompanyName]", "TblCustomerDetails", "[CompanyName] = '" & Me.CompanyName & "'")

:banghead::banghead::banghead::banghead:

 

[MarkK]

Why look up the company name using the company name? You already know the company name.

Private Sub Test1209348127()
    Const SQL As String = _
        "SELECT CompanyName FROM tCustomer " & _
        "WHERE CompanyName = [p0];"
    Dim vAnswer
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.CompanyName
        vAnswer = .OpenRecordset.Fields(0)
    End With
    
End Sub

 

[Tieval]

Can't say I've tried it but replace the ‘ with `as in change the actual apostrophe in the company name for the symbol on the key to the left of the 1 key on a standard keyboard, the one that looks like an apostrophe and isn't really.

 

[arnelgp]

Answer = DLookup("[CompanyName]", "TblCustomerDetails", "[CompanyName] = '" & fnFixApostrophe(Me.CompanyName & "") & "'")

Public Function fnFixApostrophe(ByVal strIn As String) As String
    On Error Resume Next
    Dim lngPos As Long
    Dim strTmp As String
    If Len(strIn) = 0 Then Exit Function
    lngPos = InStr(strIn, Chr(39))
    Do While lngPos <> 0
        strTmp = strTmp & Left(strIn, lngPos) & Chr(39)
        strIn = Mid(strIn, lngPos + 1)
        lngPos = InStr(strIn, Chr(39))
    Loop
    fnFixApostrophe = strTmp & strIn
End Function

 

[Neilster]

Hi Guys

Thanks for your replies, The idea is when the user enters a new company in the text field it will check for duplicates and show a warning message that this company already exsits in the database.

If a name like for example Shem's Resturant I get the runtime error 3075

syntax error (mising operator) in query expression '[companyname] = 'Shem's Resturan".

 

[MarkK]

If your data might contain quotes, then your most flexible and robust approach will be to use a parameterized querydef, which allows parameters that contain quotes. To check for the existence of a CompanyName I would use Count(), like . . .

Private Function CustomerExists(Customer As String) As Boolean
    Const SQL = "SELECT Count(*) FROM tCustomer WHERE CompanyName = [p0];"
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.CompanyName
        CustomerExists = Nz(.OpenRecordset.Fields(0), 0)
    End With
End Function

 

[Neilster]

This is how it looks full code. I'm still getting runtime error )-:

Private Sub CompanyName_AfterUpdate()

Call CustomerExists(0)
Dim Answer As Variant

Answer = DLookup("[CompanyName]", "TblCustomerDetails", "[CompanyName] = '" & Me.CompanyName & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate contact Number Found!" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, ""

Cancel = True
Me.CompanyName = ""

Else

End If

End Sub

 

[sneuberg]

Escape the single quotes with replace like:

Answer = DLookup("[CompanyName]", "TblCustomerDetails", "[CompanyName] = '" & Replace(Me.CompanyName,"'","''") & "'")

or you can make a function like:

'function to escape single quotes
Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

and use it like:

Answer = DLookup("[CompanyName]", "TblCustomerDetails", "[CompanyName] = '" & ESQ(Me.CompanyName) & "'")

 

[Neilster]

Thanks sneuburg!!!

I was thinking it was this as I've been researching. but this line works a treat!

Answer = DLookup("[CompanyName]", "TblCustomerDetails", "[CompanyName] = '" & Replace(Me.CompanyName,"'","''") & "'")

THANKS FOR EVERYONE'S HELP