VBA in Access: Refreshing a listbox's list

I'm trying to figure out the best way to refresh data in a dropdown list on a form.  The dropdown list contains customers, and the form that the dropdown list is on has a button to click on to open the "edit customers" form.  When I click on that button and add a new customer, then close out the "edit customer" form, the name doesn't appear in the dropdown list until I close and reopen the original form.  What can I do to refresh the list when the other form closes out (or if I have to, have an update button next to the dropdown list).

I tried this code (linked to a button), to close and reopen the form at the same record, but it doesn't work.

Private Sub Update_Click()
    'Store the ID of the current record, so we can come back to it
    Dim Product As String
    Product = ProductID
    'Close and reopen the form at the record to refresh the customer's column
    DoCmd.Close
    DoCmd.OpenForm "Products", WhereCondition:="ProductID=" & Product
End Sub

The reason it doesn't work is it reopens the form, but gives me a dialog box to enter the "ProductID" into, rather than going to the record that contains "Product".

Any help is appreciated, give me things to try!
wtheroAsked:
Who is Participating?
 
jefftwilleyCommented:
use the edit form's on_close event and requery the dropdown directly

forms!YourCustomerform.form.yourcombobox.requery
.........^^^^^^^^^^^^^^..........^^^^^^^^^^

use your names in place of the highlighted items of course.
J
0
 
ragoranCommented:
the command to refresh a listbox or combobox is to execute their method requery.  So if the listbox control is named lstCustomer, then execute lstCustomer.requery to see the new records.

Now you need to decide where to put this instruction.

IF the Edit customers form is open in modal mode, then the line should go just after the docmd.openform.  

IF not, it is a bit tricker.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.