472,330 Members | 1,390 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,330 software developers and data experts.

Looping thru Table1 to parse records to Table2

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


' 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
SchStart = NewDate
'Syntax to Write record in Table2 ???
'Activity, Calendar, Duration, Craft, force, SchStart



Set cnn1 = Nothing
Set myRecordSet = Nothing
End Sub

Thank you for your help

Wayne Collins

Nov 13 '05 #1
1 1903
I googled this:
Syntax to edit a row:
Dim fieldName AS String
fieldName = "Duration"
myRecordSet(fieldName) = "24"

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: JC-Atl | last post by:
I have 2 tables in a database: As an example, let's say Table1 has 20 rows, like this: Table1: CategoryNumber CategoryName 1000 ...
by: csgraham74 | last post by:
Hi guys, i have a little problem here. im attempting to write a stored procedure that compares two tables of the same data structure and adds...
by: Benoit | last post by:
Hello, I have problem to bind a combobox and a query with 2 tables. This a little code I Use : Dim myDR As SqlDataReader Dim myQuery As...
by: Hetal | last post by:
Hi.. I am working on VB.NET 2003 (windows form) application and using ADO.NET to deal with databases. The table structure i am working with is...
by: Yas | last post by:
Hello, I have 2 tables, Table1 and Table2. I have copied all data from Table1 to Table2. However Table1 is dynamic it has new rows added and...
by: Yas | last post by:
Hi everyone I am trying to create a DELETE Trigger. I have 2 tables. Table1 and Table2. Table 2 has all the same fields and records as Table1 + 1...
by: suren1503 | last post by:
Hi All Techies, I have created one DTS to copy table1 (which is on Oracle server) to table2 (on SQL Server 2000) I want to copy only new records...
by: irinamas | last post by:
I have two tables: table1 with columns (ID, A1, A2) and table2 with columns (ID, newA1, newA2). I need to update values in table 1 with new values...
by: psyvanz | last post by:
i have created 2 tables name table1 and table2 with the same fieldnames in subjectnumber(sub-0001), subjectname(english1) but in my table2 i added...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.