SitePoint Sponsor |
|
User Tag List
Results 1 to 20 of 20
Thread: VBA for each
-
Feb 4, 2011, 15:43 #1
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
VBA for each
If I have the following:
Code:Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT ID, assigned FROM tbl142", dbOpenDynaset)
Kyle Wolfe
-
Feb 5, 2011, 12:55 #2
- Join Date
- Jan 2011
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code ASP:WHILE NOT (rs.EOF) '-- Your Code Here rs.MoveNext WEND
Loops through the record set while it hasn't reached End Of File. I don't think you need a for each because you can already access the values of the record by name. rs("MyFieldValue")
-
Feb 5, 2011, 13:00 #3
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
See I wound up going with this solution by why does everyone use this method rather than for each? That's what that loop is made for...
Kyle Wolfe
-
Feb 5, 2011, 16:25 #4
- Join Date
- Apr 2009
- Posts
- 359
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
This is really more a VB topic, but
That's what that loop is made for...
I think you can use for each on a recordset anyway if you want, but I'm one that always uses do loops on recordsets.Doug G
=====
"If you ain't the lead dog, the view is always the same - Anon
-
Feb 5, 2011, 16:55 #5
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
The three or four that exist in any language have their purpose. For each should do exactly what it sounds like. For each in array. Do while is a true loops that can be done until a certain criteria is established. Yes Do while works here but the "for each" is designed to do exactly what I'm trying to do here, which is to run through every item in an array...
Kyle Wolfe
-
Feb 5, 2011, 19:02 #6
- Join Date
- Apr 2009
- Posts
- 359
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Yes Do while works here but the "for each" is designed to do exactly what I'm trying to do here, which is to run through every item in an array...Doug G
=====
"If you ain't the lead dog, the view is always the same - Anon
-
Feb 7, 2011, 07:15 #7
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
How is it not an array? Other than the technical "its not defined as one". It most definitely is a multi demensional array and for each is designed to do a specefic task for each in an array.
Kyle Wolfe
-
Feb 7, 2011, 07:49 #8
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Technically, it's not an array - it's a collection. A slight difference, but a difference nonetheless.
If you want to get it into an array, then use the getRows method which moves the collection into a two dimensional array.Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 7, 2011, 11:18 #9
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
Ok. Additionally, how can I access the value of a field by its record number? Ie. rs(2)("fieldname")
Kyle Wolfe
-
Feb 7, 2011, 12:13 #10
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
If the array is called arrRecSet, then the values are accessed via
strfieldValue = arrRecSet(fieldID, rowID) where fieldID is based on the order of the fields being selected and the rowID is the row in the recordset array being returned.
If you need the column names being returned, you can use this method: http://www.codefixer.com/articles/getrows.htmDave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 7, 2011, 12:22 #11
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
This doesn't work for me. Do I have to convert the recrdset into an array first somehow?
Kyle Wolfe
-
Feb 7, 2011, 12:41 #12
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Um - that was already said (post #8). Use GetRows() to convert the recordset to a 2-dimensional array.
-
Feb 7, 2011, 12:43 #13
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
Yes I see that.
Code:myArray = rsEmps.GetRows msgBox myArray(1,1)
Kyle Wolfe
-
Feb 7, 2011, 12:55 #14
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Did you read either of the links I provided you? They both explained the concept of getRows pretty cleanly and had decent code examples.
But to save time, here is the basic code structure I used for this type of activity. It's been a year or two, so the syntax might be a little off, but you should be able to clean those up....
Code ASP:dim strSQL, rs, arrRecSet, recCount dim varField1, varField2, varField3 strSQL = "SELECT field1, field2, field3 FROM tableName" set rs = Server.CreateObject("ADODB.recordset") rs.Open strSQL, conn if rs.EOF and rs.BOF then recCount = -1 else ' Move the values into array and get count arrRecSet = rs.GetRows(-1) : recCount = UBound(arrRecSet(0, 2)) end if ' Loop through entire recordset... for i = 0 to recCount 'move fields into temp readable fields for readability sake set varField1 = arrRecSet(0, i) : set varField2 = arrRecSet(1, i) : set varField3 = arrRecSet(3, i) ' Now do whatever you need to with values.... Response.Write "Field1 = " & varField1 & vbCrLf Response.Write "Field2 = " & varField2 & vbCrLf Response.Write "Field3 = " & varField3 & vbCrLf next
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 7, 2011, 13:18 #15
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
From your code I get invalid arguement on arrRecSet = rs.GetRows(-1)
Also I had one working except it was always skipping over the first row?Kyle Wolfe
-
Feb 7, 2011, 14:21 #16
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
That line should be fine (I've used it probably 1000 times). There was a typo though.
recCount = UBound(arrRecSet(0, 2)) should be recCount = UBound(arrRecSet, 2)Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 7, 2011, 14:37 #17
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
Not sure but having -1 for getRows arguement just isn't working. Again though. Why is it always skipping the first result? Is that what the -1 is supposed to fix?
Kyle Wolfe
-
Feb 7, 2011, 14:44 #18
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Nope - the -1 tells it to get all the records.
On your for loop - are you starting from 0 or 1? You need to start from 0.Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 7, 2011, 14:45 #19
- Join Date
- Nov 2003
- Location
- Columbus, OH
- Posts
- 2,182
- Mentioned
- 67 Post(s)
- Tagged
- 2 Thread(s)
Yes I am starting from 0. I try to access it directly via 0,0 and still goes to second result
Kyle Wolfe
-
Feb 8, 2011, 06:07 #20
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
I think we need to see your code to help you out further - there's a piece of information there that's causing the problems.
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
Bookmarks