Record Count always 100?

[kirkm]

Function MyBBList() As DAO.Database
    Set MyBBList = DAO.OpenDatabase("\\Win10-pc\Desktop\TestBBTableMay2020 (1222a).mdb", False, False)
End Function

Function GetBBPrefixAndLC(y, Title) As DAO.Recordset
    Dim sql As String
             'Construct sql

    With MyBBList.CreateQueryDef("", sql)
        If .OpenRecordset.RecordCount > 0 Then
        .OpenRecordset.MoveLast
        .OpenRecordset.MoveFirst
        End If
        Set GetBBPrefixAndLC = .OpenRecordset
        .Close
    End With
End Function

Normally I find MoveLast will give the correct Record Count (if it's more than 0)
But in this case it's not. It's always 100. Can I get the 'correct' count somehow?

 

[Gasman]

Wouldn't that depend on whatever sql is?

 

[arnelgp]

how about:

Function GetBBPrefixAndLC(y, Title) As DAO.Recordset
    Dim sql As String
             'Construct sql
    Dim rs As DAO.Recordset
    Set rs = MyBBList.CreateQueryDef("", sql).OpenRecordset
    With rs
        If Not (.BOF And EOF) Then
            .MoveLast
            .MoveFirst
        End If
        Set GetBBPrefixAndLC = rs
        .Close
    End With
End Function

 

[kirkm]

Thanks arne, that lets me get the correct record count; any idea why my routine returns either 0 or 100 ? Just curious...

 

[kirkm]

Struck a problem with Set GetBBPrefixAndLC = rs
The function doesn't return the recordset (as it does with Set GetBBPrefixAndLC = .OpenRecordset)
And causes error 3420 Object Invalid or no longer set
Any thoughts on that ?

 

[arnelgp]

don't close rs

Function GetBBPrefixAndLC(y, Title) As DAO.Recordset
    Dim sql As String
             'Construct sql
    Dim rs As DAO.Recordset
    Set rs = MyBBList.CreateQueryDef("", sql).OpenRecordset
    With rs
        If Not (.BOF And EOF) Then
            .MoveLast
            .MoveFirst
        End If
        Set GetBBPrefixAndLC = rs
    End With
End Function

 

[kirkm]

Well that was pretty easy! Kindof weird tho as hasn't it been assigned to another in the previous line?