returning indexed rows

hello!

alright, i'm having a lot of trouble doing this, i've made several attempts and this is the closest i've come...but i'm still runnin into some problems, and was wondering if someone could help me.

i need to choose every 550th person from a table. and check the 3rd column...if it's bigger than 18000 copy, paste it to table1 then find the next customer with a value smaller then 18000 and copy and paste it to table2.
go back to the 550 record and find the next record (imcremeting by 550)
and repeat the entire process over again.

here's what I have so far...

Sub Make()

Dim OriginalRS As Recordset
Dim CustWithOutWHRS As Recordset
Dim CustWithWHRS As Recordset
Dim CustCode As Long
Dim PremCode As Long
Dim TotalConsumption As Long
Dim Larger As Boolean
Dim index As Long
Dim i As Long
Dim Start As Integer
Dim NumREcords As Long

Set OriginalRS = CurrentDb.OpenRecordset("Original", dbOpenDynaset)
Set CustWithOutWHRS = CurrentDb.OpenRecordset("CustWithOutWH", dbOpenDynaset)
Set CustWithWHRS = CurrentDb.OpenRecordset("CustWithWH", dbOpenDynaset)

CustCode = OriginalRS!CustCode
PremCode = OriginalRS!PremCode
Larger = False
Start = 0
OriginalRS.MoveLast
NumREcords = OriginalRS.RecordCount
OriginalRS.MoveFirst

Do Until OriginalRS.EOF
For i = Start To NumREcords Step 550
TotalConsumption = OriginalRS!ConsumptionTOtal
If TotalConsumption > 18000 Then
Larger = True
CustWithOutWHRS.AddNew
CustWithOutWHRS!CustCode = OriginalRS!CustCode.Value
CustWithOutWHRS!PremCode = OriginalRS!PremCode.Value
CustWithOutWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value
CustWithOutWHRS.Update
Else
Larger = False
CustWithWHRS.AddNew
CustWithWHRS!CustCode = OriginalRS!CustCode.Value
CustWithWHRS!PremCode = OriginalRS!PremCode.Value
CustWithWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value
CustWithWHRS.Update
End If

If Larger = True Then
Do Until Larger = False Or OriginalRS.EOF

OriginalRS.MoveNext
TotalConsumption = OriginalRS!ConsumptionTOtal
If TotalConsumption > 18000 Then
Larger = True
Else
Larger = False
End If
Loop
CustWithWHRS.AddNew
CustWithWHRS!CustCode = OriginalRS!CustCode.Value
CustWithWHRS!PremCode = OriginalRS!PremCode.Value
CustWithWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value
CustWithWHRS.Update
Else

If Larger = False Then
Do Until Larger = True Or OriginalRS.EOF
OriginalRS.MoveNext
TotalConsumption = OriginalRS!ConsumptionTOtal
If TotalConsumption > 18000 Then
Larger = True
Else
Larger = False
End If
Loop
CustWithOutWHRS.AddNew
CustWithOutWHRS!CustCode = OriginalRS!CustCode.Value
CustWithOutWHRS!PremCode = OriginalRS!PremCode.Value
CustWithOutWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value
CustWithOutWHRS.Update
End If
End If

Start = Start + 550
OriginalRS.Move (Start)
TotalConsumption = 0
Next i

Loop

OriginalRS.Close
Set OriginalRS = Nothing
CustWithOutWHRS.Close
Set CustWithOutWHRS = Nothing
CustWithWHRS.Close
Set CustWithWHRS = Nothing


End Sub


My problem is that it won't increment by 550...instead it's going to random numbers. If someone culd help me out i'd really appreciate it. thanks

Hello yoonie_85,
I feel like coding something so, I made you a code. I patterned it with the code you gave. I used ADO Connection Object in doing DML Commands (INSERT,DELETE UPDATE).
Just find the code below. Check if it answers what you need. Good Luck! :)


Private Sub Proc_Sample()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient

Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
rs2.CursorLocation = adUseClient

Dim intLowerBound As Integer
Dim intUpperBound As Integer
Dim bolReturnToPointer As Boolean

intLowerBound = 0
intUpperBound = 550
With rs
Pointer:
.Open _
"SELECT * FROM ORIGINAL WHERE CUSTCODE = " & intUpperBound, cn, adOpenForwardOnly, adLockReadOnly
If Not .RecordCount = 0 Then
bolReturnToPointer = True

If !ConsumptionTOtal > 18000 Then
cn.BeginTrans
cn.Execute _
"INSERT INTO CustWithOutWH " & _
"(CustCode,PremCode,TotalConsumption) " & _
"VALUES " & _
"(" & !CustCode & "," & !PremCode & "," & !TotalConsumption & ") " & _
cn.CommitTrans

rs2.Open _
"SELECT * FROM ORIGINAL WHERE CUSTCODE > " & intCtr & " AND " & _
"ConsumptionTOtal > 18000 ORDER BY CUSTCODE ASC", cn, adOpenForwardOnly, adLockReadOnly
If Not rs2.RecordCount = 0 Then
cn.BeginTrans
cn.Execute _
"INSERT INTO CustWithWH " & _
"(CustCode,PremCode,TotalConsumption) " & _
"VALUES " & _
"(" & !CustCode & "," & !PremCode & "," & !TotalConsumption & ") " & _
cn.CommitTrans
End If
rs2.Close
Else

cn.BeginTrans
cn.Execute _
"INSERT INTO CustWithWH " & _
"(CustCode,PremCode,TotalConsumption) " & _
"VALUES " & _
"(" & !CustCode & "," & !PremCode & "," & !TotalConsumption & ") " & _
cn.CommitTrans

rs2.Open _
"SELECT * FROM ORIGINAL WHERE CUSTCODE > " & intCtr & " AND " & _
"ConsumptionTOtal > 18000 ORDER BY CUSTCODE ASC", cn, adOpenForwardOnly, adLockReadOnly
If Not rs2.RecordCount = 0 Then
cn.BeginTrans
cn.Execute _
"INSERT INTO CustWithOutWH " & _
"(CustCode,PremCode,TotalConsumption) " & _
"VALUES " & _
"(" & !CustCode & "," & !PremCode & "," & !TotalConsumption & ") " & _
cn.CommitTrans
End If
rs2.Close
End If
intLowerBound = intUpperBound
intUpperBound = intUpperBound + 550
Else
bolReturnToPointer = False
End If
.Close

If bolReturnToPointer Then GoTo Pointer
End With

Set rs = Nothing
Set rs2 = Nothing

End Sub