On No Data (1 Viewer)

mike60smart

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
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

Banishment Pending
Local time
Today, 13:29
Joined
May 11, 2011
Messages
11,611
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
add code to On No Data event:

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

End Sub
 

mike60smart

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
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:-

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:-

1658241265959.png
 

plog

Banishment Pending
Local time
Today, 13:29
Joined
May 11, 2011
Messages
11,611
DSUM will never be NULL.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
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?
 

Attachments

  • error.JPG
    error.JPG
    22 KB · Views: 83

mike60smart

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
@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:-

Code:
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?
 

Attachments

  • error.JPG
    error.JPG
    20.4 KB · Views: 83

Gasman

Enthusiastic Amateur
Local time
Today, 18:29
Joined
Sep 21, 2011
Messages
14,046
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

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
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?
 

Attachments

  • error.JPG
    error.JPG
    20.4 KB · Views: 83

Gasman

Enthusiastic Amateur
Local time
Today, 18:29
Joined
Sep 21, 2011
Messages
14,046
You are going to a label that does not exist? :(
Do you ever compile your code? :(
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:29
Joined
Feb 19, 2013
Messages
16,553
surprised this line even compiles

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

mike60smart

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
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:-

Code:
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:-

Code:
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

Enthusiastic Amateur
Local time
Today, 18:29
Joined
Sep 21, 2011
Messages
14,046
Do you have a link to that AB code?
 

GK in the UK

Registered User.
Local time
Today, 18:29
Joined
Dec 20, 2017
Messages
274
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:

Code:
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.

Code:
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

Registered User.
Local time
Today, 18:29
Joined
Aug 6, 2017
Messages
1,899
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:

Code:
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.

Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
see the code behind the button on form1.
 

Attachments

  • OnNoData.accdb
    456 KB · Views: 84

Users who are viewing this thread

Top Bottom