function for a report; KB article 129096

[folkie]

I'm using Access 97 and I'm trying to use code from MS KB 129096. The first function in method 3 of the article has the following:

Function CalcProduct(R As Report)
Dim tmpAmount As Double
tmpAmount = R![UnitPrice] * R![Quantity]
OrderTotal = OrderTotal + tmpAmount
GrandTotal = GrandTotal + tmpAmount
CalcProduct = tmpAmount
End Function

For what I'm doing (starting real slowly trying to adapt it to what I want to do), I do the following:

Function CalcProduct(R As Report)
Dim tmpAmount As Double
tmpAmount = R![FirstOfGRegTO]
OrderTotal = tmpAmount
GrandTotal = GrandTotal + tmpAmount
CalcProduct = tmpAmount
End Function

FirstOfGRegTO is a field in the table that my report (which uses this function) is based on. The field's datatype is Currency. In the detail section of my report I have the following control:

=CalcProduct([Report])

When I try to run the report with the code shown above, I get the following error:

Runtime error 2465. The database can't find the field FirstOfGRegTO

I've changed the code many times - getting rid of R!, specifying the table name, getting rid of bracketts, using a . instead of !, changing Double to Currency, etc. I keep getting error messages basically saying it can't find the field FirstOfGRegTO. It usually has #Error or #Name in the report where FirstOfGRegTO should print.

I'm trying to adapt this code in order to get the grand total at the end of the report for FirstOfGRegTO (and several other similar fields). When all is said and done, I don't want this field to be on the detail lines of the report, but I do want it in the group footer and in the report footer. I was thinking KB 129096 would help me do this.

Do you know where I'm going wrong?

 

[simongallop]

If all you want is a grand total in the footer of your report then why not place a textbox there with the source being =sum([FirstOfGRegTo])?

 

[folkie]

Harry,

The only way I could figure out how to do this is to have [FirstOfGRegTo] on every record of the table from which the report is based. This field comes from another, smaller table where there is an amount for each Sprg. Either I couldn't do DLookup, or if it was possible, I read too many things where people said it was slow. I ended up getting that field on every record on a much bigger table, from which this report is based.

However, that field doesn't really apply to the detail record. It applies to the Sprg field on that detail record. There can be many records with the same Sprg. I have a group footer on the report for the Sprg field. I print [FirstOfGRegTo] in the Sprg group footer and it is correct. There is one dollar amount for each Sprg. But, I'm having a problem getting a grand total for the report. If I try what you suggest (which I have), I get a grand total which includes many dollar amounts for each Sprg. I was hoping/thinking the KB article could lead me to what I want, but I can't seem to figure out the syntax.