The overall objective is to sort a recordset by clicking a command
button at the top of the column. The following code is my coding
effort and I can't even get past the basics.
The strSQL creates the correct recordset (30 records) when I plug it
into the SQL query window but it returns 30 identical copies of the
first record once it gets to the rst object. Can anyone shed some
light as to what is going on. Thanks
Private Sub cmdSortYOB_Click()
Dim X As Integer
Dim strSQL As String
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim rss As DAO.Recordset
strSQL = "select * from tblPersons" _
& " WHERE tblPersons.txtLastName = '" & Me.txtCboAft & "'"
Debug.Print strSQL
Set rst = CurrentDb.OpenRecordset(strSQL)
' REM rst.Sort = "[intYOB]"
' REM Set rss = rst.OpenRecordset
With rst
.MoveLast
.MoveFirst
intCount = .RecordCount
Debug.Print intCount
For X = 1 To intCount
Debug.Print X; Me.txtLastName & IntYOB
.MoveNext
Next X
End With
End Sub

Me.txtLastName indicates that you are running this on a form and you are
printing the value of the control on the form

Try the following.
With rst
.MoveLast
.MoveFirst
intCount = .RecordCount
Debug.Print intCount
For X = 1 To intCount
Debug.Print X; .Fields("txtLastName") & .Fields("IntYOB")
.MoveNext
Next X
End With

If all your are trying to do is sort a recordset, I would just add a sort to
the query string.

strSQL = "SELECT * FROM tblPersons" & _
" WHERE txtLastName ='" & me.txtCboAft & "'" & _
" ORDER BY intYOB"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

TeeSee wrote:
> The overall objective is to sort a recordset by clicking a command
> button at the top of the column. The following code is my coding
> effort and I can't even get past the basics.
> The strSQL creates the correct recordset (30 records) when I plug it
> into the SQL query window but it returns 30 identical copies of the
> first record once it gets to the rst object. Can anyone shed some
> light as to what is going on. Thanks
> Private Sub cmdSortYOB_Click()
> Dim X As Integer
> Dim strSQL As String
> Dim intCount As Integer
> Dim rst As DAO.Recordset
> Dim rss As DAO.Recordset
> strSQL = "select * from tblPersons" _
> & " WHERE tblPersons.txtLastName = '" & Me.txtCboAft & "'"
> Debug.Print strSQL
> Set rst = CurrentDb.OpenRecordset(strSQL)
> ' REM rst.Sort = "[intYOB]"
> ' REM Set rss = rst.OpenRecordset
> With rst
> .MoveLast
> .MoveFirst
> intCount = .RecordCount
> Debug.Print intCount
> For X = 1 To intCount
> Debug.Print X; Me.txtLastName & IntYOB
> .MoveNext
> Next X
> End With
> End Sub