Dennis said:
How can a person (or even CAN you?) generate a line of detail in a report
manually? For example, what if a person wanted to spin through a table
using
ADO and generate lines of detail in a report that way? Is it even
possible?
If so, how can it be done?
Thanks!
As a general rule it is messy if you try to do this.
The simple solution is to send that data to a temporary table, and then have
the report work off of that temp table data. That way you can do any kind of
conceivable preprocessing of the data or related information in a way
that might not be possible by standard sql etc.
Another really nice way that works well is to simply base the report on
a "testing" table with ONE collum of id's up to the max possible number
of detail lines. So, this table could be generated on the fly each time you
need it, or it could just have one table that exists all the time with a
maximum number possible of rows to be in that report. You then base the
report on this one column table and sort it numeric.
Then when you open the report, let's assume we have to open the report with
ten lines of detail code and the data inside of the report is going to be
100% generated by your software, possibly not even from a table, you could
go:
docmd.OpenReport "MyReport",acviewpreview,,"id <= 10"
Now, in the reprots on-format event, you have
dim strSql as string
strSql = "select bla bla bla where id = " & me!id
or we could go:
strMyTextFile = "c:\mytext data\text" & me!id & ".txt"
code to read in text file...
me.txtMyResult = "one value from above processing code"
As you see in the above for each detail line, the "id" will cycle from the
number 1 to 10, and then your code can simply run into any kind of
processing you want, and then stuff values into the unbound text boxes on
that line of detail (note that the ID collum will have to be bound to a text
box, but you don't have to display/show it). So you're format event code
will run once for each ID that increments from one to what ever number you
require, and at that point your code is free to stuff any conceivable values
into the unbound text boxes on that one line of detail.
The above is kind of a hybrid approach in which you still have a data source
for the report, but in your format event you're free to take the ID number
and do any kind of processing of data from any source for anything you want.
I find this approach quite practical and it also means that you're free to
use the standard report designer tools such as placement of your text boxes
etc, and you get the freedom to use code to fill those text boxes anyway you
desire.
Also keep in mind that if you talking about an recordset, you have to admit
that that recordset must have been filled with data from somewhere. so, do
note that in a report on open event you can stuff the SQL into the record
source property directly and thus you are changing the record source of the
report when it opens. so you have several approaches here depending on
actually what you need to do, but don't rule out the ability of access
reports to have the record source or data source changed. In fact a ado
recordset can be assigned directly to oh, where list box, and I can also be
assigned directly to a form's data source. Perhaps this also works for
report, but I've not tried this.
In my many years of access development, the report writer has never let me
down, and I've never had to resort to 100% code in the report. Between the
possibility of code that creates a temporary table that the report is based
on, or the above high hybrid approach in which I key some additional
processing for detail line off of the "id" means that as a general rule you
can continue to use the report writer and lay out your text boxes through a
graphical interface as opposed to trying to write some type of code to
produce the report.
