Executing qry before it hits a report

[mdweezer]

The problem I am having is when I execute a report with no data, I get a blank report. So i implemented on the "no data event" on the report a message box and then a process to kill the action and return to the form. This works flawlessly on my developer edition but when it is actually run on a users machine using the access runtimes it gets an error and exits the database.

Therefore on the form that calls the report, I would like to run the qry first, check for data and if there is no data use a msgbox to say no data and that would be it, there would be no report calling involved unless the qry returned data.

qry = "SELECT Issues.MILESTONE, TBL_MILESTONES.MILESTONES, Issues.[Status Code] AS "
qry = qry & "[Status Code1], Count(Issues.IssueNumber) AS [Total Of Status Code] "
qry = qry & "FROM Issues INNER JOIN TBL_MILESTONES ON Issues.MILESTONE = "
qry = qry & "TBL_MILESTONES.MILESTONES_ID "
qry = qry & "GROUP BY Issues.MILESTONE, TBL_MILESTONES.MILESTONES, Issues.[Status Code] "
qry = qry & "HAVING (((Issues.MILESTONE)= " & MilestoneValue & " ));"

There is the qry I run for the report. How do I run that qry on a form, check for data, if there is no data return a msgbox else if there is data call the report as I would and display it normally.

Thanks!

 

[PHV]

Why not playing with the Cancel parameter of the NoData event procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mdweezer]

heh, I implemented that actually, i'm just using this as a fallback method. The person using this database works in a seperate building and I can't get a hold of him right now to try it out, so I figure why not try this other method just in case...

However your "cancel = true" did work, however the msgbox was displayed twice for some reason....

I wish I had a machine nearby with the runtimes so I could try this stuff out but I don't, all the machines in my office are running the developer.

I also figure doing this method will broaden my access experience.

I'm currently a intern working for a fortune 500 company... I'm in no way a database guy I'm actually more of a network administration guy, but this was the only internship I could get and the pay is great! So i'm making the most of it, databases are interesting, i've done some work with mySQL when i've done some PHP work as well as some FilePro database work in my previous job.

These forums are GREAT and are the best i've seen for tech help, I've spent much time reading through posts trying to figure out whats the problem is then looking at the answer replies and wondering why they'd work. Interesting stuff. So keep up the good work all .

 

[mdweezer]

Bump - Anyone able to help?