On No Data (1 Viewer)

[mike60smart]

I have a command Button that opens a Report to display any outstanding Invoices.

If there are no invoices outstanding how would I display a Popup Message " No Outstanding Invoices"
and not display the Report?

Any help appreciated

 

[plog]

In the code, you first run a DSUM() to see if there is data to report on. If DSUM=0 show message, otherwise open your report.

 

[arnelgp]

add code to On No Data event:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
MsgBox "No outstanding invoices!"

End Sub

 

[mike60smart]

In the code, you first run a DSUM() to see if there is data to report on. If DSUM=0 show message, otherwise open your report.

Hi Plog

I tried the following Code:-

Private Sub cmdOutstanding_Click()


10        On Error GoTo cmdOutstanding_Click_Error


20    If DSum("InvoicePaid", "qryAllInvoices" = Null) Then


30    MsgBox "There are no outstanding Invoices", vbInformation


40    Else


50    DoCmd.OpenReport "rptAllInvoices", acViewPreview, "", "", acNormal
60    End If


          
70        On Error GoTo 0
80        Exit Sub


cmdOutstanding_Click_Error:


90        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOutstanding_Click, line " & Erl & "."


End Sub

But get the following error:-

 

[Minty]

@mike60smart Plog said = 0 not = null.
There is a big difference!

 

[plog]

DSUM will never be NULL.

 

[arnelgp]

I tried the following Code:-

you are just making it hard.
put the code on the On No Data event of the Report and it will show the message (not the report).

 

[mike60smart]

add code to On No Data event:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
MsgBox "No outstanding invoices!"

End Sub

Hi Arnelgp

I put the Code in the On No Data Event and the message pops up ok.

It does however also displays the following message. Can this be stopped from appearing?

 

[mike60smart]

@mike60smart Plog said = 0 not = null.
There is a big difference!

Hi Minty

I found the following Code which is supposed to do what I want:-

Private Sub Report_NoData(Cancel As Integer)


MsgBox "No outstanding invoices!"
Cancel = True
End Sub

When I run the code I get the following error:

How would I manage the 2501 error?

 

[Gasman]

Hi Arnelgp

I put the Code in the On No Data Event and the message pops up ok.

It does however also displays the following message. Can this be stopped from appearing?

Perhaps test for that error message and ignore it?
Jeez Mike, you have been using Access since 2004?

 

[mike60smart]

Perhaps test for that error message and ignore it?
Jeez Mike, you have been using Access since 2004?

Hi Gasman

I have tried the following :-

10 On Error GoTo cmdOutstanding_Click_Error
20 If Err.Number = 2501 Then
30 DoCmd.Close , "rptAllInvoices"
40 End If

The error I get is the same?

 

[Gasman]

You are going to a label that does not exist?
Do you ever compile your code?

 

[CJ_London]

surprised this line even compiles

If DSum("InvoicePaid", "qryAllInvoices" = Null)

 

[Gasman]

I do not think a compile comes into the equation.

surprised this line even compiles

If DSum("InvoicePaid", "qryAllInvoices" = Null)

 

[mike60smart]

You are going to a label that does not exist?
Do you ever compile your code?

Hi Gasman

I am trying to use the Module supplied by Allan Browne as follows:-

Public Function NoData(rpt As Report)
    'Purpose: Called by report's NoData event.
    'Usage: =NoData([Report])
    Dim strCaption As String   'Caption of report.
   
    strCaption = rpt.Caption
    If strCaption = vbNullString Then
        strCaption = rpt.Name
    End If
   
    DoCmd.CancelEvent
    MsgBox "There are no records to include in report """ & _
        strCaption & """.", vbInformation, "No Data..."
End Function

In the Reports NoData Property I have used the following as suggested by Allan:

=NoData([Report])

My lack of VB knowledge will not allow me to solve the Error 2501 ?

I know I have to do something with the On Click event that opens the Report but so far not been
able to find a solution.

All I currently have is this:-

Private Sub cmdOutstanding_Click()


10        On Error GoTo cmdOutstanding_Click_Error


20        DoCmd.OpenReport "rptAllInvoices", acViewPreview, "", "", , acNormal
      
30        On Error GoTo 0
40        Exit Sub


cmdOutstanding_Click_Error:


50        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOutstanding_Click, line " & Erl & "."


End Sub

 

[Gasman]

Why not just use arnel's suggeztion?
With AB's code you are meant to pass your report as an object.?
Just keep it simple.

Look at what you are writing????

on error goto 0 vba - Google Search

 

[Gasman]

Do you have a link to that AB code?

 

[GK in the UK]

Keep what you have shown in post #8 in the report code module. Or, it may work with Allen Browne's public routine, I don't know for sure.
Change your command button code to this:

Private Sub cmdOutstanding_Click()
On Error GoTo cmdOutstanding_Click_Error


DoCmd.OpenReport "rptAllInvoices", acViewPreview, "", "", , acNormal


Exit_handler:
  Exit sub
 
cmdOutstanding_Click_Error:
  Select Case Err.Number
      Case 2501    ' no data in the report but the message is in the report module - do nothing
      Case Else:     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOutstanding_Click, line " & Erl & "."
  End Select
  Resume Exit_handler


End Sub

For a single line of code I might just have this but you won't get to find out what happened if there was some other error.

Private Sub cmdOutstanding_Click()
On Error Resume Next            ' error 2501 expected if there's no data in the report
DoCmd.OpenReport "rptAllInvoices", acViewPreview, "", "", , acNormal
End Sub

 

[mike60smart]

Keep what you have shown in post #8 in the report code module. Or, it may work with Allen Browne's public routine, I don't know for sure.
Change your command button code to this:

Private Sub cmdOutstanding_Click()
On Error GoTo cmdOutstanding_Click_Error


DoCmd.OpenReport "rptAllInvoices", acViewPreview, "", "", , acNormal


Exit_handler:
  Exit sub

cmdOutstanding_Click_Error:
  Select Case Err.Number
      Case 2501    ' no data in the report but the message is in the report module - do nothing
      Case Else:     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOutstanding_Click, line " & Erl & "."
  End Select
  Resume Exit_handler


End Sub

For a single line of code I might just have this but you won't get to find out what happened if there was some other error.

Private Sub cmdOutstanding_Click()
On Error Resume Next            ' error 2501 expected if there's no data in the report
DoCmd.OpenReport "rptAllInvoices", acViewPreview, "", "", , acNormal
End Sub

Hi GK

Tried both of your suggested solutions and in both cases the same error.

 

[arnelgp]

see the code behind the button on form1.