Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combobox - Add New Item to List

Combo Boxes

Combobox - Add New Item to List

by  KenReay  Posted    (Edited  )
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
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top