from Form module, refer to field in source query

[GKChesterton]

I want to modify my report using VBA, based on a value found in the underlying query. The value is not one I wish to appear on the report. How do I get to the value?

I know a few ways to do this:
(a) use the field in a control on the report and make it invisible;
(b) make a DAO recordset clone and get the value from there; or
(c) use a domain aggregate query to get the value.

Ain't there a better way? It's the same for forms. I've always settled for solution (a), but am believing for something more elegant.

(I hope that's enough information. I bet the solution is very simple and obvious. And I bet if I just new the right search term, the Tek-Tips answer would have appeared in one minute.)

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PaulBricker]

If you are using a form of some kind to open the report, you could pass the value to a global variable and have it readable from the report.
The global would look like this

Public gdblRunSum As Double

Public Function RunSum() As Double
   RunSum = gdblRunSum
End Function

Then the control source for a textbox in the report would be set to =RunSum()

Paul

 

[GKChesterton]

Paul -- Yes. There are many ways to secure a value, and I agree yours is good. It just seems to me to me that if it is included on the query underlying the report or form, surely it can be accessed through a very immediate method. Me.Recordset("myvalue") or something like that.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

I fixed my problem. I don't think I understood or expressed it well, so my apology for any waste of the readers' time. Half of the issue was getting this line correct:
Me!boxDescription.ControlSource = "DescrProd"
... which I realize is painfully simple. The other half was thinking through my query better. Thanks for your consideration.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​