Automate monthly create entry (1 Viewer)

[Kayleigh]

Hi
Can someone help me with creating an append query to create invoices once a month if child is registered. It will run when main screen opens but user may not open DB every day so how to i program it to check if last invoice was 30 days ago and create new record?
I would also like to do a similar solution on a weekly basis.

Maybe I'm thinking too much into the problem - may be a simple solution!

EDIT: Just did a quick Google and found this - https://bytes.com/topic/access/answ...end-update-queries-automatically-weekly-basis . Is it the most advisable option?

Part of DB is attached.
Many thanks

 

[pbaldy]

That could work. I'd probably create a stand alone db that did the desired process when it started and then shuts down, and fire it off from scheduled tasks.

 

[Kayleigh]

If I create a query of last invoice for all students, then query against it to filter out only those more than a month, append to create new invoice entries. Invoice date would be lastDate + 1 month. Are my thoughts on the right lines?

I'm concerned that the end date for pupil may mean an incorrect invoice is issued or missed.

 

[plog]

Agree with pbaldy and here's some more specifics:

Create a new database and link to your 3 tables
paste in your query
make a macro that runs that query and then exits access
use the task scheduler to run your database and execute your macro on the schedule you want (I suggest when no one will be in the database)

I don't think you need to look at the last time someone was invoiced--everyone gets invoiced once a month via this process that gets run. Let's say the first of every month you invoice--it doesn't really matter the last invoice date, you simply need to determine the kids who's dates line up:

StartDate <= Date() AND EndDate Is Null
OR
StartDate <= Date() AND EndDate>=DateAdd(-1, Month, Date())

 

[Pat Hartman]

If you use plog's suggestion to create a separate mini-app to run this process once per month, I would still create a table to log the invoice date. This makes the process a little more flexible and gives you an easy way to recover if for some reason the task wasn't run. I've had to run batch jobs like this and it is always a problem if the server goes down or the power goes out or rarely, someone manually turns off the computer. It would be great if you could actually run the job on the server (which has good recovery software) but that won't happen. You'll have to run it from either your desktop or someone else's. That's where the log helps when the job doesn't run when you expected it to. I also had the job send me and three other people emails when it finished so we could see that it ran.

Luckily, my client had an unused desktop that we could use so, no one had to remember to leave the PC on over night or over the weekend. It was always on (except when it wasn't). We deliberately unplugged the monitor to prevent people from accidently using that particular PC.

Using the log table to determine whether or not to run the job lets you manually run the macro. It looks at the current date to determine the month and then looks at the log table to see if the invoices were created for current month -1.