Filter from form to report

Filter from form to report - problems!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 26 Apr 2004 11:11 GMT

I wonder if anyone else has experienced these filter problems I
have...

I have an Access 2002 adp connected to a sql 2000 server back-end and
a form that can be filtered using a similar 'filter form'. This builds
a filter string depending on the criteria entered in the fields (using
the BuildCriteria function) that is applied to the main form .filter
property.

(I have never been able to work out how to apply a filter with the
.serverfilter property in code. Setting a filter and using .requery
just seems to have no effect!?? Perhaps I'm missing something)

However, my main problem is with passing the applied form filter to a
report. I use the following code in the report's Open event:

Me.Filter = Forms![form].Filter
Me.FilterOn = True

I have had instances when the report filters correctly, but the
majority of the time, and when using filter criteria that includes the
'%' wildcard or multiple fields, the report filter shows an incomplete
set of records - or no records at all. The point being, the form and
report filter results are not the same!

I've been through so many ideas of trying to solve this (converting
the form filter to a query, server filters, even building a temporary
table with the filtered recordset and using this as the recordsource
of the report) and none seem to be happening for me.

I hope I'm not the only one who has experienced these filter problems
with an adp. Perhaps I'm being too ambitious with the 'filter by any
field you like' form and trying to provide the user with as much
flexibility as possible. Have I hit a limitation of Access, because it
feels like I'm fighting with bugs in the software?!

Of course - any help or comments are very much appreciated.
Thanks,
Paul

Reply to this Message

Malcolm Cook - 26 Apr 2004 18:49 GMT

Paul,

I am using similar approach to what you mention and have not (yet)
encountered your problem.

Are you sure that you are not confused by the (not entirely clearly
documented) semantics regarding interaction of client and server filtering?

One thing that has bulloxed me up quite a bit is that if you add or remove a
server filter, the client filter is ignored regardless of the setting of
.filteron.

Thus, you can get into a state where .filteron is true but the client
.filter is really not applied.

At least this this true with A2002 SP3.

I find that if I put the following in my form_applyfilter

If ApplyType = acApplyServerFilter Then

with me.form
If .FilterOn Then
.FilterOn = False
end if
end with
end if

then, I have the form's .FocusOn correctly indicating what is on the screen.

Then, I do the following to transfer the forms settings to my report_open
(BTW: which take server filters into account)

with me
.ServerFilter = mfrmGovereningAccessForm.ServerFilter
.FilterOn = mfrmGovereningAccessForm.FilterOn
.Filter = mfrmGovereningAccessForm.Filter
.OrderByOn = mfrmGovereningAccessForm.OrderByOn
.OrderBy = mfrmGovereningAccessForm.OrderBy
.InputParameters = mfrmGovereningAccessForm.InputParameters
end with

where mfrmGovereningAccessForm is the form on the screen (passed in using
openargs to the report)

The approach rocks. It lets users print the records they choose through a
report of your design.

Signature

Malcolm Cook - mec@stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

> I wonder if anyone else has experienced these filter problems I
> have...
[quoted text clipped - 35 lines]
> Thanks,
> Paul

Reply to this Message

Paul - 27 Apr 2004 13:43 GMT

Hi Malcolm,

Many thanks for your reply. It's useful to know how other people
implement the functionality I'm aiming for!

I've since discovered that using a ServerFilter for a report is a lot
more reliable than using a standard .Filter in an adp - so thanks
Malcolm for making me revisit that one! For some strange reason, the
standard filter works fine for the form though!??!

So - now I'm in the situation where I'll have to tailor the filter
string (built by the filter form using the BuildCriteria function) to
use the correct syntax for .ServerFilter. It appears that
BuildCriteria creates filter strings like (NAME ALike "%son") as
opposed to (NAME Like '%son') which is what the ServerFilter property
prefers. (Also boolean types are treated differently, -1 for true,
should be 1 in the ServerFilter). The standard .Filter property
accepts the native BuildCriteria string formatting.

If anyone has a succinct list of syntax differences between the
BuildCriteria result and what ServerFilter expects - that would be
fantastic! At the moment I think I'll be doing something like the
following:

StrFilter = Replace(StrFilter, """", "'")
StrFilter = Replace(StrFilter, " ALike ", " Like ")

any more???!! (perhaps with date and boolean types?)

Many thanks once again for all your help,
Paul

> Paul,
>
[quoted text clipped - 43 lines]
> The approach rocks. It lets users print the records they choose through a
> report of your design.

Reply to this Message

shiguirong12345678 - 17 May 2004 08:35 GMT

yyyy

> Paul,
>
[quoted text clipped - 88 lines]
> > Thanks,
> > Paul

Reply to this Message

Justin W - 18 Mar 2005 22:57 GMT

Dear Malcolm Cook,
I think I understand your use of the With statement to take many filters
from a form and apply them to a report, but I want to make sure I
understand this clearly. Your extended code would read something like:

DoCmd.OpenReport "MyReport"
With Me
.ServerFilter = mfrmGovereningAccessForm.ServerFilter
.FilterOn = mfrmGovereningAccessForm.FilterOn
.Filter = mfrmGovereningAccessForm.Filter
.OrderByOn = mfrmGovereningAccessForm.OrderByOn
.OrderBy = mfrmGovereningAccessForm.OrderBy
.InputParameters = mfrmGovereningAccessForm.InputParameters
End With

...where the "Me" here is actually the form, not the report? (Perhaps you
use the With statement to construct the SQL Where string, then run the
DoCmd line?) In any case, how do you format your report to look like you
want? With so many combinations for the number of filters a user can
choose (or not choose), I assume you have no Sorting or Grouping on your
report? I have a form that is working great with several filters (thanks
to JasonM of accessvba.com) but am now trying to work on reports. It would
be great if I could have one master "Print Report" button, which is what
your original post seems to be able to do. However, I don't think I can
preserve the Sorting and Grouping I need for my reports. The only solution
I can come up with is to create several reports with the formatting I need
and to use Select Case... statements to open a report based on the filters
chosen.

If you could please provide a little more detail about how this "With...End
With" code fits into the rest of your code, and how you format your report
(s), then it might help me understand whether or not I can use the same
approach.

Thank you for any help you can give.

Reply to this Message