Update combo if new record added

drosearup · Feb 16, 2011

I have a combo used to select a record from a master table using a 'select *' query as the row source. If the user cannot find a matching master record, I want to use a separate data entry form (dialog) to let them add a new master record. When the user has added the new master record, I want to update the row source for the combo as well as set the combo's control source to be the new master record. I am new to VBA and Access (currently 2003). How do I accomplish the above?

Thanks,

Doug

John Big Booty · Feb 16, 2011

Welcome to the forum;

I presume you have set up the combo with Limit to List = Yes

Then in it's On Not In List event put the following or similar;

Code:

MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue

Then in the On Dbl Click event put the following;

Code:

    Dim lngcombo7 As Long

    If IsNull(Me![YourComboName]) Then
        Me![YourComboName].Text = ""
    Else
        lngcombo7 = Me![YourComboName]
        Me![YourComboName] = Null
    End If
    DoCmd.OpenForm "FRM_NameOfFormToAddNewValue", , , , , acDialog
    Me![YourComboName].Requery
    If lngcombo7 <> 0 Then Me![YourComboName] = lngcombo7

FRM_NameOfFormToAddNewValue will be a form that allows the addtion of new values to your Lookup Table.

Update combo if new record added

drosearup

New member

John Big Booty

AWF VIP

Similar threads

Users who are viewing this thread