Create multiple records based on date range

[Rats]

I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.

Any suggestions or assistance would be appreciated.

Thankyou

 

[Michael J Ross]

I do something similar and use two queries to achieve this. (I don't create all the records at once, but transfer to main table when due and update next date due on planned table)

Firstly you need an append query to transfer the records from the planned transactions to your main table, I compare the future date to the current date using the Date function, criteria is <=Date().

Secondly i use an update query using DateAdd to update the date in the planned transactions again comparing to the current date using criteria <=Date()

I run both these queries simultanously from a button on a form.

Hope this helps