Getting error looping through 2 dimensional array in VBA Access

Question

I am getting subscript out of range error when use for next loop through the Companycode array. I was trying earlier with 2 single dimension arrays. And had the same issue.

I am assigning duplicated companycodes to this array and want to run the queries based on different combination of companycodes. Am I assigning the array wrongly or am I looping the array the wrong way I? Any help is really appreciated. Thanks.

Dim strSql As String
Dim qdf As DAO.QueryDef
Dim rec As DAO.Recordset
Dim rec1, rec2, rec3 As DAO.Recordset
Dim CompanyCode() As Variant
Dim intRowCount As Integer
Dim i, j, lrow As Integer

Set rec3 = CurrentDb().OpenRecordset("select count(*) as Row_Count from tblgt_customer AS GC1 LEFT 
JOIN tblGT_Customer AS GC2 ON GC1.ABN = GC2.ABN WHERE GC1.Customer<>[GC2].[Customer]")
lrow = rec3.Fields("Row_Count").Value
Set rec1 = CurrentDb().OpenRecordset("select gc1.company as company1,gc2.company as company2 from 
tblgt_customer AS GC1 LEFT JOIN tblGT_Customer AS GC2 ON GC1.ABN = GC2.ABN WHERE GC1.Customer<>[GC2]. 
[Customer]")
CompanyCode = rec1.GetRows()
rec1.Close

For j = 0 To lrow
    If CompanyCode(0, j) = "CICCO" And (CompanyCode(1, j) <> "CICCC" Or CompanyCode(1, j) <> "CAP") Then
        
        Set qdf = CurrentDb.QueryDefs("qry_Customer_Dup_Check_7")
        qdf.Execute

        Set qdf = CurrentDb.QueryDefs("qry_Customer_Dup_Check_8")
        qdf.Execute
    
    ElseIf CompanyCode(0, j) = "CAP" And CompanyCode(1, j) <> "CICCC" Then

        Set qdf = CurrentDb.QueryDefs("qry_Customer_Dup_Check_5")
        qdf.Execute

        Set qdf = CurrentDb.QueryDefs("qry_Customer_Dup_Check_6")
        qdf.Execute

    ElseIf CompanyCode(0, j) = "CICCC" Or CompanyCode(1, j) = "CICCC" Then

        Set qdf = CurrentDb.QueryDefs("qry_Customer_Dup_Check_3")
        qdf.Execute

        Set qdf = CurrentDb.QueryDefs("qry_Customer_Dup_Check_4")
        qdf.Execute
    
    End If
    
Next j

Dim rec1, rec2, rec3 As DAO.Recordset declares only rec3 As DAO.Recordset. rec1 and rec2 are variants because no datatype has been assigned to them. Same for Dim i, j, lrow As Integer. A datatype must be assigned to each variable individually. — Variatus
– Variatus, Commented Sep 25, 2020 at 1:17

Tim Williams · Accepted Answer · 2020-09-25 05:38:51Z

1

lrow is 1-based (1=1record) but your CompanyCode array is zero-based so you'd need For j = 0 To lrow - 1. You could skip a query and use UBound(CompanyCode, 2) for your loop upper bound.

Dim rec1 As DAO.Recordset
Dim CompanyCode() As Variant
Dim j As Long, v1, v2, numRecs As Long

Set rec1 = CurrentDb().OpenRecordset("select gc1.company as company1,gc2.company as company2 from " & _
                     " tblgt_customer AS GC1 LEFT JOIN tblGT_Customer AS GC2 " & _
                     " ON GC1.ABN = GC2.ABN WHERE GC1.Customer<>[GC2].[Customer] ")

rec1.MoveLast  'to get an accurate count
numRecs = rec1.RecordCount
Debug.Print "Got " & numRes & " records"
rec1.MoveFirst
CompanyCode = rec1.GetRows(numRecs) '<< specify number of rows to fetch
rec1.Close

For j = 0 To UBound(CompanyCode, 2)
    
    v1 = CompanyCode(0, j)
    v2 = CompanyCode(1, j)
    
    If v1 = "CICCO" And (v2 = "CICCC" Or v2 = "CAP") Then
        CurrentDb.QueryDefs("qry_Customer_Dup_Check_7").Execute
        CurrentDb.QueryDefs("qry_Customer_Dup_Check_8").Execute
    ElseIf v1 = "CAP" And v2 <> "CICCC" Then
        CurrentDb.QueryDefs("qry_Customer_Dup_Check_5").Execute
        CurrentDb.QueryDefs("qry_Customer_Dup_Check_6").Execute
    ElseIf v1 = "CICCC" Or v2 = "CICCC" Then
        CurrentDb.QueryDefs("qry_Customer_Dup_Check_3").Execute
        CurrentDb.QueryDefs("qry_Customer_Dup_Check_4").Execute
    End If
        
Next j

edited Sep 25, 2020 at 5:38

answered Sep 25, 2020 at 1:28

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jimmy Over a year ago

This resolevs the subscript out of range error. But this code UBound(CompanyCode, 2) results in number 0. Hence the for loop goes only 1 iteration and it runs 1 set of queries once only. That Ubound should result in 10 for my dataset.

Tim Williams Over a year ago

Have you tried looping over the recordset and using debug.Print to check the content? Is it what you expect?

Tim Williams Over a year ago

or maybe look here: stackoverflow.com/questions/30670172/…

Collectives™ on Stack Overflow

Getting error looping through 2 dimensional array in VBA Access

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related