PDA

View Full Version : Help with looping/dumping recordset



jsabo
09-17-2015, 02:00 PM
Hey guys,
Have a feeling I'm over-complicating this one... I just want to loop through three different recordsets and dump one field.
I have the loop so it begins at i = 7, so we start at range B7 and move down to B8, and so on until we have moved down the number of records. please let me know if I can clarify:


Dim i As Integer
Dim j As Integer
Dim k As Integer

recset_cost_comments.MoveFirst
recset_quality_comments.MoveFirst
recset_schedule_comments.MoveFirst

i = recset_cost_comments.RecordCount
j = recset_quality_comments.RecordCount
k = recset_schedule_comments.RecordCount

Do While Not recset_cost_comments.EOF
For i = 7 To (7 + i)
ActiveWorkbook.Sheets("Cost").Range("B" & i).Value = recset_cost_comments!Comments
recset_cost_comments.MoveNext
Next i
Loop

Do While Not recset_quality_comments.EOF
For j = 7 To (7 + i)
ActiveWorkbook.Sheets("Quality").Range("B" & i).Value = recset_quality_comments!Comments
recset_quality_comments.MoveNext
Next j
Loop

Do While Not recset_schedule_comments.EOF
For k = 7 To (7 + i)
ActiveWorkbook.Sheets("Schedule").Range("B" & i).Value = recset_schedule_comments!Comments
recset_schedule_comments.MoveNext
Next k
Loop

The error I get is, "Either BOF or EOF is True, or the current record has been deleted". However, there is one record for the first recordset, 5 for the 2nd and 4 for the 3rd. To note, it does successfully write the one record before kicking out the error.
Any ideas? Thanks.

SamT
09-18-2015, 08:10 AM
Your nested loops are both trying to loop through the records
Try these loop styles

i = 7 'You can continue to use i in the other loops, just don't reset it.
Do While Not recset_cost_comments.EOF
ActiveWorkbook.Sheets("Cost").Range("B" & i).Value = recset_cost_comments!Comments
i = i + 1
recset_cost_comments.MoveNext
Loop
' At this time i = 7 + recset_cost_comments.RecordCount + 1


For i = 7 To (7 + i) 'Must use j and k in other loops
ActiveWorkbook.Sheets("Cost").Range("B" & i).Value = recset_cost_comments!Comments
recset_cost_comments.MoveNext
Next i