Batch Generation of Records

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I am putting together a database to track community events for our
local mayor's office. It works pretty well, but entering regular
meetings is more time-consuming than it needs to be. I would like to
create a form which will easily generate recurring meetings.

The form will need to have several fields and combo boxes into which
the user will type all the meeting information except the date (i.e.
EventTitle, LocationID, etc.)

There will also be some mechanism for generating a list of dates (i.e.
user selects "second" from one combo box and "Wednesday" from another
combo box and types "2005" in a field and then has a choice of the
twelve dates of the second Wednesday of each month). I would then like
the user to be able to deselect some dates (for groups, for instance,
that take the summer months off). I have figured out the formulas for
how to generate these values into separate fields for each month, but
if they could all get into a multi-select list box, that would be more
slick.

Finally, the user should push a "generate event records" button which
will create multiple records in the Events table, each having a
separate date, but the same EventTitle, LocationID, etc.

How do I do this? Help on any part of this would be really welcome.

Cheers,
Diane
 
This is one of those cases that if you CAN just store the information about
the meeting, you are FAR better off.

So, why not just store:

Start Date
EndDate (blank = no end date)
Repeat: Daily
Every Week
Every Month
Every Year (great for birthdays)

So, to put in a persons birthday, or a anniversary day, or some civic
holidays, you only need to enter ONE record.

What happens if you put int

StartDate June 18th, 2004
EndDate Auguest 30, 2004
Repeat Daily

Ok...you now go, opps...I only meant for this to be one month.....

How the heck are you going to delete all those records? You an make a
instant mess of the data. Further, to fix the above, I just go:

StartDate June 18th, 2004
EndDate July 18th, 2004
Repeat Daily

In your case, if you want to change/edit the end date..you can't just change
the end date..but you now have a whole whack of code to try and delete all
those extra end date records. Further, with the above design that stores
just the date range, then you would NOT be asking how to write out a whole
whack of records..would you?

What if I change my mind, and want to have the daily event start at June
1st? Again, your design will have a bunch of code now that has to figure
out...hum..do I need to add a bunch more records...or delete a bunch more.

So, with the above design, you can easily add birthdays, or repeating
appointments like anniversaries.and you only have to add ONE record. Even
more cool is that if you need to delete a appointment, you just delete the
one record!

Of course, while the above approach is VERY much more flexible then a design
that simply writes out tons of records for each appointment, the skill
required to implement the above is however increased (since now your
reporting systems need to interpret those records,and "show"
results/bookings for particular dates. On the other hand, the skill to
develop a simple flat file system vs a relational database is also higher,
but the results are more worth it in the end. So, often during development,
something that takes more skill will result in a more flexible designs.

Also, before and coding gets done, any reason why something like outlook is
not being used? It has a great daily planner, and a great calendar printout.
I seen doctors,and even Lawyers use Outlook to schedule all their
appointments just using outlook. It also have great abilities in terms of
repeating events (like the last Thursday of each month).

However, if you MUST write out records...you can simply open a table in
code..and start wring out the records (how you are going to make some code
to extend, delete, shorten, lengthen the time periods for these events is
going to cost you a lot of development time, or your resulting application
will NOT be very user friendly in how appointments are changed).

On a simple start, end date, would could write out records like:


dim dtStartDate as date
dim dtEndDate as date
dim lngDatePtr as long
dim rstEvents as dao.RecordSet

dtStarteDate = ??
dtEndDate = ??
EventText = "Daily meeting"

set rstEvents = currentDb.OpenRecordSet("tblEvents")

for lngDatePtr = dtStartDate to dtEndDate
rstEvents.AddNew
rstEvents!Date = lngDatePtr
rstEvents!EventText = EventText
rstEvents!EventsID = ??
rstEvents.Update
next lngDatePtr

rstEvents.Close
set rstEvents = nothing

The above is not complete..but simply gives you an idea how you can process
a start/end date,and write out records to a table via code.

Like I said, I would have used outlook, or JUST stored the start/end date in
my designs and not had to write out records all over the place. To get an
idea as to how this works, play around with making appointments in Outlook
to get a feel for a system that stores just the time period, and does not
write out records for each occurrence of the event. (so, even if you do NOT
use Outlook...play with it to get some good ideas).
 
Thanks, Albert, that is very helpful. And yes, I see your points about
Outlook and using one record rather than multiple ones. Since I am not
an accomplished programmer, and only have a limited time to complete
this project, however, I opted for a system that I could mostly
accomplish (with a little help from the wise ones on Google).

The code that you offer for adding records will work wonderfully ...
now, does anyone have any suggestions about populating a multi-select
list box with a year's worth of 1st Wednesday dates?
 
Thanks, Albert, that is very helpful. And yes, I see your points about
Outlook and using one record rather than multiple ones. Since I am not
an accomplished programmer, and only have a limited time to complete
this project, however, I opted for a system that I could mostly
accomplish (with a little help from the wise ones on Google).
Click to expand...

Like I say, perhaps just the ability to generate some records might be all
you need here.
The code that you offer for adding records will work wonderfully ...
now, does anyone have any suggestions about populating a multi-select
list box with a year's worth of 1st Wednesday dates?
Click to expand...

Sure, try the following:

This assumes you place a un-bound listbox on your screen, and set the row
source type to value list.

Dim dtStart As Date
Dim dtDatePoint As Date
Dim strDateList As String
Dim intWeek As Integer

dtStart = Date

dtDatePoint = dtStart + (4 - Weekday(dtStart))

For intWeek = 1 To 52
If strDateList <> "" Then
strDateList = strDateList & ";"
End If
strDateList = strDateList & dtDatePoint
dtDatePoint = DateAdd("ww", 1, dtDatePoint)
Next intWeek

Me.List16.RowSource = strDateList
 
Back
Top