writing to report detail

[Tony Piperato]

Greetings,

Can someone please tell me how to write a recordset to the detail area of a
report with vba? I have a module that builds a recordset. This recordset
may have hundreds of records. I need to know how to write each record to a
report detail area. For example, my recordset my have fields such as FName,
LName, Address1, Address2, CustomField1, CustomField2, etc.. Also, I will
be using a for/next loop and will need to write the value of the loop to the
detail area as well, which is in no recordset.

Actual code examples are appreciated.

Regards,

Tony

 

[SA]

Tony:

First and most fundamentally, is there a reason that you don't set the
report's recordsource to be the query or SQL that you are opening as a
recordset and then use bound controls in the report?

If you are using anything less than Access 2002, you can't set a report's
recordsource to be equal to a recordset at run time, it will fail. (Even
when it doesn't fail in Access 2002, it doesn't work very well, you can't
sort and group in the report when the report's recordsource is set to a
recordset rather than to a query, view or SP.

If on the otherhand you simply want to loop through and as the report runs
and set values into controls, then first you need to understand how Access
reports work.

A report uses the recordsource and how many records are returned there, to
determine the number of detail lines to create as it prints. So you can't
willy nilly simply write to the detail of a report. What you've got to do
is to use some proxy query (similar to the recordset you want to use) and
set the report's recordsource to that to generate "rows".

Then of course in the On Open event open your recordsource.

In the on print event of the detail, which of course will fire with every
row now generated, you simply use and counter to loop through the rows of
your rs and assign values of fields to unbound controls.

HTH

 

[Tony Piperato]

Thanks for the answer Steve. The reason I am not using a query is because
there are complicated calculations to be performed that, as far as I have
assertained, cannot be done through a query. Using your suggestions in part
however, I may make a table then use it for the report.

Thanks again.

Tony

 

[SA]

Tony:

The "best practice" in this regard (i.e.making calculations) is to pull all
the base data into there report via a query, then do the calculations in the
report using VBA. That will increase the speed of your report, especially
in a networked situation.

HTH

 

[Tony Piperato]

Yes, but HOW do I do it? Let me expand on my needs:

1) I have a VERY complicated calculation involving a table.
2) I will run through a for/next loop to represent a 24 hour period (for x
= 1 to 24) and NO I cannot use an outer join with a table that has 24 rows.
3) I need to print the 24 hours down the page of the report along with the
results from my calculation, so the result would look like:

Hour Reading
1 5.76
2 0
3 3.45
etc..

4) I can have more than one day's worth of readings and so will need to
group on date which IS in the table data.

There you are. All I want to know is first; How do I attach the hour data
to a textbox if it is coming from a for/next loop and second; how do I
attach the "reading" data (which is a calculation remember) to the "reading"
textbox on the report.

Hope this clears things up and thanks again for all your help.

Tony

 

[Ron Weiner]

If you can not or are unwilling to try Steve's sage advise, consider
persisting your recordset to a table, and basing your reports datasource on
that table.

You need to consider problems that could (inevitably will) be encountered in
a multi-user environment where all of the users share the same database.

If you have the database broken apart FE/BE and each user has her own copy
of the front end, then this is really easy. Simply use a table in the front
end database.

If the database is not broken apart or all of the users use the same front
end database you will have to come up with a method to keep users from
stepping all over themselves should more than one of em decide to run the
same report at the same time.

Ron W

 

[SA]

Tony:

You are making this MUCH more complex than is should be. Here's how to do
it.

Let say you have a field with the Date and another with the time,
(alternately a full date time field assuming that it is long date time
format rather than a short date/time which will result in putting all the
dates in with a 12:00:00 for the time.)

