As you probably guessed, Mitch, It's not a simple task. There are issues
such as open-ended recurring appointments (theoretically into the future
infinitely), and removing/rescheduling particular appointments in the
series.
At the simplest level, your table might contain fields such as:
StartDateTime Date/Time the date and time of the first appointment.
Freq Number how often it recurs
PeriodTypeID Text frequencey type, e.g. m, d, q, yyyy
EndDate Date/Time when the series ends. Blank for open-ended.
The PeriodTypeID will be a combo offering value expressions for the period
in the DateAdd() function.
Next, you need a counting table, i.e. a table with a record for each number:
0
1
2
etc. up to 4000 or so (how ever many periods into the future you want to
go.) There's code to populate such as table for you at the end of this
article:
http://allenbrowne.com/ser-39.html
Now you create a query that uses both tables. Do not use any join between
the tables in the upper pane of table design. This gives you every possible
combination, which is how you get the dates for the recurring appointments.
You will end up with an expression like this in the Field row of the query:
AppointDate: DateAdd([PeriodTypeID], [Freq], [StartDateTime])
In the query's WHERE clause, limit the results to dates before the EndDate
like this:
WHERE ((EndDate Is Null) OR (DateAdd([PeriodTypeID], [Freq],
[StartDateTime]) <= EndDate))
That's the basic idea. The 4000 handles appointments that occur every day
for the next 10 years. Adjust as needed.
If you need to be able to reschedule/remove individual appointments, create
a table to hold each appointment. Fields:
AppointID which appointment this relates to.
OccurNum the occurrance number
OccurDate when this actually falls.
Use logic like the above to populate this table when a new recurring
appointment is created. The query is essentially identical, but you turn it
into an Append query to it adds records to the table. Primary key will be
AppointID + OccurNum. You can now remove or reschedule specific OccurNum
entries in the series. You can use cascading deletes so if the user deletes
a recurring appointment, all occurances are deleted too.
It starts to get more messy where you need to:
- modify an existing series to a different starting date/time, with or
without modifying existing appointments (including or not including
out-of-sequence (rescheduled) date/times.)
- extend existing recurring appointments beyond the last OccurDate you
allowed for.
- handle the sequence for other persons, who will have their own set of
dates they cannot attend, but do need the same rescheduled appointments.
HTH.
