VBA for each

[K. Wolfe]

If I have the following:

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]

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]

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]

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]

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]

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]

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]

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]

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

[DaveMaxwell]

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]

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

[siteguru]

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

[K. Wolfe]

Yes I see that.

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

Im getting subscipt out of range error

[DaveMaxwell]

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

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]

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]

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]

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?

[DaveMaxwell]

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.

[K. Wolfe]

Yes I am starting from 0. I try to access it directly via 0,0 and still goes to second result

[DaveMaxwell]

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.