Update combo if new record added

[drosearup]

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]

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;

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

Then in the On Dbl Click event put the following;

    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.