Check if a query is empty and open a report if it isn't

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

Guest

Hello, I would like to run a query and check if there has been any records
returned (any data meets the query criteria) and if there is any records open
a report to show these records, however if there is no records I just want to
return to the main form.

I have created the report called dupenumbers and the query is called
duplicatenumbers. I have never used VB before so if this is required it
would be good to know where to insert the code.

Many thanks in advance
 
Create a macro with the action:
Cancel Event
Save the macro with the name (say) macCancelEvent.

Open the report in design view.
Open the Properties box, and look at the properties for the report.
On the Event tab of the properties box, choose the macCancelEvent macro
beside the No Data event.

(If you don't see the event, you are looking at the properties of a text box
instead of the properties of the report.)

No if you open the report and there are no records, it just doesn't open.
 
If the main report has no records, it will cancel, regardless of whether the
subreport has records.

The main report cannot be cancelled based on the records of the subreport.
But you could do that if you use DCount() on the subreport's recordsource,
and stop the macro before it opens the report. The DCount() expression would
go in the Macro Condition column of the macro, alongsite the StopMacro
action, on the line before the OpenReport that you want to call. (Otherwise
you have to use code in the report's Open event procedure.)
 
Hi, I have had to change this original report due to the various places data
was coming from etc. my original problem is still the same however now a
little more complicated (hope its just a little).

My report is now unbound to any data source however contains 2 subreports
that are both bound to different query's. Ideally I would like to check if
any of these reports will contain any data if they do display the report else
just return to my main form. Is this possible without becoming more hassle
than it's actualy worth?

Thanks again,
 
I would like to run a query and check if there has been any records returned
(any data meets the query criteria) and if there is any records export and
save data into excel sheet to show these records, however if there is no
records I don't need to save an empty file.

thanks in advance
 
If DCount("*","NameOfYourQuery") > 0 Then
Use Transferspreadsheet method to export data to Excel
End If

Look up Transferspreadsheet method in Help file

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Steve,

Thanks it works

Mohamed


Steve said:
If DCount("*","NameOfYourQuery") > 0 Then
Use Transferspreadsheet method to export data to Excel
End If

Look up Transferspreadsheet method in Help file

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
Click to expand...
 
Back
Top