Recurring Event/Invoice

[mtn]

Hello Everyone,

After searching for a week I found the attached to be close to what I want. The attached only works for weeks and not months or years. I want to be able select an event, indicate the number of occurrence say once a week, month, quater or year starting from a particular day.

If for example I select 01/08/2010 as the starting date of an event which I want to repeat once a month on Fridays for the next four months, my ending date will be 01/12/2010.

Once I press the save button, I want the code to be able to write into table the only four dates that meets the criteria above. The same will happen when I select week, quarter or year. Once I get this working I am confident I can modify the code so that I can use it for recurring invoices. I already have on my database, code to duplicate an invoice but I want to be able to generate recurring invoices too.

Any help would be greatly appreciated please.

 

[MarkK]

- I think for each type of cycle you want to be able to calculate you're going to need to have a UI to collect the specifics for that cycle type, and you need to write a little engine that does the math. I recommend you look at the VBA DateDiff() and DateAdd() functions, which will help.
- Your WeekRecur form allows entry of the day the cycle terminates, but what about bi-weekly cycles? If something repeats every four weeks you need another textbox to collect that information.
- For monthly recurrence schemes, note that "day of month" and "weekday x of nth week of month" are completely distinct. Data you need to collect and how you calculate those terminal points are a completely different problem.
- It's commonly easiest to calculate the first day of a period. This being the case, it is sometimes easiest to calculate the last of a period as being the first day of the next period - 1

LastDayOfPeriod1 = FirstDayOfPeriod2 - 1

Hope this helps.

 

[gemma-the-husky]

you can do this with a function. you need a case of some sort within the function

function createevents(startdate as date, enddate as date, interval as long)
dim activedate as date

activedate=startdate
while activedate<= enddate
   'sql statement to insert record
   activedate=startdate+interval
wend

end function

now this works for a fixed date interval (eg 7 days, 14 days)
if you want to use it to add months/quarters, then you possibly need additional arguments, and testing within the loop.

but basically the same thing. keep incrementing the activedate, until you reach the end of the time period.

 

[mtn]

I have been able to sort this out and the code works well now. I can create a recurring schedules based on the user input on my recurring form. I have another code that checks the recurring table to see if any scheduled invoice date is <= today. If any is true, it calls the Function CreateRecurringInvoice. Invoices are automatically created for them and mark as created.