Error handling help required 2

[bikerted]

I use the following code the following code to open a report from the On Click event of a button:

Private Sub Label180_Click()
On Error GoTo Err_GiftAidGeneralPurpose_Click
Dim stDocName As String
stDocName = "GiftAidGeneralPurpose"
DoCmd.OpenReport stDocName, acPreview
Exit_GiftAidGeneralPurpose_Click:
Exit Sub
Err_GiftAidGeneralPurpose_Click:
MsgBox Err.Description
Resume Exit_GiftAidGeneralPurpose_Click
End Sub

This procedure opens the "GiftAidGeneralPurpose" report fine - whatever the data generated from the underlying query - except that, when the basis for the report (GiftAid being "Yes") has no "Yes's"

The field that sums (by an IIf statement) the "Yes's" reads: #Error. This also appears in the sum for receipts affected by this Gift Aid. What I was hoping to do was to use a message box to advise the user that there were no receipts with Gift Aid and NOT open the report. I have started reading about the various error handling procedures, but I am none-the-wiser!

Any assistance would be greatly appreciated.

Ted.

 

[PHV]

You may consider the DCount or DLookUp functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

PH,

I'm sorry, but I don't see how and where these might be used.

Ted.

 

[PHV]

My suggestion was to see if the underlaying query will return GiftAid being "Yes" before launching (or not) the report.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

Thanks PH,

I'll have to learn about using this in the query, so I can't comment right now on its success.

Ted

 

[PHV]

I'll have to learn about using this in the query
Not in the query but the VBA code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JoeAtWork]

I would use the NoData event of the report itself, like so:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Sorry, there is no data for this report."
    Cancel = True
End Sub

In the code that opens the report, you would need to check for error 2501, which is that "opening the report was cancelled", some like the following:

Private Sub cmdReport_Click()
    On Error GoTo ErrHandler
    
    DoCmd.OpenReport "TestNoData", acViewPreview, , "NetWorth = 89"
    
    Exit Sub
    
ErrHandler:
    'We can ignore the "Opening report was cancelled" error.  Any other
    'errors should be reported to the user.
    If Err.Number <> 2501 Then
        MsgBox "Sorry, error # " & Err.Number & " occurred: " & Err.Description
    End If
    
End Sub

 

[bikerted]

Thank you Joe,

Most helpful. I'm not sure about these procedures, but the NoData event code worked a treat. Not quite sure about the need to check for error 2501, too. I take it "TestNoData" is where my report name goes and the "NetWorth" = 89" is equivalent to my "GiftAid = "Yes"" (which sets off the query underlying the report)?? Also, is:

If Err.Number <> 2501 Then
MsgBox "Sorry, error # " & Err.Number & " occurred: " & Err.Description
End If
to be taken as verbatim?

Sorry, if I appear thick here, but I just can't get the hang of the syntax yet - it's quite frustrating!

Ted.

 

[JoeAtWork]

Yes, you understood everything correctly. "TestNoData" was the name of my report, "NetWorth = 89" was a filter I set so the report would return no data (because all my records have NetWorth higher than 89).

The check for error # 2501 because Access treats the cancelling of a report as an error (somewhat to my surprise). You can use my error handler code if it seems appropriate or modify to behave how you think it should.

 

[bikerted]

You're a star Joe (and I've awarded a star by way of acknowledgement), and thanks for the explanation. Perhaps I'm beginning to grasp matters at last? Thank you.

Ted.

 

[bikerted]

Joe,

This is the code for opening the report:

Private Sub Label180_Click()
On Error GoTo ErrHandler
Dim stDocName As String
stDocName = "GiftAidGeneralPurpose"
DoCmd.OpenReport stDocName, acViewPreview, , [Gift Aid] = "Yes"
Exit Sub
ErrHandler:
'We can ignore the "Opening report was cancelled" error. Any other
'errors should be reported to the user.
If Err.Number <> 2501 Then
MsgBox "Sorry, error # " & Err.Number & " occurred: " & Err.Description
End If
End Sub

(together with this in the No Data event:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no receipts with Gift Aid"
Cancel = True
End Sub

which seems in itself to be working ok)

The error message on clicking the button reads this though:

I'm obviously not quite there yet! Any assist would be appreciated. Thanks for your patience.

Ted.

 

[PHV]

Replace this:
, [Gift Aid] = "Yes"
With either this:
, "[Gift Aid] = 'Yes'"
or this:
, "[Gift Aid] = True"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

PH,

The expression, "[Gift Aid] = True", worked (the other one pulled up an error that seemed related to the original error message).

Thanks again for your help.

Ted.