Kirk said:
I'm builiding a crew scheduling database. I need to be able to build a
reoccuring event like the reoccuring appointment function of Outlook. How can
I do it.
I enter one record - with an event name, crew name and date fields. The I
would like add (append?) additional records with the same event name and crew
name but new date field data, each 7 days after the last. I would like to be
able to select the number of addtional records or add until a certain date.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'll assume you're entering this in a form.
What I'd do is have a VBA routine that adds the additional records based
on data on the form. Say you enter the event name, crew name and the
starting date field. Then you have another TextBox that holds the
number of additional event records you want to have "automatically"
produced. You can also have another TextBox that holds the number of
days between events, or you could hard-code it in the VBA routine. I'd
go for the extra TextBox 'cuz ya never know.... There would be a
CommandButton to run the whole thing.
You could also save yourself the work and buy a scheduling program ;-).
Or, use the Outlook scheduler - isn't there a part of it that has a
GANTT chart/schedule feature? I don't use Outlook anymore so I'm not
sure.
If you want to go the VBA route here's a rough routine that adds the new
records. (warning: air code)
Private Sub cmdApply_Click()
dim db as dao.database, rs as dao.recordset
dim dteEvent as date, dteEnd as date
on error goto err_
' check that all required data is present
' --- I'll leave that to you to construct
set db = currentdb
set rs = db.openrecordset("Events") ' the table name
' find out the last date for the last record.
' Use TextBoxes on form:
' txtStartDate = the date of the first event
' txtEvents = number of events to create
' txtDuration = number of days between events
dteEnd = CDate(Me!txtStartDate) + (Me!txtEvents * Me!txtDuration)
' initialize
dteEvent = CDate(Me!txtStartDate)
with rs
do while dteEvent <= dteEnd
.AddNew
!event_name = me!txtEventName
!event_start_date = dteEvent
!event_end_date = dteEvent + (Me!txtDuration-1)
!crew_name = Me!txtCrewName
.Update
' If you have more than one crew on the event you'll
' have to put them all in a separate table (a one-to-many
' relationship on the event_id).
'
' You'd get the event_id (assume an AutoNumber) like this:
'
' .BookMark = .LastModified
' lngEventID = !event_id ' gets the last created event_id
'
' Then use it in another recordset that opens and adds new
' data to the EventCrew table. Make it a subroutine for
' clarity.
' I'll leave that up to you.
' Calculate the next event date
dteEvent = dteEvent + Me!txtDuration
loop
end with
' done, so clean up
rs.close : set rs = nothing
db.close : set db = nothing
exit sub
err_:
' put in some error handling routine
End Sub
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSidUbYechKqOuFEgEQI3ygCfW3OfywjS5XUur7ywt9e2ma+UNNgAnR5Z
RZihmPemE8F7z57ULpXHrLdh
=Cq6B
-----END PGP SIGNATURE-----
