Create Recurring Events (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Wouldn't you rather post a sample copy of your db so we can take a look? I am still confused why you're using tblWeeks.
 
Last edited:

sherlocked

Registered User.
Local time
Today, 14:04
Joined
Sep 22, 2014
Messages
125
Sure, sorry to be a pain in the a**. I truly appreciate your help.

Attached for your review. Thanks :)
 

Attachments

  • MeetingScheduler2 - BlankCopy.accdb
    1.3 MB · Views: 111

Gasman

Enthusiastic Amateur
Local time
Today, 21:04
Joined
Sep 21, 2011
Messages
14,047
I think you would need to check for EOF as well as the date being exceeded after including the MoveNext.

What you have now is x*14 and x is 1 to 26 ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
Sure, sorry to be a pain in the a**. I truly appreciate your help.

Attached for your review. Thanks :)
Hi. Please see the attached modified version of your database. Open Form1 and play with it. I only did the Daily and Bi-Weekly options. I am hoping you'll be able to adapt what I did to the other intervals. Besides, you might decide this is not what you wanted to do, so I didn't want to change too much. Cheers!

Edit: By the way, I assumed you didn't want to create a meeting on a Holiday, so I excluded those.
 

Attachments

  • MeetingScheduler2 - BlankCopy.zip
    41.4 KB · Views: 112
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 08:04
Joined
Jul 4, 2013
Messages
2,770
I don't see any need for tblWeeks except to record public holidays. Making Start Date a text box with a calendar selection, then selecting any particular start also selects the Day of Week.


Also, is there a difference between 4 weeks and monthly. And does monthly mean the same numeric day each month ie 15th of the month, or the third Tuesday?


And what is the purpose of the MeetingEnd field? Seems to me that MeetingStart/End are times with another field MeetingDate required.
 

Cronk

Registered User.
Local time
Tomorrow, 08:04
Joined
Jul 4, 2013
Messages
2,770
Here is a screen shot of an input form for, in this case, sports fields bookings, with provision to exclude any number of date ranges for say school holidays or tournament events.


Start/End times are on a separate tab because there is more detail such as, if lighting is required, times for lighting start/finish which may be different to the playing times, as well as the level of lighting.


 

Attachments

  • Times.png
    Times.png
    6.5 KB · Views: 102

sherlocked

Registered User.
Local time
Today, 14:04
Joined
Sep 22, 2014
Messages
125
Hi. Please see the attached modified version of your database. Open Form1 and play with it. I only did the Daily and Bi-Weekly options. I am hoping you'll be able to adapt what I did to the other intervals. Besides, you might decide this is not what you wanted to do, so I didn't want to change too much. Cheers!

Edit: By the way, I assumed you didn't want to create a meeting on a Holiday, so I excluded those.

Appreciate this, thanks. I am just getting back in the office after a few days travel. I'll poke at it and see what shakes out. :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
Let us know how it goes...
 

sherlocked

Registered User.
Local time
Today, 14:04
Joined
Sep 22, 2014
Messages
125
Let us know how it goes...

This works FABULOUSLY for Daily, Weekly & Bi-Weekly appointments. You are a godsend.

What I am up against now is Monthly appointments. For obvious reasons this same process doesn't quite work as each month has either 30 or 31 days, so saying the first Monday of each month works fine until a month with 5 weeks and then it all goes out the window.

To replace this I've created a dropdown on the form that lets the user choose which day of the month to have the meeting occur (instead of the day of the week). What I can't figure out is how to tell the database - ok, this person wants an appointment each month on the 7th day, starting 1/7/19 let's say. I am stumped.

I messed around a little with the idea of looking at the start date and trying to add one to it each time the record is created but got an error stating this wasn't possible the way I've written it.

Code:
        Case "Monthly"
            Do While dteEvent <= EndDate
                    .AddNew
                        !MeetingType = Me.cmboMeetingType
                        !Description = Me.txtMeetingName
                        !MeetingDate = dteEvent
                        !MeetingStartTime = Me.txtStartTime
                        !MeetingEndTime = Me.txtEndTime
                        !Location = Me.cmboLocation
                        !Video = Me.chkAV
                        !CreatedBy = Me.txtCreatedBy
                        !CreatedDate = Me.txtCreatedDate
                        !Comments = Me.txtComments
                        !Host = Me.cmboHost
                    .Update
                    StartYear = DatePart("yyyy", Me.cboStart)
                    Counter = DatePart("mm", !MeetingDate)
                    MonthlyEvent = Counter + 1
                    dteEvent = "# & MonthlyEvent & / me.cmboDays / StartYear #"
            Loop


Thoughts? This is just the bit of your code that I've adapted. The rest I haven't changed. Again, thank you a million times for your help so far. :)
 

sherlocked

Registered User.
Local time
Today, 14:04
Joined
Sep 22, 2014
Messages
125
This is....kinda like reading Greek. I apologize for being such a noob, but I am one. I am taking a shot in the dark here! The below code is what I came up with based on my comprehension (what little there is) of the ideas being suggested. I get a Run Time Error 6: Overflow when trying it :)

[#CODE]Case "Monthly"

WeekCountBack = 1
CounterBack = Me.cboStart - 7
If DatePart("mm", CounterBack) = DatePart("mm", Me.cboStart) Then
WeekCountBack = WeekCountBack + 1
Else
WeekCountBack = WeekCountBack
End If

CounterUp = Me.cboStart + 7
If DatePart("mm", CounterUp) = DatePart("mm", Me.cboStart) Then
WeekCountUp = CounterUp + 7
Else
WeekCountUp = CounterUp - 1
End If

If WeekCountUp <> 0 Then
WeekCountUp = WeekCountUp + 7
Else
NextDate = WeekCountUp
End If

Do While dteEvent <= EndDate
.AddNew
!MeetingType = Me.cmboMeetingType
!Description = Me.txtMeetingName
!MeetingDate = dteEvent
!MeetingStartTime = Me.txtStartTime
!MeetingEndTime = Me.txtEndTime
!Location = Me.cmboLocation
!Video = Me.chkAV
!CreatedBy = Me.txtCreatedBy
!CreatedDate = Me.txtCreatedDate
!Comments = Me.txtComments
!Host = Me.cmboHost
.Update
Loop[/#CODE]
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
Please repost your database, and I'll take a look.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks. I see the form for requesting the recurring event/meeting but I don't see where they could request every first or second or third, etc. day of the month. I thought that's what you wanted. Otherwise, are you just saying one month later but on the same day (+30 or +31 days)?


Edit: Oh, never mind. I see it now, as soon as I selected Monthly from the dropdown.
 

sherlocked

Registered User.
Local time
Today, 14:04
Joined
Sep 22, 2014
Messages
125
haha yeah, that's just my sneaky way of keeping the form neat & trim :)
 

Cronk

Registered User.
Local time
Tomorrow, 08:04
Joined
Jul 4, 2013
Messages
2,770
Change
Code:
Dim CounterBack As Integer
to

Code:
Dim CounterBack As date
For the future, it would help if you indicate on which line any error occurs
 

sherlocked

Registered User.
Local time
Today, 14:04
Joined
Sep 22, 2014
Messages
125
Made this change. Now am getting an "invalid procedure call or argument" error here:

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

theDBguy

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
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
 

Users who are viewing this thread

Top Bottom