looping through all records is only providing values for 1st record

[markjkubicki]

the following codes 'seems' to loop through all of the records in the table, yet the debug.print shows that the values being returned are always those of the first record in the table.


this code is in the Load event of the form; the results of the loop are assigned to values in the report header section.
fields FxtTag and Qty are valid fields (with entries)

 
    Dim intCnt_Comply As Integer
    Dim intCnt_NotComply As Integer
    
    intCnt_Comply = 0
    intCnt_NotComply = 0
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbeFxtTagDetails")

    'Check to see if the recordset actually contains rows
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True  'loop through all the records in the table
            Debug.Print FxtTag & "  |  " & CStr(Qty)
            If [Efficacy] >= intNYC_MinLampEff Then
                intCnt_Comply = intCnt_Comply + Nz(Qty, 0)
            Else
                intCnt_NotComply = intCnt_NotComply + Nz(Qty, 0)
            End If
            rs.MoveNext
        Loop
    'Else
    '    MsgBox "There are no records in the recordset."
    End If

    MsgBox "Finished looping through records."

    rs.Close
    Set rs = Nothing

i'm confused (seems like i've ran variations of this code a billion-zillion times; yet tonight (!?!)

with appreciation in advance,
m.

[Minty]

You aren't referring to your recordset object?

Debug.Print FxtTag & " | " & CStr(Qty)

Should be

Debug.Print rs!FxtTag & " | " & CStr(rs!Qty)

[Welshgasman]

Congratulations. You are doing what another member does here, on several occasions.

They walk a recordset but use the form controls, and wonder why the values are the same all the time.

They have been playing with Access for over 19 years as well?

[Minty]

This is why, although it's not absolutely necessary, I still always refer to a forms or reports objects with Me.
It invokes intellisense and guarantees I know precisely what object I'm referring to.

[Welshgasman]

This is why, although it's not absolutely necessary, I still always refer to a forms or reports objects with Me.
It invokes intellisense and guarantees I know precisely what object I'm referring to.

Me.too. Pun intended.

[markjkubicki]

a mentor of mine ALWAYS used to say to me:
"when all else fails: check the obvious !"


much appreciation'
m.

[Welshgasman]

a mentor of mine ALWAYS used to say to me:
"when all else fails: check the obvious !"


much appreciation'
m.

Sometimes you cannot see the wood for the trees. :-)

I just asked a question about a report, and I was using a form.