A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Is Null in a Report



 
 
Thread Tools Display Modes
  #1  
Old April 26th, 2010, 06:48 PM posted to microsoft.public.access.reports
Tim
external usenet poster
 
Posts: 780
Default Is Null in a Report

I'm using a list box to open a report for a specific record. I'm using a
report query for the report. The data in the list box updates the query. It
work fine except I would like to add some error handling before the report is
loaded. I would like to check to see if there is any data, if it is "null"
or no data provide a message box and exit before the report is loaded. I
cant seam to get pasted the if statement to check for the null. There must
be an easier way to get this accomplished. Any help would be appreciated.

Private Sub List14_AfterUpdate()
On Error GoTo Err_List14_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As String
Dim stErrSiteNull As String

stDocName = "rptRebandingEquipFreq"
stDocName2 = "qryRebandingEquipFreq"
stErrSiteNull = "No Feeder System Set Yet, Try again Later!"
'open report Query and verify that there is data, if null close
query, message box and exit
DoCmd.OpenQuery stDocName2, acViewNormal
If IsNull(rs.Fields("SiteNum")) Then
DoCmd.Close
MsgBox stErrSiteNull, vbOKOnly, "Dude!"
GoTo Exit_List14_Click
Else
'If the query is not null close query and open report
DoCmd.Close
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_List14_Click:
Exit Sub

Err_List14_Click:
MsgBox Err.Description
Resume Exit_List14_Click

End Sub


  #2  
Old April 26th, 2010, 08:48 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Is Null in a Report

Check the number of records.

Better yet would be to use the report's no data event and trap the 2501 error
that gets generated if you cancel the report.

Report's Code
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Feeder System Set Yet, Try again Later!"
Cancel = True
End Sub

Your calling code
Private Sub List14_AfterUpdate()
On Error GoTo Err_List14_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "rptRebandingEquipFreq"
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_List14_Click:
Exit Sub

Err_List14_Click:
If Err.number 2501 THen
MsgBox Err.Description
End If
Resume Exit_List14_Click

End Sub


Otherwise you could try
IF DCount("*","qryRebandingEquipFreq") = 0 Then
Msgbox "No Feeder System Set Yet, Try again Later!"
Else
stDocName = "rptRebandingEquipFreq"
DoCmd.OpenReport stDocName, acViewPreview
End if

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tim wrote:
I'm using a list box to open a report for a specific record. I'm using a
report query for the report. The data in the list box updates the query. It
work fine except I would like to add some error handling before the report is
loaded. I would like to check to see if there is any data, if it is "null"
or no data provide a message box and exit before the report is loaded. I
cant seam to get pasted the if statement to check for the null. There must
be an easier way to get this accomplished. Any help would be appreciated.

Private Sub List14_AfterUpdate()
On Error GoTo Err_List14_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As String
Dim stErrSiteNull As String

stDocName = "rptRebandingEquipFreq"
stDocName2 = "qryRebandingEquipFreq"
stErrSiteNull = "No Feeder System Set Yet, Try again Later!"
'open report Query and verify that there is data, if null close
query, message box and exit
DoCmd.OpenQuery stDocName2, acViewNormal
If IsNull(rs.Fields("SiteNum")) Then
DoCmd.Close
MsgBox stErrSiteNull, vbOKOnly, "Dude!"
GoTo Exit_List14_Click
Else
'If the query is not null close query and open report
DoCmd.Close
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_List14_Click:
Exit Sub

Err_List14_Click:
MsgBox Err.Description
Resume Exit_List14_Click

End Sub


  #3  
Old April 27th, 2010, 12:31 AM posted to microsoft.public.access.reports
Tim
external usenet poster
 
Posts: 780
Default Is Null in a Report

Thanks John. Works perfect, I used:

Private Sub List14_AfterUpdate()
On Error GoTo Err_List14_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stErrSiteNull As String

stDocName = "rptRebandingEquipFreq"
stErrSiteNull = "No Feeder System Set Yet, Try again Later!"
'open report Query and verify that there is data, message box and exit
If DCount("*", "qryRebandingEquipFreq") = 0 Then
Beep
MsgBox stErrSiteNull, vbOKOnly, "Dude!"
Else
stDocName = "rptRebandingEquipFreq"
DoCmd.OpenReport stDocName, acViewPreview
End If


Exit_List14_Click:
Exit Sub

Err_List14_Click:
MsgBox Err.Description
Resume Exit_List14_Click


"John Spencer" wrote:

Check the number of records.

Better yet would be to use the report's no data event and trap the 2501 error
that gets generated if you cancel the report.

Report's Code
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Feeder System Set Yet, Try again Later!"
Cancel = True
End Sub

Your calling code
Private Sub List14_AfterUpdate()
On Error GoTo Err_List14_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "rptRebandingEquipFreq"
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_List14_Click:
Exit Sub

Err_List14_Click:
If Err.number 2501 THen
MsgBox Err.Description
End If
Resume Exit_List14_Click

End Sub


Otherwise you could try
IF DCount("*","qryRebandingEquipFreq") = 0 Then
Msgbox "No Feeder System Set Yet, Try again Later!"
Else
stDocName = "rptRebandingEquipFreq"
DoCmd.OpenReport stDocName, acViewPreview
End if

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tim wrote:
I'm using a list box to open a report for a specific record. I'm using a
report query for the report. The data in the list box updates the query. It
work fine except I would like to add some error handling before the report is
loaded. I would like to check to see if there is any data, if it is "null"
or no data provide a message box and exit before the report is loaded. I
cant seam to get pasted the if statement to check for the null. There must
be an easier way to get this accomplished. Any help would be appreciated.

Private Sub List14_AfterUpdate()
On Error GoTo Err_List14_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As String
Dim stErrSiteNull As String

stDocName = "rptRebandingEquipFreq"
stDocName2 = "qryRebandingEquipFreq"
stErrSiteNull = "No Feeder System Set Yet, Try again Later!"
'open report Query and verify that there is data, if null close
query, message box and exit
DoCmd.OpenQuery stDocName2, acViewNormal
If IsNull(rs.Fields("SiteNum")) Then
DoCmd.Close
MsgBox stErrSiteNull, vbOKOnly, "Dude!"
GoTo Exit_List14_Click
Else
'If the query is not null close query and open report
DoCmd.Close
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_List14_Click:
Exit Sub

Err_List14_Click:
MsgBox Err.Description
Resume Exit_List14_Click

End Sub


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.