Prev: Report Column questions
Next: Criteria from form on report
From: Tim on 26 Apr 2010 13:48
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


From: John Spencer on 26 Apr 2010 15:48
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
>
>
From: Tim on 26 Apr 2010 19:31
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
> >
> >
> .
>
 | 
Pages: 1
Prev: Report Column questions
Next: Criteria from form on report