Using global variables in report calculation.

[kstevens]

I have a global variable assigned in a module.

Public MyVar as Integer

Right before I open a report, I have the variable set.

MyVar= InputBox("Please enter the markup percentage for oursource work.")
If IsNumeric(MyVar) Then
    If MyVar>= 0 And MyVar< 300 Then
        MsgBox ("Outsourced Markup is stored at " & MyVar& "%")
    Else
        MsgBox ("Please try again.")
        Exit Sub
    End If
Else
    MsgBox ("Please try again.")
    Exit Sub
End If
 
DoCmd.OpenReport "rptWorkScope", acViewPreview

In the report, i am using the value of the outsourced markup in a calculation in the control source of a reported feild (all outsourced markups are the same).

=cost1 * MyVar

This doesnt work. When the report opens it states "Enter Parameter Value"....MyVar

Is there a way around this. I tried putting a new field on the form (invisible) that was unbound, and in the On_Open setting

NewField = MyVar

and then in the control source of the calculation using

=cost1 * NewField

but that didnt work either....seems that the "NewField" isnt available yet in the on open event....
I also tried setting the parameter in the On Load event, but still no sucess. Is there a way to refer to the global variable in the calculation?

[ADezii]

Try performing the Calculation, then assigning it to a Field on the Report in the Activate() Event, as in:

Private Sub Report_Activate()
  Me![txtSomeField] = <Some Value> * MyVar
End Sub

[kstevens]

Thanks, I ended up going a different route. The "Activate" event seems to still fire after the calculations are run, so it still didnt work. In your example, I am still trying to use "TxtSomeField" in another calculation, and it cant find the value of "TxtSomeField" before doing the MyVar calculation.....sorry, that seems clear as mud now. But not to worry, it is running a different way.