Recordset.RecordCount not accurate 1

[JTBorton]

I am trying to place all of the records in a record set into an array so that I can use the values without having to keep the recordset. There are 6 records in the recordset. The value of the record count changes as I step through the code. At the beginning of the code, the recordcount has a value of 1, so strFilterOptions is dimensioned from 1 to 3. But as the code progresses, the recordcount value suddenly changes to 6, the correct amount. Since the array is dimensioned to 3, the Do While loop results in a subscript out of range error for the array.

strSQL = "SELECT [Reason] FROM tblOutOfTownReasons"
    Set rstRecords = dbDataBase.OpenRecordset(strSQL)
    Debug.Print rstRecords.RecordCount [COLOR=red] (Here the recordcount is 1) [/color]
    ReDim strFilterOptions(1 To rstRecords.RecordCount + 2)
    strFilterOptions(1) = "All Contacts"
    strFilterOptions(2) = "In Town"
    J = 2
    rstRecords.MoveFirst
    Do While Not rstRecords.EOF [COLOR=red] (Here the recordcount value suddenly changes to the correct amount) [/color]
        J = J + 1
        strFilterOptions(J) = rstRecords![Reason]
        rstRecords.MoveNext
    Loop

Can someone tell my why this is happening?

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

As a side note, I've already found a workaround by using redim preserve in the Do While loop. But I'd like to know what I did wrong in the original code.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[dhookom]

You need to .MoveLast prior to getting an accurate count of records.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

In addition, so that you do not throw an error, the usual technique is to check that the rs has records and then move last and then move back. Certain DAO recordsets will always give you the correct count, but I forget the rules. So this technique is always a good idea before returning the count.

if not (rs.EOF and rs.BOF) then
rs.Movelast
rs.movefirst
end if
'now get count

 

[JTBorton]

Well, I had tried the .movefirst method, but it had no affect. Why does the DAO model require you to do this before it can get an accurate record count?

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Let me be more clear on my above statement: when I was originally debugging it I tried placing a movefirst statement above the RecordCount method like so,

    strSQL = "SELECT [Reason] FROM tblOutOfTownReasons"
    Set rstRecords = dbDataBase.OpenRecordset(strSQL)
    rstRecords.MoveFirst
    ReDim strFilterOptions(1 To rstRecords.RecordCount + 2)

But even with this statement, it was not until the EOF statement that the accurate record count appeared.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[MajP]

I do not know why, but in the case of dynaset-, snapshot-, or forward-only-type recordsets, you need to access all the records in the recordset before getting an accurate count of the records. Movefirst will not help.

 

[PHV]

Seems like you missed the point: .Move[!]Last[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

no no, I got the point. It's just that MajP had illustrated that MoveFirst and MoveLast are used and I was replying to that statement.

if not (rs.EOF and rs.BOF) then
rs.Movelast
rs.movefirst
end if
'now get count

It wasn't until after I posted that I realized he meant they were used together and not interchangeably, much to my stupidity. I get a little busy sometimes and start missing some of the details.

Open Mouth. Insert Foot.

However I am confused on the use of rs.EOF and rs.BOF. Doesn't seem to make much sense. It seems to my abundantly limited brain that the only time the EOF and BOF could both be true is if there are no records. Or is this just a test if the recordset is empty?

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[MajP]

That is correct. That is how you test if records exist. If it is at the BOF and the EOF at the same time then there must be no records. If you execute a move command when there is no records you will get an error. So check first for records then move last and then back to first.

 

[JTBorton]

Thank MajP, that helps a lot.

-Joshua
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]