Close report. Easy??

[VanIsleMan]

Well, you'd think this would be easy, but I'm having great difficulty with it. All I want to do is close a report if the recordset is empty. The recordset is built in the report using code. If there are no records in the recordset, control is passed to a MsgBox, like so...

If intPtVisits = 0 Then
MsgBox "blah blah blah", vbInformation, "Information"
GoTo errHandlerClose
Else
blah blah blah
End If

As you can see, at a certain point, I pass control to error handling (errHandlerClose).

errHandlerClose:
rec.Close
DoCmd.CancelEvent
DoCmd.Close

At this point, I just want to close the recordset, cancel the event, and close the report (ie - prevent a blank report from showing on the screen). However, I keep getting error 2585 - this action can't be carried out while processing a form or report event.

What am I doing wrong??

Lex

 

[PWise]

look @ on Report_NoData(Cancel As Integer) event

 

[VanIsleMan]

How would I use the NoData event when the recordset is generated in code, rather than by a report query?
Thnx
Lex


IE:
Dim db As Database
Set db = CurrentDb()
Dim StrSQL As String
StrSQL = ("SELECT TOP 9 tblPatients.PatientID, tblPatients.PatientFirstName, tblPatients.PatientLastName, tblPatients.DateofBirth, tblPatients.Gender,")
StrSQL = StrSQL & ("tblPaedNursVisits.VisitDate, tblPaedNursVisits.HeightInCm, tblPaedNursVisits.Weight, tblPaedNursVisits.HeadCircumferenceInCm, ([VisitDate]-[DateOfBirth])/365 AS VisitAgeYrs ")
StrSQL = StrSQL & ("FROM tblPatients LEFT JOIN tblPaedNursVisits ON tblPatients.PatientID = tblPaedNursVisits.PatientID ")
StrSQL = StrSQL & ("WHERE tblPatients.PatientId = " & [Forms]![frmPaedHeader].[PatientId] & " AND ")
StrSQL = StrSQL & ("([VisitDate]-[DateOfBirth])/365 >= " & " 2 " & " AND ")
StrSQL = StrSQL & ("([VisitDate]-[DateOfBirth])/365 <= " & " 20 " & " AND ")
StrSQL = StrSQL & ("tblPaedNursVisits.VisitDate IS NOT NULL" & " ORDER BY tblPaedNursVisits.VisitDate DESC")

Dim rec As Recordset
Set rec = db.OpenRecordset(StrSQL, dbOpenDynaset)

 

[AtlasAF]

You could try:

If rec.RecordCount = 0 Then
MsgBox "blah blah blah", vbInformation, "Information"
GoTo errHandlerClose
Else
rec.Close
DoCmd.CancelEvent
DoCmd.Close
End If

AtlasAF
USAF

 

[AtlasAF]

Correction:

If rec.RecordCount = 0 Then
MsgBox "blah blah blah", vbInformation, "Information"
rec.Close
DoCmd.CancelEvent
DoCmd.Close
GoTo errHandlerClose
Else
Blah Blah Blah
End If

This is what happens when you do not pay attention before you send...

AtlasAF
USAF

 

[VanIsleMan]

Nice try, AtlasAF. But the code still trips up on DoCmd.Close. I'm afraid I don't understand what "processing" the error message is referring to. Could it be because the DoCmd.Close is enclosed in an If statement and control has not yet been passed to End If?

 

[AtlasAF]

Hmm... Try

DoCmd.Close acReport, "reportname", acSaveNo

AtlasAF
USAF

 

[VanIsleMan]

Okay, I think this is working now. What I've done is this...

In the code behind the button that triggers the report, I've added error handling code to trap error 2501.

Private Sub cmdGrowthChart2to20_Click()
On Error GoTo errHandler
DoCmd.RunCommand acCmdSaveRecord

If Forms!frmPaedHeader.Gender = "M" Then
DoCmd.OpenReport "rptGrowthChartBoys2to20", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom150
Else
DoCmd.OpenReport "rptGrowthChartGirls2to20", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom150
End If

errHandler:
Select Case Err
Case 2501
Resume Next
Case Else
Exit Sub
End Select

End Sub


And in the report itself, I've revamped previously posted code like this...

If intPtVisits = 0 Then
MsgBox "blah blah blah.", vbInformation, "Information"
rec.Close
DoCmd.CancelEvent
GoTo errHandler
Else
rec.MoveLast
intPtVisits = rec.RecordCount
End If


errHandler:
Exit Sub

I hope that helps someone...

Lex