Order By Problem

[JamesJ]

When I use the following sql the label populated by the code
indicates that there is only 1 item. When I got to the second
record it displays the correct number of items (246)
Here's the odd part. When I remove DvdMovieTypeID from the
ORDER BY clause the RecCount function returns the proper number of records.

SELECT * FROM tblDvd ORDER BY DvdMovieTypeID, DvdMovieTitle

Public Function RecCount()

On Error Resume Next

Dim intCount As Integer

With CodeContextObject
intCount = .RecordsetClone.RecordCount
!lblCount.Caption = intCount & " Item(s)"
End With

End Function

Any help will be appreciated,
James

 

[Dirk Goldgar]

When I use the following sql the label populated by the code
indicates that there is only 1 item. When I got to the second
record it displays the correct number of items (246)
Here's the odd part. When I remove DvdMovieTypeID from the
ORDER BY clause the RecCount function returns the proper number of
records.

SELECT * FROM tblDvd ORDER BY DvdMovieTypeID, DvdMovieTitle

Public Function RecCount()

On Error Resume Next

Dim intCount As Integer

With CodeContextObject
intCount = .RecordsetClone.RecordCount
!lblCount.Caption = intCount & " Item(s)"
End With

End Function

Any help will be appreciated,
James

Presumably this represents a timing difference caused by the additional
sort field. If the field DvdMovieTypeID is not indexed, maybe indexing
it would improve matters. However, the most reliable approach would be
to move the RecordsetClone to the last record before getting its
RecordCount:

With CodeContextObject
With .RecordsetClone
.MoveLast
intCount = .RecordCount
End With
!lblCount.Caption = intCount & " Item(s)"
End With

 

[JamesJ]

MoveLast worked. I'll check to see if this
field is indexed.

Thanks,
James