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.
Assumptions:
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.
For cboLastName,
LimitToList : Yes
In the NotInList Event put code so:
Code:
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:
Code:
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
Code:
Private Sub cmdAddNew_Click()
On Error GoTo Err_cmdAddNew_Click
DoCmd.GoToRecord , , acNewRec
Exit_cmdAddNew_Click:
Exit Sub
Err_cmdAddNew_Click:
MsgBox Err.Description
Resume Exit_cmdAddNew_Click
End Sub
