What Code and where do I put it to Nest Loop

  • Thread starter Thread starter lgray
  • Start date Start date
L

lgray

I preface this with...Yes I am quite a novice in VBA......
In a table, I have two different criteria that I need to loop through to
build a dataset. The fields that I need to Loop through are Project_ID
AllocationDesc (P1, P2....P9). Once I've reached the AllocationDesc Loop I
will then be running code that Increments Beginning Dates for a dataset. I
have this part of the Code working. But I am having difficulty getting a
loop through the Project_ID's then another loop through the sub grouping of
AllocationDesc in order to run my current code.

Below is a sample of dataset:
Project_ID AllocationDesc BeginDate
1 P1 8/25/2011
1 P2 11/1/2011
1 P3 1/7/2012
1 P4 3/15/2012
1 P5 5/21/2012
1 P6 7/28/2012
1 P7 10/3/2012
1 P8 12/10/2012
1 P9 2/15/2013
2 P1 3/14/2012
2 P2 4/22/2012
2 P3 5/31/2012
2 P4 7/9/2012
2 P5 8/17/2012
2 P6 9/25/2012
2 P7 11/3/2012
2 P8 12/12/2012
2 P9 1/20/2013
3 P1 5/19/2012
3 P2 6/17/2012
3 P3 7/16/2012


And below is Code that I will be using once I get into the AllocationDesc
Level.

Private Sub BudgetAllocation()

Dim rs As DAO.Recordset
Dim dStartDate As Date
Dim iEstDays As Integer

iEstDays = 1

Set rs = CurrentDb.OpenRecordset(tbl_BudgetAllocation)

If rs.RecordCount <> 0 Then
rs.MoveFirst
'Initilize the first StartDate (because there's no previous record)
dStartDate = rs.Fields("BeginDate")
dEndDate = rs.Fields("EndDate")
dProject_ID = rs.Fields("Project_ID")
dConstruction_Start_Date = rs.Fields("Construction_Start_Date")
dProjected_InService_Date = rs.Fields("Projected_InService_Date")
dDuration = rs.Fields("Duration")
dDaysPerPeriod = rs.Fields("DaysPerPeriod")
dAllocationDesc = rs.Fields("AllocationDesc")
dAllocationDate = rs.Fields("AllocationDate")
dAllocationAmt = rs.Fields("AllocationAmt")
dAllocationAmtPerDay = rs.Fields("AllocationAmtPerDay")

'Loop the rs
While dStartDate <= dEndDate
'Update each record
With rs
..AddNew
rs.Fields("Project_ID") = dProject_ID
rs.Fields("BeginDate") = dStartDate
rs.Fields("EndDate") = dEndDate
rs.Fields("Construction_Start_Date") = dConstruction_Start_Date
rs.Fields("Projected_InService_Date") = dProjected_InService_Date
rs.Fields("Duration") = dDuration
rs.Fields("DaysPerPeriod") = dDaysPerPeriod
rs.Fields("AllocationDesc") = dAllocationDesc
rs.Fields("AllocationDate") = dAllocationDate
rs.Fields("AllocationAmt") = dAllocationAmt
rs.Fields("AllocationAmtPerDay") = dAllocationAmtPerDay
..Update
End With
'Reset dStartDate for the next record
dStartDate = DateAdd("d", iEstDaysm, dStartDate + 1)

Wend
rs.MoveNext
End If

rs.Close
Set rs = Nothing

End Sub
 
You guys think you are sooooo smart. It's perfect. Thank you.

Why did I think that this had to be so difficult.
--
Linda


vanderghast said:
Looping is so... 2008.

Am I wrong if your goal is to get, from each record in BudgetAllocation, to
get its BeginDate and EndDate, and to generate one record for each date
between these two calendar dates?


If so, easily done in SQL, no loop, but with the help of another table,
Iotas, one field, Iota, its primary key, with values from 0 to 999 (easy to
fill it from Excel and import those values in the Iota table of Access).


