Recordset.RecordCount is wrong ??

[Steve Perrins]

When I query across two tables the RecordCount = 1 when there are in fact 13
records returned :-
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim strOuts() As String
Dim nBudg() As Currency
Dim nTBudg As Currency
Dim nActual() As Currency
Dim nTActual As Currency
Dim nAlts() As Currency
Dim nTAlts As Currency
Dim nFDiff As Currency
Dim nODiff As Currency
Dim nCount As Integer

' Get the budget outgoings and amount
Set db = CurrentDb
strSql = "SELECT proj_outs.ID, proj_outs.outgoing, proj_outs.cost_type,
proj_outs.amount, proj_outs.adjustment, " & _
"proj_budg.budg_status " & _
"FROM proj_outs, proj_budg WHERE proj_outs.ID=proj_budg.POUTID
" & _
"AND proj_outs.PROJID=" & strID & " AND
proj_outs.inc_inbudg=Yes;"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount = 0 Then
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
End If

ReDim strOuts(rs.RecordCount, 4)
ReDim nBudg(rs.RecordCount)
ReDim nActual(rs.RecordCount)
ReDim nAlts(rs.RecordCount)
nCount = 0
nTBudg = 0
nTActual = 0
nFDiff = 0
nODiff = 0
nTAlts = 0
rs.MoveFirst
Do Until rs.EOF
' Lines commented out for debugging
' strOuts(nCount, 0) = rs!outgoing
' strOuts(nCount, 2) = rs!ID
' If StrComp("Purchase", rs!cost_type, 1) = 0 Then
' strOuts(nCount, 1) = "P"
' End If
' If StrComp("Expense", rs!cost_type, 1) = 0 Then
' strOuts(nCount, 1) = "E"
' End If
' If StrComp("Finance", rs!cost_type, 1) = 0 Then
' strOuts(nCount, 1) = "F"
' End If
' If StrComp("Finished", rs!budg_status, 1) = 0 Then
' strOuts(nCount, 3) = "F"
' Else
' strOuts(nCount, 3) = "O"
' End If
' nBudg(nCount) = rs!amount
' nTBudg = nTBudg + nBudg(nCount)
' nAlts(nCount) = rs!adjustment
' nTAlts = nTAlts + nAlts(nCount)
rs.MoveNext
nCount = nCount + 1
Loop
rs.Close

After looping through the recordset the recordcount is correct.
As I am using the record count to set the size of the arrays in my code, I
am going to have to loop through all records first then size the arrays,
then MoveFirst and then loop through the recordset again processing the
records.

Is this a bug or am I doing something wrong here?

Regards,

Steve Perrins

 

[Nikos Yannacopoulos]

Steve,

This is a VBA / DAO bug, I believe... to get the correct recordcount,
you need to force a move away from the first record first. Try:

rs.MoveLast
rs.MoveFirst
MyVal = rs.RecordCount

to get the correct number.

HTH,
Nikos

 

[Steve Perrins]

Nikos,

Thanks for the quick response.

I have done what you suggested and it does correct the value in the
recordcount, thank you.

Regards,

Steve Perrins

 

[Allen Browne]

Steve, you might appreciate this article:
VBA Traps: Working with Recordsets
at:
http://allenbrowne.com/ser-29.html
It is the 2nd item listed.

Nikos, I don't think I would call this a bug, so much as a trap. The
RecordCount is the number of records accessed so far, which will typically
be 0 (none) or 1 (some) for a dynaset type recordset.

 

[Nikos Yannacopoulos]

Allen,

The "accessed so far" part was the key... I didn't know. Thanks for
clarifying.

Regards,
Nikos

 

[RD]

Steve, you might appreciate this article:
VBA Traps: Working with Recordsets
at:
http://allenbrowne.com/ser-29.html
It is the 2nd item listed.

Nikos, I don't think I would call this a bug, so much as a trap. The
RecordCount is the number of records accessed so far, which will typically
be 0 (none) or 1 (some) for a dynaset type recordset.

Straight from Help:

Use the RecordCount property to find out how many records in a Recordset or
TableDef object have been accessed. The RecordCount property doesn't indicate
how many records are contained in a dynaset-, snapshot-, or forward-only type
Recordset object until all records have been accessed. Once the last record has
been accessed, the RecordCount property indicates the total number of undeleted
records in the Recordset or TableDef object. To force the last record to be
accessed, use the MoveLast method on the Recordset object. You can also use an
SQL Count function to determine the approximate number of records your query
will return.

Note Using the MoveLast method to populate a newly opened Recordset negatively
impacts performance. Unless it is necessary to have an accurate RecordCount as
soon as you open a Recordset, it's better to wait until you populate the
Recordset with other portions of code before checking the RecordCount property.


Regards,
RD