Access 2019 - List Box.Requery event on Main Form causes Sub Form Current event to trigger

[VBA_Coder]

I have a simple Main Form with one SubForm.  On the Main Form I have a ListBox called "listItems" whose RowSource is set to a Query.  If I issue a listItems.Requery, the Current event of the SubForm is triggered.  Why is this happening?  And is it possible to prevent the SubForm current event from triggering?

[#2]

is the listbox Bound to a Field in a table?

[#3]

No, it's an unbound list box. What I'm trying to accomplish is to have the list box show summary information from the SubForm records, which is shown as a Datasheet.

[#4]

what happens to the subform? does it change record?
you can add a Flag variable in your code that when you requery the listbox, set this flag to True.
then on the current event of the subform, check the status of the flag. if the status is True, reset the flag and immediately exit the sub.
we can only "detour" the default action of the form.

[#5]

It's a little more trickier and complicated, because of the Subform requerying itself. 

Does anyone know why this behavior happens like this? Same thing happens for combo boxes.

So what my Main Form and Sub Form do is, the Main Form has the Patient's information (Name, Address, etc...) and the Sub Form lists their Doctor's Appointments in a Datasheet by AppointmentDate. The List Box on the Main Form is supposed to list all the documents associated with this Patient's Appointment by using a Query as it's RecordSource the query has a WHERE condition that looks at the "ScheduleID" field of the SubForm (Current Record selected) to filter just those Documents for that "ScheduleID". That way, when the user clicks one of the rows of the Subform, which is an Appointment, I call the ".Requery" method of the ListBox so that it will run the query and show just those documents for that Appointment Date, and I also have code to change the label of the list box to say "Appointment Documents for: " & [AppointmentDate].

The bug-a-boo here is, when the SubForm requries itself when you call the ListBox.Requery event where the ListBox is located on the Main Form, the SubForm's current record is now in the 1st row, even though the user may have picked the 8th row.  So all my logic in the Current() event, is not pointing to the row the user selected and because the current record of the SubForm is now in the 1st row, my listbox's Query which filters based on the current row (ScheduleID) of the SubForm, will display the wrong documents because it thinks the Current Record is the 1st row of the Subform.  In actuality, what happens is, the Form_Current() event is put into an infinite loop because each time the Subform refreshes/requeries itself, it'll call the ListBox.Requery event thus causing the Subform to again refresh/requery itself and then again the SubForm's Form_Current() event will be triggered again.

The Form_Current() event can be triggered multiple times depending on how many SubForms I have on the Main Form.  My particular SubForm, which lists the Appointments for the Patient chosen in a combo box in the Main Form's Header, it's Form_Current event is triggered 4 times when a user chooses a Patient from the combo box and the Main Form is refreshed/requeried to show the Patient's information (Name, address, etc...).

Again, why did they design it such that a SubForm will requery itself when a listbox or combo box is requeried on the Main Form???

[#6]

And, because of this refresh/requery of the SubForm, you will get into an infinite loop if you put the ListBox.Requery event in the Form_Current() event because each time the Form_Current event is triggered, it'll call the ListBox.Requery event which will cause the SubForm to Requery and go back to the first row of the SubForm thus causing the SubForm's Form_Current event to trigger again, and this behavior will put you into an infinite loop.

[#7]

just maybe there is a Parent/Child Link fields on the subform and the Parent Link is the Listbox?
you can also use another subform, instead of listbox.
use Parent/Child Linke field on the "New" subform.
you may need to add an Unbound textbox to the main form.
the control source of the Unbound textbox is the PK field of the "old" subform:

=[subformname].Form![thePKField]

now add Parent/Child link fields to the "New" (the substitute to your listbox) subform.
the Parent will be the Unbound textbox and the child is the FK field.

so you do not requery the "new" subform. it get re-queried auto.

[#8]

There is no "Parent/Child Link" based on the listbox, other than the query that is the RecordSource for the ListBox has a WHERE clause that has the ScheduleID set to the "ScheduleID" in the Current Record of the subform. I have been tinkering with putting hidden text boxes on the Main Form so when the user clicks a row of Appointments in the Subform, I would store the CurrentRecord number as well as the ScheduleID in these hidden fields so that I could somehow move the SubForm back to the row/record that is in the hidden field as well as change my Query's WHERE clause that is used for the ListBox to use that hidden text box that has the ScheduleID. I still don't have an elegant solution at this time, but I'm getting closer....

[#9]

This behavior of requerying a listbox on the Main Form, causing SubForm's to reload, is causing a lot of coding issues because the Current event of the SubForm is triggering causing complexity in how I have to handle the code I want to happen in the SubForm, not when I simply want a listbox on the Main Form to requery to update it's list items

[#10]

@VBA_Coder 

Did you ever solve this issue?  In 20 years of Access I don't think I've ever seen it before, but I've been fighting it for over a day now.  My behavior seems identical to what you described

[#11]

Recently had the same issue myself. Have a base form with 3 linked continuous subforms (SF1 links to SF2, SF2 links to SF3 in the standard format for this type of form). This worked without problem until I added a 'filters toolbar' comprising 2 toggle button option groups and 3 combo boxes directly on the main form. At that point I got the same issue described here, the On Current Event of SF2 firing multiple times. (I found any 'option' type control, i.e. radio button, toggle button, combo box, all controls of that ilk all caused the same problem, even unbound and disconnected from their event procedures so they are linked to nothing and should do nothing - same result!)

Assuming this was an error on my part, I spent an annoying 2 days pulling the form to pieces but couldn't find a problem with it.

I'll skip a lengthy ramble on various things I tried!

What resolved the issue for me was moving the controls that comprise the 'filters toolbar' to its own form, and then adding this as a subform to the main form. SF2 On Current now only fires 1 time as it should!

Am I missing something? or this is an odd bug in recent versions of Access.

[#12]

@Moose123 

As I recall I did something similar where I made the filters their own sub, and I called a function on the main form to requery things as I desired.

As near as I could tell it was a bug in 2019 that maybe had not been there before; good to know for future that other controls besides list boxes can cause it but gee what a frustrating issue!  Glad you found a work around