Report the current filtered records from a Form

[melnhed]

---Report the current filtered records from a Form---

Hello All,

I've seen this topic discussed before, but the solution described then
doesn't work in my particular case.

My Config: Access 2002 front-end using SQL Server 2000 (MSDE actually)
via ADP/ADE Access Data Project.

I have a form (containing about 80 fields) on which I allow the user to
apply field/form filters -- this works fine.

The form has a <print reportbutton which should trigger a report based
on that filtered recordset -- this does not work.

I've tried the following VBA code in the button's click event handler,
which I've read should work in plain Access, but fails for me because I'm
using SQL Server at the back-end.

Dim strWhere As String
strWhere = ""
If Me.Dirty Then
Me.Dirty = False
End If
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "myreport", acPreview, , strWhere

The above code passes the form's current filter as a parameter to
OpenReport, which fails with various SQL syntax errors because Me.Filter
contains a SQL "where" clause (without the word "where), but it is in
Access/Jet SQL format, not SQLServer's SQL format. It contains quotes
instead of apostrophes around strings, uses "=True" for YES/NO fields
instead of "=1" or "=-1" for SQLServer's BIT type fields, etc. This is
strange because Access knows that it's using a SQLServer backend (it's a
..ADP project).

Ideally I'd like to know how to pass the actual recordset to the report
(instead of the above attempt at having the report re-query the db), but
this doesn't seem possible?

A second-best solution I guess would be a way to retrieve the Me.Filter
value in SQLServer's format, or call a function which does that for me.

Perhaps there are completely different approaches/solutions?

ANY help would be VERY MUCH appreciated! Please don't assume any idea is
too obvious to suggest -- while I'm a 20-year coding veteran, I'm VERY
new to Access and VBA.

A happy bonus to a solution would be to be able to pass the current sort-
ordering of the form to the report as well.

Thank you very much,

melnhed

[Tom van Stiphout]

On 27 Oct 2006 04:04:25 GMT, melnhed <[e-mail]:

It's still Access applying the filter, that's why it's in that format.
But it seems you already know what to replace, so why don't you use
the Replace function to do so.

One alternative is to iterate over the RecordsetClone, collect all
primary key values, save them to a "temporary" table (say tblTempPK),
and use that table to innerjoin with:
select * from Customers
inner join tblTempPK on Customers.CustomerID = tblTempPK.PrimaryKey
This would restrict all customers to the selected ones.
Great trick for when a really complex filter has been applied using
FilterByForm.

-Tom.

>---Report the current filtered records from a Form---

Hello All,

I've seen this topic discussed before, but the solution described then
doesn't work in my particular case.

My Config: Access 2002 front-end using SQL Server 2000 (MSDE actually)
via ADP/ADE Access Data Project.

I have a form (containing about 80 fields) on which I allow the user to
apply field/form filters -- this works fine.

The form has a <print reportbutton which should trigger a report based
on that filtered recordset -- this does not work.

I've tried the following VBA code in the button's click event handler,
which I've read should work in plain Access, but fails for me because I'm
using SQL Server at the back-end.

Dim strWhere As String
strWhere = ""
If Me.Dirty Then
Me.Dirty = False
End If
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "myreport", acPreview, , strWhere

The above code passes the form's current filter as a parameter to
OpenReport, which fails with various SQL syntax errors because Me.Filter
contains a SQL "where" clause (without the word "where), but it is in
Access/Jet SQL format, not SQLServer's SQL format. It contains quotes
instead of apostrophes around strings, uses "=True" for YES/NO fields
instead of "=1" or "=-1" for SQLServer's BIT type fields, etc. This is
strange because Access knows that it's using a SQLServer backend (it's a
.ADP project).

Ideally I'd like to know how to pass the actual recordset to the report
(instead of the above attempt at having the report re-query the db), but
this doesn't seem possible?

A second-best solution I guess would be a way to retrieve the Me.Filter
value in SQLServer's format, or call a function which does that for me.

Perhaps there are completely different approaches/solutions?

ANY help would be VERY MUCH appreciated! Please don't assume any idea is
too obvious to suggest -- while I'm a 20-year coding veteran, I'm VERY
new to Access and VBA.

A happy bonus to a solution would be to be able to pass the current sort-
ordering of the form to the report as well.

Thank you very much,

melnhed

[melnhed]

Thanks very much for the reply, Tom.

