Can't apply form's Filter to a report

[Guest]

I have a form set up with a button to open a report based on the same data.
This works fine, however if the form data has been filtered (using
filter-by-form) I want the same filter to be applied to the report.

When I tried this I found that for fields represented by combo boxes the
filter criteria turn up something like:
(([Lookup_Type of service].Name="Charitable trusts"))

The type of service table has two columns, ID and Name, the ID field is
bound for the combo box but hidden and the name is shown.

Why is this extra "Lookup_" being added to my filter and why does this work
for the form but not a report?

I can't find a table, query or function (even hidden/system) called
"Lookup_Type of service", what is it?

Kevin.

 

[Allen Browne]

This problem occurs if you use the Filter By Form to filter on a combo box
that has a zero-width bound column. In that case, the Filter contains data
from a subsequent column of the combo, as you found.

One workaround is to add the Type of Service table to the query that is the
RecordSource of the report, and alias the table with the Lookup_ prefix. To
alias the table, open the query in design view, right-click the table and
choose Properties, and set the Alias so it matches what you see in the
Filter of the form.

A better solution might be to avoid this kind of reference at the form
level, but that means avoiding the use of Filter By Form. That idea may not
be appropriate if you are the only user of the database. It is worth
considering providing other filter mechanisms if you develop for others,
esp. as FBF is not available in the runtimes.

BTW, using Name as a field name is also likely to cause you grief. Most
objects in Access have a Name property, so sooner or later you will be
caught with the Name being misinterpreted.

HTH

 

[Guest]

Thanks Allen, the aliasing suggestion fixed the problem for me. Thanks also
for the advice on naming, I don't do much VBA programming so I'm not aware of
these pitfalls.

Kevin.

This problem occurs if you use the Filter By Form to filter on a combo box
that has a zero-width bound column. In that case, the Filter contains data
from a subsequent column of the combo, as you found.

One workaround is to add the Type of Service table to the query that is the
RecordSource of the report, and alias the table with the Lookup_ prefix. To
alias the table, open the query in design view, right-click the table and
choose Properties, and set the Alias so it matches what you see in the
Filter of the form.

A better solution might be to avoid this kind of reference at the form
level, but that means avoiding the use of Filter By Form. That idea may not
be appropriate if you are the only user of the database. It is worth
considering providing other filter mechanisms if you develop for others,
esp. as FBF is not available in the runtimes.

BTW, using Name as a field name is also likely to cause you grief. Most
objects in Access have a Name property, so sooner or later you will be
caught with the Name being misinterpreted.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form set up with a button to open a report based on the same data.
This works fine, however if the form data has been filtered (using
filter-by-form) I want the same filter to be applied to the report.

When I tried this I found that for fields represented by combo boxes the
filter criteria turn up something like:
(([Lookup_Type of service].Name="Charitable trusts"))

The type of service table has two columns, ID and Name, the ID field is
bound for the combo box but hidden and the name is shown.

Why is this extra "Lookup_" being added to my filter and why does this
work
for the form but not a report?

I can't find a table, query or function (even hidden/system) called
"Lookup_Type of service", what is it?

Kevin.