Folks,
Need your help. I searched the entire forum and found couple of postings on this, but none of them indicates if there is any fix for this problem.
If there is no data, basically I want to show a message and close the report. I have some code to handle this under no data event like below:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = True
End Sub
But this 2501 always pops up and I couldn't find a way to stop this. I also searched Microsoft knowledgebase, no help.
Here's how my clik event procedure looks like:
Private Sub cmdSearch_Click()
On Error GoTo Err_PreviewRprt
Dim strTbl As String
Dim strSQL As String
Dim strOptItem As String
Dim strOptBlrType As String
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
'Set up SQL statement for report record source
If IsNull(optCriteria) Then optCriteria = 1
strTbl = Me.cboTypeOfGuar.Column(0)
strOptItem = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")
strOptBlrType = Choose(optBoilerType, "SWUP", "RB", "CFB", "All")
strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblProjts1.chrBoilerType, " & _
strTbl & ".memGuranItem, " & strTbl & "." & strOptItem & _
" FROM tblProjts1 INNER JOIN " & strTbl & " ON " & _
"tblProjts1.intProjectId = " & strTbl & ".intProjectId" & _
" WHERE (((" & strTbl & ".memGuranItem)" & " IS NOT NULL)" & " AND " & "((tblProjts1.chrBoilerType) = '" & strOptBlrType & "'))"
'Open report in Design view to set the report's record source
'and search item label's caption
DoCmd.Echo False 'suppress the screen updates
DoCmd.OpenReport "rptBlrSrchItem1", acViewDesign
With Reports("rptBlrSrchItem1")
.RecordSource = strSQL
.Controls("strOptItem").ControlSource = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")
End With
DoCmd.Close , , acSaveYes
'Now show the search results to the user
DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview
DoCmd.Echo True
Exit_PreviewRprt:
Exit Sub
Err_PreviewRprt:
If Err = 2501 Then
Resume Exit_PreviewRprt
Else
MsgBox Err.Description
Resume Exit_PreviewRprt
End If
End Sub
Any help is much appreciated.
Need your help. I searched the entire forum and found couple of postings on this, but none of them indicates if there is any fix for this problem.
If there is no data, basically I want to show a message and close the report. I have some code to handle this under no data event like below:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = True
End Sub
But this 2501 always pops up and I couldn't find a way to stop this. I also searched Microsoft knowledgebase, no help.
Here's how my clik event procedure looks like:
Private Sub cmdSearch_Click()
On Error GoTo Err_PreviewRprt
Dim strTbl As String
Dim strSQL As String
Dim strOptItem As String
Dim strOptBlrType As String
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
'Set up SQL statement for report record source
If IsNull(optCriteria) Then optCriteria = 1
strTbl = Me.cboTypeOfGuar.Column(0)
strOptItem = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")
strOptBlrType = Choose(optBoilerType, "SWUP", "RB", "CFB", "All")
strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblProjts1.chrBoilerType, " & _
strTbl & ".memGuranItem, " & strTbl & "." & strOptItem & _
" FROM tblProjts1 INNER JOIN " & strTbl & " ON " & _
"tblProjts1.intProjectId = " & strTbl & ".intProjectId" & _
" WHERE (((" & strTbl & ".memGuranItem)" & " IS NOT NULL)" & " AND " & "((tblProjts1.chrBoilerType) = '" & strOptBlrType & "'))"
'Open report in Design view to set the report's record source
'and search item label's caption
DoCmd.Echo False 'suppress the screen updates
DoCmd.OpenReport "rptBlrSrchItem1", acViewDesign
With Reports("rptBlrSrchItem1")
.RecordSource = strSQL
.Controls("strOptItem").ControlSource = Choose(optCriteria, "memPred", "memGuar", "memRiskLevel", "memLDs")
End With
DoCmd.Close , , acSaveYes
'Now show the search results to the user
DoCmd.OpenReport "rptBlrSrchItem1", acViewPreview
DoCmd.Echo True
Exit_PreviewRprt:
Exit Sub
Err_PreviewRprt:
If Err = 2501 Then
Resume Exit_PreviewRprt
Else
MsgBox Err.Description
Resume Exit_PreviewRprt
End If
End Sub
Any help is much appreciated.