NotInList code

[jk12]

Hi all. This is the code that I have used in my NotInList event

Private Sub Suppliers_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Suppliers_NotInList

'-- We may need to add another Product
Response = MsgBox("[" & NewData & "] is not yet a valid Supplier..." & vbCr & vbCr & _
"Would you like to add this Supplier to your DataBase?", vbYesNo)

If Response = vbYes Then
'-- Create a new Product record
Dim db As DAO.Database
Dim MySQL As String
Set db = CurrentDb()
MySQL = "Insert Into TableSupplier (Suppliers) " & _
"Values(""" & NewData & """)"
db.Execute MySQL, dbFailOnError
Set db = Nothing
Response = acDataErrAdded '-- will requery!!
Else
Response = acDataErrContinue
End If

Exit_Suppliers_NotInList:
On Error Resume Next
Set db = Nothing
Exit Sub

Err_Suppliers_NotInList:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_Suppliers_NotInList
End Sub

The problem i'm having is that the code is not working. However, I have used the same code but modified to suit on another field elsewhere in the form which works fine. Any ideas????

 

[Rickster57]

not in list

Here's some code I used but can't remember where I got it from...but works

On Error GoTo oops:
Dim ctl As Control
Dim strSQL As String
Dim capitalizefirst

' Return Control object that points to combo box.
Set ctl = Me.Event1
' Prompt user to verify they wish to add new value.
If MsgBox("This event is not in list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to products table.

strSQL = " INSERT INTO tbltechnicalevent (technicalevent) SELECT '" & (NewData) & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

go2:

Exit Sub

oops:
Resume go2

 

[RuralGuy]

Hi jk12,
Your code looks like it should work. Are you getting an error? Have you single stepped the code to see what the values are?

 

[jk12]

Hi thanks for responding. problem is sorted now. thanks though

 

[RuralGuy]

Glad to hear it! Thanks for posting back.