Notwithstanding that you can parse a time field using the DatePart function
to pull the hour the reading occurred on a 24 hour basis. I.e.
datepart("h",#23:15:00#) = 23

Then create a simple little table in your db that just has one column called
HOUR and fill it with 24 rows with 1 to 24 as values for the Hour field.

Then, add your data table to a query adding the date, datepart of time and
the reading; that's query one. Create a second query which includes just
the date field from your data table (filtered as appropriate for the date
range you want), and the 24 hour table, drag the date and the hour field on
to the query grid, but do NOT join the tables. You should get as a result
from this query something like this:

5/2/2003 1
5/2/2003 2
5/2/2003 3

etc.

One row for each date and the 24 hours.

Then create a third query which incorporates the first data query and to
that query add your date/hours query. Create a left outer join between the
date/hour query and the data query joining on date and hour including all
the rows form the date/hour query and only those rows from the data query
that have matching rows. In the measure field, alias that with a little
IIF function (don't use NZ it tends to return strings rather than numbers)
to make sure that if you have a null value for any hour's reading you return
a zero as in:

Read: IIF(IsNull([Reading]),0, [Reading])

The result from this thid query might look like this:


5/2/2003 1 3.65
5/2/2003 2 0
5/2/2003 3 5.63
etc.

So now you have all your data drawn by queries, which are substantially
faster than VBA loops.

In your report then, simply create groups on the date and in the group
footer, if you need a calc such as a sum, use the report to do that
calculation; if you need calcs for each detail line use the On print event
to do your calc.

 

[Tony Piperato]

Steve,

YOU HAVE DONE WHAT I HAVE ASKED FOR NOW FOR MANY WEEKS!!! THANK YOU! I
have not tried your suggestion but I will in the morning. Thank you again
for finally offering an answer...yes I know my question was poorly worded,
but that's because in other postings when I have spelled out the need I have
received incomplete replies so I just gave up.

Thanks again Steve.

Tony

Tony:

You are making this MUCH more complex than is should be. Here's how to do
it.

Let say you have a field with the Date and another with the time,
(alternately a full date time field assuming that it is long date time
format rather than a short date/time which will result in putting all the
dates in with a 12:00:00 for the time.)

Notwithstanding that you can parse a time field using the DatePart function
to pull the hour the reading occurred on a 24 hour basis. I.e.
datepart("h",#23:15:00#) = 23

Then create a simple little table in your db that just has one column called
HOUR and fill it with 24 rows with 1 to 24 as values for the Hour field.

Then, add your data table to a query adding the date, datepart of time and
the reading; that's query one. Create a second query which includes just
the date field from your data table (filtered as appropriate for the date
range you want), and the 24 hour table, drag the date and the hour field on
to the query grid, but do NOT join the tables. You should get as a result
from this query something like this:

5/2/2003 1
5/2/2003 2
5/2/2003 3

etc.

One row for each date and the 24 hours.

Then create a third query which incorporates the first data query and to
that query add your date/hours query. Create a left outer join between the
date/hour query and the data query joining on date and hour including all
the rows form the date/hour query and only those rows from the data query
that have matching rows. In the measure field, alias that with a little
IIF function (don't use NZ it tends to return strings rather than numbers)
to make sure that if you have a null value for any hour's reading you return
a zero as in:

Read: IIF(IsNull([Reading]),0, [Reading])

The result from this thid query might look like this:


5/2/2003 1 3.65
5/2/2003 2 0
5/2/2003 3 5.63
etc.

So now you have all your data drawn by queries, which are substantially
faster than VBA loops.

In your report then, simply create groups on the date and in the group
footer, if you need a calc such as a sum, use the report to do that
calculation; if you need calcs for each detail line use the On print event
to do your calc.

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Yes, but HOW do I do it? Let me expand on my needs:

1) I have a VERY complicated calculation involving a table.
2) I will run through a for/next loop to represent a 24 hour period

(for

x
= 1 to 24) and NO I cannot use an outer join with a table that has 24 rows.
3) I need to print the 24 hours down the page of the report along with the
results from my calculation, so the result would look like:

Hour Reading
1 5.76
2 0
3 3.45
etc..

4) I can have more than one day's worth of readings and so will need to
group on date which IS in the table data.

There you are. All I want to know is first; How do I attach the hour data
to a textbox if it is coming from a for/next loop and second; how do I
attach the "reading" data (which is a calculation remember) to the "reading"
textbox on the report.

Hope this clears things up and thanks again for all your help.

Tony
pull
all in
the

as

a to got detail
area such
as Also,