Auto Schedule (1 Viewer)

[agehoops]

I have a table which stores all transactions performed on a bank account. I've now created a table which allows the user to enter in any transactions that will occur on a monthly basis, and the day number that they occur on.

What I want, is some code that will check the table for any that are due for today (or whatever day it runs) and if there are any (could be more than one) then to create the transaction in the main transactions table.

I think I can do it for 1 of them, but not sure how to when there is more than one on the same day.

Any help?

Thanks

 

[RuralGuy]

The question I have is what would be wrong with simply creating your recurring transactions directly in the checking transaction table instead of in a separate table?

 

[agehoops]

If i put them into the transactions table then they're logged, the money is logged as gone in or out, and it becomes completely manual. I'm looking for it to be automated, so that when I run the system, it simply creates the transaction if there is one or more to be done on that day and then it changes the balance.

Basically I just want an automatic system

 

[RuralGuy]

If i put them into the transactions table then they're logged, the money is logged as gone in or out...

But not until the date of the transaction which is what you want to happen.

 

[agehoops]

yea but then i'll have to put it in on the date every month, and if there is more than one on that date then i'm putting it in every month as well. If i stored it in another table and just stored the day number, it would do it automatically every month no matter how many there were, then i only need to manually put it in once and it'll do the rest

 

[RuralGuy]

Have you ever used PeachTree or QuickBooks? When you post recurring transactions, the software asks for how many periods and then just posts the transactions that many times bumping the date a month each time. I'll admit that the software knows that the transaction is a recurring transaction because when you go to delete it, it wants to know if you want to delete just this one or all of the rest of the recurring transactions. If you use your technique, what is to stop the system from applying the transaction more than once? How do you know if the transaction has been posted yet?

 

[agehoops]

I've got a table which logs whether the transactions have been completed for that day. if they have, then it won't do them again, if it hasn't, it'll check if there are any to be done, then do them, then log that they've been done.

At the beginning of a month, it'll remove the log of completed transactions, and start again for that month

 

[RuralGuy]

It is of course your design and you can do what you want. It just seems like a lot of work for something that should just be natural. Your transaction table is just a bunch of debits and credits with a date attached. Your original problem is how to determine what recurring transactions need to be posted right? Can you guarantee the system will be run every day? What if there is a problem and the system is down for a day or two? Hardware does break. Your problem is that you are having trouble creating the query that will return the transactions that need posting, right?

 

[agehoops]

Well it would be natural, but it will be used for multiple bank accounts. I'm creating ready for University in a few weeks time, so it'll be used for about 3 or 4 accounts, so remembering all of the recurring ones could be a pain.

I can't guarentee it'll be run everyday, but i'll get it to check all of the transactions for today and before today that haven't been completed yet.

The problem I have, is making it run through them all until they're all done.

I'm pretty sure I can work the SQL out which will actually get the information, and put it into the transactions table, but only for one record, not if there is more than one on a day.

So basically i need a loop for all records that are found on the day it is run and any day before that day which hasn't been logged as complete (if that makes sense)

 

[RuralGuy]

Wouldn't it just be an Append query?

 

[agehoops]

Yea, but then how do I pass variables into the queries criteria? But yea, i just need the code which will loop through the records it finds.

 

[Rich]

It's not really difficult, something like
SQLStmt1 = "INSERT INTO Account ( AccDate, Deposits, Heading2, Description, AccountTypeID )SELECT DateDue.DateDue, DateDue.PaymentAmount, DateDue.Heading, [PymtType] & ' ' & 'to' & ' ' & [Payee] & ' ' & 'for' & ' ' & [Description] AS Det, DateDue.AccTypeID1 FROM DateDue WHERE (((DateDue.DateDue)<=Date()) AND ((DateDue.PaymentAmount)<>0) AND (Not (DateDue.AccTypeID1) Is Null) AND ((DateDue.Appended)=No));"

 

[agehoops]

Alright cool thanks, i'll see if I can get it doing what I want it to do. Thanks for the help