Array clarification

[Thompyt]

Dim db As DAO.Database
Dim rS As DAO.Recordset
Dim CtrPlat As Integer
Dim PlatArr As Variant

Set db = CurrentDb()
Set rS = CurrentDb.OpenRecordset("Table1")

Do While Not rS.EOF
    rS.Edit

    If rS![Row Type] = "PLAT" Then
        CtrPlat = CtrPlat + 1
    End If

        ReDim PlatArr(1 To CtrPlat, 1 To 9)
    
    rS.Movenext
Loop

Run-time error '9': subscript out of range

I was thinking when you redim it will get the new value. So this would get PlattArr(1 to 867, 1 to 9). If there were 867 records. I think the 1 to 9 would be referenced as the first record -

PlatArr(CtrPlat, 1) = rS![Column 1

PlatArr(1, Column 1) = ……..



I think this geared more towards Excel though. so the Column 1 part could be removed. BUT with ReDim PlatArr(1 To CtrPlat) I get the same error.

[June7]

Code works for me.

Don't need to loop through recordset to get recordcount. Why don't you open recordset filtered to PLAT records?

Set rS = CurrentDb.OpenRecordset("SELECT * FROM Table1 WHERE [Row Type]='PLAT'")
rs.MoveLast
ReDim PlatArr(1 To rs.RecordCount, 1 To 9)

Or use DCount()?

ReDim PlatArr(1 To DCount("*","Table1",[Row Type]='PLAT'), 1 To 9)

[Thompyt]

June7,
I have more than 1 array:

    If rS![Row Type] = "PLAT" Then
        CtrPlat = CtrPlat + 1
    ElseIf rS![Row Type] = "FE" Then
        CtrFE = CtrFE + 1
    ElseIf rS![Row Type] = "MEQUIP" Then
        CtrMEquip = CtrMEquip + 1
    ElseIf rS![Row Type] = "DISMEQUIP" Then
        CtrDisMEquip = CtrDisMEquip = 1
    End If
    'Builds out arrays with the needed data
        ReDim PlatArr(1 To CtrPlat, 1 To 9)
        ReDim FEArr(1 To CtrFE, 1 To 9)
        ReDim MEquipArr(1 To CtrMEquip, 1 To 8)
        ReDim DEquipArr(1 To CtrDisMEquip, 1 To 9)

I am trying to avoid using DCount as it slows down the system a lot.

[June7]

Just 4 DCount() calls are noticeably slow? Looping slows performance as well. Reduce the recordset to 4 records, assuming there are only 4 types.

Set rS = CurrentDb.OpenRecordset("SELECT [Row Type], Count(*) AS CntType FROM Table1 GROUP BY [Row Type]")
While Not rS.EOF
    If rS![Row Type] = "PLAT" Then
        ReDim PlatArr(1 To rS!CntType, 1 To 9)
    ElseIf rS![Row Type] = "FE" Then
        ReDim FEArr(1 To rS!CntType, 1 To 9)
    ElseIf rS![Row Type] = "MEQUIP" Then
        ReDim MEquipArr(1 To rS!CntType, 1 To 8)
    ElseIf rS![Row Type] = "DISMEQUIP" Then
        ReDim DEquipArr(1 To rS!CntType, 1 To 9)
    End If
Wend

Not seeing any code that populates arrays. Why do you need arrays?

[Thompyt]

Thanks June7,
It did work and minimized some code. I have a code I am working from to use in access to use the arrays to count.