Change a report's running sum count to data

[rccline]

I have a report based upon a query that uses =1 to display a running sum.

I would like to save the number as a field associated with the record it is counting. If I were to reproduce the query, I need to make sure that the displayed records correspond with the original query.

How do I go about this?
Thanks.

Robert

 

[JPeters]

I would use recordsets. I'm assuming that the report is built off of a query and you only want to effect records within that query. So do this in the Report's OnClose event or something like that.

Dim db as Database
Dim rs as Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset("qryReportQuery", dbOpenDynaset)

Do While Not RS.EOF
rs.Edit
rs!NumberField = MyNum
rs.Update
rs.MoveNext
Loop

Set db = Nothing
Set rs = Nothing

End Sub

NumberField is the field you're storing the data in. MyNum is the value of the number that the report uses to populate. Ie: =1. I don't know where you call this numbre from, but that will have to be added/edited/manipulated to be set correctly.

If these records are effected by the report again, you will ofcourse overwrite the NumberField. You may consider also running an append query that stores this data in a table. Bear in mind this table will grow - since we always add new records and never clear out for storage purposes. Maybe time stamp them also and have a delete query run through and delete records after their date reaches a certain point in time like 3 months have passed or a year... Just ideas.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------