By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

For Loop error

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1. For Each dr In Tables.FCG10.rows
  2.     Select Case dr.[field]
  3.         Case "person1"
  4.             longGuinta = longGuinta + 1
  5.         Case Is = Null
  6.             longUncontacted = longUncontacted + 1
  7.     End Select
  8. Next dr
  9.  
May 8 '10 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1.  Dim dbs As DAO.Database
  2.   Dim rst As DAO.Recordset
  3.   Dim strSQL As String
  4.   strSQL = "SELECT FCG10.person1 FROM FCG10;"
  5.  
  6.   Set dbs = CurrentDb()
  7.   Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
  8.  
  9.   Do While Not rst.EOF
  10.     If IsNull(rst!person1) Then
  11.         longUncontacted = longUncontacted + 1
  12.     Else
  13.         longGuinta = longGuinta + 1
  14.     End If
  15.   rst.MoveNext
  16.   Loop
  17.   rst.Close
  18.   dbs.Close
  19.   Set rst = Nothing
  20.   Set dbs = Nothing

Share this Question
Share on Google+
6 Replies


Jim Doherty
Expert 100+
P: 897
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

Expand|Select|Wrap|Line Numbers
  1.  Dim dbs As DAO.Database
  2.   Dim rst As DAO.Recordset
  3.   Dim strSQL As String
  4.   strSQL = "SELECT FCG10.person1 FROM FCG10;"
  5.  
  6.   Set dbs = CurrentDb()
  7.   Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
  8.  
  9.   Do While Not rst.EOF
  10.     If IsNull(rst!person1) Then
  11.         longUncontacted = longUncontacted + 1
  12.     Else
  13.         longGuinta = longGuinta + 1
  14.     End If
  15.   rst.MoveNext
  16.   Loop
  17.   rst.Close
  18.   dbs.Close
  19.   Set rst = Nothing
  20.   Set dbs = Nothing
May 8 '10 #2

P: 6
@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
May 9 '10 #3

Jim Doherty
Expert 100+
P: 897
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 :)
May 9 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 9 '10 #5

P: 6
@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.
May 9 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
You're most welcome.

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

Welcome to Bytes!
May 10 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.