Forum Discussion
Access 2019 - List Box.Requery event on Main Form causes Sub Form Current event to trigger
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?
- is the listbox Bound to a Field in a table?
- 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.
- 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.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???
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
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.
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
