Refresh combo box field after a new record is added.

[only412c]

I have created a combo box field to allow user to browse records from a table. The form consisting of the combo box field has also a command button to allow the user to bring up a pop-up form to create a new record. Once the user adds a record, it returns control to the combo box field and popup screen disappears. My question: How to I tell access to requery the following query below that is on the Row Source of the Combo box field and thus refresh the the combo box with the new record created?

Thanks in advance for your suggestions.

Juan

In Row Source the following appears:

SELECT ReportInfo.ReportId, ReportInfo.LastName, ReportInfo.FirstName, ReportInfo.ReportNickname, ReportInfo.ReportName FROM ReportInfo ORDER BY ReportInfo.Lastname;

 

[FancyPrairie]

Do it something like this:

If (Msgbox("Item doesn't exist.  Do you want to Add it?",vbYesNo+vbQuestion) = vbNo) then

    Response = acDataErrContinue
    ctlComboBox.Undo

else
    DoCmd.OpenForm "FormName", , , , acAdd, acDialog

    If (gbolRecordAdded) Then
        
        Response = acDataErrAdded
    
    Else
        
        Response = acDataErrContinue
        ctlComboBox.Undo
    
    End If

End If

Note that the form was opened as a dialog form (acDialog). That's because you don't want your code to execute until the user exists the form. Note also that the user could cancel adding the record. If so, no need to requery the combobox. That's why I check the global variable gbolRecordAdded. In the OnOpen event of the form I set it to false. In the AfterInsert event of the form I set it to true.

 

[only412c]

Thanks,

I got it to work. One final question, just so I can understand. The (Response = acDataErrAdded) and (Response = acDataErrContinue) what exactly is it doing in the program logic. Is acDataErrAdded variable you assigned or are they builtin and part of the msgbox function? I am still learning to code.

 

[PHV]

Consult the VBA help file for NotInList.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244