Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-28-2019, 11:41 AM   #16
sherlocked
Newly Registered User
 
Join Date: Sep 2014
Posts: 125
Thanks: 1
Thanked 0 Times in 0 Posts
sherlocked is on a distinguished road
Re: Create Recurring Events

Thanks. here's a snippet of how I used it - however the code now hangs up forever and I must CNTRL+BREAK in order to stop it.

Code:
Case "Bi-Weekly"

strSql = "SELECT tblWeeks.DayofWeek, tblWeeks.SchedDate FROM tblWeeks " _
    & "WHERE tblWeeks.DayofWeek = '" & Form_frmDataEntry.txtDayofWeek & "' " _
    & "AND tblWeeks.SchedDate > #" & Form_frmDataEntry.txtStartDate & "# OR tblWeeks.SchedDate = #" & Form_frmDataEntry.txtStartDate & "#"

Set rsIn = CurrentDb.OpenRecordset(strSql)
Set rsOut = CurrentDb.OpenRecordset("tblMeeting", dbOpenDynaset)

rsIn.MoveFirst

Do Until rsIn!SchedDate = DateAdd("d", 14, Form_frmDataEntry.txtStartDate)
rsOut.AddNew
rsOut!MeetingType = Form_frmDataEntry.txtMeetingName
rsOut!MeetingStart = rsIn!SchedDate
rsOut.Update
Loop

sherlocked is offline   Reply With Quote
Old 01-28-2019, 11:49 AM   #17
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Create Recurring Events

Don't you need a rsIn.MoveNext as well ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 01-28-2019, 11:55 AM   #18
sherlocked
Newly Registered User
 
Join Date: Sep 2014
Posts: 125
Thanks: 1
Thanked 0 Times in 0 Posts
sherlocked is on a distinguished road
Re: Create Recurring Events

Sure do! This time it created about 1.4 million records with the exact same date of 7/15/19. I just don't know where I'm going wrong here!

sherlocked is offline   Reply With Quote
Old 01-28-2019, 12:00 PM   #19
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 11
Thanked 262 Times in 256 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Create Recurring Events

Hi. Gasman was saying you forgot to add the .MoveNext part in your code. It should go something like this:
Code:
Do Until loop here...
    .AddNew here
    .Other stuff here
    .Update here
    .MoveNext here
Loop
Without the .MoveNext command, the loop will just stay on the same record and never stop because it'll never hit the .EOF.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 01-28-2019, 12:07 PM   #20
sherlocked
Newly Registered User
 
Join Date: Sep 2014
Posts: 125
Thanks: 1
Thanked 0 Times in 0 Posts
sherlocked is on a distinguished road
Re: Create Recurring Events

Fiddled more. Got it to actually create some records with the below. However it appears to have spontaneously, for no reason I can determine, started creating records every THREE weeks instead after March 4th. I am so confused, lol!

Here's my code for bi-weekly meetings now below - and here are the dates that were created:

Did not create 1/28/19 as it should start with, not sure why
2/11/2019 - great
3/4/2019 - great
3/25/2019 - skipped to three weeks!
4/15/2019
5/6/2019
5/27/2019
6/17/2019
7/8/2019
7/29/2019
8/19/2019
9/9/2019
9/30/2019
10/21/2019
11/11/2019
12/2/2019
12/23/2019
1/13/2020
2/3/2020
2/24/2020
3/16/2020
4/6/2020
4/27/2020
5/18/2020
6/8/2020
6/29/2020
7/20/2020

Code:
Case "Bi-Weekly"

strSql = "SELECT tblWeeks.DayofWeek, tblWeeks.SchedDate FROM tblWeeks " _
    & "WHERE tblWeeks.DayofWeek = '" & Form_frmDataEntry.txtDayofWeek & "' " _
    & "AND tblWeeks.SchedDate > #" & Form_frmDataEntry.txtStartDate & "# OR tblWeeks.SchedDate = #" & Form_frmDataEntry.txtStartDate & "#"

