Requery List Boxes

**MattBaldry** · 09-10-2018, 07:42 AM

Is there any way I can get a form to requery all the list boxes when I close one form and the other form becomes active? I have tried so many different methods for this but not found anything simple yet.

~Matt

**orange** · 09-10-2018, 07:46 AM

Please give readers some context for your question. Sample data?
Often when you requery the form- Me.Requery - you get the latest data.

**MattBaldry** · 09-10-2018, 07:48 AM

Originally Posted by orange

Please give readers some context for your question. Sample data?
Often when you requery the form- Me.Requery - you get the latest data.

It is not the form data I want to requery. It is list boxes on an unbound form.

~Matt

**John_G** · 09-10-2018, 07:53 AM

When you say "...all the list boxes...", which list boxes are you referring to? "All" doesn't tell us much. Requerying list boxes on a form you are closing is meaningless, because the changes are not saved when the form is closed. When a form opens, its list boxes are either initially populated according to the record source for each of them, or their recordsource can be defined with VBA when the form opens.

Please give us a bit more information on what you need to do.

**orange** · 09-10-2018, 07:58 AM

Perhaps you should attach your database with clear instructions to help readers experience your issue, and a clear example of what you want to occur.

**davegri** · 09-10-2018, 08:00 AM

This might work:

Code:

Option Compare Database
Option Explicit


Private Sub Form_Activate()
    lstOne.requery
    lstTwo.requery
    lstThree.requery
    .
    .
    .
End Sub

**kd2017** · 09-10-2018, 08:02 AM

Code:

Me.ListBoxName.Requery

Will refresh your listbox.

As others have stated we're not clear on what exactly you're doing but guessing the following info maybe of use:
Let's say you have a Listbox of items on your unbound form and you want to launch another from to add/edit the recordsource for that listbox.
You might have a command button on your main/unbound form with the following code

Code:

Private Sub CmdEditRows_Click()
    DoCmd.OpenForm "your_form_name", , , "criteria_goes_here", , acDialog

    'Because the form was opened with the acDialog option the following code wont execute until the form launched above is closed.
    Me.ListBoxName.Requery
    
End Sub

Note the last option of the DoCmd.OpenForm command "acDialog", this will pause the execution of the code in the CmdEditRows_Click() procedure until the "your_form_name" is closed. Then when it refreshes your listbox presumably your edits to the rowsource have already been saved.

**MattBaldry** · 09-10-2018, 08:03 AM

Originally Posted by John_G

When you say "...all the list boxes...", which list boxes are you referring to? "All" doesn't tell us much. Requerying list boxes on a form you are closing is meaningless, because the changes are not saved when the form is closed. When a form opens, its list boxes are either initially populated according to the record source for each of them, or their recordsource can be defined with VBA when the form opens.

Please give us a bit more information on what you need to do.

Okay, I shall try to explain better.
I have a customer overview form with the customer address and other data. I have an unbound list box with all the customer quotes listed (data source is a query where the CustomerID field = Forms!frmCustomerOverview!CustomerID). When I add a new quote it opens frmQuote, I enter the data and send the quote. When I click close it goes back to to frmCustomerOverview and now I want the List Box with the customer quotes to requery to show the quote I just sent. At the moment I have a Requery module that is activated when my cmdClose is clicked. It closes the current form and then checks which form is now open and then Requerys the list boxes on that form. I was hoping there may be an easier way.

~Matt

**kd2017** · 09-10-2018, 08:05 AM

Originally Posted by MattBaldry

Okay, I shall try to explain better.
I have a customer overview form with the customer address and other data. I have an unbound list box with all the customer quotes listed (data source is a query where the CustomerID field = Forms!frmCustomerOverview!CustomerID). When I add a new quote it opens frmQuote, I enter the data and send the quote. When I click close it goes back to to frmCustomerOverview and now I want the List Box with the customer quotes to requery to show the quote I just sent. At the moment I have a Requery module that is activated when my cmdClose is clicked. It closes the current form and then checks which form is now open and then Requerys the list boxes on that form. I was hoping there may be an easier way.

~Matt

We replied at the same time, check my post #7 and see if that will help you.

**MattBaldry** · 09-10-2018, 08:09 AM

Originally Posted by kd2017

We replied at the same time, check my post #7 and see if that will help you.

Yes, that looks like it could help. I shall give it a try. Would I be stuck on the acDialog form until it is finished with? Sometimes I will open the frmQuote but then have to visit other forms IE frmPriceList, to get the pricing for the quote. If the acDialog keeps that form on top until I am done with my entry, it would not work.

