UPDATE: Regarding Filters and SubForm Source Objects

[rubbernilly]

For those of you who tried to help me before with my subform object filter problem (back story explanation here: thread705-1139800 "Undocumented Filter"), I thought I would share this update that I got from Frank Rice (MSDN Office Developer Center) at Microsoft:

Let me first apologize for taking so long to get back to you but I wanted to discuss this with various members of the Access product team. The outcome of some detailed investigation and discussions on this behavior is that this is a by-product of the interactions between different processes in the source code. Because it is not a feature per se, and can’t be traced to specific code or to a specific process, it can’t be surfaced as something that can be manipulated or modified. As such, we have to classify this as an unsupported behavior.

I apologize for any inconvenience that this might cause you but will hopefully answer your question.

Frank Rice
MSDN Office Developer Center

http://blogs.msdn.com/frice

(I verified with Frank that it was OK for me to share his response so as to disseminate this information.)

Simultaneously, I was working with Jitin in Microsoft's Product Support and we discovered that in Access 2003, the functionality to set and retrieve the Filter is there.

In Access 2000, this produces an error when the SourceObject for the SubFormControl is a query (at least, a crosstab query):
Me.SubFormControl.Form.Filter

However, in Access 2003, when a SubFormControl has a query as the SourceObject, the above line of code will return the filter being applied to the SubForm data.

I also received this from Frank, which basically says the same thing:

I’m not able to find any documentation on the Filter property on subform controls in Access 2K (or in Access 2003 for that matter). Would something like this work?

Private sub cmbSelect_change()
  dim strFilter as string
  strFilter = "numfieldName = " & cmbSelect.value
  'or 
  'strFIlter = "strfieldName = '" & cmbSelect.value & "'"
  with me.controls("subControlName").form
     .filter = strFilter
      .filteron = true
  end with
end sub

So, although it is undocumented and unsupported in both Access 2000 and 2003, it does appear to at least work in 2003. I guess that means I'll be converting and developing my database in 2003.

I just thought I'd share this so that others would not have to go through the weeks of frustrated searching on the web and in help documentation that I went through trying to find something that is not there.

Thank you to those who have tried to help me along the way!