Updating VBA calculated Text Box throught Report

wintermute · March 27th, 2008, 05:19 PM

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:

Code:

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 :)

mmcdonal · March 28th, 2008, 06:48 AM

One way to do this is to do the totals within the UserID group. So put that calculation in the Group Header or Group Footer for each user.

Alternatively, you could put the values in either the query behind the report, or calculate it entirely in code, and supply the value to the text boxes from a vba function.

Can you post the calculation?

mmcdonal

Look it up at: http://wrox.books24x7.com

wintermute · March 28th, 2008, 12:00 PM

Thank you for your post, it helped a lot :)

I have used the things you suggested to pass the Text Box value totals from the report groupings to the Bonus calculation in VBA, and then passing the result back to a Result Text Box. For example:

Code:

Public Function getAcc() As Double

    getAcc = Reports![rptAllUsersOverall]![tbTotalAccuracy]

End Function

And then...

Code:

=getBonus( getAcc(),getAvg(),getMulti(), getReportType() )

...in the Unbound Result Text Box. My problem was my unfamiliarity with the 'Reports' keyword

Is there a more effective or elegant solution I should use next time?