Is it possible to reference the value of a control on a report that isnt open?

I want to insert the value of a textbox into an outlook message.

When the report is not open, I get the error "the report name is misspelled or isnt open"
When the report is open, I get the error "the expression has no value"

I am refering to the value with Reports!myreport!mycontrol.Value

I have checked the name of the control and it does have a value.

Is there an error in my reference?

Hi

Well, you definitely can't refer to a control on the report unless the report is open. You should be able to do it as long as it's open though. Where about's are you trying to use your expression - eg in the Control Source of a textbox on a form, in VBA code etc

If it were directly in the Control Source of a textbox then you'd need the equals sign as well

=[Reports]![myreport]![mycontrol]

Depends where abouts on the report the control you're wanting to refer to is as well. For example, if the control is a textbox in the Detail section of the report then I'm guessing that there could be many different 'values' dependant on the number of records in the reports Record Source. I'd guess in that scenario that you'd get the value of the last record (not tested that though)

Could you not use DLookup() or some other method to get the value you need. Wouldn't matter if the report was open or not then. Just a thought

It is likely that the Value of the TextBox (on the Report) comes from somewhere else in your database so just try to get the Value from the Source rather than via the Report.

Edit: Just noted that Alan also mentioned DLookUp() which would be one way to get the Value from somewhere else in the database.

Thanks for the responses.

The expression is vba behind a command button on the form.

The value of the textbox I want to insert is a rather complex and dynamic calculation. I have considered doing the calculation within the vba procedure but it is really tedious.

I also do not want to do the calculation on the form because it would make it look crowded. Perhaps I could insert it as a control but toggle it to be invisible?

Hi Andy

Hard to say without knowing more about whatever it is you're calculating, but you could possibly do the calculation you want in a Public Function, where the value you want will again be available whenever/wherever you need it

The report is based on input from the form. It doesnt really have a record source. Here is a basic example of my project:

Given I have data on 3 grocery stores price listings. I ask the user to input their shopping list, push a button and they get a report for each grocery store. Included at the end of each report is a total estimated cost.

I want to insert the total estimated cost into an outlook message. I have the outlook automation working but I can't get the value property of the textbox control to print into the message. It keeps saying the object has no value even if the report is open.

Hi Andy

Unfortunately your last post has made things more confusing as to what's happening

It will all make perfect sense to you as it's your project and you know exactly what happens re the flow of things, but for others (well, for others certaily read me!!) who have no knowledge of your db we don't have that luxury.

I'm not sure now with your latest post whether the report get's it's various values by referencing controls on the form (as you say it doesn't have a recordsource) and then the calculations are perfomed by the report, or there's something else happening.

I'd still suggest that maybe you could perform whatever calculation(s) you need via the Function route, but again without a lot more detailed info from you it's not really possible to hazard a guess.

Perhaps you could post in more detail exactly what happens and where values are coming from/calculations being perfomed ?