Loop through rows of data 2

[nicks60610]

This is a complete rookie question as I never code in VB, well not much anyway. I have a table with a few columns, and when I query the table, I get about 4 rows of data, one of the columns I have Sum(col1+col2) As Cycles. Now the record "Cycles" I want to loop through each one and add the total of and call it "TotalCycles" and then use that # for something else. What do I need to do next here in my code to get this done, can someone give me some hints, maybe tell me where to look online to learn the logic, etc?

Thanks in advance:


Dim cmdLookupMachines As New ADODB.Command
Dim rsMachinesIdLookup As New ADODB.Recordset



cmdLookupMachines.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};Dbq=""

cmdLookupMachines.CommandText =
"SELECT [Production Data Table].Date,
[Production Data Table].Shift,
[Production Data Table].Line,
Sum(([Pieces]+([Scrap])/2)) AS [Cycles]" & _

" FROM [Production Data Table]" & _

" WHERE ((([Production Data Table].Date) Between Date() And Date()-1)

AND (([Production Data Table].Line)='1A'))" & _

" GROUP BY [Production Data Table].[Date],
[Production Data Table].[Shift],
[Production Data Table].[Line] "

cmdLookupMachines.CommandTimeout = 15
cmdLookupMachines.CommandType = adCmdText

Dim rowsId As Long

Set rsMachinesIdLookup = cmdLookupMachines.Execute(rowsId)

Dim intRecordCount As Integer

intRecordCount = 1

Dim Line As String
Dim TabWareMachine As String
Dim IntCycles As Long
Dim LastCycleUpdate As Date
Dim idReqDataItemOfMachine As String

While (Not rsMachinesIdLookup.EOF)
Line = rsMachinesIdLookup("Line").Value
TabWareMachine = rsMachinesIdLookup("Line").Value
IntCycles = rsMachinesIdLookup("Cycles").Value




rsMachinesIdLookup.MoveFirst
Do Until rsMachinesIdLookup.EOF
IntCycles = IntCycles + rsMachinesIdLookup("Cycles").Value

Loop


Dim TotalCyclesCounted As Single
TotalCyclesCounted = IntCycles + dblCurrentMeter
End
StandardExit:
Exit Sub

 

[nicks60610]

I get this to work (almost), it looped through rows 1 and 2, and then stopped, it should go through a total of 6 rows:

Do While IntCycles = rsMachinesIdLookup("Cycles").Value And Not rsMachinesIdLookup.EOF
rsMachinesIdLookup.MoveNext
If rsMachinesIdLookup.EOF Then
Exit Do
End If

Loop

 

[petermeachem]

have you tried

Do Until rsMachinesIdLookup.EOF
IntCycles = IntCycles + rsMachinesIdLookup("Cycles").Value
rsMachinesIdLookup.MoveNext
Loop

That should work ok.

 

[BobRodes]

It looks like you're trying to loop through a subset of the records in your recordset. To do this, apply a filter to your recordset and then loop just as if you were going through all the records:

With rs
    .Filter = "Cycles = " & IntCycles 'or any other valid sql WHERE clause
    .MoveFirst
    Do Until .EOF
        'do whatever you're doing for each record
        .MoveNext
    Loop
End With

Using the With construct is a bit more efficient, since you only have to tell the compiler that you're using the rs object once.

HTH

Bob

 

[nicks60610]

Excellent, I will try both solutions this AM and let you know, thanks again!