dao.recordset - only do 10

[Ruegen]

I'm trying to move through a recordset but I only want it to move through 10 records and then stop (even if the record count is higher)



I tried a for loop but I get mixed results.

a rs.eof works fine but I don't want all the results I only want the 10 of those (or less if there are less)

remember if there are less than 10 records I can't use rs.movenext or I get an error as there aren't more than 10


suggestions?

Dim rs As dao.Recordset
Dim db As dao.Database


Dim strSQL As String


strSQL = "SELECT tblSchoolMemos.*, tblSchoolMemos.ID " & _
"FROM tblSchoolMemos " & _
"ORDER BY tblSchoolMemos.ID;"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
TextBoxControl = ""




rs.MoveFirst
rs.MoveLast


    If rs.RecordCount = 0 Then
    TextBoxControl = "No contact history as of yet"
    Else
Dim countRec As Long
countRec = rs.RecordCount
Debug.Print String(255, vbNewLine)
Debug.Print countRec


rs.MoveFirst
For i = 0 To IIf(countRec >= 10, 10, countRec)
 i = i + 1

            TextBoxControl = i & "   " & rs!contactmemo & "   " & rs!memodate & vbCrLf & TextBoxControl
            rs.MoveNext
Next


    End If
rs.Close
Set rs = Nothing

[Ruegen]

This works too however I can't limit it to ten loops or less

Dim rs As dao.Recordset
Dim db As dao.Database


Dim strSQL As String


strSQL = "SELECT tblSchoolMemos.*, tblSchoolMemos.ID " & _
"FROM tblSchoolMemos " & _
"ORDER BY tblSchoolMemos.ID;"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
TextBoxControl = ""




rs.MoveFirst
rs.MoveLast


    If rs.RecordCount = 0 Then
    TextBoxControl = "No contact history as of yet"
    Else


Debug.Print String(255, vbNewLine); o




rs.MoveFirst
Do While Not rs.EOF
            
     
            TextBoxControl =  "   " & rs!contactmemo & "   " & rs!memodate & vbCrLf & TextBoxControl
            rs.MoveNext
Loop


    End If
rs.Close
Set rs = Nothing

[amrut]

You can use SELECT TOP 10 instead of selecting all records in the SQL string. You must select the fields to be sorted precisely so as to achieve desired results. Another option is using a counter as you have done in post #1 and testing its value as you loop through the recordset. Perform the calculations as long as it is less then 10 else continue to MoveNext.

i=0
Do While Not rs.EOF
i=i+1
if i<=10 then     
'perform your calculations
EndIf
rs.MoveNext
Loop

[Ruegen]

You can use SELECT TOP 10 instead of selecting all records in the SQL string. You must select the fields to be sorted precisely so as to achieve desired results. Another option is using a counter as you have done in post #1 and testing its value as you loop through the recordset. Perform the calculations as long as it is less then 10 else continue to MoveNext.

i=0
Do While Not rs.EOF
i=i+1
if i<=10 then     
'perform your calculations
EndIf
rs.MoveNext
Loop

Yeah, again that method doesn't work either. As soon as I try stuff like this it doesn't give me what I need correctly. It's very weird.

I might have to make the SQL string limited to 10 results.

[Ruegen]

I have

Dim rs As dao.Recordset
Dim db As dao.Database


Dim strSQL As String


strSQL = "SELECT TOP 10 tblSchoolMemos.*, tblSchoolMemos.ID " & _
"FROM tblSchoolMemos " & _
"ORDER BY tblSchoolMemos.ID desc;"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
TextBoxControl = ""




rs.MoveFirst
rs.MoveLast


    If rs.RecordCount = 0 Then
    TextBoxControl = "No contact history as of yet"
    Else




Debug.Print String(255, vbNewLine); o


rs.MoveFirst
Do Until rs.EOF




TextBoxControl = TextBoxControl & rs!memodate & "   " & rs!contactmemo & vbCrLf
rs.MoveNext
Loop






    End If
rs.Close
Set rs = Nothing

it works and I think I will use this method.