SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Thread: VBA for each

  1. #1
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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)
    How do I reference that to make a for each loop?

  2. #2
    SitePoint Member
    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")

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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...

  4. #4
    SitePoint Addict
    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...
    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.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Doug G View Post
    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...

  6. #6
    SitePoint Addict
    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...
    An ADO recordset is not an array.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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.

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    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.
    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

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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")

  10. #10
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    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.htm
    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

  11. #11
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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?

  12. #12
    SitePoint Wizard siteguru's Avatar
    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.
    Ian Anderson
    www.siteguru.co.uk

  13. #13
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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)
    Im getting subscipt out of range error

  14. #14
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    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

  15. #15
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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?

  16. #16
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    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

  17. #17
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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?

  18. #18
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    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

  19. #19
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    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

  20. #20
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •