For Loop error

[anduril12]

I'm trying to create an "For Each...Next" setup where I can go through every row on a table. I need to check a specific column, and depending on that value I have a count running. It seems to work but it keeps raising a "Error 424: Object required" box.

Any Ideas? Btw this code is being executed on a form that displays statistics.

For Each dr In Tables.FCG10.rows
    Select Case dr.[field]
        Case "person1"
            longGuinta = longGuinta + 1
        Case Is = Null
            longUncontacted = longUncontacted + 1
    End Select
Next dr

[Jim Doherty]

Hello Anduril12 :)

I am not sure I quite understand the actual specifics intentions of your code post. My assumption (and I may well be wrong in my interpretation) is that you want to examine a column named 'person1' in a table called FCG10 and then loop down that column to apply some logic as posted to increment your variables+1

I see you framing of your code is inclined towards looping for each field but what is the point if you know the name of the column? Why not just call that individual column as a 'single column' dataset ie not drag up the entire table.

The following is my interpretation of what I 'think' you are trying to do

 Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  strSQL = "SELECT FCG10.person1 FROM FCG10;"

  Set dbs = CurrentDb()
  Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

  Do While Not rst.EOF
    If IsNull(rst!person1) Then
        longUncontacted = longUncontacted + 1
    Else
        longGuinta = longGuinta + 1
    End If
  rst.MoveNext
  Loop
  rst.Close
  dbs.Close
  Set rst = Nothing
  Set dbs = Nothing

[anduril12]

@Jim Doherty

Thanks, that works perfectly! I sincerely appreciate your help, it wasn't exactly where I was going (I didn't even know that you could call up a single column) but it's great!

~anduril

[Jim Doherty]

Yes you can :) It is a fundamental part of calling any recordset namely to proclaim the bounds of your dataset. I wish more people were considering of this instead of opening entire tables every time. It might reduce the moans much later about performance hits and memory loss through lack of foresight...... ..just my two cents worth...rant end hahaha

Glad it helped you :)

[NeoPa]

The error message is the clue here SwordMan.

For ... Each constructs work only for arrays and object collections. A recordset doesn't equate to a collection (which are typically held internally rather than remotely on disk).

Jim has already shown how Recordsets should be manipulated, and given some further good explanations with it, so I won't go there. I just wanted to explain exactly why you got your error message.

[anduril12]

@NeoPa That makes a whole lot of sense! I'm glad that I understand how it really works now, I think that this will help me allot in my future usage. Thank you both so much, I was so completely stuck.

[NeoPa]

You're most welcome.

BTW I've selected the best answer for you :)

Welcome to Bytes!