在VBA Access中循环使用二维数组时出错

Question

当用于Companycode数组的下一个循环时，我得到了下标超出范围的错误。我之前尝试过使用2个一维数组。也有同样的问题。

我将重复的公司代码分配给这个数组，并希望基于不同的公司代码组合运行查询。我是错误地分配了数组，还是以错误的方式循环数组？非常感谢您的帮助。谢谢

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   

Answer 1

lrow是基于1的(1=1记录(，但您的CompanyCode数组是基于零的，因此您需要For j = 0 To lrow - 1。您可以跳过一个查询，并使用UBound(CompanyCode, 2)作为循环上限。

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