Apparently this Access/VBA newbie (that's me) quite underestimated the
complexity of this task. I had assumed that generating a report on a
current recordset would be a simple task in Access, perhaps taking at
most a few minutes to implement.

I'll look into both of your suggestions.

The "Replace" idea I had already implemented for the string and
boolean/bit fields, but I recognized that I'll probably run into
problems with strings containing apostrophes or quotation marks, and
then there are possibly other filter types to investigate like
less-than/greater-than (which Access seems to support when users are
field-filtering), and probably other constructs that I'm not even aware
of yet (again, I am fairly new at this!). Too bad Access doesn't seem
to make such a conversion function available to VBA code -- or maybe it
does and we just haven't found it.

I understand your alternative idea at a high level, but I have much
learning to do before I could implement such a technique. I won't
bother you by asking for more technical details on your idea, as I can
research these myself.

I would like to ask you these higher-level questions about your
"temporary table" idea though: would Access create this table natively
in Jet format on the local machine, or would it create it on the
SQLServer back-end? If on the back-end, would this be incredibly slow
(one sql insert per record)? Also, if on the back-end, could the
temp-table be private to the current user (I need to support
multiple-users).

Thanks again for taking the time to respond.

-melnhed

Tom van Stiphout <no*************@cox.netwrote in
news:du********************************@4ax.com:

On 27 Oct 2006 04:04:25 GMT, melnhed <[e-mail]:

It's still Access applying the filter, that's why it's in that format.
But it seems you already know what to replace, so why don't you use
the Replace function to do so.

One alternative is to iterate over the RecordsetClone, collect all
primary key values, save them to a "temporary" table (say tblTempPK),
and use that table to innerjoin with:
select * from Customers
inner join tblTempPK on Customers.CustomerID = tblTempPK.PrimaryKey
This would restrict all customers to the selected ones.
Great trick for when a really complex filter has been applied using
FilterByForm.

-Tom.

>>---Report the current filtered records from a Form---

Hello All,

I've seen this topic discussed before, but the solution described then
doesn't work in my particular case.

My Config: Access 2002 front-end using SQL Server 2000 (MSDE
actually) via ADP/ADE Access Data Project.

I have a form (containing about 80 fields) on which I allow the user
to apply field/form filters -- this works fine.

The form has a <print reportbutton which should trigger a report
based on that filtered recordset -- this does not work.

I've tried the following VBA code in the button's click event handler,
which I've read should work in plain Access, but fails for me because
I'm using SQL Server at the back-end.

Dim strWhere As String
strWhere = ""
If Me.Dirty Then
Me.Dirty = False
End If
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "myreport", acPreview, , strWhere

The above code passes the form's current filter as a parameter to
OpenReport, which fails with various SQL syntax errors because
Me.Filter contains a SQL "where" clause (without the word "where), but
it is in Access/Jet SQL format, not SQLServer's SQL format. It
contains quotes instead of apostrophes around strings, uses "=True"
for YES/NO fields instead of "=1" or "=-1" for SQLServer's BIT type
fields, etc. This is strange because Access knows that it's using a
SQLServer backend (it's a .ADP project).

Ideally I'd like to know how to pass the actual recordset to the
report (instead of the above attempt at having the report re-query the
db), but this doesn't seem possible?

A second-best solution I guess would be a way to retrieve the
Me.Filter value in SQLServer's format, or call a function which does
that for me.

Perhaps there are completely different approaches/solutions?

ANY help would be VERY MUCH appreciated! Please don't assume any idea
is too obvious to suggest -- while I'm a 20-year coding veteran, I'm
VERY new to Access and VBA.

A happy bonus to a solution would be to be able to pass the current
sort- ordering of the form to the report as well.

Thank you very much,

melnhed

[melnhed]

I've been playing with the RecordsetClone as you suggested,Tom, but am
stuck at a very early stage.

For me, the following TEST-ONLY code in my form's button click handler
displays the # of records in the original recordset, not the
user-filtered subset. Any thoughts on how to make this RecordSetClone
reflect the filtered records only? Also, the RS.Filter shows as simply
the digit "0".

Code:

If Me.FilterOn Then
Dim n As Integer
Dim RS As Recordset
Set RS = Me.RecordsetClone
RS.MoveLast
n = RS.RecordCount

MsgBox "# of records: " & CStr(n)
MsgBox RS.Filter
End If
Thank you again for any input!


Tom van Stiphout <no*************@cox.netwrote in
news:du********************************@4ax.com:

On 27 Oct 2006 04:04:25 GMT, melnhed <[e-mail]:

It's still Access applying the filter, that's why it's in that format.
But it seems you already know what to replace, so why don't you use
the Replace function to do so.

One alternative is to iterate over the RecordsetClone, collect all
primary key values, save them to a "temporary" table (say tblTempPK),
and use that table to innerjoin with:
select * from Customers
inner join tblTempPK on Customers.CustomerID = tblTempPK.PrimaryKey
This would restrict all customers to the selected ones.
Great trick for when a really complex filter has been applied using
FilterByForm.

-Tom.

>>---Report the current filtered records from a Form---

Hello All,

I've seen this topic discussed before, but the solution described then
doesn't work in my particular case.

My Config: Access 2002 front-end using SQL Server 2000 (MSDE
actually) via ADP/ADE Access Data Project.

I have a form (containing about 80 fields) on which I allow the user
to apply field/form filters -- this works fine.

The form has a <print reportbutton which should trigger a report
based on that filtered recordset -- this does not work.

I've tried the following VBA code in the button's click event handler,
which I've read should work in plain Access, but fails for me because
I'm using SQL Server at the back-end.

Dim strWhere As String
strWhere = ""
If Me.Dirty Then
Me.Dirty = False
End If
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "myreport", acPreview, , strWhere

The above code passes the form's current filter as a parameter to
OpenReport, which fails with various SQL syntax errors because
Me.Filter contains a SQL "where" clause (without the word "where), but
it is in Access/Jet SQL format, not SQLServer's SQL format. It
contains quotes instead of apostrophes around strings, uses "=True"
for YES/NO fields instead of "=1" or "=-1" for SQLServer's BIT type
fields, etc. This is strange because Access knows that it's using a
SQLServer backend (it's a .ADP project).

Ideally I'd like to know how to pass the actual recordset to the
report (instead of the above attempt at having the report re-query the
db), but this doesn't seem possible?

A second-best solution I guess would be a way to retrieve the
Me.Filter value in SQLServer's format, or call a function which does
that for me.

Perhaps there are completely different approaches/solutions?

ANY help would be VERY MUCH appreciated! Please don't assume any idea
is too obvious to suggest -- while I'm a 20-year coding veteran, I'm
VERY new to Access and VBA.

A happy bonus to a solution would be to be able to pass the current
sort- ordering of the form to the report as well.

Thank you very much,

melnhed