Using NotInList in a combo box

I use the NotInList event a lot.   But with a little thought.  For example, I'm not at all sure I want users entering new part types very often because there should only be ten or twenty.   Or not at all if there are only three or four status types.

Dim strMsg As String, strSQL As String

    strMsg = "Driver '" & NewData & "' is not in the driver list " & vbCrLf & vbCrLf & _
        "Click Ok to add the driver."
    If MsgBox(strMsg, vbOKCancel) = vbOK Then
        Response = acDataErrAdded
        strSQL = "INSERT INTO Driver ( dDriverName ) " & _
            "VALUES (" & Chr$(34) & NewData & Chr$(34) & ");"
        CurrentDb.Execute strSQL, dbFailOnError
    Else
        Response = acDataErrContinue
        Me.tContactID.Undo
    End If

Note that the chr$(34) handle the situation with apostrophes in the data such as "O'Neil" and such interesting problems.  

If I need to update the record with additional data I then open a form.

Dim rs As Recordset, pID As Long

If MsgBox(tt_FetchMsg(6, NewData), vbOKCancel) = vbOK Then 
    Set rs = CurrentDb.OpenRecordset("Parts")
    rs.AddNew
    rs("pPartNumber") = NewData
    rs.Update
    rs.Move 0, rs.LastModified
    pID = rs("pID")
    rs.Close: Set rs = Nothing
    DoCmd.OpenForm "PartsDetail", , , "pID = " & str$(pID), , acDialog
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
    Me.cboepPartID.Undo
End If

The rs.Move 0, rs.LastModified and pID = rs("pID") get the ID of the just added record so I can use it to open the PartsDetail form.  

In this case the most important reason for opening the PartsDetail form is to assign a parts group.   By making that a required field in the table definition I can't add the record in a combo box NotInList event and then open a form based on that record.  So I have to ensure that parts without a part group are handled elsewhere in the system.  

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available