Cancel report causes error

[cranebill]

I have a report that when opened goes striaght to the printer. When it is opened and there is no data then I get the error:

Run Time Error '2501':
The OpenReport action was cancelled.

However if I run the report in preview mode the code below works fine and a message box pops up like it should.

The question is how can I get the msgbox to pop up in acNormal like is does in Acpreview?

The following code is the only code in the report.

Option Compare Database
Private Sub Report_Open(Cancel As Integer)
Dim team_lead As String
Dim R_source As String
team_lead = DLookup("[team_lead_flag]", "qa_employee_list", "[userid] = '" & WinUser & "'")
If team_lead = "Y" Then
R_source = "SQL STATEMENT"
Else: R_source = "Another SQL Statement"
End If
Me.RecordSource = R_source
End Sub

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1
End Sub

 

[PHV]

Simply catch the error in the procedure calling the DoCmd.OpenReport method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cranebill]

So ok in the onclick event of the form that calls for this to open, I would basically have to run the same sqls to check for records...

I could write a procedure in oracle that would accomplish this however not sure of the syntax in access.

 

[PHV]

I would basically have to run the same sqls to check for records
Not necessarly as this will double the query time.
In your error handler simply does nothing if error 2501 is raised.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886