Not In List

[rnjalston via AccessMonster.com]

I have 4 comboboxes that are each filtered from the previous (Lets say they
are comboboxes A thru D). They are functioning properly. When I enter a
record that is not in the list for Combobox A, it says not in list would you
like to add it?. Answer yes and record is added. Now to combobox B, same
thing happens. However, They should be related. What code do I need to add
to have the record added to combobox B to be related to Combobox A?

Here is the code for the NotInList:

' If Entered Product is not in List allow add.
Private Sub cboProduct_NotInList(NewData As String, Response As Integer)
'Supress the default error message.
Response = acDataErrContinue
'Ask user if they want to add the new Product to the list/database
If MsgBox(NewData & " is not in list of Products. Add it?", vbYesNo) =
vbYes Then
Dim db As Database
Dim rstProducts As Recordset
Dim sqlProducts As String
Set db = CurrentDb()
sqlProducts = "Select * From Products"
'Create a new recordset to add the new PRODUCT to the database.
Set rstProducts = db.OpenRecordset(sqlProducts, dbOpenDynaset)
rstProducts.AddNew
rstProducts![Product] = NewData
rstProducts![ManufacturerID] = cboMfg.Value
rstProducts.Update
'Inform the combobox that the desired item has been added to the list.

Response = acDataErrAdded
rstProducts.Close 'Close the recordest
End If
End Sub

 

[BruceS]

When a value in a combo box changes (new value or not), do a re-query on all
combo boxes down the line from the one that changes. For example, in the
After_Update for the first one (A), you would have
Me.cboB.Requery
Me.cboC.Requery
Me.cboD.Requery

The second one's After_Update would have:
Me.cboC.Requery
Me.cboD.Requery

Note that a record added in the NotInList automatically forces a requery of
that combo box.

Also, when inserting the new value, more simple and direct code is to do it
with an Insert query, e.g.:

mySQL = 'INSERT INTO Products ([Product], [Manufacturer]) SELECT '" & _
NewData & "', " & Me.cboMfg.Value & ";"
DBEngine(0)(0).Execute mySQL, dbFailOnError

HTH,
Bruce