Recurring Events Count Off - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Recurring Events Count Off

James Hopkins

16 months ago

Hey Richard and the Crew, Happy Year's. I have created a Function that Generate Dates by the User selection of the Interval of the Event. The problem I am running into is when the Function generates the Dates. They are not starting on the Start Date entered and continuing to the create the Dates needed between the Start and End Dates.

James Hopkins OP @Reply

16 months ago

Here's the Function's Code:

Details

Private Sub GenerateFutureEvents()



    Dim rs As DAO.Recordset

     

    Dim dailyInterval As Integer

    Dim weeklyInterval As Integer

    Dim WeekdayName As String

    Dim weekdayNumber As Integer

    Dim monthlyInterval As Integer

    Dim monthlyOnInterval As Integer

    Dim onTheInterval As Integer

    Dim onTheWeekdayName As String

    Dim MonthName As Integer

    Dim monthWeekdayNumber As Integer

    Dim YearMonth As Integer

    Dim countEvents As Integer

    Dim schFreq As String

    Dim StartDate As Date

    Dim EndDate As Date

    Dim i As Integer

    Dim futureDate As Date



    ' Get User input

    dailyInterval = CboScheduleEveryifDaily.Column(2)

    weeklyInterval = CboScheduleEveryifWeekly.Column(2)

    WeekdayName = CboScheduleOnWeekday.Column(1)

    weekdayNumber = CboScheduleOnWeekdayNumber.Column(2)

    monthlyInterval = CboScheduleEveryifMonthly.Column(2)

    monthlyOnInterval = CboScheduleMonthEveryifMonthly.Column(2)

    onTheInterval = CboScheduleEveryWeekdayOn.Column(2)

    onTheWeekdayName = CboScheduleWeekday.Column(1)

    MonthName = CboScheduleOnMonth.Column(2)

    monthWeekdayNumber = CboScheduleOnMonthWeekdayNumber.Column(2)

    YearMonth = CboScheduleYearOnMonth.Column(2)

    countEvents = CountofEvent

    StartDate = ScheduleStartDate

    EndDate = ScheduleEndDate

    schFreq = CboHowOthen.Column(1)



    For i = 1 To countEvents - 1

        Select Case schFreq

            Case "Daily"

                futureDate = DateAdd("d", (i - 1) * dailyInterval, StartDate)

            Case "Weekly"

                futureDate = GetNextWeeklyEvent (StartDate, i, weeklyInterval, WeekdayName)

            Case "Monthly"

                If ChkOn1 = True Then

                    futureDate = GetNextMonthlyEventChkOn1(StartDate, i, weekdayNumber, monthlyInterval)

                ElseIf ChkOn2 = True Then

                    futureDate = GetNextMonthlyEventChkOn2(StartDate, i, onTheInterval, onTheWeekdayName, monthlyOnInterval)

                End If

            Case "Yearly"

                If ChkOn1 = True Then

                    futureDate = GetNextYearlyEventChkOn1(StartDate, i, MonthName, monthWeekdayNumber)

                ElseIf ChkOn2 = True Then

                    futureDate = GetNextYearlyEventChkOn2(StartDate, i, YearMonth, onTheInterval, onTheWeekdayName)

                End If

        End Select



        ' Open the Schedule Table

        Set rs = CurrentDb.OpenRecordset("ScheduleT")



        ' Add the Future Event to the Table

        rs.AddNew

        rs!ScheduleTableField = EventFormField

rs!ScheduleTableField = EventFormField

‘ Etc.



'Add the Future Event Form Data to the Product(s) to Detail Table

        rsDetail!ScheduleID = rs!ScheduleID

        rsDetail!ScheduleTableField = EventFormField

                      

        'Add the Future Event Form to the Service(s) to Detail Table

        rsDetail!ScheduleID = rs!ScheduleID

        rsDetail!ScheduleTableField = EventFormField

rsDetail!ScheduleTableField = EventFormField

    

        'Update Data to Schedule Table and Detail Table

        rs.Update

        rsDetail.Update

    Next i

    

    ' Close the Recordset

    rs.Close

    rsDetail.Close



' Set Recordsets to Nothing

    Set rs = Nothing

    Set rsDetail = Nothing



End Sub

James Hopkins OP @Reply

16 months ago

The Calculating the Monthly Interval same to not return the correct Dates for the Future Dates. The Function is not calculating the Number of Intervals or the Weekday Number and Weekday.

Details

