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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|