asked on

How copy record from command button on a form if case = 2

The following code worked for what I need it to do if case = 1 (every day). Here are the possibilities:

But what if the selection was 2 (daily weekday) - (Same as Case 1 but skipping weekends)

Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte
Dim RS As DAO.Recordset

Select Case Frequency


    Case 1
    
    'bytWeekday = 1 'Sunday
    dtStartDate = Me.txtTaskWhenSpecificDate
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    'If Weekday(dtLoopDate) = 1 Then

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

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

Open in new window

Mlanda T

How about:

1. Using this same case then to handle both 1 and 2

Case 1, 2

Open in new window

2. Changing the loop condition to

Do Until dtLoopDate <= dtEndDate

Open in new window

3. And then your increment to add the weekends as well

'Increment the loop date
If Frequency = 1 Then
    dtLoopDate = dtLoopDate + 1
else If Frequency = 2 and WeekdayName(Weekday(dtLoopDate))="Friday"  Then
    dtLoopDate = dtLoopDate + 3 '(include a Sat and Sunday)
end if

Open in new window

That said, I don't really like your UPDATE in the loop. It would be better to do the UPDATE outside the loop (so that you effectively write to the database only once.

SteveL13

ASKER

Mlanda, I must have something wrong or out of order. When I try to compile I get "Case without Select Case on the line: "Case 4"

Here's what I have:

Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte
Dim RS As DAO.Recordset

Select Case Frequency


    Case 1, 2
    
    

'    Case 1
'
    'bytWeekday = 1 'Sunday
    dtStartDate = Me.txtTaskWhenSpecificDate
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate <= dtEndDate

    'If Weekday(dtLoopDate) = 1 Then

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

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

    Loop
    
    'Increment the loop date
    If Frequency = 1 Then
        dtLoopDate = dtLoopDate + 1
            Else
        If Frequency = 2 And WeekdayName(Weekday(dtLoopDate)) = "Friday" Then
    dtLoopDate = dtLoopDate + 3 '(include a Sat and Sunday)
    End If


    'Case 2
    
    'Schedule recurring task every Weekday  -  Same as Case 1 but skip weekends
        
    'Loop



    'Case 3
    
    'Do not code for case 3 - Is not needed
        
    'Loop




    Case 4
    
    bytWeekday = 1 'Sunday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 1 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    Case 5
    
    bytWeekday = 2 'Monday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 2 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    Case 6
    
    bytWeekday = 3 'Tuesday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 3 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    Case 7
    
    bytWeekday = 4 'Wednesday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 4 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    Case 8
    
    bytWeekday = 5 'Thursday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 5 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    Case 9
    
    bytWeekday = 6 'Friday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 6 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    Case 10
    
    bytWeekday = 7 'Saturday
    dtStartDate = Me.txtTaskWhenSpecificDate + 7
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate = dtEndDate

    If Weekday(dtLoopDate) = 7 Then

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

    End If
    'Increment the loop date
    dtLoopDate = dtLoopDate + 1
    
    Loop
    
    
    
    'Case 11
    
    'Schedule recurring task every month
        
    'Loop
    
    
    
    'Case 12
    
    'Schedule recurring task every year
        
    'Loop

 
    
    
    'This goes at the very end just before End Sub
    End Select
    
    Me.chkbxRecurringScheduled = True

Open in new window

IrogSinta

@MlandaT, I believe the OP wants to create a record for each recurring event within a specified period. So the UPDATE has to be inside the loop to create multiple records.

@SteveL13, why do you set your recordset to where ID = " & Me.txtID.Value and then use FindFirst "[ID]=" & Me.txtID? If your reecordset returns that particular ID, there's no need to FindFirst. For that matter, why are you concerned with retrieving the record with that particular ID when you don't even use it in any of your update parameters? You can just use Select * from tblTasks Where False and forget the FindFirst.

In addition, why do you need number 3 (every week) when numbers 4 to 10 really represent every week?

Ron

Mlanda T

If Frequency = 1 Then
dtLoopDate = dtLoopDate + 1
ElseIf Frequency = 2 And WeekdayName(Weekday(dtLoopDate)) = "Friday" Then
dtLoopDate = dtLoopDate + 3 '(include a Sat and Sunday)
End If

Make that ELSEIF one word... OR ... add a second "End If" after the one that is already there...

SteveL13

ASKER

Mlanda, I just tested using your code and no records were added. The code is supposed to add records to the table using either "1 - Daily Every Day" or "2 - Daily Every Weekday".

Here again is my code for this part of the routine:

Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte
Dim RS As DAO.Recordset

Select Case Frequency

    Case 1, 2
   

'    Case 1
'
    'bytWeekday = 1 'Sunday
    dtStartDate = Me.txtTaskWhenSpecificDate
        dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do Until dtLoopDate <= dtEndDate

    'If Weekday(dtLoopDate) = 1 Then

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

    'End If
    'Increment the loop date
    If Frequency = 1 Then
        dtLoopDate = dtLoopDate + 1
        ElseIf Frequency = 2 And WeekdayName(Weekday(dtLoopDate)) = "Friday" Then
       dtLoopDate = dtLoopDate + 3 '(include a Sat and Sunday)
    End If

    Loop

Open in new window

SteveL13

ASKER

IrogSinta: I also put the update inside the loop and still got no new records. Like:

Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte
Dim RS As DAO.Recordset

Select Case Frequency

Case 1, 2

