repost - bind a virtual recordset to a report

[tina]

hi folks. i posted this question a week ago - see below - and got no
response. it *is* long-winded, and i apologize for that; i tried to paint a
clear picture so as not to waste anybody's time in a long Q&A. i'm hoping
somebody either has a suggestion, or....even a positive "it's not possible"
response would be helpful to me. any takers?

tia, tina

**********************

hi folks,

using A97. here's the scenario: i have an Excel worksheet which is printed
out as a blank form with lines in columns, which is filled in by hand (don't
ask). the leftmost and middle columns are pre-filled with sequential numbers
calculated in the spreadsheet so that on the first printed page you see
numbers 1 - 20 in the left column, 21 - 40 in the middle column; second page
41 - 60 in the left column and 61 - 80 in the middle column, and so on...for
however many pages are printed. to advance the numeric sequence at the next
printing, i open the spreadsheet, look at the very last number in middle
column, for instance 200, then go back to the very top of the first column,
type in 201 and exit the cell, at which point the rest of the values
automatically increment up to 400.

i want to move this setup into Access. now i know that i can create a table
to hold temporary records, use a VBA loop to populate the table 1 - 200, and
bind a report to the table to print out the format i want. then delete all
the records from the table, storing only the highest number, and at the next
printing i can repopulate the table incremented from the stored high number,
and print again, etc, etc.

here's the question: is there some way to build a *virtual* recordset to
base the report on, so that i can store the high value only and avoid
appending/deleting records from a "real" table at each printing?

all comments and suggestions are appreciated.

tia, tina

**********************

 

[Brendan Reynolds]

You can't bind a form or report to a recordset in Access 97. In later
versions of Access, you can assign a recordset to the Recordset property of
a *form* in an MDB, but even in Access 2003, the Recordset property of a
*report* is only available in an ADP.

 

[Guest]

You may be able to do it all inside a transaction, print the report,
and roll back the transaction. I've never tried it.

You can use a linked table, and delete the linked mdb at completion.

Actually, if all you want is a report, reports are built so that you
can, if necessary, use an unbound report, and use code to move
down the report, writing in the values as you go. There are some
move commands for reports to do this.

(david)

 

[tina]

okay, at least now i won't waste any more time scratching my head over it -
thanks Brendan!

 

[Marshall Barton]

hi folks. i posted this question a week ago - see below - and got no
response. it *is* long-winded, and i apologize for that; i tried to paint a
clear picture so as not to waste anybody's time in a long Q&A. i'm hoping
somebody either has a suggestion, or....even a positive "it's not possible"
response would be helpful to me. any takers?

using A97. here's the scenario: i have an Excel worksheet which is printed
out as a blank form with lines in columns, which is filled in by hand (don't
ask). the leftmost and middle columns are pre-filled with sequential numbers
calculated in the spreadsheet so that on the first printed page you see
numbers 1 - 20 in the left column, 21 - 40 in the middle column; second page
41 - 60 in the left column and 61 - 80 in the middle column, and so on...for
however many pages are printed. to advance the numeric sequence at the next
printing, i open the spreadsheet, look at the very last number in middle
column, for instance 200, then go back to the very top of the first column,
type in 201 and exit the cell, at which point the rest of the values
automatically increment up to 400.

i want to move this setup into Access. now i know that i can create a table
to hold temporary records, use a VBA loop to populate the table 1 - 200, and
bind a report to the table to print out the format i want. then delete all
the records from the table, storing only the highest number, and at the next
printing i can repopulate the table incremented from the stored high number,
and print again, etc, etc.

here's the question: is there some way to build a *virtual* recordset to
base the report on, so that i can store the high value only and avoid
appending/deleting records from a "real" table at each printing?

Instead of a recordset, you can just fool an unbound report
into doing that. I'll assume you have a form to specify the
start and end numbers (or where you can calculate them) in
two text boxes named txtStart and txtEnd.

Create the report with its Report Header section invisible.
Set its margins, columns and width as needed.

Add a text box named txtLineNo to the detail section along
with a line control or whatever else you need.

Add code to the report header section's Format event to
initialize the line number:

Me.txtLineNo = Forms!theform.txtStart - 1

Add code to the detail section's Format event to increment
the line number:

If Me.Top + Me.Detail.Height <= 9 * 1440 Then
Me.txtLineNo = Me.txtLineNo + 1
End If
Me.NextRecord = (Me.txtLineNo >= Forms!theform.txtEnd)

The 9 is the page height less the bottom margin.

Alternatively, you can use a query as your virtual
recordset. First create a table named Digits with one field
named N and populate it with 10 records 0, 1, . . ., 9. The
query would then be:

SELECT CLng(D1 & D2 & D3 & D4) As LineNo
FROM Digits As D1, Digits As D2, Digits As D3, Digits As D4
WHERE CLng(D1 & D2 & D3 & D4) Between Forms!theform.txtStart
AND Forms!theform.txtEnd

 

[tina]

Marsh, i just tested your first solution - it's just what i was hoping for!
way cool, and thank you so much!

 

[tina]

thanks, david; the unbound report idea, as detailed by Marsh, hit the spot.

 

[Marshall Barton]

Glad to help tina.

Actually, I prefer the query approach because it is not as
"delicate" as the unbound report.