repost - bind a virtual recordset to a report

  • Thread starter Thread starter tina
  • Start date Start date
T

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

**********************
 
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.
 
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)
 
okay, at least now i won't waste any more time scratching my head over it -
thanks Brendan! :)
 
tina said:
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?
Click to expand...


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
 
Marsh, i just tested your first solution - it's just what i was hoping for!
way cool, and thank you so much! :)
 
Glad to help tina.

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

hmm, well, i picked on the unbound report approach immediately because
there's no table involved, which met my original goal. is there some
inherent instability or some other problem in this solution, that i should
watch out for?


Marshall Barton said:
Glad to help tina.

Actually, I prefer the query approach because it is not as
"delicate" as the unbound report.
--
Marsh
MVP [MS Access]

Marsh, i just tested your first solution - it's just what i was hoping for!
way cool, and thank you so much! :)
Click to expand...
Click to expand...
 
tina said:
hmm, well, i picked on the unbound report approach immediately because
there's no table involved, which met my original goal. is there some
inherent instability or some other problem in this solution, that i should
watch out for?
Click to expand...


By "delicate", I mean that it can collapse if you make some
inocculous change. If you'll note the use of
If Me.Top < page height - bottom margin)
you should wonder why that's obscure check is in there.
Well, it's because the first detail in a column is formatted
twice, once to determine that it won't fit in the previous
column/page and again to place it in its final position.
While that is normal, the other code in the report
increments the line number twice for that detail. This
shows up as a gap in the numbers, so the check for the last
detail is used to avoid incrementing the line number an
extra time.

In normal situations it would be more robust to use a
running sum text box instead of code to increment the line
number, but a different mechanism precludes that as a viable
option. Running sum only increments on different detail
records and the use of NextRecord = False means that the
same detail is being processed multiple times.

What all that adds up to is something that works for your
exact situation, but might very well not work if you want it
to do something a little different.
 
okay, got it. i've saved this entire thread, so if i find that the unbound
report solution doesn't fit in some circumstance, i'll still have the query
solution to turn to. thanks again! :)
 
Back
Top