Order By Problem

  • Thread starter Thread starter JamesJ
  • Start date Start date
J

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
 
JamesJ said:
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
Click to expand...

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
 
Back
Top