Report sent daily, if new records?

[kbrooks]

I just recently learned how to create a button that, when pressed, will use the SendObject function in a macro to send a report through email.

I would like a report emailed daily, only if new records have been added since the last time the report was created. Am I asking too much? Can it be done?

My database is one where several different users enter charge corrections for patient accounts that need done. I want a report sent to me so I don't have to remember to go run it every day.

Thanks in advance!

 

[Fizzio]

It can be done farily easily, but to be able to track changes, you may have to do a couple of things to your data. Initially you will need to add an TimeCreated field in the table that you want to monitor (and if you want to be frugal a TimeChanged Field) which you can add/alter on adding a new record / changing a record.
You will then need an audit table of when the report was run. The simplest is just with 1 field with the date and time. Essentially you (using a query), select the records that have been added since the last time the report was run, run the report and add the date and time to a new record in the audit table.
There are a couple of ways to do this, I would use the select query to pick out the records, vba to check if there were any records present, run and send the report and then use a recordset of the audit table to add the precise date and time the report was sent. (Unless anyone has an easier solution)