Create Recurring Events (1 Viewer)

Solo712

Solo712

Registered User.
Local time
Today, 02:16
Joined
Oct 19, 2012
Messages
828
sherlocked said:
Made this change. Now am getting an "invalid procedure call or argument" error here:

If DatePart("mm", CounterBack) = DatePart("mm", Me.cboStart) Then
Click to expand...

Should be:
Code: 
[B][I] If DatePart("m", CounterBack) = DatePart("m", Me.cboStart) Then[/I][/B]

Best,
Jiri
 
S

sherlocked

Registered User.
Local time
Yesterday, 23:16
Joined
Sep 22, 2014
Messages
125
theDBguy said:
Hi. I created the following helper function for you. I am hoping it can give you a nudge to proceed with your project.
Code: 
Public Function GetNthWeekdayOfMonth(DayIndex As Long, DayOfWeek As Long, DateOfMonth As Date) As Variant
'thedbguy@gmail.com
'2/4/2019
'Returns the nth weekday of the month
'Parameters: DayIndex = 1 to 5, DayOfWeek = 1 to 7 (Sun to Sat), DateOfMonth = any date within the month of interest
'Usage: To return the 4th Monday in February
'GetNthWeekdayOfMonth(4,2,#2/14/2019#)
'Returns: 2/25/2019
'Note: A day index of zero (0) or less will return the 1st date
'and an index of 5 or greater will return the "last" date with a matching weekday

Dim dteNthDate As Date

'set up
If DayIndex <= 4 Then
    dteNthDate = DateSerial(Year(DateOfMonth), Month(DateOfMonth), 1)
Else
    dteNthDate = DateSerial(Year(DateOfMonth), Month(DateOfMonth) + 1, 0)
End If

'get first ocurrence of date
Do While Weekday(dteNthDate) <> DayOfWeek
    If DayIndex <= 4 Then
        dteNthDate = DateAdd("d", 1, dteNthDate)
    Else
        dteNthDate = DateAdd("d", -1, dteNthDate)
    End If
Loop

'get nth date
If DayIndex >= 1 And DayIndex <= 4 Then
    dteNthDate = DateAdd("ww", DayIndex - 1, dteNthDate)
End If

GetNthWeekdayOfMonth = dteNthDate

End Function
Click to expand...

I appreciate this, and the time you took to write it.

I have Googled until I'm blue in the face. I am a novice at writing code and therefore most of the threads I've reviewed, while full of information, either don't address my needs directly or only address the THEORY behind how to do this, but nothing practical is outlined. I simply do not have the skills or knowledge to write this out from theory alone.

I have come up with this, based on what you wrote; however I'm getting a data type mismatch at the bolded line. Thoughts?

Code: 
NextDate = GetNthWeekdayOfMonth(Me.cmboDays, DatePart("m", Me.cboStart), Me.cboStart)
            
            Do While dteEvent <= EndDate
                        .AddNew
                        !MeetingType = Me.cmboMeetingType
                        !Description = Me.txtMeetingName
                        !MeetingDate = dteEvent
                        !MeetingStartTime = Me.cmboStartTime
                        !MeetingEndTime = Me.cmboEndTime
                        !Location = Me.cmboLocation
                        !Video = Me.chkAV
                        !CreatedBy = Me.txtCreatedBy
                        !CreatedDate = Me.txtCreatedDate
                        !Comments = Me.txtComments
                        !Host = Me.cmboHost
                        !Recurrence = Me.cboInterval
                    .Update
                    dteEvent = NextDate
            Loop
 
Last edited:
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,467
Hi. I hear your frustration. What you're trying to do is not for beginners. What I posted earlier is just a "helper" function. For example, let's say the user entered the following information on your form:

Day of week: Monday
How often? Every 2nd
Start date: 2/6/2019
End date: 12/31/2019

So, the helper function can help you find out the first date they requested. By running the following code:
Code: 
GetNthWeekdayOfMonth(2,2,#2/6/2019#)
You should get 2/11/2019. That would be your first record. So, in your code to create the next records, you would simply step through them somewhat like the following:
1. Get the first date using the helper function
2. Add one month to it until you reach the end date entered by the user.

Or, in cases where the user wanted the fifth or last week, then you can use the helper function for each month from the start date to the end date.
 
S

sherlocked

Registered User.
Local time
Yesterday, 23:16
Joined
Sep 22, 2014
Messages
125
Good to know it's not just that I'm incapable of understanding! I am feeling rather challenged.

Here's what I've done using your helper function. However, I am getting a "type mismatch" error at the line in red below. I think it's because the last input is set as VARIANT but I'm trying to pass a variable to it. Is this just a syntax problem?

Code: 
            Do While dteEvent <= EndDate
                        .AddNew
                        !MeetingType = Me.cmboMeetingType
                        !Description = Me.txtMeetingName
                        !MeetingDate = dteEvent
                        !MeetingStartTime = Me.cmboStartTime
                        !MeetingEndTime = Me.cmboEndTime
                        !Location = Me.cmboLocation
                        !Video = Me.chkAV
                        !CreatedBy = Me.txtCreatedBy
                        !CreatedDate = Me.txtCreatedDate
                        !Comments = Me.txtComments
                        !Host = Me.cmboHost
                        !Recurrence = Me.cboInterval
                    .Update
              [B]  [COLOR="DarkRed"]WhatComesNext = GetNthWeekdayOfMonth(Me.cmboDays, Me.cboDay, "# & dteDate & #")[/COLOR][/B]
                    dteEvent = WhatComesNext
            Loop
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 07:16
Joined
Sep 21, 2011
Messages
14,265
Try

Code: 
WhatComesNext = GetNthWeekdayOfMonth(Me.cmboDays, Me.cboDay, "#" & dteDate & "#")
 
S

sherlocked

Registered User.
Local time
Yesterday, 23:16
Joined
Sep 22, 2014
Messages
125
AMAZING!! SUCCESS!! I fiddled more. This works!!!!

Thank you, thank you thank you a million times thank you for your patience and for sticking with it! Wish I could hug you!

Code: 
            Do While dteEvent <= EndDate
                        .AddNew
                        !MeetingType = Me.cmboMeetingType
                        !Description = Me.txtMeetingName
                        !MeetingDate = dteEvent
                        !MeetingStartTime = Me.cmboStartTime
                        !MeetingEndTime = Me.cmboEndTime
                        !Location = Me.cmboLocation
                        !Video = Me.chkAV
                        !CreatedBy = Me.txtCreatedBy
                        !CreatedDate = Me.txtCreatedDate
                        !Comments = Me.txtComments
                        !Host = Me.cmboHost
                        !Recurrence = Me.cboInterval
                    .Update
                    NextMonth = dteEvent + 28
                    WhatComesNext = GetNthWeekdayOfMonth(Me.cmboDays, Me.cboDay, NextMonth)
                    dteEvent = WhatComesNext
            Loop
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,467
Hi. I agree with Gasman. Give it a try or you could do it this way too, somewhere in there.
Code: 
dteEvent=GetNthWeekdayOfMonth(Me.cmboDays, Me.cboDay, DateAdd("m",1,dteEvent))
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,467
Hi. Too slow this morning, I guess. Glad to hear you got it sorted out. Good luck with your project.
 
You must log in or register to reply here.

Similar threads

A
TSBAPI_GetSaveFileName works in 32 BIT, but not in 64 BIT
Replies
1
Views
1,803
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom