How do I get every fifth record to print?

P

prphan

I am trying to run a code that will pull and print every fifth record in a
recordset. When this runs, I only get the first record in the set. What am
I doing wrong?


Private Sub Every5_Click()
Dim rs As ADODB.Recordset ' Declares rs as ADO Recordset
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection ' Creates open connection to database
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn1 ' Sets cnn1 as data location for rs

Dim SQL As String
SQL = "SELECT Layout.id, People.name, [dbo_wrk grp].txt,
dbo_ProcedureMain.ProcGroup, dbo_ProcedureMain.ProcNum,
dbo_ProcedureMain.ProcName, Periods.Period, Layout.DLC, Layout.DNC FROM
Periods INNER JOIN ([dbo_wrk grp] INNER JOIN ((Layout INNER JOIN People ON
Layout.Personid = People.id) INNER JOIN dbo_ProcedureMain ON Layout.SOPid =
dbo_ProcedureMain.id) ON [dbo_wrk grp].[wg id] = Layout.Deptid) ON Periods.ID
= Layout.Periodid WHERE (((Layout.id)=" & Me.id & "));"
rs.Open SQL

Dim Rows
For Rows = 0 To 100 Step 5
Next Rows

Debug.Print rs.Fields(0).Value, rs.Fields(1).Value, rs.Fields(2).Value,
rs.Fields(3).Value, rs.Fields(4).Value, rs.Fields(5).Value,
rs.Fields(6).Value, rs.Fields(7).Value
End Sub
 
D

Dodo

I am trying to run a code that will pull and print every fifth record
in a recordset. When this runs, I only get the first record in the
set. What am I doing wrong?


Private Sub Every5_Click()
Dim rs As ADODB.Recordset ' Declares rs as ADO Recordset
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection ' Creates open connection to
database Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn1 ' Sets cnn1 as data location for rs

Dim SQL As String
SQL = "SELECT Layout.id, People.name, [dbo_wrk grp].txt,
dbo_ProcedureMain.ProcGroup, dbo_ProcedureMain.ProcNum,
dbo_ProcedureMain.ProcName, Periods.Period, Layout.DLC, Layout.DNC
FROM Periods INNER JOIN ([dbo_wrk grp] INNER JOIN ((Layout INNER JOIN
People ON Layout.Personid = People.id) INNER JOIN dbo_ProcedureMain ON
Layout.SOPid = dbo_ProcedureMain.id) ON [dbo_wrk grp].[wg id] =
Layout.Deptid) ON Periods.ID = Layout.Periodid WHERE (((Layout.id)=" &
Me.id & "));"
rs.Open SQL

Dim Rows
For Rows = 0 To 100 Step 5
Next Rows

Debug.Print rs.Fields(0).Value, rs.Fields(1).Value,
rs.Fields(2).Value, rs.Fields(3).Value, rs.Fields(4).Value,
rs.Fields(5).Value, rs.Fields(6).Value, rs.Fields(7).Value
End Sub
Click to expand...

Would this help?

http://support.microsoft.com/?kbid=199679
 
C

Craig Alexander Morrison

Of course SQL is far more efficient than procedural programming.

SELECT Every5thCopy1.SelectedField, Every5thCopy1.AnotherField, Count(*) Mod
5 AS Rank
FROM Every5th AS Every5thCopy1 INNER JOIN Every5th AS Every5thCopy2 ON
Every5thCopy1.SelectedField >= Every5thCopy2.SelectedField
GROUP BY Every5thCopy1.SelectedField, Every5thCopy1.AnotherField
HAVING (((Count(*) Mod 5)=0));

This ranks the records and the Mod 5 selects every fifth record.

Note this SQL will not display in the Design Window because of the >=
operator on the INNER JOIN. Take the > out and display it in the window get
the fields you need working and then go back to SQL View and add the >.

Have fun!
 
C

Craig Alexander Morrison

Suggest you get the names of table and at least the join field right before
going to design window.

--
Slainte

Craig Alexander Morrison
Craig Alexander Morrison said:
Of course SQL is far more efficient than procedural programming.

SELECT Every5thCopy1.SelectedField, Every5thCopy1.AnotherField, Count(*)
Mod 5 AS Rank
FROM Every5th AS Every5thCopy1 INNER JOIN Every5th AS Every5thCopy2 ON
Every5thCopy1.SelectedField >= Every5thCopy2.SelectedField
GROUP BY Every5thCopy1.SelectedField, Every5thCopy1.AnotherField
HAVING (((Count(*) Mod 5)=0));

This ranks the records and the Mod 5 selects every fifth record.

Note this SQL will not display in the Design Window because of the >=
operator on the INNER JOIN. Take the > out and display it in the window
get the fields you need working and then go back to SQL View and add the
.
Click to expand...

Have fun!

--
Slainte

Craig Alexander Morrison
prphan said:
I am trying to run a code that will pull and print every fifth record in a
recordset. When this runs, I only get the first record in the set. What
am
I doing wrong?


Private Sub Every5_Click()
Dim rs As ADODB.Recordset ' Declares rs as ADO Recordset
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection ' Creates open connection to
database
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn1 ' Sets cnn1 as data location for rs

Dim SQL As String
SQL = "SELECT Layout.id, People.name, [dbo_wrk grp].txt,
dbo_ProcedureMain.ProcGroup, dbo_ProcedureMain.ProcNum,
dbo_ProcedureMain.ProcName, Periods.Period, Layout.DLC, Layout.DNC FROM
Periods INNER JOIN ([dbo_wrk grp] INNER JOIN ((Layout INNER JOIN People
ON
Layout.Personid = People.id) INNER JOIN dbo_ProcedureMain ON Layout.SOPid
=
dbo_ProcedureMain.id) ON [dbo_wrk grp].[wg id] = Layout.Deptid) ON
Periods.ID
= Layout.Periodid WHERE (((Layout.id)=" & Me.id & "));"
rs.Open SQL

Dim Rows
For Rows = 0 To 100 Step 5
Next Rows

Debug.Print rs.Fields(0).Value, rs.Fields(1).Value, rs.Fields(2).Value,
rs.Fields(3).Value, rs.Fields(4).Value, rs.Fields(5).Value,
rs.Fields(6).Value, rs.Fields(7).Value
End Sub
Click to expand...
Click to expand...
 
C

Craig Alexander Morrison

Well this SQL may work but it's not very efficient, oops!

--
Slainte

Craig Alexander Morrison
Craig Alexander Morrison said:
Of course SQL is far more efficient than procedural programming.

SELECT Every5thCopy1.SelectedField, Every5thCopy1.AnotherField, Count(*)
Mod 5 AS Rank
FROM Every5th AS Every5thCopy1 INNER JOIN Every5th AS Every5thCopy2 ON
Every5thCopy1.SelectedField >= Every5thCopy2.SelectedField
GROUP BY Every5thCopy1.SelectedField, Every5thCopy1.AnotherField
HAVING (((Count(*) Mod 5)=0));

This ranks the records and the Mod 5 selects every fifth record.

Note this SQL will not display in the Design Window because of the >=
operator on the INNER JOIN. Take the > out and display it in the window
get the fields you need working and then go back to SQL View and add the
.
Click to expand...

Have fun!

--
Slainte

Craig Alexander Morrison
prphan said:
I am trying to run a code that will pull and print every fifth record in a
recordset. When this runs, I only get the first record in the set. What
am
I doing wrong?


Private Sub Every5_Click()
Dim rs As ADODB.Recordset ' Declares rs as ADO Recordset
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection ' Creates open connection to
database
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn1 ' Sets cnn1 as data location for rs

Dim SQL As String
SQL = "SELECT Layout.id, People.name, [dbo_wrk grp].txt,
dbo_ProcedureMain.ProcGroup, dbo_ProcedureMain.ProcNum,
dbo_ProcedureMain.ProcName, Periods.Period, Layout.DLC, Layout.DNC FROM
Periods INNER JOIN ([dbo_wrk grp] INNER JOIN ((Layout INNER JOIN People
ON
Layout.Personid = People.id) INNER JOIN dbo_ProcedureMain ON Layout.SOPid
=
dbo_ProcedureMain.id) ON [dbo_wrk grp].[wg id] = Layout.Deptid) ON
Periods.ID
= Layout.Periodid WHERE (((Layout.id)=" & Me.id & "));"
rs.Open SQL

Dim Rows
For Rows = 0 To 100 Step 5
Next Rows

Debug.Print rs.Fields(0).Value, rs.Fields(1).Value, rs.Fields(2).Value,
rs.Fields(3).Value, rs.Fields(4).Value, rs.Fields(5).Value,
rs.Fields(6).Value, rs.Fields(7).Value
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I write a SELECT query to get every fifth record? 2

Top