Filtering a subform problem...

A

AlexT

Folks

I have a typical Parent / Child data structure that I display in a very
standard form & subform layout. The subform simply displays all the
linked child records in a datasheet view.

Everything works just fine... but I now have the need to filter my
child view.

I have tried to use the filter property but it doesn't seem to
work... so I have resorted to change (via VBA) the subform data source
as needed.

The problem is that I'm not getting the expected results...

If I use the "show all" query it works ok

SELECT * FROM dbo.qChildLines

But whenever I try to add a WHERE clause to limit the output, such as

SELECT * FROM dbo.qChildLines WHERE (NOT (ldNoPolIdx > 1))

No records are displayed, although running the same query "alone"
(not in the form's context) DOES return the expected results.

I'm probably missing something obvious...

Any help welcome


--AlexT
 
A

Allen Browne

Alex, this could be several things.

Firstly, it is possible to apply a Filter to a subform, e.g.:
Me.Filter = "[IdNoPolldx] <= 1"
Me.FilterOn = True
However, Access can get confused if you also apply a filter or OrderBy to
the main form as well as the subform. Details:
http://allenbrowne.com/bug-02.html

Secondly, when you reassign the RecordSource of a subform or the
SourceObject of a subform contorl, Access is likely to take a wild swing at
whatever it thinks the LinkChildFields and LinkMasterFields should be. This
means that your code need to check these properties are correct, and fix
them if not.

If that does not solve the problem, tell us more about this query. It looks
like it is stacked on another query (qChildLInes), so is IdNoPolldx a
calculated field? If so, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Are you expecting the Nulls to be returned? If so, try:
SELECT * FROM dbo.qChildLines
WHERE ((ldNoPolIdx <= 1) OR (IdNoPolldx Is Null));
 
A

AlexT

Thanks a lot for your quick answer !

To start with I should say that this is an ADP project...

That being said:

* IdNoPolldx is NOT a calculated field.
* Filtering doesn't seem to be available: if I open my subform
(which would then show - as expected - all child records) the
filtering UI is grayed out. not sure why...
* Not sure what to do with the LinkChildFields and LinkMasterFields
properties. Should I compare them before and after setting the
datasource for the subform ?! Some code sample would be welcome...

Again, any feedback most welcome

--alexT
 
A

Allen Browne

Has the form's AllowFilters property been set to No?

Yes, compare LinkChildFields and LinkMasterFields before and after. They are
just strings. Typically one field name, but might be a semicolon(?)
delimited string.
 
A

AlexT

Hi

Well, reseting the LinkChildFields and LinkMasterFields after chaging
the datasource made the trick !

Not sure exactly why but good enough at the moment :)

Thanks

--alexT
 
Top