Create Recurring Events

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:
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 ?
 
sherlocked said:
Sure, sorry to be a pain in the a**. I truly appreciate your help.

Attached for your review. Thanks :)
Click to expand...
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

Last edited:
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.
 
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.


4HSm2t3X7wWZMAAAAASUVORK5CYII=
 

Attachments

  • Times.png
    Times.png
    6.5 KB · Views: 139
theDBguy said:
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.
Click to expand...

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. :)
 
Let us know how it goes...
 
theDBguy said:
Let us know how it goes...
Click to expand...

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. :)
 
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:
Please repost your database, and I'll take a look.
 
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.
 
haha yeah, that's just my sneaky way of keeping the form neat & trim :)
 
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
 
Made this change. Now am getting an "invalid procedure call or argument" error here:

If DatePart("mm", CounterBack) = DatePart("mm", Me.cboStart) Then
 
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
 
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,928
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

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