Subform NOT Refreshing When Reseting Filter Criteria to Null/Nothi

[Guest]

I have a form with 2 subforms. On the main form the user can select from a
series of combo/drop down boxes to filter the information displayed in the
subform. When the user selects an item from the combo/drop down box, the
afterupdate event creates the SQL string to filter.

Me.subfrmA.Form.FilterOn = True
Me.SubfrmA.Filter = SqlStr
Me.subfrmB.Form.FilterOn = True
Me.SubfrmB.Filter = SqlStr

This works great. The problem is when the user deselects all the criteria,
hence there is no filter. The following is the code that gets executed:

Me.subfrmA.Form.FilterOn = False
Me.SubfrmA.Filter = ""
Me.subfrmB.Form.FilterOn = False
Me.SubfrmB.Filter = ""

The subfrmA requeries in the Main form correctly, subfrmB does not refresh
in the Main Form. SubfrmB does not change at all. I have tried the adding
one or more of the following lines:

me.subfrmB.requery
me.subfrmB.form.requery
me.requery
me.subfrmB.form.recordset.requery

Nothing is working. This is frustrating because of the fact that one
Subform refreshes and the second does not. As far as know, I am using the
exact same method for both.

Does anybody have any ideas. Thank you for your help.

--
Roger Maniccia
emdeon
26 Century Blvd
Nashville, Tenn
615-886-9031

 

[Douglas J. Steele]

When you refer using Me.subfrmA, subfrmA refers to the name of the subform
control on the parent form. Depending on how you added the subform to the
parent form, the name of the subform control may not be the same as the name
of the form being used as a subform. Check the name of the control.

 

[Guest]

Douglas,

Yes what you are stating is true; the reference to Me.subfrmA, subfrmA
refers to the name of the subform control on the parent form. The question
still is why does subfrmB refresh properly in all instances where the filter
criteria has a "value", say

PayerID = 62

or an instance with the following filter criteria

(PayerID = 62) and (RegionID = 9) and (TimePeriod between 1/1/2006 and
1/1/2007)

Both subforms refresh/requery perfectly. However if I set/clear the filter
criteria to view all the records, SubfrmB does not refresh, but subfrmA
does?? The only way I know to remove the criteria is to set the filterOn
property to False, and set the Filter property to "".

I have provided the exact code that I using below. There are no differences
that I can see in the method employed between the various criteria filtering
or between the 2 subforms.

Do you have any thoughts. I have run into this problem before. The only
way I was able to workaround the problem was to reset/change the recordsource
of the subform, the Microsoft Access refreshed the recordset of the subform.

Thank you for your help.
--
Roger Maniccia
emdeon
26 Century Blvd
Nashville, Tenn
615-886-9031