Enter records only on weekdays

[James Shentel]

I am working on a repetitive entry table to repeat tasks
much like the appointment recurrence form in Outlook. I
have had some success setting up the form to add
activities based on the number of occurences. I would now
like to be able to add activities still based on a number
of occurences but enter them only on a weekday. Much of
the following code won't need to be bothered, but I am
trying to accomplish this task in the if...then statement
in the middle of the code. When the code is executed the
proper number of events are entered but they are entered
every day of the week.

1. How can I get the code to work so that the events are
only entered on week days.

2. In the do then loop statement, how can I be sure that
if the code moves the date to the following monday that
that monday date will be the new starting date for the
code. In other words, if part of the loop is: field1 =
date+1, How can I be sure that if the code moves the date
to monday that it will pick up the new date and the next
entry will be Tuesday?


Private Sub Command122_Click()

Dim intOccurences As Integer
Dim dteDueDate As Date
Dim Blah As Integer
Dim intDateDif As Integer

If Me.Frame17 = 1 And Me.FrameDaily = 2 And Me.Frame114 =
1 Then

'Open the columnar activity form in hidden mode
DoCmd.OpenForm "fsubActivityColumn", , , , , acHidden
'Set the initial value of the occurences
intOccurences = 1
'Set the initial value of the date due to DatePicker3
dteDueDate = Me.DTPicker3

Do While Me.Text7 >= intOccurences
'Go to a new record on the columnar form
DoCmd.GoToRecord acForm, "fsubActivityColumn",
acNewRec
'Transfer the constants from the main activity
'form to the columnar activity form
Forms!fsubActivityColumn!ActivityNote = Forms!
frmActivity!ActivityNote
Forms!fsubActivityColumn!ClientID = Forms!frmActivity!
ClientID
Forms!fsubActivityColumn!TransactionID = Forms!
frmActivity!TransactionID
Forms!fsubActivityColumn!ActivityType = Forms!
frmActivity!ActivityType
Forms!fsubActivityColumn!StartTime = Forms!
frmActivity!StartTime
Forms!fsubActivityColumn!EndTime = Forms!frmActivity!
EndTime
Forms!fsubActivityColumn!AgentAssign = Forms!
frmActivity!AgentAssign

'Add the number of days specified to the date due
event
dteDueDate = dteDueDate + Me.txtDaily
If Weekday(dteDueDate) = 1 Then
Forms!fsubActivityColumn!DateDue =
dteDueDate + 1
ElseIf Weekday(dteDueDate) = 7 Then
Forms!fsubActivityColumn!DateDue =
dteDueDate + 2
End If
'Assign that value to the DateDue field on the
columnar form
Forms!fsubActivityColumn!DateDue = dteDueDate

'Find the difference between the DateStart and the
DateDue
'fields on the main activity form
intDateDif = Forms!frmActivity!DTPicker0 - Forms!
frmActivity!DTPicker3
'Assign the same difference in days to the columnar
activity form
Forms!fsubActivityColumn!DateStart = Weekday
(dteDueDate - intDateDif, vbMonday)
'Add an occurence
intOccurences = 1 + intOccurences
'Save the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
'Start over
Loop

DoCmd.Close acForm, "fsubActivityColumn", acSaveYes

End If

End Sub

Can someone please point me in the right direction?
Thanks in advance for all of your help.

James Shentel

 

[Scott Flack]

Hi James,

If you still need help on this subject, please feel free
to contact me; I think I may be able to help you. I had
a similar inquiry and pieced code together from about 4
different sources (including yours) and got something to
work.

Thanks,
Scott Flack
(e-mail address removed)