Updating Calculated report totals /Fields to a Table

[pcdaveh]

Has anyone ever Updated Calculated report totals /Fields to Table or an array? Can anyone lead me to some on-line documentation on how to accomplish this.

Thanks in advance

pcdaveh

 

[jjonesal]

I've done it like this...but there may be a better way.

If you have your totals in the report footer section, then on the On Print Event of that section use:

Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("INSERTQuery&quot
x = qdf.SQL = "INSERT INTO Table2 VALUES (" & [Reports]![Reportname]![fieldname].Value & ""

With dbs
DoCmd.OpenQuery qdf.Name
.QueryDefs.Delete qdf.Name
End With

Where INSERTQuery is a temporary query created to insert the needed values (deleted when you are done)
Table2 is the table you want to insert into
Reportname is the name of the report
fieldname is the name of the field you want to insert into the table

If you wanted to insert multiple records, you could loop through, or if you wanted to insert more than one field per record you could add additional controls from the report in the INSERT statement.

Note, this will insert will happen each time you run the report. I sometimes include a date field in the table and insert the current date there so I know when the total was updated.


Hope this helps....like I said there's probably a better way, but this works....

J. Jones
jjones@cybrtyme.com