How to trap the refresh of a subform’s recordset? (A2K)

Topic

New Reply

Basically, I have a main form with a combo box and three subforms. Subform1 is populated by a query which returns records based on the value in the combo box. Subform2 is logically a child of Subform1 but is placed on the main form. Subform3 is logically a child of Subform2, but again sits on the main form. The main reason the various subforms are all on the main form is one of design; putting each subform in the header/footer section of its parent would produce a horrible-looking form.

So the subset of records displayed in Subform3 can change when either the record selected in Subform2 changes; when the record in Subform1 changes or when the combo box value on the main form is changed.

This is one of those questions that has bugged me for ages: is there an easy way, within a subform itself, of trapping when its recordset is refreshed as a result of something that happens in one of its parents.

I know the form’s Current event will fire after this has happened because the current record has changed, but obviously it also occurs when moving from record to record. I would really like to be able to keep the code in the subform so that (a) as much of its necessary code is encapsulated within the subform itself and ( if I use the subform elsewhere, I don’t have to start including extra code in the forms I drop it on.

Many thanks,

Simon

Reply | Quote

Replies

You can trap this change with the On Apply Filter of the subform
or
You can write you functions in the subforms.
The when something changes in the parent, just call the code from within the parent with the following code:
Call Me.[SubFormName].Form.MyFunction

Reply | Quote

Francois, are you sure about this? I can’t get either the Filter or the ApplyFilter events to occur in the subform unless I actually filter it (e.g. from the toolbar filter options or by right-clicking in the record). The pseudo-filtering caused by the master/child relationship doesn’t seem to fire the event at all. Have you definitely had this working with A2K?

I know I could do it from within the parent, but it seems so much cleaner to be able to handle it in the subform if I can.

Cheers,

Simon

Reply | Quote

Simon,

Attached a little mdb. Run the Namen form and you’ll have a beep and some delay when you browse through the records by arrows or by the combobox.
The code there for is in the apply filter of the sub form.

Reply | Quote

Francois,

Without a doubt, your example works. But how? There must be something I’m missing but I don’t see what. Let’s simplify things: I’ve opened the sample Northwind.mdb database and I’ve opened the subform “Quarterly Orders Subform” in design mode. I’ve opened the properties window and have selected the event tab. I’ve clicked on ApplyFilter and then opened an event procedure into which I’ve typed “Beep” on one line. I’ve saved this.

I then open the “Quarterly Orders” form and as I move from record to record, the subform gets requeried to reflect the changing record in the main form, but I get no beep whatsoever. If I open the subform on its own and filter from the menu then it Beeps. Any ideas?

Simon

Reply | Quote

Simon,

I have to search what was the difference between the two database and finally found it.
In on open event of the main form namen, I have the following code :
Me.tblContact_subform.Form.OrderBy = “txtcontactnaam”
Me.tblContact_subform.Form.OrderByOn = True
to order my contacts by name.
That is what triggers the apply filter. Strange.
If I add
Private Sub Form_Open(Cancel As Integer)
Me.Quarterly_Orders_Subform.Form.OrderBy = “productname”
Me.Quarterly_Orders_Subform.Form.OrderByOn = True
End Sub
to on open event of the Quarterly Orders forms of NorthWind.mdb it works.

I hope you can use something similar.

Reply | Quote

I don’t understand what the purpose is of this entire exercise. *Why* do you care where a refresh/requery originated. Logically there is no difference, so what kind of code would you be running only if the change was forced from the parent? And to me it does NOT seem cleaner to run it from the subform. If you only wnat to do it when the parent form triggers the refresh/requery, then that’s the place to run the code.

Reply | Quote

Charlotte,

Firstly, I’ve never said I wanted to know “where a refresh/requery originated” merely to know that it has occurred. I also never said that I *only* want to do it “when the parent form triggers the refresh.

Some reasons why encapsulating all the code in the subform seems like a good idea to me:

1. If the subform is used on a number of different forms it keeps the code to do a particular job in the place it does the job rather than elsewhere and/or without the need for numerous references to it all over the place.

2. As I originally described, I have, in effect, two-levels of nesting; so a change in the top level forces a requery in the intermediate level (via the master/child relationship there) which in turn forces a requery in the lower level (via the master/child relationship there). Alternatively, it could be a change of record (via user input) in the intermediate level which causes a change in the lower level. As both my intermediate subform and the lower level subform are both actually on the main form (the hierarchy is only “logical”)
I could use the intermediate subform in more than one place with or without the lower level subform. How messy is it, if each time I use it and I change records in it, I’ve got to check for the presence every possible combination of other subform on the main form and action them accordingly?

3. I would have said that discovering when some other part of the application is messing with your recordset is potentially quite a useful facility to have.

Here are some things that would be quite nice to do: on one subform I have only the more important fields from its record source visible on screen. The form also has some buttons in the footer which (among other things) allows the user to edit the more obscure and unimportant fields. When the master record changes, there may no longer be any records on the subform – how much more professional to disable these buttons in this case rather than to wait till clicked upon to report there’s no record there. A bit like the way access enables/disables it’s navigation buttons (if you have them: in this instance I don’t) on subforms automatically and doesn’t rely on the main form calling something to force it to happen.

Secondly, the recordset refresh/requery as a result of the master/child relationship doesn’t happen (as far as the master is concerned) at a specified event time – for example, if you test the child’s recordsetclone property in the master’s Form_Current event you’ll get the result of the old recordset, not the one requeried as a result of a change of the current record in the master. If you would choose to test a subforms recordset in the master form, where/when would you do it?

Simon.

Reply | Quote

First some definitions. There is a difference between Refresh and Requery.

Once you have create a recordset, you can Refresh the contents of the recordset. Access does this automatically by itself, but you can force it by using the .Refresh method. For example, if your recordset has 10 records, and another user deletes one of these records, after a Refresh your recordset will still have 10 entries, but one of them will be flagged as “Deleted”.

A Requery, by contrast, tells Access to go out and rerun the query that created the recordset. In the example above, after a .Requery you would only have 9 records in your recordset.

If your used the Linking Child/Master properties for your subform controls, then Access should automatically requery each subform after you have made a change in one of the Master fields.

Reply | Quote

Mark, aside from the fact that I originally said “refresh” instead of “requery” I’ve no problem with any of that and my subforms refresh/requery perfectly well (but I never said they didn’t :-).

What I want to know is whether this refreshing/requerying can be detected from within the subform so the subform can perform some other actions when it happens.

Cheers,

Simon

Reply | Quote

I don’t think there is a way to tell if a subform has been a forced requery caused by a change in the parent.

Reply | Quote