Printer friendly report based on a filtered form

[slickdock]

I have a continuous form that users can filter or sort at will. Once they
have finished filtering and sorting to their heart's content, I'd like to
have a print command button that opens a printer friendly report that I have
designed for that form. But the report's sorting and filtering should match
what the user has currently done with the continuous form.

Can this be done? Thank you.

 

[Allen Browne]

Okay, there's 2 parts to your question: filtering, sorting.

It's easy enough to take the form's Filter property and use it as the
WhereCondition for OpenReport. The code would look like this:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

There's a bit more to it when it comes to sorting. If the report has nothing
in its Sorting'n'Grouping box, then you can use the report's Open event to
examine the OrderBy property of the form (if its OrderByOn is true), and
apply the same string to the report's OrderBy property (remembering to set
OrderByOn also.)

But if the report has Sorting'n'Grouping, OrderBy won't work. You need to
set the ControlSource of the GroupLevel, as explained here:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

 

[Tom van Stiphout]

On Fri, 31 Jul 2009 13:48:01 -0700, slickdock

The way I solve this is by writing the primary key values of the
resulting recordsetclone to a "temp" table, and inner-join my query
with that table.

-Tom.
Microsoft Access MVP

 

[Tom van Stiphout]

On Sat, 1 Aug 2009 12:24:00 +0800, "Allen Browne"

I have tried your suggestion in the past, but found that it does not
work in the general case. For example filtering on a dropdown may
create ~sq* queries behind the scenes, and the approach may fail.
Certainly there are some, maybe many, scenarios where it does work.

-Tom.
Microsoft Access MVP

 

[Allen Browne]

Hi Tom

Yes, I should have pointed out that in Access 2002 and later, where a form
has a combo box, and its bound column is not the display value, the Filter
of the form may contain something like:
Lookup_DoctorTable.Combo1 = "Dr Tom"

If you need to handle that, the workaround is to include the lookup table in
the query that feeds the report, and set the Alias property of the table so
that it matches the name in the form's Filter string. If you do that, the
report should be able to use the form's Filter string.

Or was it something else you had in mind, Tom?

 

[slickdock]

I like the idea of writing the filtered records to a temp table, and basing
the report on that temp table. I know how to create the temp table, clear
existing records, and use an append query to write new records to the temp
table. But how do I assign the form's currently displayed records to that
table? I am, by the way, using a combo box to filter records, plus whatever
else the user might have done to further filter and sort the records.