Solved Refresh listbox on another form (1 Viewer)

[mafhobb]

So I mave a form called "frmPropertyHistory" with a textbox "findGuest". When I enter a string in this textbox, another form opens "frmFindGuest" with a listbox "SearchResults" displaying the matcing results. This is the query in the listbox Row Source:
"SELECT [qryFindGuest].[ID], [qryFindGuest].[PropertyName], [qryFindGuest].[GuestName], [qryFindGuest].[CheckInDate], [qryFindGuest].[CheckOutDate], [qryFindGuest].[ReservationDate], [qryFindGuest].[Status] FROM qryFindGuest ORDER BY [CheckInDate]; "
I have now added a double click even on this listbox to open the selected record to edit it on a pop up form. This works well as I can open the pop up form and edit the selected record. However, when I close the pop up form, I'd like for the listbox to be updated so it shows the updated data.
I have tried to requery the listbox from the pop up form on close event "[Forms]![frmFindGuest]![SearchResults].Requery" but this does not seem to do it.
I have also tried to requery the listbox using the on current event (Me.Requery) but that doe snot seem to do it either.
What am I missing?
mafhobb

 

[Minty]

How are you opening the pop edit form? Can you post the code.

 

[mafhobb]

Private Sub SearchResults_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReservationEdit"

stLinkCriteria = "[ID]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

 

[moke123]

In the close event of your pop up form

Public Sub RequeryAllLists()

    Dim ctl As Control
    Dim frm As Variant

    For Each frm In CurrentProject.AllForms
        If frm.IsLoaded Then
            For Each ctl In Forms(frm.Name).Controls
                If ctl.ControlType = acListBox Then
                    ctl.Requery
                End If
            Next
        End If
    Next

End Sub

 

[mafhobb]

In the close event of your pop up form

Public Sub RequeryAllLists()

    Dim ctl As Control
    Dim frm As Variant

    For Each frm In CurrentProject.AllForms
        If frm.IsLoaded Then
            For Each ctl In Forms(frm.Name).Controls
                If ctl.ControlType = acListBox Then
                    ctl.Requery
                End If
            Next
        End If
    Next

End Sub

Thanks, but this still does not do it. When the pop up form is closed, the listbox on the original form does not update to show the new data.

 

[Minty]

If you open the popup as a dialog the calling code stops running until the popup is closed so you can do this:

Private Sub SearchResults_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReservationEdit"

stLinkCriteria = "[ID]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog  ' Code Pauses Until closed

' Now requery your listbox
Me.SearchResults.Requery

End Sub

 

[moke123]

Thanks, but this still does not do it. When the pop up form is closed, the listbox on the original form does not update to show the new data.

That should requery every list box on every open form.

Another method I use, in addition to Minty's, when I have value lists that are populated by various procedures to use withevents and make a custom event . All this code goes in the main form which calls the pop up.

Dim WithEvents MyForm As Form   'In the declarations section

Private Sub SomeButton_Click()

DoCmd.OpenForm "SomeFormName", , , "ID = " & Me.ID

    Set MyForm = Forms("SomeFormName")
    MyForm.OnClose = "[Event Procedure]"

End Sub

Private sub MyForm_Close()      

' code to requery or run other procedures goes here

Set MyForm = nothing

End Sub

 

[mafhobb]

If you open the popup as a dialog the calling code stops running until the popup is closed so you can do this:

Private Sub SearchResults_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReservationEdit"

stLinkCriteria = "[ID]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog  ' Code Pauses Until closed

' Now requery your listbox
Me.SearchResults.Requery

End Sub

Hi Minty. This still does not do it. Just as before, I need to close the form where the list is in and reload it for the changes to appear.
Are there any form settings that could preveind the requerry to happen?

 

[Minty]

There is something else going on then.

Can you post up a stripped-out version of the DB with enough to demonstrate the problem?

Both Moke's and my methods should work.

 

[mafhobb]

That should requery every list box on every open form.

Another method I use, in addition to Minty's, when I have value lists that are populated by various procedures to use withevents and make a custom event . All this code goes in the main form which calls the pop up.

Dim WithEvents MyForm As Form   'In the declarations section

Private Sub SomeButton_Click()

DoCmd.OpenForm "SomeFormName", , , "ID = " & Me.ID

    Set MyForm = Forms("SomeFormName")
    MyForm.OnClose = "[Event Procedure]"

End Sub

Private sub MyForm_Close()     

' code to requery or run other procedures goes here

Set MyForm = nothing

End Sub

Ok, I am not sure if I understand:

Dim WithEvents MyForm As Form   'This is in the form that the pop up originates from, which is frmFindGuest

Private Sub SearchResults_DblClick(Cancel As Integer)   'This is the listbox

DoCmd.OpenForm "frmReservationEdit", , , "ID = " & Me.ID    'I am now opening the form "frmReservationEdit"

    Set MyForm = Forms("SfrmReservationsEdit")
    MyForm.OnClose = "[Event Procedure]"                   'I am not sure what goes here??'

End Sub

[CODE]Private sub MyForm_Close()     

' code to requery or run other procedures goes here
Me.frmFindGuest.requery

Set MyForm = nothing

End Sub

[/CODE]
Like this?

 

[mafhobb]

Here it is. It is very barebones, so it will give you a lot of errors when loading becasue of deleted subforms and tables.

To reproduce the issue, open the db, click on the textbox under "Guest Search", enter the word "Guest" and then click on "Find Guest". This will load the list.

Then, double-click on any record and the record will appear on the pop up form. Here double-click on the form header to unlock the record, make any changes that you want and click OK.

At this point, the pop up will disapear and the list will reapear again, but it will not be updated with the changes just made.

A similar thing happens when entering a new reservation:
On the main form select a property in the "PropertySearch" combobox. The continuous form below will show the reservations for that property. Then, click on "New reservation" and a pop up form will appear where the new reservation data can be entered. Once this new reservation is entered, I need the continuous form to update, but I just can't figure out how.

mafhobb

 

[Minty]

You haven't added the dialog switch to the do.cmd action:

Your Code:

Private Sub SearchResults_DblClick(Cancel As Integer)

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmReservationEdit"

    stLinkCriteria = "[ID]=" & Me![SearchResults]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
' Now requery your listbox
    Me.SearchResults.Requery

End Sub

What I suggested:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReservationEdit"

stLinkCriteria = "[ID]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

' Now requery your listbox
Me.SearchResults.Requery

 

[mafhobb]

I see.
That seems to do it, but why? It is becasue the code stops until the other form closes? Why would a requery from the pop up form on close not have the same effect?

 

[Pat Hartman]

When form A opens form B in dialog mode, the code in form A stops at the Open event. When form B closes, the code in form A picks up on the next line after the OpenForm method.

Why would a requery from the pop up form on close not have the same effect?

It should. Are you sure the code actually ran?

In any event, the first method is best. That way the popup form doesn't have to know what form called it.

 

[moke123]

example with the 3 methods

 

[mafhobb]

When form A opens form B in dialog mode, the code in form A stops at the Open event. When form B closes, the code in form A picks up on the next line after the OpenForm method.


It should. Are you sure the code actually ran?

In any event, the first method is best. That way the popup form doesn't have to know what form called it.

Many, many thanks.

 

[mafhobb]

example with the 3 methods

Thanks. I'll check the examples and save this for future reference.