RecordCount not working... 2

[Quintios]

I have the following code. It's supposed to count the number of records in one recordset and compare that to the number of records in the second recordset:

Dim dbTag As Database
Dim dbStep As Database
Dim rsTag As DAO.Recordset
Dim rsStep As DAO.Recordset
Dim strSQLSource As String
Dim TagRecord As Record

    strSQLSource = "SELECT TagName, StepTagName FROM tblStepActions"
    strSQLSource = strSQLSource & " WHERE TagName = " & "'" & Me.TagName & "'"

    Set dbTag = CurrentDb()
    Set rsTag = dbTag.OpenRecordset(strSQLSource)

    strSQLSource = "SELECT TagName, Sequence, TagTypeID FROM tblTags"
    strSQLSource = strSQLSource & " WHERE Tagtypeid = 9"
    strSQLSource = strSQLSource & " AND Sequence = " & Me.SeqNumber
    
    Do While Not rsTag.EOF
        rsTag.MoveNext
    Loop
    
    Set dbStep = CurrentDb()
    Set rsStep = dbStep.OpenRecordset(strSQLSource)

    Debug.Print rsTag.RecordCount
    Debug.Print rsStep.RecordCount
    
    rsTag.Close 
    dbTag.Close
    rsStep.Close
    dbStep.Close
       
End Sub

The odd thing is, unless I loop through the first recordset, it returns the value '1' no matter how many records I have in the set. With the loop in I get the correct number.

What gives?

Thanks in advance,

Onwards,

Q-

 

[KyleS]

I do know that sometimes it's required to do a "rst.MoveLast" before using a recordcount. I don't know why, but it's just become second nature. As a side note, you don't need 2 database variables in this code, since they're pointing to the same DB, you could also get rid of both of them and just use "CurrentDB.OpenRecordset(strSQLSource)" Kyle :

 

[Quintios]

Hey, thanks for the info, and the tip!

One star for you!

Onwards,

Q-

 

[WildHare]

You might find the DCount Function a bit less cumbersome as well.

Z=Dcount("{key-field}", "{Recordset}&quot
ZZ = DCount("{key-field)", "{Other-recordset}&quot

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[scking]

Kyle was pretty well right on. Here is the standard template. After you do a rst.MoveFirst if there are records it will list 1 in all cases.

Set Db = CurrentDb()
Set rst = CurrentDb.OpenRecordset(strSQLSource)
rst.MoveFirst
If rst.RecordCount <> 0 Then
Do While Not rst.EOF
' Do your thing
rst.MoveNext
Loop
End If

Steve King Growth follows a healthy professional curiosity