Filter report records from form

[swas]

Hi,

I have a form which also has a report with the same recordsource. I did it
this way, so when the 'View Report' button on the form is pressed, if any
form filters are in place the user is asked if the same filters are required
on the report. If so, the report filters are set to the form filter, and
everything works fine.

However, what I have noticed if I use the "Advanced Filter / Sort" button
for more complex form filters (Even simple ones...), when these are applied
the form filter uses the full form name instead of just the field name.

Examples:

Select a field, press 'Filter By Selection", the filter generated is
"Product = Fruit"

Generate the same filter via "Advanced Filter Sort", the filter becomes
"frmFormName.Product = Fruit"

In the second case, the report asks for the paramater value for
frmFormName.Product and hence bombs.

I have no way of detecting how a filter may have been generated, or need to
parse these filter strings and try and remove form references. Any easier
ideas? It seems wierd to have two different filters generated.

Thanks in advance


swas

 

[Allen Browne]

So you need to process the form's Filter string, replacing the form name
with the appropriate table name so you can pass the filter to the report.

Presumably you already know what the form name is, so you know what to
search for. You can get at the name of the table as:
Forms(strFormName).RecordsetClone.Fields(strFieldName).SourceTable

Use Replace() to modify the string.
Post back if you need more detail.

Another situation arises in Access 2002 where you filter on a combo where
the display field is not the bound field (e.g. the bound column is
zero-width, so it displays text instead of an autonumber.) In this case,
Access adds a Lookup_xxx prefix to the field, and uses the text value.

 

[swas]

Allen,

Thanks for the reply.

Your solution works fine. I just removed the 'frmFormName.Name & "."' from
the filter, leaving only the field names and not replacing with the table /
query name.

I assume this works in all cases, rather than replacing with the table /
query name as suggested? Perhaps not if there are same field names from
different tables in the recordsource, which isn't in my case.

Had I waited another 30 minutes for your reply, I could have saved myself
some coding. I had created a function to parse / strip the text, not
remembering the function Replace() that does the exact same job... I hate
that...

Still seems wierd that two essentially the same toolbar buttons return
different filters.

Thanks again


swas

 

[Allen Browne]

Removing the prefixed form name will be fine.

The only case where you need the table name is if the query outputs 2 fields
with the same name. For example, of you have ClientID in the Client table,
and ClientID in the Orders table, and the query outputs both, then the
filter has to be specific about which one you are referring to.