I cannot get Access XP (2002) to recognize a subform's Filter property in this situation. Perhaps you can help me get the correct VBA.

Situation: Unbound Main form is open. It has two subforms, A and B. Both A and B are in datasheet view. Subforms A and B are based on two tables with one common field called lngFieldA.

What I want: When the cursor is placed inside txtFieldA in Subform A (i.e. txtFieldA has the focus), I want Subform's B filter to change. Right now, I have this in (Subform A) txtFieldA's On Got Focus event.

Me.Parent.Form.sfrSubformB.Form.Filter = "[lngFieldA] = " & Me.txtFieldA

That is, whatever value the cursor sits on in Subform A Field A, make SubformB's filter change so only Subform B Field A values are shown. However, I'm getting an error message saying that the filter property is not valid. Why not? I can do this:

MsgBox Me.Parent.Form.sfrSubformB.Form.Name, vbInformation, "Name"

and it will actually pop up a message box that says, "sfrSubformB". So if the .Name property works, why doesn't the .Filter property work? :(


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Try this;
Me.Parent.Form.sfrSubformB.Form.Filter = "[lngFieldA] = " & Me.txtFieldA
Me.Parent.Form.sfrSubformB.Form.FilterOn = True

Just for the record, this syntax...
sfrSubformB.Form.Filter = "[lngFieldA] = " & Me.txtFieldA
sfrSubformB.Form.FilterOn = True
...should work.

& to show all records again, simply
sfrSubformB.Form.FilterOn = False

Hope this helps, good luck!

Nope... none of that works. It's like just because I'm calling the .Filter property from Subform A and applying it to Subform B, it no longer exists in the library. The filter works great if I use it from the main form on a subform or from within the same subform, but if I use it from one subform to ANOTHER, it becomes invalid. :(

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Sorry Greg, I took a bit for granted, at first.
This worked for me.
Forms!MainFormName![sfrSubformB].Form.Filter = "[lngFieldA] = " & Me.txtFieldA
Forms!MainFormName![sfrSubformB].Form.FilterOn = True

Hope this is the ticket!
Good Luck!