Filter from form to report - problems!

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

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
 
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.


--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Paul said:
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
Click to expand...
 
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
 
yyyy
Malcolm Cook said:
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.


--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

Paul said:
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
Click to expand...
Click to expand...
 
Back
Top