Add records Not In List

[Kysteratwork]

Hi all,

I found this fantastic code online (unfortunately I forgot where) which lets the user add a new record to a dropdown (ice cream flavours in this case), if the record is not in the list.

Private Sub Combo111_NotInList(NewData As String, Response As Integer)
    Dim rstFlavors As ADODB.Recordset
    Dim intAnswer As Integer
    
    On Error GoTo ErrorHandler
    
    intAnswer = MsgBox("Add " & NewData & " to the list of currencies?", _
                       vbQuestion + vbYesNo)
    
    If intAnswer = vbYes Then
        Set rstFlavors = New ADODB.Recordset
        rstFlavors.Open "Icecream", CurrentProject.Connection, _
                        adOpenStatic, adLockOptimistic

        rstFlavors.AddNew
        rstFlavors!flavour = NewData
        rstFlavors.Update
        Response = acDataErrAdded
    Else
        Response = acDataErrDisplay
    End If
    
    rstFlavors.Close
    Set rstFlavors = Nothing
    Exit Sub
    
ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

I would love to apply this code to one of my dropdowns, but slightly elaborated: it contains FirstName and LastName... (The dropdown shows EmployeeID and Name:[FirstName] &" "&[LastName])
Isn't there a smooth way to that it adds first the first name, then it asks me for the last name?

Can anyone help me out?

Kysteratwork

 

[Minkers]

I have a combo box that has sever columns, and I found a good way to add to all the colums was to pop up a small seperate form when the item was not in the list. My code is below.

HTH!
Mink


First, in the NotInList of the combo box:

Private Sub PartID_NotInList(newdata As String, Response As Integer)
Response = acDataErrContinue
Call Account_Name_Not_Found(newdata)
End Sub

Public Sub Account_Name_Not_Found(newdata)
Dim ans As Variant
' new account
gbl_exit_name = False

ans = MsgBox("Add " & newdata & " to the list of Parts?", _
vbYesNo, "Add New Part?")

If ans = vbNo Then
    Me.PartID = Null
    DoCmd.GoToControl "PartID"
    GoTo exit_it
End If

' add account
DoCmd.OpenForm ("frmPartNumber")
DoCmd.GoToRecord acForm, "frmPartNumber", acNewRec
Form_frmPartNumber.txtPartNumber = newdata


Me.PartID = Null

DoCmd.GoToControl "txtPartNumber"

exit_it:

End Sub

And on the mini form after filling in the required information and hitting submit:

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

DoCmd.RunCommand acCmdSaveRecord
Form_frmECN.Cost.Form.PartID.Requery
Form_frmECN.Cost.Form.PartID = Me.PNID
DoCmd.Close
Form_frmECN.Cost.Form.PartID.SetFocus
Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click
End Sub

 

[Kysteratwork]

I will try that. Thanks!

Kysteratwork