Looping thru Table1 to parse records to Table2

[u473]

I am still a beginner with VBA and I need the VBA Code for the
following problem.

I have a list of activities with their Scheduled Start date, Duration,
and Working hours in Table1.

I need to loop thru each record in Table1, split the long activity
durations according to their Working Hours shown in the Calendar field
and write these records to Table2.

For instance, if I have a 24 hours activity duration and my allowed
working hours are 5 days a week at 8 hours per day,
I would expect this activity to be ditributed over 3 days.

Source Table, Named : Table1

Activity Calendar Duration Craft Force Scheduled Start
A 5-8 24 PF 2 JUN 3 2005 7:00
B 7-24 48 EL 4 JUN 3 2005 7:00
C 6-12 6 MW 3 JUN 3 2005 7:00

Expected Table2.

Activity Calendar Duration Craft Force Scheduled Start

A 5-8 8 PF 2 JUN 3 2005 7:00
A 5-8 8 PF 2 JUN 6 2005 7:00
A 5-8 8 PF 2 JUN 7 2005 7:00
B 7-24 24 EL 4 JUN 3 2005 7:00
B 7-24 24 EL 4 JUN 4 2005 7:00
C 6-12 6 MW 3 JUN 3 2005 7:00
__________________________________________________ ____________
' The following code is my attempt at resolving this problem
Option Compare Database
Public Sub ActSplit()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
myRecordSet.Open

myRecordSet.MoveFirst

' Note Tables fields list in both tables :
' Activity, Calendar, Duration, Craft, Force, SchStart

Dim Dur as Integer 'Activity Duration
Dim Cal as String 'Activity Calendar
Dim WkDays as Integer 'Number of working Days per week
Dim WkHours as Integer 'Number of working Hours per Day
Dim Start as Date 'Original Scheduled Start Date
NewStart as Date 'New Start Dates

While Not myRecordSet.EOF
Dur = Duration
Cal = Calendar
Start = SchStart
NewDate = SchStart

Select Case Cal
Case "5-8"
WkDays = 5
WkHours = 8
Case "6-12"
WkDays = 6
WkHours = 12
Case "7-24"
WkDays = 7
WkHours = 24
End Select
If Dur > WkHours
For Dur = Duration To 0 Step - WkHours
' Skip Weekends
' Work starts on Friday on a 5 day week
If WeekDay(NewDate,2) = 5 and WkDays = 5
NewDate = NewStart+2
End If
' Skip Sundays
' Work starts on Friday on a 6 day week
If WeekDay(NewDate,2) = 6 and WkDays = 6
NewDate = NewStart+1
End If

If Dur > WkHours
NewDate = NewDate +1
Duration = WkHours
Dur = Dur - WkHours
Else 'Dur < ShiftHours
NewDate = NewDate +1
Duration = Dur
Dur = 0
Endif
SchStart = NewDate
'Syntax to Write record in Table2 ???
'Activity, Calendar, Duration, Craft, force, SchStart
Next

EndIf

myRecordSet.MoveNext
Wend

myRecordSet.Close
Set cnn1 = Nothing
Set myRecordSet = Nothing
End Sub

Thank you for your help

Wayne Collins

[shumaker]

I googled this:
http://www.georgehernandez.com/xData.../Recordset.htm
Syntax to edit a row:
Dim fieldName AS String
fieldName = "Duration"
myRecordSet(fieldName) = "24"