ComboBox Not In List - Help needed

J

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?
 
G

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
 
J

Joe

Thanks Graham, that did the trick.

Graham R Seach said:
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 said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Validation Rule Problem 4
using multiple fields from a table 3
Not in List Event 4
Limit to List question 11
acDialog - Form Invisible? 1
Help with this Event Procedure 14
Not In List CBO Help 0
Not In List Cbo Help 5

Top