The following is an example of adding to a table via the combobox "NotInList" Event:
Private Sub cboClientID_NotInList(NewData As String, Response As Integer)
Dim intCurrentID As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
If vbNo = MsgBox("Add a new Client to the Names table?", vbQuestion + vbYesNo, "User action required") Then
Response = acDataErrContinue
Exit Sub
End If
intCurrentID = GetNextNameID()
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbNames", dbOpenDynaset)
rs.AddNew
rs.Fields(0) = intCurrentID
rs.Fields(1) = NewData
rs.Update
rs.Close
Set rs = Nothing
Response = acDataErrAdded
Set rs = db.OpenRecordset("JCNNameFiles", dbOpenDynaset)
rs.AddNew
rs!FileID = Me!cboFileID
rs!NameID = intCurrentID
rs.Update
Set db = Nothing
Forms!hfrmScratch!sName = NewData
If vbNo = MsgBox("Add detail to the Name record just created?", vbQuestion + vbYesNo, "User action required") Then
Me!lstClients.Requery
Exit Sub
End If
Me!lstClients.Requery
DoCmd.OpenForm "frmNameDetail"
Forms!frmNameDetail.Requery
End Sub
GetNextNameID is a simple function which gets the next unique index id for the table. I don't use the autonumber data type.