In a new query, bring BudgetAllocation and Iotas.
In the grid, bring the information fields from BudgetAllocation.


Add a computed column:


CurrentDate: [BudgetAllocation].[StartDate] + [Iotas].[iota]

and add the criteria: <= [BudgetAllocation].[EndDate]


and... that's all folk!

You don't belive me? Try it! And you will see how Looping is so ... the old
way... No loop, no recordset, almost no code (SQL is code, but you did it
... graphically! ) Why not ?


Sure, maybe that was not what you wanted to do, in the first place... :-)




Vanderghast, Access MVP





lgray said:
I preface this with...Yes I am quite a novice in VBA......
In a table, I have two different criteria that I need to loop through to
build a dataset. The fields that I need to Loop through are Project_ID
AllocationDesc (P1, P2....P9). Once I've reached the AllocationDesc Loop
I
will then be running code that Increments Beginning Dates for a dataset.
I
have this part of the Code working. But I am having difficulty getting a
loop through the Project_ID's then another loop through the sub grouping
of
AllocationDesc in order to run my current code.

Below is a sample of dataset:
Project_ID AllocationDesc BeginDate
1 P1 8/25/2011
1 P2 11/1/2011
1 P3 1/7/2012
1 P4 3/15/2012
1 P5 5/21/2012
1 P6 7/28/2012
1 P7 10/3/2012
1 P8 12/10/2012
1 P9 2/15/2013
2 P1 3/14/2012
2 P2 4/22/2012
2 P3 5/31/2012
2 P4 7/9/2012
2 P5 8/17/2012
2 P6 9/25/2012
2 P7 11/3/2012
2 P8 12/12/2012
2 P9 1/20/2013
3 P1 5/19/2012
3 P2 6/17/2012
3 P3 7/16/2012


And below is Code that I will be using once I get into the AllocationDesc
Level.

Private Sub BudgetAllocation()

Dim rs As DAO.Recordset
Dim dStartDate As Date
Dim iEstDays As Integer

iEstDays = 1

Set rs = CurrentDb.OpenRecordset(tbl_BudgetAllocation)

If rs.RecordCount <> 0 Then
rs.MoveFirst
'Initilize the first StartDate (because there's no previous record)
dStartDate = rs.Fields("BeginDate")
dEndDate = rs.Fields("EndDate")
dProject_ID = rs.Fields("Project_ID")
dConstruction_Start_Date = rs.Fields("Construction_Start_Date")
dProjected_InService_Date = rs.Fields("Projected_InService_Date")
dDuration = rs.Fields("Duration")
dDaysPerPeriod = rs.Fields("DaysPerPeriod")
dAllocationDesc = rs.Fields("AllocationDesc")
dAllocationDate = rs.Fields("AllocationDate")
dAllocationAmt = rs.Fields("AllocationAmt")
dAllocationAmtPerDay = rs.Fields("AllocationAmtPerDay")

'Loop the rs
While dStartDate <= dEndDate
'Update each record
With rs
.AddNew
rs.Fields("Project_ID") = dProject_ID
rs.Fields("BeginDate") = dStartDate
rs.Fields("EndDate") = dEndDate
rs.Fields("Construction_Start_Date") = dConstruction_Start_Date
rs.Fields("Projected_InService_Date") = dProjected_InService_Date
rs.Fields("Duration") = dDuration
rs.Fields("DaysPerPeriod") = dDaysPerPeriod
rs.Fields("AllocationDesc") = dAllocationDesc
rs.Fields("AllocationDate") = dAllocationDate
rs.Fields("AllocationAmt") = dAllocationAmt
rs.Fields("AllocationAmtPerDay") = dAllocationAmtPerDay
.Update
End With
'Reset dStartDate for the next record
dStartDate = DateAdd("d", iEstDaysm, dStartDate + 1)

Wend
rs.MoveNext
End If

rs.Close
Set rs = Nothing

End Sub
Click to expand...
Click to expand...
 
Back
Top