Function GetNextWeeklyMaintenance(StartDate As Date, index As Integer, weeklyInterval As Integer, WeekdayName As String) As Date



    Dim futureDate As Date

    Dim DaysToAdd As Integer



    DaysToAdd = (index - 1) * 7 * weeklyInterval

    futureDate = DateAdd("d", DaysToAdd, StartDate)



    While WeekDay(futureDate) <> GetWeekdayNumber(WeekdayName)

        futureDate = DateAdd("d", 1, futureDate)

    Wend



    GetNextWeeklyMaintenance = futureDate



End Function

Details

Function GetNextMonthlyMaintenanceChkOn1(StartDate As Date, index As Integer, weekdayNumber As Integer, monthlyInterval As Integer) As Date



    Dim futureDate As Date

    Dim DaysToAdd As Integer



    DaysToAdd = (index - 1) * 30 * monthlyInterval

    futureDate = DateAdd("d", DaysToAdd, StartDate)



    While WeekDay(futureDate) <> weekdayNumber

        futureDate = DateAdd("d", 1, futureDate)

    Wend



    GetNextMonthlyMaintenanceChkOn1 = futureDate



End Function

Details

Function GetNextYearlyMaintenanceChkOn1(StartDate As Date, index As Integer, MonthName As Integer, monthWeekdayNumber As Integer) As Date



    Dim futureDate As Date

    Dim DaysToAdd As Integer



    DaysToAdd = (index - 1) * 365

    futureDate = DateSerial(Year(StartDate), MonthName, 1)



    While WeekDay(futureDate) <> monthWeekdayNumber

        futureDate = DateAdd("d", 1, futureDate)

    Wend



    GetNextYearlyMaintenanceChkOn1 = futureDate



End Function

Details

Function GetWeekdayNumber(WeekdayName As String) As Integer



    Select Case WeekdayName

        Case "Sunday": GetWeekdayNumber = vbSunday

        Case "Monday": GetWeekdayNumber = vbMonday

        Case "Tuesday": GetWeekdayNumber = vbTuesday

        Case "Wednesday": GetWeekdayNumber = vbWednesday

        Case "Thursday": GetWeekdayNumber = vbThursday

        Case "Friday": GetWeekdayNumber = vbFriday

        Case "Saturday": GetWeekdayNumber = vbSaturday

    End Select



End Function

	James Hopkins OP @Reply 16 months ago
Hey Guys I think I figured out why my Code was not Generating Dates correct. But I have a question about how if the I select "1st of Every 2 Months" and the Count of Events is "3". I get "2/1/25, 4/6/25, and 6/1/25". Why am I get this off date of 4/6/25. How to calculate this correctly.

	Alex Hedley @Reply 16 months ago
Thought about using Rubberduck to write some Unit Tests?

	James Hopkins OP @Reply 16 months ago
Hey Alex, I will try to use Rubberduck to figure out what I missing up at.

	Alex Hedley @Reply 16 months ago
Sure there was a series on Week in Review a while back. Maybe it was from https://www.accessjumpstart.com/

James Hopkins OP @Reply

16 months ago

Hey Guys, I ran into an RunTime Error '5' with the DateAdd when use this Function:

Details



Function GetNextYearlyMeetingChkOn1(StartDate As Date, index As Integer, MonthName As Integer, monthWeekdayNumber As Integer) As Date



    Dim futureDate As Date

    Dim DaysToAdd As Integer



    DaysToAdd = (index - 1) * 365

    futureDate = DateSerial(Year(StartDate), MonthName, 1)



    While WeekDay(futureDate) <> monthWeekdayNumber

        futureDate = DateAdd("d", 1, futureDate)

    Wend



    GetNextYearlyMeetingChkOn1 = futureDate



End Function

	Sami Shamma @Reply 16 months ago
what values you have in the variables: index, StartDate, MonthName, monthWeekdayNumber?

	James Hopkins OP @Reply 16 months ago
index As Integer StartDate As Date = StartDate on the Form Dim MonthName As Integer MonthName = CboScheduleOnMonth.Column(2) Dim monthWeekdayNumber As Integer monthWeekdayNumber = CboScheduleOnMonthWeekdayNumber.Column(2)

	Sami Shamma @Reply 16 months ago
Actual values when the error occurred.

	Kevin Robertson @Reply 16 months ago
Shouldn't MonthName be a String? What are the columns in CboScheduleOnMonth?

	James Hopkins OP @Reply 16 months ago
ID - Column(0), Month Name - Column(1), & Month Number - Column(2)

	James Hopkins OP @Reply 15 months ago
Kevin, should I create a Function to find the Month Number, instead of use the Number in the CboScheduleOnMonth Combo Box?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View