VBA in Access: Refreshing a listbox's list

Posted on 2007-09-28
Last Modified: 2013-11-28
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.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!
Question by:wthero
    LVL 14

    Assisted Solution

    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.
    LVL 34

    Accepted Solution

    use the edit form's on_close event and requery the dropdown directly


    use your names in place of the highlighted items of course.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now