ComboBox Not In List - Help needed

[Joe]

I have a combo box on a form which provides a list of possible medical
diagnoses. The list can be added to if the item being input does not match
any existing items in the list using the following Not In List procedure...

Private Sub cboDiagnosis_NotInList(NewData As String, Response As Integer)
On Error GoTo CboDiagnosis_NotInList_Err
Dim intAnswer As Integer
Dim stSQL As String
intAnswer = MsgBox("The Diagnosis " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "FBW Physiotherapy")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Diagnosis has been added to the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrAdded
Else
MsgBox "Please choose a Diagnosis from the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrContinue
End If
CboDiagnosis_NotInList_Exit:
Exit Sub
CboDiagnosis_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CboDiagnosis_NotInList_Exit
End Sub

When a user enters an item with an apostrophe (i.e. Baker's Cyst) or
attempts to use a hyphen, semicolon or colon to describe co-existing
conditions the item is rejected with a syntax error. How can I get around
this problem?

 

[Graham R Seach]

Joe,

Use double-quotes, instead of single quotes. You can do that in one of the
following two ways:
Chr(34) & Chr(34) & Chr(34)
....or...
"""

In the following examples, I've used the second method.

intAnswer = MsgBox("The Diagnosis """ & NewData & _
""" is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "FBW Physiotherapy")

strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & NewData & """);"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Joe]

Thanks Graham, that did the trick.

Joe,

Use double-quotes, instead of single quotes. You can do that in one of the
following two ways:
Chr(34) & Chr(34) & Chr(34)
....or...
"""

In the following examples, I've used the second method.

intAnswer = MsgBox("The Diagnosis """ & NewData & _
""" is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "FBW Physiotherapy")

strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES (""" & NewData & """);"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a combo box on a form which provides a list of possible medical
diagnoses. The list can be added to if the item being input does not
match
any existing items in the list using the following Not In List
procedure...

Private Sub cboDiagnosis_NotInList(NewData As String, Response As Integer)
On Error GoTo CboDiagnosis_NotInList_Err
Dim intAnswer As Integer
Dim stSQL As String
intAnswer = MsgBox("The Diagnosis " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "FBW Physiotherapy")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Diagnosis([Diagnosis]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Diagnosis has been added to the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrAdded
Else
MsgBox "Please choose a Diagnosis from the list." _
, vbInformation, "FBW Physiotherapy"
Response = acDataErrContinue
End If
CboDiagnosis_NotInList_Exit:
Exit Sub
CboDiagnosis_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CboDiagnosis_NotInList_Exit
End Sub

When a user enters an item with an apostrophe (i.e. Baker's Cyst) or
attempts to use a hyphen, semicolon or colon to describe co-existing
conditions the item is rejected with a syntax error. How can I get around
this problem?