.recordcount returning odd values...?

[mikewood1980]

Hi Guys

I have the following code:

 sQuery = "select * from tblBoardChoices where ((tblBoardChoices.intMassPerM2) >= " & intBoard1Low & ")"
    
    MsgBox (sQuery)
    
    Set rsBoardTypes = dbcurr.OpenRecordset(sQuery)
    
    rsBoardTypes.MoveFirst
    MsgBox (rsBoardTypes.RecordCount)
    
    
    Do Until rsBoardTypes.EOF = True
        
        MsgBox (rsBoardTypes!chrBoardTypes)
        rsBoardTypes.MoveNext
        
        
    Loop

    MsgBox (rsBoardTypes.RecordCount)

The first message box (MsgBox (rsBoardTypes.RecordCount)) returns a value of 1 all the time and the second (MsgBox (rsBoardTypes.RecordCount)) returns the correct value - why is this?? I thought that record count just returned the number a values in a recordset?

If anyone can help I would really appreciate it

Thanks
Mike

 

[namliam]

Becuase at the moment of the first messagebox access has not yet completly opened the recordset. It only knows there is data, therefor returns a 1.

Tho not pretty, this will resolve your issue
rsBoardTypes.MoveLast
rsBoardTypes.MoveFirst
MsgBox (rsBoardTypes.RecordCount)

 

[DCrake]

Simple Software Solutions

If you are using DAO you have to move the recordpointer to the last record before you can get the record count. ADODB does not have this issue. If you are not using either then again you have to use the .MoveLast to get the record count.

CodeMaster:

 

[mikewood1980]

Cheers Guys

Just tried that and it solved the problem!
Thanks again!
Mike

 

[LPurvis]

To be fair - it isn't really an "issue".
It's a design feature. DAO dynaset and snapshot recordsets don't populate completely upon loading. It's intentional and a deliberate response to Jet's iterative loading method.
So the recordcount is accurate - just the recordset isn't yet in a state to access all records (until you've navigated to the end).
Table Type recordsets do offer the recordcount upon opening - but then a table offers its count of records as a property.

ADO has a much broader intended source base and loading completely upon opening was more appropriate (as only database engines are really going to provide rows in batches).
But, of course, ForwardOnly cursor type recordsets don't support it at all (and that's what you'll get if you don't specify otherwise).