Access Form to Report Filter Inconsistency Problem

[Jerry McManus]

When values in look up combo boxes on an access form are used with
filter-by-form, the same filter can't be handled by a report based on
the same recordsource and filter as the form.

Example:
Form uses a query which contains fields which have primary keys for
lookup tables.
In combo boxes on the form the value from the lookup table is
displayed
query field = "TAX_RIDER", value = "P"
Rowsource for combobox is table "ztlkTaxRider" in the front end
database with fields Code and Description as follows:
Code Description
P "Percentage"
N "None"
R "Reimbursable"
With filter-by-form "Percentage" is selected
The filter then contains the following
((Lookup_TAX__RIDER.Description="Percentage"))
When the filter is applied this works fine, even though the Lookup__
part does not seem to be standard SQL and an extra Underscore has been
added.

Now a report is generated with a command button on the main form to
preview a report using the same recordsource and filter on the report
that is on the main form.

A message box is generated to "Enter Parameter Value" with contents of

"Lookup_TAX__RIDER.Description"

The message I seem to be getting is that Microsoft doesn't like
reports that use combobox lookup fields with filter-by-form.

Since I want to use lookup tables in the form and want to save space
and keep the database in higher normal form (storing the "P" instead
of "Percentage") in the backend table and reduce network traffic, it
looks like the answer would be to write code to loop through all the
phrases beginning with "Lookup_" in the filter and replace them with
proper SQL phrases.

I am hoping I am overlooking some very obvious setting or technique to
solve this problem. Comments appreciated.

 

[Allen Browne]

Change the source query for the report so it included the Lookup_TAX_RIDER
table as well.

In query design view, you may need to double-click the line joining this
table to the other one, so you can choose option 2 or 3 (an outer join).