Passing Parameters from Form to Report

[Guest]

I created a report which has in its open event a call to a form that
asks for date range. The form has date from and to. It also has OK &
Cancel buttons.
Once I hit OK (with valid dates) I change the report recordsource and
add the date range criteria. The report then displays fine.. 2
Questions...

1 ) If I hit cancel on the form, the report still opens.. I tried
adding code under the cancel command and say DoCmd.Close report... but
it did not allow me doing so

2) I want to show on the report the date range that was selected. But
when I refere to the form date from and to, it says it does not exist,
which is correct, since I close the form after I open the report.. Is
there a way I can capture the date from and to for display?

 

[Guest]

Unless you actually cancel the opening of the report in its Open event
procedure it will continue to open. Here's the code from a simple demo
report's module which works in a similar way to yours. As well as opening
the form it puts the name entered in the form in a text box in the report's
header by assigning it to a module level variable before closing the form.
In this example that could be done with a bound control of course, but its
here just to illustrate the method used:

'''module starts''''
Option Compare Database
Option Explicit

Dim strName As String

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)

' assign value of variable to unbound text box
Me.txtName = strName

End Sub

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form
Dim strSQL As String

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
DoCmd.OpenForm "frmNameDlg"
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
' assign SQL statement to report's RecordSource property
strSQL = "SELECT AddressID, FirstName, LastName " & _
"FROM Addresses WHERE LastName= """ & frm.txtLastName & """"
Me.RecordSource = strSQL
' assign value from form to variable box and close form
strName = frm.txtLastName
DoCmd.Close acForm, frm.Name
End If
End If

End Sub
''''module ends''''

The OK button on the dialogue form opens the report with the OpenReport
method of the DoCmd object if a name has been entered in the form, the Cancel
button simply closes the form. As the report is being cancelled if the form
is not open you would need to handle the error which this raises if you open
the report from anywhere else but the dialogue from in code, e.g.

Const REPORTCANCELLED = 2501

On Error Resume Next
DoCmd.OpenReport "YourReport", acViewPreview
If Err <> 0 Then
If Err = REPORTCANCELLED Then
' anticipated error so do nothing
Else
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error Opening Report"
End If
End If

Ken Sheridan
Stafford, England

 

[Guest]

Excellent! I changed the code around and that works perfect!
Thanks!