By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

Declaring a variable used in multiple reports

P: 1
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.Allocated) 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.
May 4 '16 #1
Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function getAllocated() As Variant
  2.     getAllocated = DSum("Allocated", "BPOU")
  3. End Function
Then you can rewrite your expression to something like this:
Expand|Select|Wrap|Line Numbers
  1. =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.
May 4 '16 #2

zmbd
Expert Mod 5K+
P: 5,397
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.formnamehere![FieldOrCtrlNameHere]

+ 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.formnamehere.requery and be sure, of course, that the form is open using the currentproject.allforms("FormNameHere").isloaded and open the form if not using something like DoCmd.OpenForm FormName:="frmName", windowmode:=acHidden
(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
May 5 '16 #3

NeoPa
Expert Mod 15k+
P: 31,434
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.
May 8 '16 #4

zmbd
Expert Mod 5K+
P: 5,397
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.
May 8 '16 #5

Post your reply

Sign in to post your reply or Sign up for a free account.