Error message on combobox

[Tony Williams]

I have a dropdown box called cmbCompany2 which is based on a table
tblCompany on a form frmMain which is based on a table tblMain. The data
selected in the combobox is stored in the field txtcompany in the table
tblmain. There is a NotinList event with the code
Private Sub cmbCompany2_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Value." & vbCrLf
strMsg = strMsg & " Do you want to add the new Value to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompany", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!cmbCompany = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
When the user selects a company from the list there is an error message that
says
"You tried to Assign the Null value to a variable that is not a Variant data
type"
Clicking on OK takes the user back to the form and the data is selected.
Why am I getting this message and how can I get rid of it?
TIA
Tony Williams

[Keith]

Tony,

Just a stab in the dark here but it might be something with the
'Response' variable which is defined as an Integer - I don't know what
the likes of 'acDataErrContinue' contain but if it's ever 'null' then it
might cause the error you're getting.

Regards,
Keith.
www.keithwilby.com

Tony Williams wrote:

I have a dropdown box called cmbCompany2 which is based on a table
tblCompany on a form frmMain which is based on a table tblMain. The data
selected in the combobox is stored in the field txtcompany in the table
tblmain. There is a NotinList event with the code
Private Sub cmbCompany2_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Value." & vbCrLf
strMsg = strMsg & " Do you want to add the new Value to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompany", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!cmbCompany = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
When the user selects a company from the list there is an error message that
says
"You tried to Assign the Null value to a variable that is not a Variant data
type"
Clicking on OK takes the user back to the form and the data is selected.
Why am I getting this message and how can I get rid of it?
TIA
Tony Williams