The following is an example of a slightly different approach. It again uses
the report's open event procedure to open a dialogue form with a cboName
combo box on it, but Cancels the opening of the report if the form is not
open, in which case it opens the form, passing the report's name to it as its
OpenArgs property. the report is then opened from a button on the form. The
report's underlying query includes the following parameter:
Forms!frmNameDlg!cboName
The code for the report's Open event procedure is:
Private Sub Report_Open(Cancel As Integer)
Const FORMNOTOPEN = 2450
Dim frm As Form
On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If
End Sub
The combo box on the form has a RowSource which lists the names in the
underlying table on which the report's query is based. The button on the
form to open the report has the following as its Click event procedure:
Private Sub cmdOpenReport_Click()
Const REPORTCANCELLED = 2501
On Error Resume Next
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select
End Sub
The REPORTCANCELLED error is handled here to allow the report's NoData event
procedure to cancel the opening of the report if it contains no data with:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "Name not found.", vbInformation, "Report Cancelled"
Cancel = True
End Sub
Note that if you are opening the report with code from elsewhere in the
database you must handle the error there too as the Open event procedure
cancels the opening of the report if the form is not open, so would raise the
same error in the calling procedure as the NoData event procedure. The code
to open the report from elsewhere would thus be like this:
Const REPORTCANCELLED = 2501
On Error Resume Next
DoCmd.OpenReport "ReportNameGoesHere", acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select
Ken Sheridan
Stafford, England