Update table using report group section print event

[lizzbizz]

I'm trying to update a table (DEPARTMENT) with data calculated in an unbound control in a report grouping/summary section (TOTAL USABLE SQUARE FEET). I've read numerous topics which hint at the following solution. The code would be placed in the group section print event:
Dim qdf As QueryDef, strSQL As String
strSQL = "UPDATE Departments SET Departments.TotalUSF = ????"
strSQL = "WHERE (((Departments.DeptID)=???));"
Set qdf = dbs.CreateQueryDef("DeptTotalsUpdate", strSQL)
Docmd.???

First of all, am I on the right track? If so, I'm not sure what code to put in lieu of the question marks ??????. If I'm not on the right track, please point me in the right direction. Thanx!

 

[RickSpr]

I've never done this, but it seems as if it should work.

Assuming your report has fields named [DeptID] and [TotalUsableSF], your code would look like this:

    Dim strSQL As String
    strSQL = "UPDATE Departments SET Departments.TotalUSF = " & Me![TotalUsableSF]
    strSQL = strSQL & " WHERE Departments.DeptID='" & Me![DeptID] & "';"
    CurrentDb.Execute strSQL, dbFailOnError

(Note: If DeptID is a numeric value, delete the single quotes in the third line.)
You could also do it with the QueryDef, as your original code was doing, but it's easier just to execute the SQL on the database.

But now for the follow-up. Why do this from within the report? Is it just so you can get the total? You could do that by making a copy of the report's record source query, modifying it to a Totals query with grouping by department, and then making an Update query on the join of the first query and the Departments table. You could then just run the update query instead of producing the report.

Maybe your report method would be all right if you only want the update to occur when the report is run. But you could also run the update query after the report was successfully generated. What concerns me is that, if you open the report in print preview, and close it before paging down to the end, some of your departments will be updated but not all of them. You may not even want any updating to occur when you're just previewing. Rick Sprague