VBA for each

**K. Wolfe** · Feb 4, 2011 15:43

If I have the following:

Code:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ID, assigned FROM tbl142", dbOpenDynaset)

How do I reference that to make a for each loop?

**WebDevGirl** · Feb 5, 2011 12:55

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")

**K. Wolfe** · Feb 5, 2011 13:00

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...

**Doug G** · Feb 5, 2011 16:25

This is really more a VB topic, but

That's what that loop is made for...

If this were true there would only be one loop construct in VB, not the three or four that exist.

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.

**K. Wolfe** · Feb 5, 2011 16:55

Originally Posted by Doug G

This is really more a VB topic, but
If this were true there would only be one loop construct in VB, not the three or four that exist.

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.

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...

**Doug G** · Feb 5, 2011 19:02

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...

An ADO recordset is not an array.

**K. Wolfe** · Feb 7, 2011 07:15

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.

**DaveMaxwell** · Feb 7, 2011 07:49

Originally Posted by K. Wolfe

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.

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.

**K. Wolfe** · Feb 7, 2011 11:18

Ok. Additionally, how can I access the value of a field by its record number? Ie. rs(2)("fieldname")

**DaveMaxwell** · Feb 7, 2011 12:13

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.htm

**K. Wolfe** · Feb 7, 2011 12:22

This doesn't work for me. Do I have to convert the recrdset into an array first somehow?

**siteguru** · Feb 7, 2011 12:41

Um - that was already said (post #8). Use GetRows() to convert the recordset to a 2-dimensional array.

**K. Wolfe** · Feb 7, 2011 12:43

Yes I see that.

Code:

myArray = rsEmps.GetRows
msgBox myArray(1,1)

Im getting subscipt out of range error

**DaveMaxwell** · Feb 7, 2011 12:55

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

**K. Wolfe** · Feb 7, 2011 13:18

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?

**DaveMaxwell** · Feb 7, 2011 14:21

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)

**K. Wolfe** · Feb 7, 2011 14:37

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?