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:

Choices
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

SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

MlandaTCommented:

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.
SteveL13Author Commented:
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

IrogSintaCommented:
@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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

MlandaTCommented:
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...
SteveL13Author Commented:
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

SteveL13Author Commented:
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
SteveL13Author Commented:
And you are correct.  We do not need #3.
IrogSintaCommented:
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
SteveL13Author Commented:
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


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

Ron
SteveL13Author Commented:
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
IrogSintaCommented:
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
SteveL13Author Commented:
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.
IrogSintaCommented:
Change line 22 to:
dtEndDate = Me.txtFrequencyEndDate

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
SteveL13Author Commented:
Nice!  Great so far.  I'm going to test tonight and get back tomorrow.
IrogSintaCommented:
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
SteveL13Author Commented:
Understood.  I'm not sure how I want this to work but hope to have it figured out today.
SteveL13Author Commented:
I'm going to go with 30 and 365 for now.  Thanks.  Seems to be working fine.
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.