How to get the value from a report and show it in a form without opening the report

[lbcarvalho]

Hi,

I have a DB with a form as the Main Menu and a value calculated into a text box in a report.

I would like to see this value in the Main Menu form.

I have already managed to do that, but the value shows only when I open the report. If you don't open the report, the form box value shows as #Error.

I used this formula to get the value: =[Reports]![ITABalance]![Text39].

How do I get the value to appear in the Main Menu form without opening the report?

Thanks in advance for any hint to solve this problem.

lbcarvalho

[orange]

Can you do the computation/calculation using vba? If so, you do that when the form is open and display the value on the form.
Reports are typically meant for output; printing; display -- not usually as an input source for populating a control on a form.

[lbcarvalho]

This is what I tried:

Private Sub Form_Open(Cancel As Integer)


Me.txtBAL_IN_MAIN_MENU = [Reports]![ITA_BALANCE]![Text39]


End Sub

But all I get is an error message: Run-Time error 2465. Can't find the field '1' referred to in your expression

[Gicu]

I think what Orange meant was to redo the calculation that you do in the report in the main form, realizing that it might not be the exact same formula as you might need to use domain aggregate functions like dsum, dcount and your reports record source. The short answer is no, cannot reference a control on a report that is not open.

Cheers,

[ArviLaanemets]

It is really simple! As report results exists only when report is opened, then they don't exist when report is not opened :-)

Of course you can make same calculations in form and got the wanted result.

[Bullschmidt]

Or I guess you COULD actually have code behind the form's OnActivate or OnOpen that opens the report and then run code like this to set the focus back to the form:
Me.SetFocus