Adding new entries to a Combo Box based on a query or table.
Say you have a combo box with entries taken from a table or query, where you wish to limit the user to choosing entries from the list, but allow the user the option to add new entries to the list.
the combo box to be added to is called cboLastName.
The form to Maintain the table (or query) on which cboLastName is based is called frmPeople.
LimitToList : Yes
In the NotInList Event put code so:
Private Sub cboLastName_NotInList(NewData As String, Response As Integer) If MsgBox("Add to List?", vbYesNo, "Warning") = vbYes Then DoCmd.OpenForm "frmPeople", acNormal, , , acFormAdd, acDialog, NewData Response = acDataErrAdded End If End Sub
Ie ask the user if they want to make a new entry
If yes, open the appropriate form, as a modal form, in Add record mode, and pass the entered data (NewData) to the form frmPeople
On returning from the form frmPeople, inform combo box that the data was added.
For the Form frmPeople
In the Form Open Event extract the data passes using the OpenArgs property:
Private Sub Form_Open(Cancel As Integer) If IsNull(Me.OpenArgs) Then Else cmdAddNew_Click Lastname = Me.OpenArgs End If End Sub
Ie Extract the passed data from the æSendingÆ form
Make a new record (using standard wizard code as shown below), and populate the new data
Private Sub cmdAddNew_Click() On Error GoTo Err_cmdAddNew_Click