Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help with For Each...Next Statement

    Hi folks,

    I'm having difficulty using a For Each...Next Statement.

    Basically I have created an ADO recordset, and now I want to call another function on each row.

    This is my current code:

    Code:
    Public Function GetEmployers()
     
    Dim sSQL As String
    Dim adoConn As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim rs As ADODB.Recordset
     
    Set adoConn = CurrentProject.Connection
    Set adoCmd = New ADODB.Command
     
        sSQL = " SELECT Emp_ID FROM dbo_tblEmployer WHERE BArea ='" & "Animal Care" & "'"
     
        With adoCmd
            .ActiveConnection = adoConn
            .CommandType = adCmdText
            .CommandText = sSQL
        End With
     
    Set rs = adoCmd.Execute
     
        If rs.EOF = False Then
            For Each rs.GetRows
                Call GetBArea(rs![Emp_ID])
            Next
        End If
     
    rs.Close
    Set rs = Nothing
    End Function
    Everytime I debug it displays the message 'syntax error' and highlights the For Each line.

    I take it that I shouldn't be using the rs.GetRows, but what else can I use to run through each row in the recordset?

    Appreciate any help.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In stead of

    If rs.EOF = False Then
    For Each rs.GetRows
    Call GetBArea(rs![Emp_ID])
    Next
    End If

    try


    do while not rs.eof
    Call GetBArea(rs![Emp_ID])
    rs.movenext
    loop
    Roy-Vidar

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    So I don't need to use a For Each...Next statement.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Correct.

    The help states it repeats a group of statements for each element in an array or collection.

    The syntax is often something like

    for each <item> in <some collection or group>
    ....
    next <item>

    GetRows is neither an item nor a collection, though it returns a variant whose value is a two dimensional array - look it up in the help files.

    When you need to loop a recordset, then that's what you do. Start from one position (start?) and loop to another position (end?), as demonstrated, unless you can achieve your objective through a query.
    Roy-Vidar

  5. #5
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Tip: don't forget the rs.MoveNext.

    It can be quite frustrating waiting for a never ending loop to end!

Posting Permissions

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