Inserting new argument to table through search form of another table

Question

In my database I have a form for searching products (fields : id, name, manufacturer_id[set to display manufacturer's name], category_id, price). I would like to have the ability of adding manufacturers through this form. So I've created vba script but it does not work. Still when I enter new name it gives me prompt to "Select element from the list". What am I doing wrong ?

Private Sub manufacturer_id_NotInTheList(NewData As String, Response As Integer)

Dim strSQL As String, strInfo As String

strInfo = "Manufacturer " & NewData & " is not on the list." & vbCrLf & "Add?"
If MsgBox(strInfo, vbYesNo + vbQuestion, "Element not on the list") = vbYes Then
  strSQL = "INSERT INTO manufacturer (name, country, id_distributor) VALUES ('" & NewData & "','Undefined', '0');"
  DoCmd.SetWarnings (False)
  DoCmd.RunSQL strSQL
  Response = acDataErrAdded
Else
  Response = acDataErrContinue
  NewData = ""
  Me.manufacturer_id.Text = ""
End If

End Sub

Answer 1

If the Limit to List property of your drop down box is set to true, Access will intercept and complain first (because your user has added a manufacturer that is not in the list).

Try setting Limit to List to false, then your procedure should run.