Working on my database I use a modified piece of code gotten from thescripts. The code works perfectly except for one thing. When entering data containing the character ' , I get the error: Syntac error (missing operator) in query expression.
It took me a while but I discovered that entering the character ' twice in the form, it's added to the table with only 1 '.
Example 1:
Jenny O'Neal -> gives an error
Example 2:
Jenny O''Neal -> accepted into database as Jenny O'Neal -> getting the message that it can't be found and that I should select another one from the list -> I select Jenny O'Neal from the list.
I'm not botherd with having to enter ' twice but the error is somewhat annoying. How can I resolve this? Either by getting ' to be accepted from the start instead of using ' twice (preferred) or by not getting the error message as stated in the second example. On the forum I saw the mention of Chr(39) but I keep failing(unfortu nately since I'm relatively new to this). The code is as follows:
It took me a while but I discovered that entering the character ' twice in the form, it's added to the table with only 1 '.
Example 1:
Jenny O'Neal -> gives an error
Example 2:
Jenny O''Neal -> accepted into database as Jenny O'Neal -> getting the message that it can't be found and that I should select another one from the list -> I select Jenny O'Neal from the list.
I'm not botherd with having to enter ' twice but the error is somewhat annoying. How can I resolve this? Either by getting ' to be accepted from the start instead of using ' twice (preferred) or by not getting the error message as stated in the second example. On the forum I saw the mention of Chr(39) but I keep failing(unfortu nately since I'm relatively new to this). The code is as follows:
Code:
Private Sub Distributie_NotInList(NewData As String, Response As Integer)
On Error GoTo Distributie_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("Het bedrijf" & Chr(34) & NewData & _
Chr(34) & "staat niet in de lijst." & vbCrLf & _
"Wil je deze aan de lijst toevoegen?" _
, vbQuestion + vbYesNo, "Bedrijven")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Bedrijven([Bedrijven]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Het bedrijf is toegevoegd aan de lijst." _
, vbInformation, "Bedrijven"
Response = acDataErrAdded
Else
MsgBox "Kies een bedrijf uit de lijst." _
, vbInformation, "Bedrijven"
Response = acDataErrContinue
End If
Distributie_NotInList_Exit:
Exit Sub
Distributie_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Distributie_NotInList_Exit
End Sub
Comment