if command for further data required

[Delid4ve]

Firstly apologies if this is in the wrong place,

I have a sub form with a combo box that selects from another table, if not in the list it gets added to the source table as follows:

Private Sub Combo8_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new cylinder?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

'Append the NewData as a record in the Categories table.
strTmp = "INSERT INTO Cylinder ( CylinderSerial ) " & _
"SELECT """ & NewData & """ AS CylinderSerial;"
DBEngine(0)(0).Execute strTmp, dbFailOnError

'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded
End If
End Sub

However, if the new data is added to the list i also need the user to select from a list the type of product within that cylinder. Can this be done? And if so, what would the 'if command be to pull a further 'list box' for the user to select from.

Any help appreciated, many thanks

 

[Garrett!]

Maybe with a recordset? Loop through the recordset a look for a match. If is not in the list set focus to the other combobox and after the user selects from the second combobox add the record to the recordset.

 

[TJPoorman]

Probably the best way in order to guarantee that you will get both pieces of information is to create a modal popup that they would select the type, then insert that to the tables.