how to return every 5th records?

[polyubi]

I have a recordset that has more than 50 records. Now I want to use ADO method to return every 5th records i.e.5;10;15;20....... to makeup a new recordset. And I have the following codes:

Private Sub Command4_Click()
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rst1.Open "select * from sheet2", cnn, adOpenKeyset, adLockOptimistic
rst2.Open "sheet3", cnn, adOpenKeyset, adLockOptimistic
For i = 1 To rst1.RecordCount Step 5
rst2.AddNew
rst2.Fields(1) = rst1.Fields(0)
rst2.Fields(2) = rst1.Fields(1)
rst2.Fields(3) = rst1.Fields(2)
rst2.Fields(4) = rst1.Fields(3)
rst2.MoveNext
Next i
rst2.Update
Set Me.Sheet2_subform.Form.Recordset = rst2
Set rst1 = Nothing
Set rst2 = Nothing
End Sub

But it failed to work. Could anyone help me out?

 

[jdraw]

When you say Failed, what exactly fails ie message?

I think the rst2.MoveNext is incorrect based on the following.

When you start, there are no records in rst2. So you do an AddNew,
but that record isn't added until you issue the rst2.Update.
I don't think you have to do rst2.MoveNext, you just do another AddNew and Update

Also, I'm not following how you are getting every 5th record. I see no movement on rst1
Just my thoughts on a quick look.

Just tried a sample (had to adapt code since I don't have your form)
Note I'm just using your For Loop to iterate. The rst1.Movenext is what is moving along rst1. At the opening you're 4 records from rec 5, after that you want an extra rst1.movenext to be 5 records further along rst1.

Sub AWF_EVERY5thRecord()
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rst1.Open "select * from sheet2", cnn, adOpenKeyset, adLockOptimistic
rst2.Open "sheet3", cnn, adOpenKeyset, adLockOptimistic
For i = 1 To rst1.RecordCount Step 5
rst1.MoveNext
rst1.MoveNext
rst1.MoveNext
rst1.MoveNext

rst2.AddNew
rst2.Fields(1) = rst1.Fields(0)
rst2.Fields(2) = rst1.Fields(1)
rst2.Fields(3) = rst1.Fields(2)
rst2.Fields(4) = rst1.Fields(3)
rst2.Update   'jed force the write of the record

rst1.MoveNext

'rst2.MoveNext  '
Next i
'rst2.Update
'Set Me.Sheet2_subform.Form.Recordset = rst2 'DONT KNOW WHAT THIS IS
Set rst1 = Nothing
Set rst2 = Nothing
End Sub

 

[polyubi]

Thanks a lot Jdraw!
'Set Me.Sheet2_subform.Form.Recordset = rst2 ........this is to show the results in my subform.
When I say "failed" means the subform can run, but not the desired result. The subform still shows every records (from 1 to 51 and more....)
Now I made a little change:
for i=5 to recordCound / 5 step 5
.........
Is it OK?

 

[jdraw]

I don't think the For Loop is the issue.
I set up a test table with 6 fields - one of which was a number that I incremented in order to see which record I was actually copying to rst2.
Instead of the For Loop, you could use Do while not rst1.eof
The key thing is you must move along rst1 - you said every 5th record. So I used a series of rst1.MoveNext to get further along the rst1 recordset.


I've attached:

my sheet2 the (rst1) recordset and
my sheet3 (rst2)
good luck

 

[polyubi]

Hey Jdraw,
Can you please attach your mdb file?

 

[jdraw]

Here it is.
The procedure to find every 5th record is in the Module AccessMonster.
There are a few other things there also. I just copied the module and tables to a new mdb.

 

[polyubi]

Jdraw, thank you very much!
Now you have 60 records in "sheet2", but instead, if you only have 59 records you will get an error message. Why?

 

[jdraw]

Because the MoveNext statements are trying to move past the end of the Recordset.
Each loop goes for 5 records, and if the number of records in rst1 is divisible by 5 - no problem. If you only have 59 records (not divisible by 5) you're attempting to go past the EOF.

To have no errors

use this For Loop

For i = 1 To Int(rst1.RecordCount / 5) Step 1
(note step 1 is default)
will only allow the loop to execute 11 times, so it will only process to record 55. This will only process for that integer multiple of 5 - which is less than the record count times. It is like a Mod function.

 

[polyubi]

last question:
before addnew, can I use the following sentence to clear the table "sheet3"?
with rst2
.delete
rst2.update
end with
.........

 

[jdraw]

last question:
before addnew, can I use the following sentence to clear the table "sheet3"?
with rst2
.delete
rst2.update
end with
.........

No.
rst2.delete will only delete 1 record.

In order to delete all records in rst2, you
could use SQL

sqldel = "DELETE FROM Sheet3"

currentdb.execute sqldel

or call some sort of loop something like
(you have to do this in reverse order)
x = rst2.recordcount
For j = x to 0 step -1
rst2.delete
next x

Why do you want to clear the table?
If it's for testing, you can go Open Table Sheet3 in spreadsheet mode
select all records and hit delete -- that will remove existing records.