need code to cancel report opening from its onload event

[Juddy58]

Hello, i have a report with dao code in its onload event.
it contains the following if statement for its associated recordset:
If rs.bof and rs.eof then
cancel opening report.
else
do something else
end if

the problem i have is with the code to cancel the opening of the report. it goes into the if statment ok.
i thought i could just use: Exit Sub
but then i realised this doesn't stop the report from opening. i have also tried: rtpReportName.close with no luck.
I know there is a simple statement i can use, but stupid me cant seem to figure it out.
Any help would be greatly appreciated thanks.

 

[tEkHEd]

try

doCmd.Close (rptName)

 

[Juddy58]

Thanks for the reply tekhed
I have tried using that command but get an error message.
:This action requires an object name arguement!
I think it has something to do with the fact that if the condition is met the report normally falls over because it is missing some required records. Thats why i need to stop it from opening from its own onopen event.

 

[danvlas]

You have two options:

1: use the NoData event:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

2: use the Open event:

Private Sub Report_Open(Cancel As Integer)
'Your code here
If rs.bof and rs.eof then
Cancel = True opening report.
else
Do something else
End if
End Sub

In either way, you will have to track error 2501 (cancel) in the object that triggered opening the report.

Good luck,

Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Juddy58]

thanks for the help danvlas, i will give it a try!

 

[tEkHEd]

Danvlas..

Good solution.. I have a quick Q for you thou..

I want to know how to surpress the error "2501" (OpenReport Command was cancelled)

so that the closure is transparent to the user..

can I code this with "docmd.setwarnings false" but this doesn't seem to work...

 

[danvlas]

On Error Resume Next
DoCmd.OpenReport ...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tEkHEd]

hehe.. thanks.. but where do I have to code this?

in the report's On no data event?

Private Sub Report_NoData(Cancel As Integer)
On Error Resume Next
Cancel = True
MsgBox "There is no information for your selection." _
    & " Please change your search criteria and try again."

End Sub

This doesn't surpress the error..

 

[tEkHEd]

addition..

the report is being called via a case statement and I cannot put your code in the middle of the case statement as it gives me a further error..

 

[danvlas]

Well, the error 2501 does not occur in the report. It occurs in the object that called the report.

For instance, you attempt to open the report from a button on a form, right?
You click the button and call the DoCmd object:
DoCmd.OpenReport blah blah blah

The report has no data => Cancel is set to True and the error is thrown in the DoCmd.OpenReport line in the form's module.

Placing the error trapping in the report module has no effect whatsoever in this case

As an example:

Private Sub CommandButton_Click()
'You may have an error trapping routine
'called as below
On Error GoTo ErrHandler

Select Case Weather

Case Rainy
DoCmd.Quit

Case Sunny
On Error Resume Next
DoCmd.OpenReport YourReport
Err.Clear
On Error GoTo ErrHandler

Case Else
MsgBox "Hello World!"
End Select

Exit Sub

ErrHandler:
Select Case Err.Number
Case 3022 'Duplicate
MsgBox "Duplicate"


'You could include the 2501 error here:
'uncomment the green lines and comment the blue lines
'Case 2501 'Cancel
'Resume Next

Case Else
MsgBox err.Number & " " & err.Description
Exit Sub
End Sub


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[tEkHEd]

mmm maybe this isn't working for me because I have an IF statement built within the Case statement to check if the report is open or not..

so my code for this section is..

Case 2 ' Delta Reporting - Resizes form to allow user to select the required Business Unit
            On Error Resume Next
            ' Sets LinkCriteria to the same report type as selected via cboRptType dropdown
            stLinkCriteria = "[ReportName] = 'rptName'"
            ' finds first entry matching the LinkCriteria
            rs.FindFirst stLinkCriteria
            ' Sets up the SQL string
            stSQL = rs!SQL & Me.cboSBU & "*'"
            
            ' Amends query with SQL string
            For Each qdf In dbs.QueryDefs
                If qdf.Name = rs!QueryName Then
                    qdf.SQL = stSQL
                End If
            Next qdf
            
            ' code to check if the report is open, and closes it and re-opens
            ' it if required.
            If SysCmd(acSysCmdGetObjectState, acReport, "rptName") Then
                MsgBox "Report is currently open. " _
                    & "Closing report and refreshing queries."
                DoCmd.Close acReport, "rptName"
                Err.Clear
                On Error GoTo Err_Handler
            Else
                DoCmd.OpenReport "rptName", acViewPreview
                Err.Clear
                On Error GoTo Err_Handler
            End If
case 3
blah

    End Select
    
Err_Handler:

Select Case Err.Number
    Case 3022 'Duplicate
        MsgBox "Duplicate"
    Case Else
        MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Select

might be my fuzzy logic here.. i.e. can you check for no data after closing the same rpt kind of thing...

 

[danvlas]

I'm not sure what you want to accomplish, but your code should be structured in the following way:

Sub WhateverProcedure()

Dim WhateverVariables

'Any error should direct the code to ErrHandler

On Error GoTo ErrHandler


'========================================
'...the part before the Select Case here
'========================================

Select Case YourCases
Case 1
'code for value 1

Case 2
'==================================
'Your code until Next qdf
'==================================

'This will throw no error even if report is NOT open
'And i don't see the reason to show a MsgBox here...
'You can drastically shorten the code
'and won't annoy user with useless messages
DoCmd.Close acReport, "rptName"

'Try to open the report
'If cancelled, it will jump to ErrHandler
DoCmd.OpenReport "rptName", acViewPreview

Case 3
'blah
End Select

Exit Sub 'so that ErrHandler is not reached without error

ErrHandler:
Select Case Err.Number
Case 2501 'Cancel
Resume Next

Case Else
MsgBox Err.Number & " " & Err.Description
Exit Sub 'be careful, in this case you may leave some object variables in place
End Select
End Sub

Good luck


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html