Declaring a variable used in multiple reports

[DBrodin]

I have a number that is used in several math expressions in multiple reports/sub-reports.

IE: the 290 in the expression "=Round(([Total Of Present]/290)*100)".

I would like replace it with a variable from the output of "SELECT Sum(BPOU.Alloca ted) AS SumOfAllocated FROM BPOU;".

I am not sure how to define it, and more importantly WHERE to define it that it can be used for all the reports.

[jforbes]

There are a few different ways to go about this. My favorite is to create a Function to return the value and then use the Function as part of the ControlSource.

If you don't have a module to place a new function into, {F11} to open the VB Editor, then you can click Insert|Module from the Menu.
Once you have a Module to work with, you can create a function something like this:

Public Function getAllocated() As Variant
    getAllocated = DSum("Allocated", "BPOU")
End Function

Then you can rewrite your expression to something like this:

=Round(([Total Of Present]/getAllocated())*100)

This isn't the most efficient way to go about this, but it's pretty flexible. If you are using the function a lot, like in a query that is returning 100,000 records, you might not want to use this approach. But if it will only be called once, twice or ten times for a Report, this method is plenty fast.

[zmbd]

Two other options:

The tempvars collection - which I am finding the be very useful - however, that just holds a stored string/value.

OR

as you mention the use of an aggregate query, bind it to a form.

+ You do not have to have this form visible.

+ You do not have to place any controls on the form. You can either refer to the field or the control via:
forms.formnameh ere![FieldOrCtrlName Here]

+ You can also add controls and set the value of the controls as one would with any unbound calculated control. Unfortunately, you could not use the constants within the form module as they are forced to be "Private." Be cautious here, text controls may toss your values at you as strings when referring to them so you might review the conversion functions:
Type Conversion Functions (MS) incase you see the Nasty "Type mismatch (Error 13)"

+ You would have to re-query the form anytime you update the underlying recordset. However, that should be minor, in the on_open or on_load event of your forms or reports you can issue the forms.formnameh ere.requery and be sure, of course, that the form is open using the currentproject. allforms("FormN ameHere").isloa ded and open the form if not using something like DoCmd.OpenForm FormName:="frmN ame", windowmode:=acH idden
(notice the use of the property names instead of a ton of commas... :) )

+ Finally, best practice would be to include in your main switchboard some VBA or Macro action to close the form before closing down the database.

>> Of note, one could run the query in code and then store the value of that result in the TempVars collection, updating the variable as needed.

-z

[NeoPa]

I won't argue against the technique, but be aware that function calls in your queries (SQL) will fail if you are in break mode in your VBA code.

I suspect this only applies to calls to functions within your project, as opposed to those from the any of the other libraries.

[zmbd]

To build on Neopa, not only may the function call fail in break mode, the entire thing will fail if the Admins or Users force the database in to an untrusted and disabled code state.