Requery List Boxes (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
I have tried many different methods and cannot find one that suits my database structure. I am after a solution for the below.

So, I have a main customer list form, from here you can add or edit customers. I click Add, customer form opens in add record state. I add the new record and when I click close I would like the customer list form to requery the listbox to show the added customer in the list. At the moment I have it as part of the close button. It checks to see if the customer list form is active and the does the requery event.

Is there any method of putting this in the form code such as form activate or something so each time that, or another form, becomes active it does the requery event on the list boxes?

I have also tried adding a code that checks to see if a form is open, and if it does it requeries all the list boxes, but if I have mutliple forms opens it requeries them all.

I want to find a way to only requery the form that has just become re-active. So, something like below.

frmCustomerList > Click Add
frmCustomerOverview > Opens > Enter Data > Click Close
frmCustomerList > Becomes Active > List Box Requery

Does this make sense?

~Matt
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
If you open the add form in acdialog code execution stops in the calling form. So in the code that opens the add form put a requery.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
If you open the add form in acdialog code execution stops in the calling form. So in the code that opens the add form put a requery.

I am not sure that solution would work as most times after the customer is added, I then have to add a quote to the customer which then opens a new form while the customer form is still open, which is why I was looking for a solution for when the form is active or such.

~Matt
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
It will because even if you open the second form code execution will not return to the calling form until first acdialog closes.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
It will because even if you open the second form code execution will not return to the calling form until first acdialog closes.

Okay, so where would I put the requery code, in the add button on the first form?

~Matt
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
Yes, in the calling form.
Open the called form in acdialog.
The next line of code is a requery. It returns to that line only when called form closes
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:30
Joined
Jul 9, 2003
Messages
16,245
I think I've got the gist of your question, but I thought I should reiterate it for clarity.
You have a pop-up form in which you can add a new customer.
When this popup form is closed, you want to update the list box on the form that called it.
Also - If you have any other forms open, which contain a similar customer List box, you want those list boxes to update as well?

Is that about right?
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
Yes, in the calling form.
Open the called form in acdialog.
The next line of code is a requery. It returns to that line only when called form closes

I will give that a try. If it works, I am taking you out for a steak :D

~Matt
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
There are other ways to do this, such as passing a reference for the calling form to the called form using openargs. Then have the called form do the requery.
 

moke123

AWF VIP
Local time
Today, 11:30
Joined
Jan 11, 2013
Messages
3,852
I use a public sub to iterate through the forms collection and if the form is open and has a listbox it is requeried. I usually call it on close of data entry forms.


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
 

GK in the UK

Registered User.
Local time
Today, 15:30
Joined
Dec 20, 2017
Messages
274
I've done something very similar with my current (only) Access project.

In the form that displays the list, I have a WithEvents on the child (add/edit) form.

The WithEvents is defined and set to fire on the AfterInsert event of the child form.

So, when the child form has inserted the new record, the host (customer list in your case) form then requeries the list, and the record appears in the list. It does this as soon as the new record has been saved, even if I remain on the child form to do more editing.

I don't use a list box on the host form, just a datasheet, but I think it could be adapted for a listbox.

It doesn't rely on the child form being in dialog mode, and it doesn't requery if you open an existing record for editing.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
There are other ways to do this, such as passing a reference for the calling form to the called form using openargs. Then have the called form do the requery.

The acDialog method does not work. It runs the requery as soon as I click the button and not when the opened for is closed.

DoCmd.OpenForm "frmCustomerOverview", acNormal, , , acFormAdd, acDialog
Me.lstCustomerList.Requery

~Matt
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
I use a public sub to iterate through the forms collection and if the form is open and has a listbox it is requeried. I usually call it on close of data entry forms.


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

I have used this code but if I have 3 or 4 forms open, it requeries them all and is not very fast as I really only want to requery the list boxes on the form I am closing down to.

~Matt
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
I've done something very similar with my current (only) Access project.

In the form that displays the list, I have a WithEvents on the child (add/edit) form.

The WithEvents is defined and set to fire on the AfterInsert event of the child form.

So, when the child form has inserted the new record, the host (customer list in your case) form then requeries the list, and the record appears in the list. It does this as soon as the new record has been saved, even if I remain on the child form to do more editing.

I don't use a list box on the host form, just a datasheet, but I think it could be adapted for a listbox.

It doesn't rely on the child form being in dialog mode, and it doesn't requery if you open an existing record for editing.

Could you share the code you use please?

~Matt
 

GK in the UK

Registered User.
Local time
Today, 15:30
Joined
Dec 20, 2017
Messages
274
In the host form, the form that shows the list:

At the beginning of the code module:

Code:
Private WithEvents ChildForm As Access.Form ' invoice or payment
In the cmdAddNewRecord procedure (the press of the button):

Code:
        DoCmd.OpenForm Me.txtFrmName, _
                        DataMode:=acFormAdd, _
                        OpenArgs:=strDocType & "|" & "frmThIndex"
        ' set the event handler, we can't do this until the form is loaded
        ' this code executes as soon as the child form is loaded
        Set ChildForm = Forms(Me.txtFrmName).Form
        ' listen for the AfterInsert event on the child form
        ' could be invoice or payment form
        ' this is so we know when to requery the datasheet
        ChildForm.AfterInsert = "[Event Procedure]"
In the code module of the host form (the procedure parameter is to inform RequeryIndex the RecordID of the new ls added record, and navigates to the newly added record as soon as it appears in the list

Code:
Private Sub ChildForm_AfterInsert()
' code to run on the AfterInsert event of frmInvoice or frmPayment
' we've added a new document, requery so it appears
txtTransHeaderID = ChildForm.TransHeaderID
RequeryIndex (txtTransHeaderID)
End Sub
You don't need anything in the child form. If you have an AfterInsert on the child form with code, I think I'm right is saying that executes as well as the host form code, but I can't remember in which order they fire. Don't think you'd need code in both places.

Mine is slight more sophisticated than you need because I have two forms that can add or edit records to the host, so I have to maintain txtFrmName and set the ChildForm.AfterInsert to whichever child form has been opened. But I need only one Private Sub ChildForm_AfterInsert()
 

moke123

AWF VIP
Local time
Today, 11:30
Joined
Jan 11, 2013
Messages
3,852
I've never had a speed issue with that code.

you can also do a similiar thing with a single form.
just pass the form name as a string.

Code:
Public Sub RequeryFormLists(frm As String)

    Dim ctl As Control

            For Each ctl In Forms(frm).Controls
                If ctl.ControlType = acListBox Then
                    ctl.Requery
                End If
            Next
            
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
The acDialog method does not work. It runs the requery as soon as I click the button and not when the opened for is closed.

DoCmd.OpenForm "frmCustomerOverview", acNormal, , , acFormAdd, acDialog
Me.lstCustomerList.Requery
Not sure what you did wrong, but you did something wrong. See demo
 

Attachments

  • Requery.accdb
    464 KB · Views: 64

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 15:30
Joined
Feb 5, 2019
Messages
292
Not sure what you did wrong, but you did something wrong. See demo

Could it be the acNormal part I have? It is the only part I do not see on your code.

~Matt
 

isladogs

MVP / VIP
Local time
Today, 15:30
Joined
Jan 14, 2017
Messages
18,186
Matt
I've not been following this thread but in response to your last question acNormal is the default value and, like all such default arguments, it can be omitted without any change to functionality
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:30
Joined
May 21, 2018
Messages
8,463
Could it be the acNormal part I have? It is the only part I do not see on your code.
/QUOTE]
As stated it should not make a difference. There may be some other factor with your design, but I do not know how to replicate what you are seeing without seeing your set up
 

Users who are viewing this thread

Top Bottom