' Case 1
'
'bytWeekday = 1 'Sunday
dtStartDate = Me.txtTaskWhenSpecificDate
dtLoopDate = dtStartDate
dtEndDate = Me.txtFrequencyEndDate + 1

Do Until dtLoopDate <= dtEndDate

'If Weekday(dtLoopDate) = 1 Then

Set RS = CurrentDb.OpenRecordset("Select * from tblTasks where ID = " & Me.txtID.Value)
With RS
.FindFirst "[ID]=" & Me.txtID
.AddNew
!DateCreated = Me.txtDateCreated
!TaskDescription = "RECURRING EVENT" & " - " & Me.cboTaskDescription
!Priority = Me.cboPriority
!ScheduleTask = Me.cboTaskWhenComment
!TaskWhenSpecificDate = dtLoopDate
!TaskType = Me.cboTaskType
!ParetoPrincipal = Me.cboParetoPrincipal
' .Update
' End With

'End If
'Increment the loop date
If Frequency = 1 Then
dtLoopDate = dtLoopDate + 1
ElseIf Frequency = 2 And WeekdayName(Weekday(dtLoopDate)) = "Friday" Then
dtLoopDate = dtLoopDate + 3 '(include a Sat and Sunday)
End If

.Update
End With

Loop

SteveL13

ASKER

And you are correct. We do not need #3.

IrogSinta

The reason you are not getting any records back is because of this:
Do Until dtLoopDate <= dtEndDate
Since dtLoopDate is already less than dtEndDate at the onset, you end up bypassing the loop.

Some advice to remember: If you have code that is pretty much copy and paste, you are doing something wrong. Having repetitive code is a programmer's no-no. Here's how you could handle this:

    Dim dtStartDate As Date
    Dim dtEndDate As Date
    Dim dtLoopDate As Date
    Dim RS As DAO.Recordset
    Dim interval As Integer
    Dim daysAway As Integer
    Dim dayOfWeek As Integer
    
    interval = Choose(Frequency, 1, 1, 7, 7, 7, 7, 7, 7, 7, 30, 365)
    If interval = 7 Then
        'get the first Monday (or tuesday or wednesday, etc.) after the start date
        dayOfWeek = Choose(Frequency - 2, vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, vbSaturday)
        daysAway = dayOfWeek - Weekday(dtStartDate)
        If daysAway < 0 Then daysAway = daysAway + 7
        dtStartDate = Me.txtTaskWhenSpecificDate + daysAway
    Else
        dtStartDate = Me.txtTaskWhenSpecificDate + interval
    End If
    
    
    dtLoopDate = dtStartDate
    dtEndDate = Me.txtFrequencyEndDate + 1
    
    Set RS = CurrentDb.OpenRecordset("Select * from tblTasks False")
    Do While dtLoopDate <= dtEndDate
            
        With RS
            .AddNew
            !DateCreated = Me.txtDateCreated
            !TaskDescription = "RECURRING EVENT" & " - " & Me.cboTaskDescription
            !Priority = Me.cboPriority
            !ScheduleTask = Me.cboTaskWhenComment
            !TaskWhenSpecificDate = dtLoopDate
            !TaskType = Me.cboTaskType
            !ParetoPrincipal = Me.cboParetoPrincipal
            .Update
        End With
        
        If Frequency = 2 And Weekday(dtLoopDate) = vbFriday Then dtLoopDate = dtLoopDate + 2
        dtLoopDate = dtLoopDate + interval
        
    Loop
    
    RS.Close
    Set RS = Nothing

Open in new window

I wasn't sure how exactly you wanted to handle Monthly or Yearly so I just put 30 and 365 for now. Line 12 could also be shortened to dayOfWeek = Frequency-2, but I included the Choose function to give it some clarity.

Ron

SteveL13

ASKER

IrogSinta:

Using your code and if I select 4 (weekly on Sunday), the first new record I get is 7/7/2015 instead of 7/12/2015 if my txtTaskWhenSpecificDate date is entered as 7/5/2015.

And then I also get dates entered as 7/14/2015 and 7/21/2015 which aren't Sundays

???

IrogSinta

I forgot to mention that I got rid of your original #3 so Sunday is now 3.

Ron

SteveL13

ASKER

Ok. I got rid of my original 3 so now Sunday is 3 but now I'm getting.

Using your code and if I select 3 (weekly on Sunday), the first new record I get is 7/6/2015 instead of 7/12/2015 if my txtTaskWhenSpecificDate date is entered as 7/5/2015.

And then I also get dates entered as 7/13/2015 and 7/20/2015 and 7/27/2015 which aren't Sundays

IrogSinta

Sorry, here's a correction for lines 13 and 14:

        daysAway = dayOfWeek - Weekday(Me.txtTaskWhenSpecificDate)
        If daysAway <= 0 Then daysAway = daysAway + 7

Open in new window

Ron

SteveL13

ASKER

Ok. That worked. But now if I select 1 - Daily Every Day, I get one more record (date) in the table past the end date.

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Nice! Great so far. I'm going to test tonight and get back tomorrow.

IrogSinta

Don't forget, you may have to make some revisions to handle Monthly and Yearly. I'm not sure what your intent is... do you want the same day each month, the first Tuesday of each month, every 30 days, or whatever other scenario you have in mind. Right now it's just set for every 30 days from your start date.

Ron

SteveL13

ASKER

Understood. I'm not sure how I want this to work but hope to have it figured out today.

SteveL13

ASKER

I'm going to go with 30 and 365 for now. Thanks. Seems to be working fine.