Repeat a scheduled event

[A & K Merchandise]

I am new to coding and therefore don't have much experience.

My problem is this:

I have a date field and other fields within a table.
I want to schedule a certain event but to have it REPEAT either weekly
(every Wednesday) or daily (monday thru friday) until a certain date (Mar
31, 2004 or 1 year or 16 weeks)

What coding should I use or is there a template or macro that can do this
for me?

Any help on this matter would be appreciated.
You could also email me the info to: ak****@charter.net

Thank you in advance for the help.

[Pieter Linden]

"A & K Merchandise" <ak****@charter.net> wrote in message news:<vq************@corp.supernews.com>...

I am new to coding and therefore don't have much experience.

My problem is this:

I have a date field and other fields within a table.
I want to schedule a certain event but to have it REPEAT either weekly
(every Wednesday) or daily (monday thru friday) until a certain date (Mar
31, 2004 or 1 year or 16 weeks)

What coding should I use or is there a template or macro that can do this
for me?

The only way I can think of doing it is to write a module that will do
it. The problem is that you need to create a looping structure, and
those aren't that easy in macros. The kicker is the "do it only on
weekdays". AFAIK, there's no way to do that without code.

[Pieter Linden]

Ok, here's some really ugly code that really should be in a module by
itself, and with proper error handling.

It should really be in a subroutine in a code module, not in code
behind a form, but I didn't do it that way...

Sub ScheduleEvents(ByVal dtmStartFirst As date. ByVal dtmFinishFirst
As Date, ByVal dtmStartLast as Date, ByVal intFrequency as Integer)

so that the logic/processing is totally separate from the form...

Option Compare Database
Option Explicit

Private Sub cmdCreateEvents_Click()
Dim dtmStartFirst As Date
Dim dtmFinishFirst As Date
Dim dtmStartLast As Date
Dim dtmThis As Date

Dim strEvent As String
Dim sngDuration As Single
Dim intFrequency As Integer

Dim dbs As DAO.database
Dim rst As DAO.Recordset

If IsDate(Me.txtStart) And IsDate(Me.txtFinish) Then

intFrequency = CInt(Me.Combo4)
dtmStartFirst = CDate(Me.txtStart)
dtmFinishFirst = CDate(Me.txtFinish)
dtmStartLast = CDate(Me.txtDateLast)

sngDuration = DateDiff("n", dtmStartFirst, dtmFinishFirst)
strEvent = Me.txtEventName

Set dbs = DBEngine(0)(0)
Set rst = dbs.OpenRecordset("tblEvent", dbOpenTable)

dtmThis = dtmStartFirst

Do Until dtmThis > dtmStartLast + 1

If Weekday(dtmThis) <> vbSaturday And Weekday(dtmThis) <>
vbSunday Then
rst.AddNew
rst.Fields("EventStart") = dtmThis
rst.Fields("EventFinish") = DateAdd("n", sngDuration,
dtmThis)
rst.Fields("EventName") = strEvent
rst.Update
End If

'imcrement date
dtmThis = DateAdd("d", intFrequency, dtmThis)

Loop
Else
MsgBox "You must supply a valid start and end date."
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub