How to stop Report On Close event from firing when No Data

[JeffBarker]

Hi guys,

I'm sure this is an absurdly easy question to answer, but after plenty of Googling and searching in these forums, I can't find the answer that I'm looking for.

It's been ages since I had to do any coding, so am a bit rusty! :banghead:

Anyway, all I'm looking to do is stop the On Close event of my Tent Cards report from firing if there's no data in the recordsource behind it:

    Dim vResult As Integer
    
    vResult = MsgBox("Flag Tent Cards as printed?", vbYesNo, "Tent Cards")
    If vResult = vbNo Then
        Forms![frmPrintOptionTent].Visible = True
        Exit Sub
    Else
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryMarkTentCardsAsPrinted", acViewNormal
        DoCmd.SetWarnings True
    End If

So literally want to have the above 'missed' or stepped over if there's no data in the recordsource...could someone assist with the correct code to achieve this, please?

The code I have in the NoData event is as follows:

  MsgBox "There are no Tent Cards to Print", vbExclamation, "Event Manager - AHCP 2017"
  Cancel = True

Thanks in advance, guys.

Jeff.

 

[RuralGuy]

Me.Recordset.RecordCount = 0 when there are no records. Does that help?

 

[The_Doc_Man]

Allan's suggestion is something you could test BEFORE you even OPEN the tent-card report. Put his suggested test where you are about to open the report and just don't open it if you got nothing back. The best way to prevent an OnClose event is to never open the thing that would eventually need closing. Another facet is that once you open anything, a very basic rule of life says you need to close it. (Remember the old "Everything I Needed to Know, I Learned in Kindergarten" book?) If you open it, close it. If you take it out, put it back. That sort of thing applies to Access, too.

To coin a variant phrase, "A couple of lines of prevention equals a couple of hundred lines of correction."

 

[Cronk]

Use the OnNoData event of the report. Display the message and set Cancel = true

 

[JeffBarker]

Me.Recordset.RecordCount = 0 when there are no records. Does that help?

Hi RuralGuy,

Thanks for the response (and The_Doc_Man for the follow up), but can you advise in which procedure I should place this piece of code please?

I placed this in the NoData procedure, as follows:

Private Sub Report_NoData(Cancel As Integer)
  
  MsgBox "There are no records to report", vbExclamation, "No Records"
  Cancel = True
  
  Me.Recordset.RecordCount = 0

End Sub

And received a "Run-time error '32585': This feature is only available in an ADP".

 

[RuralGuy]

Try this in the Close event:

If Me.Recordset.RecordCount <> 0 Then
    vResult = MsgBox("Flag Tent Cards as printed?", vbYesNo, "Tent Cards")
    If vResult = vbNo Then
        Forms![frmPrintOptionTent].Visible = True
        Exit Sub
    Else
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryMarkTentCardsAsPrinted", acViewNormal
        DoCmd.SetWarnings True
    End If
End If

 

[JeffBarker]

Try this in the Close event:

If Me.Recordset.RecordCount <> 0 Then
    vResult = MsgBox("Flag Tent Cards as printed?", vbYesNo, "Tent Cards")
    If vResult = vbNo Then
        Forms![frmPrintOptionTent].Visible = True
        Exit Sub
    Else
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryMarkTentCardsAsPrinted", acViewNormal
        DoCmd.SetWarnings True
    End If
End If

Hi RuralGuy, and thanks again for the reply - unfortunately I'm still seeing the 'only available in an ADP' error, even though I've copied and pasted your code above in to the Close even of my report, as per the below:

Private Sub Report_Close()

    Dim vResult As Integer
    
    If Me.Recordset.RecordCount <> 0 Then
        vResult = MsgBox("Flag Tent Cards as printed?", vbYesNo, "Tent Cards")
        If vResult = vbNo Then
            Forms![frmPrintOptionTent].Visible = True
            Exit Sub
        Else
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "qryMarkTentCardsAsPrinted", acViewNormal
            DoCmd.SetWarnings True
        End If
    End If

End Sub

The code is failing on the new RecordCount line.

 

[RuralGuy]

Okay, something weird is going on. Let's try Doc's suggestion. What is the line of code you have to open the report?

 

[JeffBarker]

Okay, something weird is going on. Let's try Doc's suggestion. What is the line of code you have to open the report?

Hi RuralGuy, thanks for the continued help!

We're currently using this code, attached to the OnClick event of an open form:

Private Sub btnPrint_Click()
On Error GoTo Err_btnPrint_Click

    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim vOption As Integer
       
    vOption = opt1
    
    stDocName1 = "rptTentCards"
    stDocName2 = "rptTentCards_Reprint"

    Me.Form.Visible = False
    If vOption = 2 Then
        DoCmd.OpenReport stDocName2, acViewPreview
    Else
        DoCmd.OpenReport stDocName1, acViewPreview
    End If
           
Exit_btnPrint_Click:
    Exit Sub

Err_btnPrint_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_btnPrint_Click
    
End Sub

 

[The_Doc_Man]

If you can run a DCount of the records that would have been printed based on some flag in the record, then you might try this. Just before your "Me.Form.Visible=False" line, add:

    If DCount("[Flags]","TheTentCardTable","[Flags]=False") Then
        MsgBox "There are no records to print", vbOKOnly, "Aborting Print Process"
        Exit Sub
    End If

Substitute the proper field name, table or query names, and the proper criteria for deciding that the record should be printed. Note that in this context, Me.RecordSet refers to the .RecordSource for the form holding the button to be clicked, whereas in the body of the report, Me.RecordSet refers to the .RecordSource for the data to be printed. If these are not the same, then a Me.RecordSet.Count from the form won't work to stop the report because it would potentially see a different recordset.

 

[JeffBarker]

If you can run a DCount of the records that would have been printed based on some flag in the record, then you might try this. Just before your "Me.Form.Visible=False" line, add:

    If DCount("[Flags]","TheTentCardTable","[Flags]=False") Then
        MsgBox "There are no records to print", vbOKOnly, "Aborting Print Process"
        Exit Sub
    End If

Substitute the proper field name, table or query names, and the proper criteria for deciding that the record should be printed. Note that in this context, Me.RecordSet refers to the .RecordSource for the form holding the button to be clicked, whereas in the body of the report, Me.RecordSet refers to the .RecordSource for the data to be printed. If these are not the same, then a Me.RecordSet.Count from the form won't work to stop the report because it would potentially see a different recordset.

Hello again!

I've tried two different approaches for the Dcount, first of all using the main table itself (TentCardPrinted is a Date/Time field):

    If DCount("[BkgRef]", "tblMain", "[TentCardPrinted]=Null") Then

And then using a query that I have elsewhere in the system to show how many Tent Cards require printing:

    If DCount("[BkgRef]", "qryTentCardsUnprinted", "[BkgRef]=0") Then

And have tried placing these two separate pieces of code in front of the "no records to print" part, as advised, but when stepping through it's skipping straight to the End If part of this statement, and not going through the MsgBox part, even though there are no Tent Cards to be printed.