Access Report Running Queries Before Executing Report Open Event

2
votes

I have a query called AdmQuery, a form called Admform and report called AdmReport. In the Open_Event for Admreport I have called the AdmForm to input start date and end date to two unbound boxes and use that to execute the query and show its results on the report.

This works however the problem is that when I run the report, the query on the report and the open event runs together because of which the form itself opens along with the report (with initially no data on it). Thus i have to close the report , input the dates on the form and then preview the report through another button on the form and everything is fine.

Is there any way to delay the access report running the underlying query only after executing the Report_Open_Event ?

I use access 2003.

1
ms-accessvbams-access-2003

1 Answers

4
votes

Open AdmForm in dialog mode to force AdmReport's Report_Open procedure to wait while the user enters date values in Admform.

My AdmForm includes a command button named cmdOK, and it's click event reopens the form in hidden mode, which allows AdmReport's Report_Open procedure to continue. Although AdmForm is now hidden, its text boxes and the values they contain will be available to AdmQuery (the record source for AdmReport).

Here is the code for the command button's click event.

Private Sub cmdOK_Click()
    DoCmd.OpenForm Me.Name, acNormal, , , , acHidden
End Sub

Here is the code behind my version of AdmReport. I tested it with Access 2007, but believe it should work with Access 2003, too.

Option Compare Database
Option Explicit

Const mcstrForm As String = "AdmForm"

Private Sub Report_Close()
    DoCmd.Close acForm, mcstrForm
End Sub

Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm mcstrForm, acNormal, , , , acDialog
End Sub

Notice I included a Report_Close() procedure for AdmReport. The purpose is simply to close the (now hidden) AdmForm.