Updating a combo box after record has been added on another form

[scheeps]

I've got a scenario where a user can add a Contact if the contact does not appear in the Contacts combo box (bound) just by clicking on a Add Contact button from where a the Add Contact modal form opens.

The user add the new Contact and saves the record.

But my problem is that the new contact is in the Contacts table but if the user clicks the combo box to select the newly added Contact, then the Row Source has not been updated for the newly added Contact to reflect.

I've tried by adding the following code to the OnActivate and OnGotFocus events, but no luck:

    Dim strSQL As String
    
    strSQL = "Select distinct Contact_Name, Contact_ID from ODS.Contact "
    strSQL = strSQL & " where Company_ID = " & cboCompany & " "
    
    cboContact.RowSource = strSQL
    
    Me.cboContact.Requery

Do you guys have any other suggestions on how I can refresh the combo box for the newly added contact to reflect?

 

[John Big Booty]

The usual Double Click event to add items to a combo looks something like;

    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

This code opens a form through which new records can be added to the table populating the Combo, then requeries the combo once that form has been closed.

 

[scheeps]

Awesome...works like a charm!

Thanks a lot John.