Auto Fill Combo Box Issue

[stlrain95]

I have a combo box in a form that I need the ability to add new data if necessary. I created the combo tied to a table. here is the code behind the combo box.

Dim demo As ComboBox, strMsg As String
Set demo = Me!CmbCustomer
strMsg = "This Customer is not in the list. Would you like to add it?"
If Msgbox(strMsg, vbOKCancel) = vbOK Then
Response = acDataErrAdded
DoCmd.OpenForm "CUSTOMER"
acFormAdd , acDialog, NewData

Else
CmbCustomer.Undo
Response = acDataErrContinue
End If

I get a problem around the following
acFormAdd, acDialog, New Data....but would like to to be where I add just the new data.

Please help.

 

[GingerR]

Instead, put this code in the On Not In List event of the combo box. Here, my combo box is named "PART" and my table is named "PARTS". You'll have to tweak for your own purposes. This adds data to the table.

Private Sub Part_NotInList(NewData As String, Response As Integer)
    Dim ctl As Control
    
    ' Return Control object that points to combo box.
    Set ctl = Me!Part
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("Parts")
        rs.AddNew
        rs!Part = NewData
        rs.Update
        Set rs = Nothing
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

End Sub

 

[stlrain95]

Thank you ma'am...got it.