How schedule recurring events in a task table

This is going to look like I'm re-inventing an Outlook calendar.  Anyway, I'm trying to figure out how to create recurring events in a task table via a command button on a form.

On the form are various data entries that I want to copy over to a new record in the table.

But also on the form is:
A checkbox (chkbxRecurring, table field name = Recurring)
A combbox selection which the users selects from if the checkbox was checked (cboFrequency, table field name = cboFrequency)
A textbox which gets populated after a selection has been made in cboFrequency (txtTimesPerDay, yable field name = TimesPerDay)
A textbox (txtFrequencyEndDate, table field name = FrequencyEndDate = Date field)

If the user clicks the commandbutton I want new records to be entered, unless they have already been entered that duplicates various other fields on the form but only if:

chkbxRecurring has been marked true
And enter a recurring date based on the field, cboFrequency.

Here is a snapshot of the records in cboFrequency:

Frequency Descriptions
And stop creating new records on the txtFrequencyEndDate

If anyone has a clue as to how to code this one it'd sure be appreciated.
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Steve,

 There are a multitude of ways to structure this, but it boils down to performing a loop.    Just open a recordset:

  Set rstEvents = CurrentDB().Openrecordset("Select * from tblEvents Where 1=0")

 The 1=0 thing is a sneaky way to get an empty recordset for adding records.

 Then perform the loop.  "The Loop" is where is gets complicated in that, you have lots of different ways to do it.  For example, at it's simplest, you start on the start date, test against the frequency (is it a thrusday?), if so add a record, then increment by one day, and repeat.  Continue until end date is reached.

Or you might implement that as starting with the first Thursday after the start date, save, add 7 days, and repeat until end date is passed.   See what I mean?

My suggestion would be to use a case statement for the frequency in the procedure, and then do each one separately.  Once you've done that, go back and then see if you can optimize based on what you've learned.

The other way to approach this entirely is to look at a date, then decide if any of the tasks land on this date rather than saving records at all.  Some food for thought there.

Jim.
0
SteveL13Author Commented:
I wish I knew where to begin.  Here is a snapshot of the form I'm using to setup the original task:

 Task Form
Here is the revised selections the user can make from the field "Frequency":

Frequency Choices
So when the user clicks the command button labeled [Schedule Recurring], in this case it should add records to tblTasks every Saturday starting with 7/11/2015, the 1st Saturday after the original schedule date of 7/4/2015 and going through 10/22/2015.

I understand using the case statement and in this case I would use case 10 since that is the PK for "Weekly - On Saturday".

What might the code be for the case 10?

--Steve
0
Jeffrey CoachmanMIS LiasonCommented:
See Jim's post.
You still have a lot of things to work out before you get to the code.
For example:
What if today is Monday, and you recur every Monday, do you start the recurrence Today, or Next Monday?
What about leap years and Holidays?

Also it is not clear what "Code" you are looking form...?
Are you saying that you need the complete code, ...or just one part of it, ...or do you just need pseudo code?

I am sure you can work out how to add the records to the table...

JeffCoachman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Some very basic code might look like this:
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte

bytWeekday = 7 'Saturday
dtStartDate = Date
dtLoopDate = dtStartDate
dtEndDate = #10/22/2015#


Do Until dtLoopDate = dtEndDate
    If Weekday(dtLoopDate) = 7 Then
        MsgBox "Add record to table: " & Format(dtLoopDate, "dddd mm dd, yyyy")
    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
Loop

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
But as Jim states,...it might be better to just increment by the week, ...but this will require more foresight on when you start the loop.

This is why you need to think about all the contingencies here...

But I am sure what we have posted here can get you started.
;-)

Jeff
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<What might the code be for the case 10?>>

 As I mentioned in my first comment, certainly you can loop from start to end incrementing one day at a time.

In that case, you'd use Weekday() and decide if the current date was a saturday.  If so, then add a rec.  If not do, nothing.

a good optimization though would be to have a variable increment.  So if you had case 1 or 2, the increment would be 1.  For 3-10, the increment would be 7.    11 would be a little different because that would be specific to the nth day of the month.  12 would be 365.

 But take the simple route first.  Have a date variable for the loop.  Start with the start date and then increment by one each time.  Stop when your > then the end date.

Jim.
0
SteveL13Author Commented:
I am close.  Here is what I have so far.  But I'm getting a "End With without With"  ???

Private Sub cmdScheduleRecurring_Click()

Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte

bytWeekday = 7 'Saturday
dtStartDate = Me.txtTaskWhenSpecificDate + 7
dtLoopDate = dtStartDate
dtEndDate = Me.txtFrequencyEndDate


Do Until dtLoopDate = dtEndDate
    If Weekday(dtLoopDate) = 7 Then
        'MsgBox "Add record to table: " & Format(dtLoopDate, "dddd mm dd, yyyy")
        
Dim RS As DAO.Recordset

    Set RS = CurrentDb.OpenRecordset("Select * from tblTasks where ID = " & Me.txtID.Value)
    With RS
        If .RecordCount = 0 Then
        .AddNew
        !DateCreated = Me.txtDateCreated
        !TaskDescription = "RECURRING EVENT" & " - " & Me.cboTaskDescription
        !Priority = Me.cboPriority
        !ScheduleTask = Me.cboTaskWhenComment
        !TaskWhenSpecificDate = Me.txtReminderDate
        !ReminderDate = Me.txtReminderDate
        !TaskType = Me.cboTaskType
        !ParetoPrincipal = Me.cboParetoPrincipal
        .Update
    End With

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
Loop

End Sub

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
To keep things simple here I will step aside and let you continue on with Jim.

I posted some very basic code.

I am sure Jim can help you fine tune this system

Jeff
0
Jeffrey CoachmanMIS LiasonCommented:
...In your code, ...your "End If", ...is outside the loop...
;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
you are missing an "End If"  after  ".Update"


        .Update
   
     End if

    End With
0
SteveL13Author Commented:
Both offered great advise and knowledge.  I'm awarding this post but am going to post three more that are related.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.