MsgBox instead of Error result in Report 2

[kmkland]

This was originally posted in the wrong forum.....

I need to produce rptOccurCode from frmParameters having combo box cboAbsenceCode. The user selects a code from the combo box and produces the report. This works fine, however, if there are no results to show, I would like a MsgBox to display to notify the user that no results were found, and no report will be produced.
I am slowly learning VB, but here is what I currently have:

Private Sub Ok_Click()

    If IsNull(Me.StartDate) And IsNull(Me.EndDate) And IsNull(Me.cboAbsenceCode) Then
        MsgBox "You must first select an Absence Code and  " _
        & vbCrLf & "enter the Start Date and End Date to preview the report. "
            GoTo Exit_OK_Click
    End If
    
    If Not IsNull(Me.cboAbsenceCode) Then
        DoCmd.OpenReport "rptOccurCode", acViewPreview
        GoTo Exit_OK_Click
    End If
    
     If rstOrd.RecordCount = 0 Then
     MsgBox "There are no Occurrences to Print ", vbOKOnly, " frmParameters "
     GoTo Exit_OK_Click
    
    End If
       
     
Exit_OK_Click:
     Me.cboAbsenceCode.SetFocus     
     Exit Sub
    
End Sub

Any thoughts on what I need to do/change?????

Thanks soooo much!
KMK

 

[Remou]

Hi
You could use the On No Data property of the report, I think.

 

[kmkland]

Ok. I have the code written for the On No Data property, and the MsgBox is generated...twice! What do I change?

Kmkland

 

[Remou]

Hi
Please post the code, maybe it will show something.

 

[kmkland]

Thanks Remou.

This is the code for the On No Date property:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no results for the code selected", vbOKOnly, "Result Findings"
End Sub

Quite simple, I know.
As I said, it's producing 2 MsgBoxes instead of 1...

Rgds,
Kmkland

 

[Remou]

You need to cancel, I think.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no results for the code selected", vbOKOnly, "Result Findings"
[blue]Cancel = True[/blue]
End Sub

And I do not think you will need this message (from your first post):

     If rstOrd.RecordCount = 0 Then
     MsgBox "There are no Occurrences to Print ", vbOKOnly, "

 

[kmkland]

Thanks Remou!

But now, when I click my command button on my form which has the following code:

Private Sub Ok_Click()
    If IsNull(Me.StartDate) And IsNull(Me.EndDate) And IsNull(Me.cboAbsenceCode) Then
        MsgBox "You must first select an Absence Code and  " _
        & vbCrLf & "enter the Start Date and End Date to preview the report. "
            GoTo Exit_OK_Click
    End If
    
    If Not IsNull(Me.cboAbsenceCode) Then
        DoCmd.OpenReport "rptOccurCode", acViewPreview
        GoTo Exit_OK_Click
    End If
    
       
     
Exit_OK_Click:
    Me.cboAbsenceCode.SetFocus 
    Exit Sub
    
End Sub

...and generate my report, which has the following code on the On No Data Property:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler

    MsgBox "There are no results for the code selected", vbOKOnly, "Result Findings"
    Cancel = True

ExitHandler:
    Exit Sub

ErrorHandler:

    If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If

End Sub

...then I get a run time error 2501 message that leads me back to this line of my command buttoncode when I choose to debug:

DoCmd.OpenReport "rptOccurCode", acViewPreview

Any suggestions for this???

Thanks in advance, Remou!

Rgds,
Kmkland

 

[dashen]

Which line is it faulting on? breakpoints

If rstOrd.RecordCount = 0 Then
// where is this recordset created? I don't usually use the RecordCount to find no occurances. There are better more efficient ways (.EOF), but that should not be a problem, so it must be the the cboAbsenceCode is not being used to when you initialize the recordset. Or... wait, did you even set up your recordset rstOrd? If I am to understand this, you created a public recordset that is initialized on cboAbsenceCode_AfterUpdate event and then your Report is accessing that record? That seems a little strange to me

It may be better if you just use a stLinkCriteria string in your OpenReport command to apply a filter on your report and just use a IF DLOOKUP(<criteria>) to test if not found before this.

If IsNull(DLookup(<output>, <table>, "[absenceField]=" & cboAbsenceCode)) Then
MsgBox...
Exit Sub
Else
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptWhatever"

stLinkCriteria = "[absenceField]=" & cboAbsenceCode
DoCmd.OpenReport stDocName, , , stLinkCriteria

 

[Remou]

Hi
I think you need to put the Error 2501 coding here:

Private Sub Ok_Click()
On Error GoTo ErrorHandler
...
   
Exit_OK_Click:
    Me.cboAbsenceCode.SetFocus 
    Exit Sub

ErrorHandler:
  If Err.Number <> 2501 Then
     MsgBox "Error " & Err.Number ...
  End If
End Sub

Rather than on your report.
As a note, your first "If Null" mentions three fields and your second mentions only one. Would an Else in there somewhere work better?

    If IsNull(Me.StartDate) And IsNull(Me.EndDate) And IsNull(Me.cboAbsenceCode) Then
        MsgBox "You must first select an Absence Code and  " _
        & vbCrLf & "enter the Start Date and End Date to preview the report. "
            GoTo Exit_OK_Click
[Blue]Else[/Blue]
        DoCmd.OpenReport "rptOccurCode", acViewPreview
        GoTo Exit_OK_Click
    End If

 

[kmkland]

Dashen,

Thank you for your suggestion.
I did find that Remou's suggestion was a bit more simpler for me to understand (as I am a VB learner at the early stages , and in fact, it did work.

So, thank you both so much for the help!!

Kind Rgds,
Kmkland

 

[kmkland]

Oops!

Remou,
This did remove the duplicate MsgBox but now it gives a message box when I know I have results!!!

Did I omit something that I was not supposed to omit???? Here is the code:

Private Sub Ok_Click()
On Error GoTo ErrorHandler

    MsgBox "There are no results for the code selected", vbOKOnly, "Result Findings"
    Cancel = True
    
ExitHandler:
    Exit Sub
    
    If IsNull(Me.StartDate) And IsNull(Me.EndDate) And IsNull(Me.cboAbsenceCode) Then
        MsgBox "You must first select an Absence Code and  " _
        & vbCrLf & "enter the Start Date and End Date to preview the report. "
            GoTo Exit_OK_Click
    
    End If
        
    If Not IsNull(Me.cboAbsenceCode) Then
        DoCmd.OpenReport "rptOccurCode", acViewPreview
        GoTo Exit_OK_Click
    End If
    
       
     
Exit_OK_Click:
    Me.cboAbsenceCode.SetFocus 
    Exit Sub

ErrorHandler:

    If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
    
End Sub

Kindest Rgds,
Kmkland

 

[dashen]

Private Sub Ok_Click()
On Error GoTo ErrorHandler

MsgBox "There are no results for the code selected", vbOKOnly, "Result Findings"
Cancel = True

ExitHandler:
Exit Sub

This part is run first regardless of anything that is written after it. You need to move it. And if it hits an error it displays an error msgbox and goes to ExitHandler.

 

[kmkland]

Dashen,

You're so helpful!
Runs smoothly now!!!!!

Cheers,
Kim