Results 1 to 10 of 10
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Requery a listbox

    I have 3 list boxes on a form. When I go to another form to add information (person, vehicle, property), I want the form to update and the list boxes to show the new information. So, on activate, I entered: Requery. Nothing happened. So, I then wrote: DoCmd.GoToControl "lstInvolvedPersons". Nothing happened either. I realized that I needed to save the form and then entering F5 updated the list boxes. So, I made a change to the code:
    Private Sub Form_Activate()


    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Requery "lstInvolvedPersons"
    DoCmd.Requery "lstProperty"
    DoCmd.Requery "lstInvolvedVehicle"
    DoCmd.GoToControl "Narrative"

    End Sub
    Still, nothing happens. When I say nothing happens, I mean when I close the other form and go back to this form, it does not update the list boxes.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps something like:
    Code:
    Private Sub Form_Activate()
    
    Me.lstInvolvedPersons.Requery
    Me.lstProperty.Requery
    Me.lstInvolvedVehicle.Requery
    Me.Narrative.SetFocus
    
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Still didn't work. I have to press F5 to update the list boxes.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Put a breakpoint at the start of the code to check that it does actually run
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Check the activate event. There are some instances where that event doesn't fire.

    I sometimes use a procedure like the ones below.

    the first, ReQListBoxes, will iterate through the controls on the supplied form and requery any listboxes. It will also identify subforms and iterate through its controls.
    I usually call it from the data entry forms OnClose event. you'd call it like - Call ReQListBoxes(forms("YourFormName"))
    the NullValue argument is true/false depending whether you want to also clear any selections

    the 2nd procedure, RequeryAllLists, will iterate through the forms collection and if the form is open will call ReQListBoxes for each form

    Code:
    Public Sub ReQListBoxes(frm As Form, Optional NullValue As Boolean)
    
    
        Dim ctl As Control
        Dim ctl2 As Control
        
        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
    
    
    End Sub

    Code:
    Public Sub RequeryAllLists()
    
    
        Dim frm As Variant
        Dim f As Access.Form
        
        For Each frm In CurrentProject.AllForms
    
    
            If frm.IsLoaded Then
            
                'Debug.Print Frm.Name
    
    
                Call ReQListBoxes(Forms(frm.Name), False)
                
            End If
    
    
        Next
    
    
    End Sub
    HTH

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK, the form Activate event doesn't fire if it's a popup, or modal, or if it's already open. So I took a look here. Turns out there are other cases where it does not fire.
    Perhaps the best approach would be to requery the target form by either an update of the last control involved in the data (assuming the input order and completeness is controlled) or by a command button on the form that processes those entries.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    That's probably the reason why it's not firing. I have it set to popup. I will have to try another approach. Thanks.

  8. #8
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I ran it several ways and this is what I came up with. On the form I was leaving, I entered this in the CLOSE button:
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmRptOfficerReport.SetFocus
    Forms!frmRptOfficerReport!lstInvolvedPersons.Reque ry
    DoCmd.Close acForm, "frmRptSubjectOR"

    That updated my list box. I used it on each button that I needed to enter information. Of course, it was changed as to which form was being used.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Somethings you might want to consider: command button close code won't run if users can click the form close button at the top right (header bar of the form window). When basically repeating one process several times, a Public sub or function in a standard module might make later edits easier. For example, say that you write this functionality 3 times for 3 different forms, then decide you want to add a feature, such as a message box. You have to edit each procedure instead of just one. You can pass just about anything to aforementioned procedure (including objects) but in this case, you'd pass only the name of the form and any control that needs to be updated/requeried. Should you later decide to add a message, you could pass the message as well. Not necessarily would this make your current situation that much better (considering you seem to have it resolved) but it might be food for later thought. If you'd like an example of how this might look, PM me or reply to this thread.

    P.S. : if this DoCmd.RunCommand acCmdSaveRecord is on the bound form being saved, Me.Dirty = False is more concise than what you have, although either way I'd be inclined to test if the form was updated before running either.

  10. #10
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Thanks. I thought about making it a Public Sub, but I actually only added 2 lines of code. The rest had to be written specifically for that button. It was just as easy to put the 2 lines of code in. I took the close button off of the form. So, the user has to use my buttons to navigate.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trying to requery a listbox
    By UT227 in forum Programming
    Replies: 23
    Last Post: 01-22-2018, 08:17 PM
  2. Requery Listbox with the following code
    By UTLee in forum Programming
    Replies: 9
    Last Post: 09-05-2014, 08:37 AM
  3. Requery Does Not Update Listbox Row Source
    By szucker1 in forum Forms
    Replies: 7
    Last Post: 02-11-2014, 08:58 AM
  4. Requery ListBox on Mainform from Subform
    By Ruegen in forum Forms
    Replies: 11
    Last Post: 10-29-2013, 09:27 PM
  5. after DAO update, listbox.requery not refreshing
    By EuniceH in forum Programming
    Replies: 2
    Last Post: 10-21-2011, 04:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums