record count before opening report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find a way to count the number of records that will display on the
report before I choose whether I want to open the report. The idea is that
if the user enters search criteria that return no results, I want to display
a message and not open the report.

I have written my own SQL and am passing that to the RecordSource property
of the report, then I open the report in PrintPreview.

Thanks in advance for your help.
Emily
 
A better solution would be to use the No Data property of the report. It
will know if there is not data and you can do what you need to do at that
point. You can display your message from there and close the report. It
will not show up, all you will see is the message.
 
Thanks for your help - I didn't know there was such a property. I used the
HasData property based on your suggestion.

Thanks,
Emily
 
-There is an event available in the PROPERTIES window OnNoRecords(?) or
something - its pretty obvious that you can put code behind to cancel
opening the report.
 
I tried working with the event, and was able to cancel; however, I am opening
the report from another form and the OnNoRecords event is triggered by my
opening the report and then it causes an error when returning to the line of
code that opens the report because "the action was cancelled".

Thanks for your help,
Emily
 
I need to find a way to count the number of records that will display
on the report before I choose whether I want to open the report.
Click to expand...

although you have had a couple of answers already, here in another
method. Assuming you have access to the recordset on which the report is
based, you can simply COUNT the number of records like one of these:

' if the report is based on a querydef
If DCount("*", "epMyReportQuery") > 110 then
MsgBox "There are too many to print"

Else
' open the report, etc

End If


or you can just repeat the WHERE clause if you know it:

jetCount = "SELECT COUNT(*) AS NumRecs FROM MyTable " & _
"WHERE QueuedForPrinting = TRUE"
Set fossCount = db.OpenRecordset( _
jetCount, dbOpenSnapshot, dbForwardOnly)

If fossCount!NumRecs < 70 Then
"Too few records, not worth printing"
Else
' etc

End If

and so on.
Hope that helps

Tim F
 
Thanks for the alternate answer, but in this case, I'm not using a query def
for the report. Since the HasData property works, I'll just stick with that.

Thanks again,
Emily
 
I actually need to count the number of records but the method below won't
work for me.

I have a form that creates filter criteria.

Ex:
I have a table with all my data. Then for rep_OldData I query the entire
table with the criteria of "Old". Then I have a form that filters the old
data to "[TableAllData].date like *2002*"
The criteria of "Old" doesn't change but the filter of "2002" can...

How do i capture how many records are being returned?

I have read through a number of the dicsussions and was able to create a txt
box on my report that counts the amt of records but i don't know how to
capture that number in VB.

FYI:
My end result will be
Private Sub Report_Open(Cancel As Integer)
OrderByOn = True
If "number or records" > 1 Then
DoCmd.OpenForm "frmSortData"
End If
End Sub

It is very annoying to get a pop up sorting form when you've asked for one
record...
 
I ended up using DCount after all...

I did the following
DCount("*","Table that report is based on", filter)

For the "filter" i just had to recreate the criteria of "old" as a filter
and then add it to the filter of "2002" that i created using the form.

filter = strOld & " AND " & strDate


--
-CLSWL


CLSWL said:
I actually need to count the number of records but the method below won't
work for me.

I have a form that creates filter criteria.

Ex:
I have a table with all my data. Then for rep_OldData I query the entire
table with the criteria of "Old". Then I have a form that filters the old
data to "[TableAllData].date like *2002*"
The criteria of "Old" doesn't change but the filter of "2002" can...

How do i capture how many records are being returned?

I have read through a number of the dicsussions and was able to create a txt
box on my report that counts the amt of records but i don't know how to
capture that number in VB.

FYI:
My end result will be
Private Sub Report_Open(Cancel As Integer)
OrderByOn = True
If "number or records" > 1 Then
DoCmd.OpenForm "frmSortData"
End If
End Sub

It is very annoying to get a pop up sorting form when you've asked for one
record...


--
-CLSWL


Tim Ferguson said:
although you have had a couple of answers already, here in another
method. Assuming you have access to the recordset on which the report is
based, you can simply COUNT the number of records like one of these:

' if the report is based on a querydef
If DCount("*", "epMyReportQuery") > 110 then
MsgBox "There are too many to print"

Else
' open the report, etc

End If


or you can just repeat the WHERE clause if you know it:

jetCount = "SELECT COUNT(*) AS NumRecs FROM MyTable " & _
"WHERE QueuedForPrinting = TRUE"
Set fossCount = db.OpenRecordset( _
jetCount, dbOpenSnapshot, dbForwardOnly)

If fossCount!NumRecs < 70 Then
"Too few records, not worth printing"
Else
' etc

End If

and so on.
Hope that helps

Tim F
Click to expand...
Click to expand...
 
Back
Top