Problem with NotInList Event

[kjpreston]

I have the following code to allow adding an item to a combo box's underlying table:

Private Sub ORIGIN_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_ORIGIN_NotInList
'Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

'Confirm that the user wants to add the new Origin
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to Add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add a customer, set the Response
'argument to suppress an error message and undo changes.
Response = acDataErrContinue
'Display a customized message
MsgBox "Please try again."

Else
'If the user chose to add a new ccustomer, open a recordset
'using the Origin table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("TBL_ORIGIN", dbOpenDynaset)
Do Until Rs.NoMatch
NewID = InputBox("Origin" & NewID & " already exists." & _
vbCr & vbCr & Msg, NewID & "Already Exists")
Rs.FindFirst BuildCriteria("ORIGIN", dbText, NewID)
Loop
'Create a new record
Rs.AddNew
'Assing the NewID to the ORIGIN Field
Rs![ORIGIN] = NewID
'Save the record.
Rs.Update
'Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_ORIGIN_NotInList:
Exit Sub
Err_ORIGIN_NotInList:
'An unexpected error occurred, display the normal error message
MsgBox Err.Description
'Set the Response argument to suppress an error message and undo
'changes.
Response = acDataErrContinue
End Sub

The problem is it will not allow the use of commas. My users need to be able to add City, State to this combo box. The message I get is "The expression you entered contains invalid syntax, or you need to enclose your text in quotes." I tried enclosing the text in quotes and it didn't like that either.

Any suggestions?

Thanks,
kjpreston

 

[PHV]

I don't see in your code where you add NewData in any table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjpreston]

The code will add data to the table, although I'm not sure how the only place the table name is is "Set Rs = Db.OpenRecordset("TBL_ORIGIN", dbOpenDynaset)", unless you use a comma like in City, State. Is there some way to tell it to allow the commas?

 

[kjpreston]

Thanks anyway folks, I figured it out. ::_)

 

[rccline]

For the record: Please tell us the answer.

Robert

 

[kjpreston]

I had to change it to the If statement to:
If intAnswer = vbYes Then
strSQL = "INSERT INTO TBL_ORIGIN ([ORIGIN]) " & _
"Values ( '" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new origin has been added to the list." _
, vbInformation, "Traffic"
Response = acDataErrAdded
Else
MsgBox "Please choose an origin from the list." _
, vbInformation, "Traffic"
Response = acDataErrContinue
End If

And that did the trick.