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

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

Frequency choices
But what if the selection was 2 (daily weekday)

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

chaauCommented:
I would change the program like this. First of all we need a function to get the next day of week:
Private Function GetNextDayOfWeek(startDate As Date, dayOfWeek) As Date
    GetNextDayOfWeek = startDate
    Do While Not Weekday(GetNextDayOfWeek) = dayOfWeek
        GetNextDayOfWeek = GetNextDayOfWeek + 1
    Loop
End Function

Open in new window

And now, with its help the whole program will look like this:
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim dtLoopDate As Date
Dim bytWeekday As Byte
Dim RS As DAO.Recordset
Dim AddDays As Integer

    AddDays = 1
    dtStartDate = Me.txtTaskWhenSpecificDate

    Select Case Frequency
    Case 1 'daily
        dtLoopDate = dtStartDate
        AddDays = 1
        
    Case 2 'daily every weekday
        If Weekday(dtStartDate) = vbSunday Or Weekday(dtStartDate) = vbSaturday Then
            dtLoopDate = GetNextDayOfWeek(dtStartDate, vbMonday)
        Else
            dtLoopDate = dtStartDate
        End If
        AddDays = -1 ' will be calculated later
    Case 3 'weekly
        dtLoopDate = dtStartDate
        AddDays = 7
    
    Case 4 'weekly - Sunday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbSunday)
        AddDays = 7
        
    Case 5 'weekly - Monday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbMonday)
        AddDays = 7
        
    Case 6 'weekly - Tueday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbTueday)
        AddDays = 7
        
    Case 7 'weekly - Wednesday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbWednesday)
        AddDays = 7
        
    Case 8 'weekly - Thursday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbThursday)
        AddDays = 7
        
    Case 9 'weekly - Friday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbFriday)
        AddDays = 7
        
    Case 10 'weekly - Saturday
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbSaturday)
        AddDays = 7
        
    Case 11 'monthly
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbMonday)
        AddDays = -1 ' will be calculated later
        
    Case 12 'yearly
        dtLoopDate = GetNextDayOfWeek(dtStartDate, vbMonday)
        AddDays = -1 ' will be calculated later
        
    End Select
    dtEndDate = Me.txtFrequencyEndDate + 1

    Do While dtLoopDate = dtEndDate

    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

    'Increment the loop date
    If AddDays > 0 Then
        dtLoopDate = dtLoopDate + AddDays
    Else
    Select Case Frequency
        Case 2 'daily every weekday
            dtLoopDate = dtLoopDate + 1
            If Weekday(dtLoopDate) = vbSunday Or Weekday(dtLoopDate) = vbSaturday Then
                dtLoopDate = GetNextDayOfWeek(dtLoopDate, vbMonday)
            End If
            
        Case 11 'monthly
            dtLoopDate = DateAdd("m", 1, dtLoopDate)
            
        Case 12 'yearly
            dtLoopDate = DateAdd("yyyy", 1, dtLoopDate)
            
        End Select
    End If
    
    Loop

Open in new window

0
Gustav BrockCIOCommented:
There is some mismatch here.

What is the difference between Weekly-EveryWeek and Weekly-OnSomeWeekday?

/gustav
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
SteveL13Author Commented:
Gustav,

You are absolutely correct.  There is no difference.  I'm going to delete the choice for Weekly - Every Week and move on to the next new topic which I will post momentarily.  

The title will be "How copy record from command button on a form if case = 2"
0
chaauCommented:
It is too late now to comment as you have opened another question. The way I saw this is when you select the "Weekly" option the scheduler starts on the day (every 7 days from the Start Date). The other "weekly" options would specifically start on the day of week following the start date.

As you already have your answer at another question I recommend you check my answer as well (you can delete the option 2, it will still work). Check the performance of my answer and the other answers and advise which is better.
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.