Simple Close Report on OnNoData Not Working(?) 1

[marcus101]

Hello:

I'm really scratching my head on this one.

I've been rewriting some code for my database but something that seemed to work fine before now apparently doesn't, or at least, not as I'd expect.

Here's what I have:

I have a form that populates certain fields in a query that is used as the source for the report.

My form:

1. Grabs the entered values form the form
2. Selects a query appropriate for those values
3. Opens the report in design mode to set the Report's control source for that query
4. Opens the report in acPrintPreview.

When there is data for the report it Previews fine.

Now here's where I'm struggling:

I have a simple piece of code on OnNoData:

varNoData = 1

That's it.

I use this variable as part of the Report's OnActivate Event to test for whether or not there are no values in the report when it first opens..

(cribbing from OnActivate code, this is at the top of the code before anything else is done..)

If varNoData = 1 Then

MsgBox "No items in the report."
DoCmd.Close acReport, "ReportName"

Else

' do nothing..

End If

My problem is varNoData fires and gets set to 1 when there are no report values, so that works, but it does not get passed to OnActivate, so I get a 2501 or other error!

In the past, I thought this worked! I can't be sure, which is what is so frustrating - am I missing something?

I've tried removing this approach and code completely and tried the FAQ 703-1594 that deals exclusively with this issue - all I want is to print out a Message Box and then close the report, is this so hard?

When I use the FAQ code, I see the "No Data" message, so that works, but despite including the error traps exactly as the code instructs, I STILL get Error 2501 after the No Data Message appears!

errr...HELP?

Thanks,


marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[marcus101]

Hello:

An update:

The error trap now seems to work, and I get the Message Box informing there is no data to populate the report..

But now the display just freezes at the form and doesn't let me use it - I have to totally exit Access because of this crash.

Thanks in advance for any ideas on this one.

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[genomon]

In the on no data event, try simply setting Cancel = True.

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[marcus101]

Hi genomon:

Sorry to report, but I've already tried that - it's covered in the FAQ I mentioned.

I am still getting hangups and locks.

Here is what I have right now:

OnNoData:

On Error GoTo ErrorHandler

MsgBox "No Records Available To Print For This Report.", vbOKOnly, "NO RECORDS AVAILABLE FOR THIS REPORT"
    
Cancel = True
    
ExitHandler:
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "No Data"
    Resume ExitHandler

End Sub

OnActivate:

I have code here that processes the report. This never caused problems because my simple varNoData used to work.

I'm not quite sure why it doesn't now but I have my suspicions.

I DO have an ErrorTrap for this, though, and I'm wondering if maybe I should remove it from this event...

---

But I've just realized something-I've changed my controls on the report itself slightly, and the OnActivate code cycles through these controls, looking for some controls that have since been relabeled and now no longer exist.

And I think THAT is the problem - I rewrote this code to work for an OLDER version of the database, but failed to take into account the changes.

The way I was originally planning to check this was to run the query populating the report - but having already checked this, the query is FINE, it's the REPORT CONTROLS and CHECKS in OnActivate that is no doubt causing the issue.

Wish Access had a better error for this. I'd probably see it if I removed my trap for Error 94, checks for nulls, in my Error trapping code.

And also, even if this IS my problem, I still don't quite get why Cancelling isn't working as expected.

I'll keep beavering away at it and will report back what I find.

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[dhookom]

You must trap for error 2501 in the code that calls the OpenReport method

On Error GoTo errHandler
DoCmd.OpenReport,"rptYourName", acPreview
ExitHere:
Exit Sub

errHandler:
  Select Case err
     Case 2501
        'ignore
     Case Else
        msgbox "Error Number: " & Err.Number & _
         vbCrLf & "Description: " & err.Description
  End Select
  Resume ExitHere
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]