Vb. Open report error based on a form that is just opened.

[Eddie Nugroho]

I have a main form, FrmMain. Put a button to open "FrmPrice". Works OK. In FrmPrice I put another button that open a report, ReportOffer, based on FrmPrice. Works OK too.

I tried to put a button in FrmMain to directly open FrmPrice and then open ReportOffer, the code failed in calculated control with #error.

DoCmd.Openform "FrmPrice"
DoCmd.Openreport "ReportOffer"

Tried to put me.Requery. Didn't help.
Please help... Thanks
*Access 2007

[Seth Schrock]

When you say you open ReportOffer based on FrmPrice, what do you mean? Do you have to enter/select a value for a control in FrmPrice that has to do with the calculated field in ReportOffer?

[Eddie Nugroho]

Yes, correct. It works OK if I open the report AFTER the form is fully opened.

[twinnyfo]

Eddie,

I believe the problem comes that you are trying to open the two objects essentially simultaneously. If ReportOffer only runs after FrmPrice is opened, you must actually wait until the form fully loads before the report can use any data on that Form.

Seth, do you know of a way to check the Form's load status programmaticall y? If not, I think one way would be to send a value to the Form and in the OnLoad event of the Form, if that value is True, to open the Report, otherwise, wait for the user to click the button.

This is one of the challenges with using Form values as part of a Record Source for any other object in Access--it is not a "wrong" process, but there are several challenges you must be careful for, and this is one of them.

[Seth Schrock]

My guess is that the value in FrmPrice isn't getting entered/selected and thus it's not available for the report. One option would be to move the control from FrmPrice to FrmMain and that would eliminate the step of opening FrmPrice. Another option would be to open FrmPrice as a Dialog Box which stops code execution in the calling form until it closes, which would allow you to select the value necessary for the calculation of the report. Or you could use code to set the value in FrmPrice before you open the report.

However, I don't believe that the problem is that the report is opening before the form is completely open. I have coded many times where I set a control value immediately after using the DoCmd.OpenForm command. This wouldn't work if the form hadn't finished opening. I think that the control is empty when the report is calling for it, meaning the user hasn't supplied the value.

[Eddie Nugroho]

Yes, the control is empty when the report is opened. Hence the error. Tried the acDialog, but I couldn't get it to work (open the report without closing the form). Thanks anyway

[Eddie Nugroho]

Thank you all... Solved the issue. In the main form I write DoCmd.OpenForm "FrmPrice". Below this line I wait for the control in the opened FrmPrice to get its value:

While Forms!FrmPrice!ctrlXxx = 0
   DoEvents
Wend

Then the open report command line. It works.
Thanks again...

I had used the DoEvents before, but without waiting for the control to get its value.

[twinnyfo]

Eddie,

Glad you found a solution that worked.!

[Eddie Nugroho]

Thanks for your attention. Appreciate it.