Count records in report's recordsource

[Johnny Bright]

Hi there,

I'm running the following code:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![frmReports]![optChooseReport]
Case 1
Me.RecordSource = "qryRptCategory"
Case 2
Me.RecordSource = "qryRptSubCategory"
Case Else
Me.RecordSource = "qryAllProds"
End Select

If Me.RecordSource.RecordCount = 0 Then
Cancel = True
MsgBox "No products were found. Please try another category",
vbOKOnly, "No Samples"

End If
End Sub

So, what I want to do is if there are no records, cancel and give me a msg
box but all it's doing is cancelling the report operation. What am I doing
wrong?

Thanks!

 

[Marshall Barton]

I'm running the following code:

Private Sub Report_Open(Cancel As Integer)
Select Case Forms![frmReports]![optChooseReport]
Case 1
Me.RecordSource = "qryRptCategory"
Case 2
Me.RecordSource = "qryRptSubCategory"
Case Else
Me.RecordSource = "qryAllProds"
End Select

If Me.RecordSource.RecordCount = 0 Then
Cancel = True
MsgBox "No products were found. Please try another category",
vbOKOnly, "No Samples"

End If
End Sub

So, what I want to do is if there are no records, cancel and give me a msg
box but all it's doing is cancelling the report operation. What am I doing
wrong?

The RecordSource property does not have any properties, much
less a RecoordCount.

Remove the entire If block from your code. Then use this
line in the report's NoData event:
Cancel = True

Back in your form event procedure that opens the report add
error handling to trap the 2501 (cancelled) error.

Air code example:

On Error GoTo ErrHandler
. . .
DoCmd.OpenReport . . .
. . .
ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
MsgBox "No products were found. . .
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume ExitHere
End Sub


Note, you should pass the query name to the report in the
OpenReport method's OpenArgs argument instead of making the
report dependent on the form. The report's open event
procedure would then look like:

Private Sub Report_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If
End Sub