Set rsIn = CurrentDb.OpenRecordset(strSql)
Set rsOut = CurrentDb.OpenRecordset("tblMeeting", dbOpenDynaset)

rsIn.MoveFirst

For x = 1 To 26
rsOut.AddNew
rsOut!MeetingType = Form_frmDataEntry.txtMeetingName
rsOut!MeetingStart = DateAdd("d", x * 14, rsIn!SchedDate)
rsOut.Update
rsIn.MoveNext
Next
sherlocked is offline   Reply With Quote
Old 01-28-2019, 12:08 PM   #21
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 11
Thanked 262 Times in 256 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: 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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 01-28-2019 at 12:20 PM.
theDBguy is offline   Reply With Quote
Old 01-28-2019, 12:17 PM   #22
sherlocked
Newly Registered User
 
Join Date: Sep 2014
Posts: 125
Thanks: 1
Thanked 0 Times in 0 Posts
sherlocked is on a distinguished road
Re: Create Recurring Events

Sure, sorry to be a pain in the a**. I truly appreciate your help.

Attached for your review. Thanks
Attached Files
File Type: accdb MeetingScheduler2 - BlankCopy.accdb (1.25 MB, 15 views)

sherlocked is offline   Reply With Quote
Old 01-28-2019, 12:42 PM   #23
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Create Recurring Events

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 ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 01-28-2019, 01:25 PM   #24
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 11
Thanked 262 Times in 256 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Create Recurring Events

Quote:
Originally Posted by sherlocked View Post
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.
Attached Files
File Type: zip MeetingScheduler2 - BlankCopy.zip (41.4 KB, 19 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 01-28-2019 at 01:34 PM.
theDBguy is offline   Reply With Quote
Old 01-29-2019, 03:58 PM   #25
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,029
Thanks: 3
Thanked 441 Times in 434 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Create Recurring Events

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 is offline   Reply With Quote
Old 01-29-2019, 04:13 PM   #26
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,029
Thanks: 3
Thanked 441 Times in 434 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Create Recurring Events

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.


Attached Images
File Type: png Times.png (6.5 KB, 19 views)
Cronk is offline   Reply With Quote
Old 01-31-2019, 12:35 PM   #27
sherlocked
Newly Registered User
 
Join Date: Sep 2014
Posts: 125
Thanks: 1
Thanked 0 Times in 0 Posts
sherlocked is on a distinguished road
Re: Create Recurring Events

Quote:
Originally Posted by theDBguy View Post
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.
sherlocked is offline   Reply With Quote
Old 01-31-2019, 08:00 PM   #28
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 11
Thanked 262 Times in 256 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Create Recurring Events

Let us know how it goes...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-04-2019, 08:01 AM   #29
sherlocked
Newly Registered User
 
Join Date: Sep 2014
Posts: 125
Thanks: 1
Thanked 0 Times in 0 Posts
sherlocked is on a distinguished road
Re: Create Recurring Events

Quote:
Originally Posted by theDBguy View Post
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 is offline   Reply With Quote
Old 02-04-2019, 08:10 AM   #30
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,144
Thanks: 11
Thanked 262 Times in 256 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Create Recurring Events

Hi. Take a look at this other discussion to see if you can get some ideas from it. Cheers!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Recurring events stevekos07 General 1 03-29-2017 07:53 PM
Recurring Events via Allen Browne PaulWilson Queries 8 06-24-2014 06:52 PM
Help with Allen Browne's Recurring Events - Exclude Weekends Graceland General 1 02-20-2014 11:35 PM
Help with Allen Browne's Recurring Events Elpidoforos General 8 01-31-2014 05:48 AM
Tip Recurring events + Cashflow eurochildus General 0 02-14-2012 02:07 AM




All times are GMT -8. The time now is 01:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World