Updating Vba Calculated Text Box Throughout Report

[wintermute]

Hello, first post so please advise if I can structure my questions better.

Using Access 2007:

I have a database containing three tables and a linked excel spreadsheet. I have a query combining all of the above. The query datasheet (or dynaset) contains records for a number of Users (User_ID) specifying integer values (NoOfRecords) for certain dates (Date).

I have designed a report that would present the integer values for every existing date for each user. The report is grouped on User_ID and sorted by Date, with NoOfRecords included in the detail. It looks like this:

User_ID
    Date         NoOfRecords
    01/01/2008   6
    05/01/2008   34
    ...          ...

Each page of the report contains the above data for a unique User. NoOfRecords is totalled at the bottom of each page and this total combined with another value also present on the report (Quality) to calculate a final value (Bonus). This Bonus calculation requires VBA with a few switch statements and the Bonus is displayed at the bottom of the report in an Unbound Text Box.

Now the Dates and NoOfRecords change on each page of the report according to the User_ID, resulting in different totals. This is correct. However the VBA calculated total stays the same throught the report, always based on the first User_ID in the query and not changing with the rest of the data. Can someone tell me how can I make it change please?

Thanks

 

[edtab]

It appears you are missing a "User ID" footer in your report design where you could calculate the result for each unique id using an unbound text box.

In design view, right click anywhere on your report and add a UserId footer, then place your textbox and required calculation in it.