Macro problem again

[Arendvan]

Continueing from my previous thread. I created a Macro to stop the print if it has no records The Macro only has one argument cancelEvent in the Properties "On No Data" field of the report. It partially works however I get an error message "The expression you entered has a function name that Microsoft Access can't find" - Any clues as to what is causing this. If I run the report on it's own it works fine however if I run the report as part of a Macro it fails with the above error. Any views?

 

[DannySeager]

You've not posted the code you are using to call the macro.

Why use a macro at all.. you could use some code like

Insert this code behind the button you use to open the report. (cmdButton refers to the name of your button)

Private Sub cmdButton_Click()
On Error GoTo ErrorHandler

    DoCmd.OpenReport "rptName", acViewPreview

ExitHandler:
    Exit Sub

ErrorHandler:

' This traps the Output to Error message
    If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
    
End Sub

Insert this code in the On NoData event procedure of the report:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler

    MsgBox "There are currently no records", vbOKOnly, _
          "Title of Message Box"
    
    Cancel = True
    
ExitHandler:
    Exit Sub

ErrorHandler:
    MsgBox Err.Description
    Resume ExitHandler

End Sub

 

[Arendvan]

Thanks Danny - I used the On NoData event in the report as there are multiple different reports running from the command key - It worked fine - you don't happen to know how to change the length of a report do you - These reports I am doing usually have a first page which has variable data on it and then upto 3 pages of "procedure [ just text]" after it. The problem I have is that I can only create 2 pages of text in a report so I have had to create a second report which prints the last two pages the problem arises is that if you are sending to say 5 differing people report 1 runs and creates pages 1 and 2 for all 5 and then report 2 runs which creates pages 3 & 4 which means I have to manually collate each of the outputs.

You've not posted the code you are using to call the macro.

Why use a macro at all.. you could use some code like

Insert this code behind the button you use to open the report. (cmdButton refers to the name of your button)

Private Sub cmdButton_Click()
On Error GoTo ErrorHandler
 
    DoCmd.OpenReport "rptName", acViewPreview
 
ExitHandler:
    Exit Sub
 
ErrorHandler:
 
' This traps the Output to Error message
    If Err = 2501 Then
    Resume ExitHandler
 
    Else
        MsgBox Err.Description
        Resume ExitHandler
 
        End If
 
End Sub
 
Insert this code in the On NoData event procedure of the report:
 
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler
 
    MsgBox "There are currently no records", vbOKOnly, _
          "Title of Message Box"
 
    Cancel = True
 
ExitHandler:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description
    Resume ExitHandler
 
End Sub

 

[DannySeager]

Microsoft access has a limitation in the length of a report section to 22 inches.