Looping thru an array cheking for values in a table

[FLCoderMike]

       arrLocations = Me!customerBranchLocations.Value
        'MsgBox (arrLocations)
        
        arrParams = Split(arrLocations, ";")
            For i = 0 To UBound(arrParams)
            MsgBox (arrParams(i))
            
            strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & ""
            'MsgBox (strSql1)
            DoCmd.RunSQL strSql1
            Set rs = db.OpenRecordset(strSql1)
            Dim count
            count = rs.RecordCount
 
            
            Next

Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?

Thx.

 

[WayneRyan]

Mike,

Two options:

Dim arrPfarams As Variant
Dim i As Integer
Dim rst As DAO.Recordset
Dim count As Integer
    
arrParams = Split(Me.customerBranchLocations, ";")

For i = 0 To UBound(arrParams)
    MsgBox (arrParams(i))
            
    Set rst = CurrentDb.OpenRecordset("SELECT branchName " & _
                                      "FROM ustax_customerBranchLocationsTBL " & _
                                      "WHERE branchName = '" & arrParams(i) & "' AND " & _
                                      "      branchCustomerParentID = " & Me.customerID)
    rst.MoveLast
    count = rs.RecordCount
    Next i

Alternatively, use the DCount function:

Dim arrPfarams As Variant
Dim i As Integer
Dim count As Integer
     
arrParams = Split(Me.customerBranchLocations, ";")

For i = 0 To UBound(arrParams)
    MsgBox (arrParams(i))
            
    Count = DCount("[branchName]" _
                   "[ustax_customerBranchLocationsTBL]" _
                   "[branchName] = '" & arrParams(i) & "' AND " & _
                   "[branchCustomerParentID] = " & Me.customerID)
    Next i

hth,
Wayne

 

[FLCoderMike]

Thanks...with a little tweeking it worked.