Solved Refresh listbox on another form

M

mafhobb

Registered User.
Local time
, 19:15
Joined
Feb 28, 2006
Messages
1,245
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
 
How are you opening the pop edit form? Can you post the 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

End Sub
 
In the close event of your pop up form

Code: 
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
 
moke123 said:
In the close event of your pop up form

Code: 
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
Click to expand...
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.
 
If you open the popup as a dialog the calling code stops running until the popup is closed so you can do this:

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, , acDialog  ' Code Pauses Until closed

' Now requery your listbox
Me.SearchResults.Requery

End Sub
 
mafhobb said:
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.
Click to expand...
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.

Code: 
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

Code: 
Private sub MyForm_Close()      

' code to requery or run other procedures goes here

Set MyForm = nothing

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

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, , acDialog  ' Code Pauses Until closed

' Now requery your listbox
Me.SearchResults.Requery

End Sub
Click to expand...
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?
 
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.
 
moke123 said:
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.

Code: 
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

Code: 
Private sub MyForm_Close()     

' code to requery or run other procedures goes here

Set MyForm = nothing

End Sub
Click to expand...
Ok, I am not sure if I understand:

Code: 
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: 
[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?
 
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
 

Attachments

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

Your Code:
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
 
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?
 
Last edited:
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?
Click to expand...
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.
 
Pat Hartman said:
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.
Click to expand...
Many, many thanks.
 
You must log in or register to reply here.

Similar threads

M
Refresh Listbox Query
Replies
6
Views
157
monika_V
M
M
Solved Listbox RowSource
Replies
9
Views
770
mafhobb
M
J
Refreshing List Box
Replies
8
Views
207
JamesJoey
J
E
Solved Listbox search not working with aggregated query
Replies
7
Views
483
elannesse
E
M
Double click of listbox on a form
Replies
10
Views
470
moi
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom