Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Recurring Appt on Same Day Each Month

    I am using the code below to add recurring appointments to a database

    It works well when a frequency of 7, 14, or 21 days is selected...

    It adds a recurring entry for every Mon,Tues,etc depending on the start date


    when I select a frequency of 30 days, ift adds the appointment, but the days may be different...

    If my starting date is Wed 2/1/06 and I want to schedule appointments thru 5/31/06 I am getting this...


    Wed 02/01/2006
    Fri 03/03/2006
    Sun 04/02/2006
    Tues 05/02/2006
    Thurs 06/01/2006

    What I really want is to schedule it on the initial start day (Wenesday) in this case each month..

    Any ideas on how to do this are appreciated..

    Thanks - AB


    If Me.Frequency = 7 Then

    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & StartDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")



    Do While iDate < EndDate



    zDate = DateAdd("d", 7, iDate)
    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & zDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")

    iDate = zDate


    Loop


    ElseIf Me.Frequency = 14 Then



    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & StartDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")



    Do While iDate < EndDate



    zDate = DateAdd("d", 14, iDate)
    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & zDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")

    iDate = zDate


    Loop




    ElseIf Me.Frequency = 21 Then



    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & StartDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")



    Do While iDate < EndDate



    zDate = DateAdd("d", 21, iDate)
    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & zDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")

    iDate = zDate


    Loop



    ElseIf Me.Frequency = 30 Then



    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & StartDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")


    Do While iDate < EndDate



    zDate = DateAdd("d", 30, iDate)
    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID, ServiceID) " & _
    " Values(" & Me.ScheduleID & ",#" & zDate & "#," & Me.CrewNo & "," & Me.CustomerID & "," & Me.ServiceID.Column(0) & ")")

    iDate = zDate


    Loop


    End If

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is an example of a way to calculate dates for 30-day appointments and have them recur on the same day of the week. In a loop, the procedure adds a month to the starting date, and then adds days if needed get the date of the target weekday. The code below can be copied into a new module, then while viewing the code, click on the procedure name and then click the "Run" icon to see it work.

    Jerry

    Code:
    Sub Schedule_Appt()
    
    Dim StartDate As Date
    Dim EndDate As Date
    Dim iDate As Date
    Dim counter As Integer
    Dim WkDayNum As String
    
    StartDate = "02/01/2006"
    EndDate = "05/31/2006"
    
    iDate = StartDate
    WkDayNum = Weekday(StartDate)
    counter = 0
    
    Do While iDate < EndDate
        counter = counter + 1
        'add days to iDate until target day-of-week is reached
        While Weekday(iDate) <> WkDayNum
            iDate = DateAdd("d", 1, iDate)
        Wend
        MsgBox WeekdayName(Weekday(iDate)) & " " & iDate
        iDate = DateAdd("m", counter, StartDate)
    Loop
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •