Why don't my nested Do Loops work? 1

[mrf1xa]

Hi folks, I would be most grateful for a fresh pair of eyes on this. I am using the code below to populate some calculated costs. At present it falls over at the last Loop line, saying 'Loop without Do..' but as far as I can see I have 3 Do's and 3 Loops- what am I missing?

If I delete either the 2nd or 3rd loops te code runs fine. Can I not nest in this way? If not, how should I set this out to achieve the same thing?

Many thanks in anticipation

Dim rst1 As DAO.Recordset 'CBA Records
Dim rst2 As DAO.Recordset 'ResourceUsed records
Dim rst3 As DAO.Recordset 'ResourceWSheet records
Dim mth As Date 'Month variable for use in selecting recordset
Dim totcost As Long 'number variable to hold calculated forcast cost
Dim totwkd As Long 'variable to hold calculated actual cost
'Open a recordset from tblCBA for current project only
Set rst1 = CurrentDb.OpenRecordset("Select * FROM [tblCBA] WHERE (([tblCBA].[ID] = " & _
"" & Forms![frmCBA]![ID] & &quot);&quot
If rst1.RecordCount > 0 Then 'If no records exit now
rst1.MoveFirst

'Initiate loop through all monthly records for this project
Do Until rst1.EOF
mth = Format(rst1![Month], "mm/dd/yyyy&quot 'set mth to American format
'open subrecordset from tblResourceUsed for this project and selected month
Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM [tblResourceUsed]" & _
"WHERE (([tblResourceUsed]![Month] =#" & mth & "#))AND " & _
"(([tblResourceUsed].[ID] = " & Forms![frmCBA]![ID] & &quot);&quot
If rst2.RecordCount > 0 Then 'If no records exit now
rst2.MoveFirst
totcost = 0 'initialise totcost at zero
'initialise loop through all records for this month
Do Until rst2.EOF
'totcost = previous value * no of days * 7 (hours in day) * hourly rate for this resource
totcost = totcost + rst2![Time] * 7 * DLookup("[Cost per hour]", _
"tblresources", "[ResourceID] = '" & rst2![ResourceID] & "'&quot
rst2.MoveNext
Loop
rst2.Close
Set rst2 = Nothing
End If

'open subrecordset from tblResourceWSheet for this project and selected month
Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM [tblResourceWSheet]" & _
"WHERE (([tblResourceWSheet]![ResMonth] =#" & mth & "#))AND " & _
"(([tblResourceWSheet].[ProjID] = " & Forms![frmCBA]![ID] & &quot);&quot
If rst3.RecordCount > 0 Then 'If no records exit now
rst3.MoveFirst
totwkd = 0 'initialise totwkd at zero
'initialise loop through all records for this month
Do Until rst3.EOF
'totwkd = previous value * no of hours * hourly rate for this resource
totcost = totcost + rst3![Hours] * DLookup("[Cost per hour]", _
"tblresources", "[ResourceID] = '" & rst3![ResourceID] & "'&quot
rst3.MoveNext
Loop
rst3.Close
Set rst3 = Nothing
rst1.Edit
rst1![FcastResCost] = totcost 'insert totcost total into CBA table
rst1![ActResCost] = totwkd 'insert totwkd into CBA table
rst1.Update
rst1.MoveNext
Loop
End If

Set rst1 = Nothing

Me.Refresh 'refresh form to show results Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[PaulBricker]

You're missing an End If after the line

rst3.MoveNext
Loop
Here I think
rst3.Close

It's hard to read with the lines bending around the textpage. But I'm sure it's the missing End If that's triggering the error.

Paul

 

[mrf1xa]

Spot on Paul- works a treat now, must have been the 'wood for the trees' syndrome I think!

Thanks for taking the time to read- it's a shame it pastes all over the place when it looks nice and neat in my code window! Anyway, a star for the effort... Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[PaulBricker]

Maybe Friday Fantasies as we call them around here.;-)
Paul