Neat trick: How to base a report on a form's recordset without re-running the query
Keri Hardwick
this is a question asked fairly frequently:
"How can I make a report showing only the (filtered) records on the form
without re-running the query or re-filtering when I open the report?"
Here is a way!
1. Create a new "driver" table (Table1 here) with one field - ItemCount.
Fill it
with numbers such that you have enough records to match the max number of
records you would need to print on the report. (So, if the table you're
filtering has 3,000 records, go to at least 3,000 in this table.) Allow
room for growth as needed.
2. Create your report, with all the controls you need in the detail
section, all unbound.
3. Time for code. In this example, LastName is the only control in the
Detail section, add your other controls in both the Format and Retreat
events. Form1 is the form, which must be open.
Option Compare Database
Option Explicit
Dim rst As Recordset
Dim reccnt As Integer 'could be Long if you have a lot of records
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not rst.EOF Then
Me.LastName = rst!LastName
rst.MoveNext
End If
End Sub
Private Sub Detail_Retreat()
rst.MovePrevious
Me.LastName = rst!LastName
End Sub
Private Sub Report_Open(Cancel As Integer)
Set rst = Forms!form1.RecordsetClone
rst.MoveLast
reccnt = rst.RecordCount - 1
Me.RecordSource = "Select TOP " & reccnt & " ItemCount from Table1"
rst.MoveFirst
End Sub
The purpose of Table1 is to simply provide a recordsource to trigger the
detail printing enough times.
Anyhow, I think this is pretty cool and hope it works for those looking for
this functionality.
Keri
George Shears
Couple of thoughts....
Add a ReportHeader and an OnFormat handler that does a rst.MoveFirst - this
should let the users preview wirhout losing sync.
You might want to check the FormatCount in Detail_Format() to determine
whether or not a move next/last is required. I have seen instances where a
section is reformatted more than once but no Retreat event is generated.
George Shears
Keri Hardwick wrote in message
<7q40vn$sul$1...@birch.prod.itd.earthlink.net>...
->OK, maybe I'm the only one who thinks this is a neat trick, but it seems
->this is a question asked fairly frequently:
->
->"How can I make a report showing only the (filtered) records on the form
->without re-running the query or re-filtering when I open the report?"
->
->Here is a way!
->
->1. Create a new "driver" table (Table1 here) with one field - ItemCount.
->Fill it
->with numbers such that you have enough records to match the max number of
->records you would need to print on the report. (So, if the table you're
->filtering has 3,000 records, go to at least 3,000 in this table.) Allow
->room for growth as needed.
->
->2. Create your report, with all the controls you need in the detail
->section, all unbound.
->
->3. Time for code. In this example, LastName is the only control in the
->Detail section, add your other controls in both the Format and Retreat
->events. Form1 is the form, which must be open.
->
->Option Compare Database
->Option Explicit
->Dim rst As Recordset
->Dim reccnt As Integer 'could be Long if you have a lot of records
->
->Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
->If Not rst.EOF Then
-> Me.LastName = rst!LastName
-> rst.MoveNext
->End If
->End Sub
->
->Private Sub Detail_Retreat()
-> rst.MovePrevious
-> Me.LastName = rst!LastName
->End Sub
->
->Private Sub Report_Open(Cancel As Integer)
->Set rst = Forms!form1.RecordsetClone
->rst.MoveLast
->reccnt = rst.RecordCount - 1
->Me.RecordSource = "Select TOP " & reccnt & " ItemCount from Table1"
->rst.MoveFirst
->End Sub
->
->The purpose of Table1 is to simply provide a recordsource to trigger the
->detail printing enough times.
->
->Anyhow, I think this is pretty cool and hope it works for those looking
for
->this functionality.
->
->Keri
Jon Logan
time. See if the following works for you.
ff = Me.Filter
stDocName = "MyReport"
DoCmd.OpenReport stDocName, acViewPreview, , ff
One thing I found out a while back is that when you use this method, you
can't use acPreview. Its old code that doesn't give the desired results,
even with Access97. Use acViewPreview. Let me know.
Jon
Keri Hardwick wrote in message
<7q40vn$sul$1...@birch.prod.itd.earthlink.net>...
>OK, maybe I'm the only one who thinks this is a neat trick, but it seems
>this is a question asked fairly frequently:
>
>"How can I make a report showing only the (filtered) records on the form
>without re-running the query or re-filtering when I open the report?"
>
>Here is a way!
>
>1. Create a new "driver" table (Table1 here) with one field - ItemCount.
>Fill it
>with numbers such that you have enough records to match the max number of
>records you would need to print on the report. (So, if the table you're
>filtering has 3,000 records, go to at least 3,000 in this table.) Allow
>room for growth as needed.
>
>2. Create your report, with all the controls you need in the detail
>section, all unbound.
>
>3. Time for code. In this example, LastName is the only control in the
>Detail section, add your other controls in both the Format and Retreat
>events. Form1 is the form, which must be open.
>
>Option Compare Database
>Option Explicit
>Dim rst As Recordset
>Dim reccnt As Integer 'could be Long if you have a lot of records
>
>Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>If Not rst.EOF Then
> Me.LastName = rst!LastName
> rst.MoveNext
>End If
>End Sub
>
>Private Sub Detail_Retreat()
> rst.MovePrevious
> Me.LastName = rst!LastName
>End Sub
>
>Private Sub Report_Open(Cancel As Integer)
>Set rst = Forms!form1.RecordsetClone
>rst.MoveLast
>reccnt = rst.RecordCount - 1
>Me.RecordSource = "Select TOP " & reccnt & " ItemCount from Table1"
>rst.MoveFirst
>End Sub
>
>The purpose of Table1 is to simply provide a recordsource to trigger the
>detail printing enough times.
>
>Anyhow, I think this is pretty cool and hope it works for those looking for
>this functionality.
>
>Keri
>
>
>
>
Dimitri Furman
The point is that in Keri's solution the report is run "without
re-running the query or re-filtering", which may be a big perf gain.
Jon Logan
Menachem
itemcount in the table and update the table if neccesary
Menachem
George Shears <gjsh...@prodigy.net> wrote in message
news:7q4mom$1vdu$1...@newssvr03-int.news.prodigy.com...
> Love it. Love those "driver" tables.
>
> Couple of thoughts....
>
> Add a ReportHeader and an OnFormat handler that does a rst.MoveFirst -
this
> should let the users preview wirhout losing sync.
>
> You might want to check the FormatCount in Detail_Format() to determine
> whether or not a move next/last is required. I have seen instances where
a
> section is reformatted more than once but no Retreat event is generated.
>
> George Shears
>
>
> Keri Hardwick wrote in message
> <7q40vn$sul$1...@birch.prod.itd.earthlink.net>...
> ->OK, maybe I'm the only one who thinks this is a neat trick, but it seems
> ->this is a question asked fairly frequently:
> ->
> ->"How can I make a report showing only the (filtered) records on the form
> ->without re-running the query or re-filtering when I open the report?"
> ->
> ->Here is a way!
> ->
> ->1. Create a new "driver" table (Table1 here) with one field -
ItemCount.
> ->Fill it
> ->with numbers such that you have enough records to match the max number
of
> ->Anyhow, I think this is pretty cool and hope it works for those looking
> for
Robin Stoddart-Stones
I think you are missing the point.
Situation.
You are working in a recordset (not a report).
You have created / modified the data through a complex set of query,
calculation, data input etc.
You want to report that complex set of data.
You have choices.
Open the report with a query that recreates what you have done.
if the query is complex (or possibly uncreatable. Take a
recordset and remove some records on a personal choice basis without
deleting them from the underlying table) then rerunning the query may
take a lot of time
Copy the dataset to a temporary table
(Time factor, duplication of data/ housekeeping/ size)
Use the Keri method to use a small but quick table to access the
complex recordset without re-running the complex query.
I could not see how your method recreated the data without re-opening
the query with the overhead of the filter.
Have Fun
Robin
On Fri, 27 Aug 1999 02:16:29 -0600, "Jon Logan"
<JonL...@spamispigbutts.com> wrote:
> I think I'm missing something, cause I create that functionality all the
>time. See if the following works for you.
>
> ff = Me.Filter
> stDocName = "MyReport"
> DoCmd.OpenReport stDocName, acViewPreview, , ff
>
>One thing I found out a while back is that when you use this method, you
>can't use acPreview. Its old code that doesn't give the desired results,
>even with Access97. Use acViewPreview. Let me know.
>Jon
>Keri Hardwick wrote in message
><7q40vn$sul$1...@birch.prod.itd.earthlink.net>...
>>OK, maybe I'm the only one who thinks this is a neat trick, but it seems
>>this is a question asked fairly frequently:
>>
>>"How can I make a report showing only the (filtered) records on the form
>>without re-running the query or re-filtering when I open the report?"
>>
>>Here is a way!
>>
>>1. Create a new "driver" table (Table1 here) with one field - ItemCount.
>>Fill it
>>with numbers such that you have enough records to match the max number of
>>records you would need to print on the report. (So, if the table you're
>>filtering has 3,000 records, go to at least 3,000 in this table.) Allow
>>room for growth as needed.
>>
>>2. Create your report, with all the controls you need in the detail
>>section, all unbound.
>>
>>3. Time for code. In this example, LastName is the only control in the
>>Detail section, add your other controls in both the Format and Retreat
>>events. Form1 is the form, which must be open.
>>
>>Option Compare Database
>>Option Explicit
>>Dim rst As Recordset
>>Dim reccnt As Integer 'could be Long if you have a lot of records
>>
>>Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>>If Not rst.EOF Then
>> Me.LastName = rst!LastName
>> rst.MoveNext
>>End If
>>End Sub
>>
>>Private Sub Detail_Retreat()
>> rst.MovePrevious
>> Me.LastName = rst!LastName
>>End Sub
>>
>>Private Sub Report_Open(Cancel As Integer)
>>Set rst = Forms!form1.RecordsetClone
>>rst.MoveLast
>>reccnt = rst.RecordCount - 1
>>Me.RecordSource = "Select TOP " & reccnt & " ItemCount from Table1"
>>rst.MoveFirst
>>End Sub
>>
>>The purpose of Table1 is to simply provide a recordsource to trigger the
>>detail printing enough times.
>>
>>Anyhow, I think this is pretty cool and hope it works for those looking for
>>this functionality.
>>
>>Keri
>>
>>
>>
>>
>
>