Me Equivalent for Reports? 1

[mkasson]

Each record in my database has about 40 fields that are used in several calculations.

I created a form that uses a bunch of functions that do the calculations that I need. Because there are so many fields that the functions make reference to, I don't pass the values to the functions as arguments; the functions make reference to the fields on Me (e.g. [S1Amt], [S1Date],[S2Amt]).

Now I'm trying to generate reports and I need to use these or similar functions. I've tried putting functions in the code for the report. When I tried testing this by putting a test function (Add2) in a report ("=Add2()"), it prompted me with a box asking for a value for Add2. Add2 was defined as a public function in the report's code module.

I tried putting Add2 in the Project's Module1. Now the report can find the function, but it won't let me just refer to the current record's fields (e.g. [S1Amt]).

How can I have functions that use the current record's field values without having them passed as arguments?

And can't a report have a code module for local functions?

Ideally there would be a way to create these functions once rather than having to create almost identical functions for the form and the report.

Thanks much.
-MSK

- MSK

 

[SteveR77]

MSK,

The Me property works for Reports as well. If the names of your fields are the same then you should get the same results for the fields on your report as you do on your form.


HTH,

Steve

 

[mkasson]

I don't get it then. Where should the functions be?

I've made sure the report has HasModule = Yes, yet the report prompts me for Parameter Value for Add2 (which is located in the Report's code module). It then puts #Name? in the report. Add2 has no parameters:

Public Function Add2() As Double
Add2 = Nz([S1Amt]) + Nz([S2Amt])
End Function

I also set up Add3 in the Module1 code module. When the report calls Add3 it tells me I have a Type Mismatch error. Shouldn't be a type mismatch error; I'm guessing that the fields are out of scope here. I get #Error in the report where Add3 is included. Same code:

Public Function Add3() As Double
Add3 = Nz([S1Amt]) + Nz([S2Amt])
End Function

Really appreciate the help.

- MSK

 

[mkasson]

Sorry. Embarrassed to say that the Add2 code was IN THE WRONG REPORT'S MODULE!!

OK, so Add2 gives the right answer (of course without prompting for a parameter), but...

Can I do this without having two sets of functions (one for the form and another for the report)?

Thanks.

- MSK