use if-else logic based on null record set

[dkape]

I currently have an access db in which the autoexec macro runs an exception report and then e-mails the report to two users. I have this db run automatically each night, so each morning the report is in the user e-mail inbox. Sometimes the query that the report is based on returns no data, which is fine, the report just has no data.

What I would like to do is have the macro evaluate the results of the report/query and if no data is found, not e-mail the report. That way the report would only be sent if there is data in the report.

I would be open to any other thoughts on how this might be done as well.

Thanks, Doug

 

[JeremyNYC]

Doug,

Sounds like a good plan, though I might modify one part of it. If it were my app I would want to make it so that if there is no data it e-mails the users to let them know that there is no data. That way they get positive feedback (not in the sense of "You guys did such a good job here's your gold star", but in the sense of "I'm letting you know that there's no data. You don't have to wonder if maybe the database croaked last night.&quot

To do this I'd toss the sql from the querydef item behind the report into a string, make a recordset based on that string, and check to see if rst.bof and rst.eof are true at the same time.

This would require calling a function from your autoexec.

Then, if there are no records, you change the parameters of the call to sendObject, or whatever it is you're using to send the report.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[ssecca]

If you are using VBA to get your recordset then try adding this line immediately after set the recordset.

if rst.BOF = true and rst.EOF = true then
'recordset is empty take empty action
ELSE
'recordset contains data take full action
end if

If your using a macro to get your dataset I would seriously consider changing to VBA code. I have not used amacro in several years except autoexec and then only to call a function. VBA will get your app running a whole bunch faster with a LOT more options available to you.
Good Luck
ssecca

 

[dkape]

Thanks for the feedback. Could either of you give me some more information on using VB to get the recordset as you mentioned. I'm not familiar with how to do that.

Thanks,
Doug

 

[JeremyNYC]

Doug,

Check it out in the help files. They'll have examples in there and you should be able to just copy and paste directly into your routine and then make a few changes.

You'll have to play around a good bit, and it will take a little time. Definitely come back with specific questions.


Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.