Not on list event

[TaylorLeigh]

When I am in a subform and enter a product that is not on the list I want to
be prompted "Do you want to add to list" and when I say yes it will poplulate
the product table. If what I am doing is not a good idea then I want it to
go to the product form, let me add the product and then return me to the
subform in the product field so I can continue entering data.

Thanks,
Private Sub Product_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Product...")
If i = vbYes Then
strSQL = "Insert Into tblProducts ([strProducts]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[Arvin Meyer [MVP]]

Better to open the form and add the Product so that you can add the other
details. Otherwise you will wind up with partial data in many of your
products. I have sample code to do just that at:

http://www.datastrat.com/Code/NotInListCode.txt

and a sample application using that code at:

http://www.accessmvp.com/Arvin/NotInListDemo.zip