I was hoping for something when the form gains focus but I have tried those and they do not seem to Requery.

~Matt

**kd2017** · 09-10-2018, 08:44 AM

Yes. But you could open frmPriceList from frmQuote. davegri's suggestion may be more appropriate for your situation. Or even your current approach in reply #8 seems reasonable.

**moke123** · 09-10-2018, 12:23 PM

I have 2 public subs I use. I usually call them in an OnClose event.
the first, ReQListBoxes, will requery all listboxes on a given form.

Code:

Call  ReQListBoxes(Forms("YourFormName"),true)

The second, RequeryAllLists, will requery the listboxes in any open forms. It calls ReQListBoxes so you need both subs for this.

Code:

Call RequeryAllLists

Code:

Public Sub ReQListBoxes(frm As Form, Optional NullValue As Boolean)


    Dim ctl As Control
    Dim ctl2 As Control


    On Error GoTo ReQListBoxes_Error


    For Each ctl In frm.Controls


        If ctl.ControlType = acListBox Then


            If NullValue = True Then


                ctl.Value = Null


            End If


            ctl.Requery


        End If


        If ctl.ControlType = acSubform Then


            For Each ctl2 In ctl.Form.Controls


                If ctl2.ControlType = acListBox Then


                    If NullValue = True Then


                        ctl.Value = Null


                    End If


                    ctl2.Requery


                End If
            Next


        End If


    Next


    On Error GoTo 0
    Exit Sub


ReQListBoxes_Error:


    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure ReQListBoxes of Module modLBX"


End Sub


Public Sub RequeryAllLists()


    Dim frm As Variant
    Dim f As Access.Form


    On Error GoTo RequeryAllLists_Error


    For Each frm In CurrentProject.AllForms


        If frm.IsLoaded Then


            Call ReQListBoxes(Forms(frm.Name), False)


        End If


    Next


    On Error GoTo 0
    Exit Sub


RequeryAllLists_Error:


    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure RequeryAllLists of Module modLBX"


End Sub

HTH

**MattBaldry** · 09-11-2018, 01:42 AM

Originally Posted by moke123

I have 2 public subs I use. I usually call them in an OnClose event.
the first, ReQListBoxes, will requery all listboxes on a given form.

Code:

Call  ReQListBoxes(Forms("YourFormName"),true)

The second, RequeryAllLists, will requery the listboxes in any open forms. It calls ReQListBoxes so you need both subs for this.

Code:

Call RequeryAllLists

Code:

Public Sub ReQListBoxes(frm As Form, Optional NullValue As Boolean)


    Dim ctl As Control
    Dim ctl2 As Control


    On Error GoTo ReQListBoxes_Error


    For Each ctl In frm.Controls


        If ctl.ControlType = acListBox Then


            If NullValue = True Then


                ctl.Value = Null


            End If


            ctl.Requery


        End If


        If ctl.ControlType = acSubform Then


            For Each ctl2 In ctl.Form.Controls


                If ctl2.ControlType = acListBox Then


                    If NullValue = True Then


                        ctl.Value = Null


                    End If


                    ctl2.Requery


                End If
            Next


        End If


    Next


    On Error GoTo 0
    Exit Sub


ReQListBoxes_Error:


    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure ReQListBoxes of Module modLBX"


End Sub


Public Sub RequeryAllLists()


    Dim frm As Variant
    Dim f As Access.Form


    On Error GoTo RequeryAllLists_Error


    For Each frm In CurrentProject.AllForms


        If frm.IsLoaded Then


            Call ReQListBoxes(Forms(frm.Name), False)


        End If


    Next


    On Error GoTo 0
    Exit Sub


RequeryAllLists_Error:


    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure RequeryAllLists of Module modLBX"


End Sub

HTH

That looks like what I need, I shall give this a try later. Thank you.

~Matt

**MattBaldry** · 09-11-2018, 04:51 AM

@moke123,

That was exactly what I was looking for. I have integrated it into my database and it works perfect. Thank you for your help. This thread can now be solved

~Matt

**moke123** · 09-11-2018, 10:00 AM

Happy to help. Good luck with your project.

Requery List Boxes

Thread Tools

Requery List Boxes

Similar Threads

AfterUpdate requery does not requery list box

Requery with cascading combo boxes

Cascading Combo Boxes Requery Problem

Help with Combo Boxes/requery....etc

Dependent List Boxes do not refresh using ReQuery

Posting Permissions