get row count before displaying report

  • Thread starter Thread starter M. Scholtz
  • Start date Start date
M

M. Scholtz

I've got a report that's run via code - the code passes
along a Where clause.
What I'd like to do is pop a MsgBox that tells the user how
many rows were selected by the SQL statement underlying the
report, then gives the user the option whether to actually
preview the report or just close it.

I know I could copy the SQL from the RecordSource and run
that SQL as a separate query via code and get the count
that way. But that seems wasteful given as that exact same
query is about to be run by the report itself - why do it
twice?

So can I get at the row count via the report without first
displaying the report?

I've tried putting an invisible txt box on the report
itself that gives the row count. Works fine - problem is I
can't reference this value from the report's Open event -
it hasn't been evaluated yet. I tried putting the box in
the Report Header, then referencing it using via
ReportHeader_Format event. This works great, I get the row
count, except it won't let me then close the report during
this event if that's what the user chooses - I get an error
"this action can't be carried out while processing a form
or report event" if I try DoCmd.Close.

So what's the best way to accomplish this? Am I missing
something easy?

Thanks much in advance,
Matthew
 
Back
Top