array subscript out of range

[jadams0173]

I've search and read numerous posts about this error but the simple (which I know it will be) solution is still out of reach. Can someone please assist. Here is where I initalize the array. I've also put

Dim aryParts in the general declarations section

ReDim aryParts(1 To rsCBO.RecordCount, 3)
rsCBO.MoveFirst
    For L = 1 To rsCBO.RecordCount
        aryParts(L, 1) = rsCBO.Fields("partnumber")
        aryParts(L, 2) = FormatCurrency(rsCBO.Fields("cost"), 2)
        aryParts(L, 3) = rsCBO.Fields("partID")
        rsCBO.MoveNext
    Next L

Now I want to add a row to the array and here's what I'm trying and getting the subcript out of range error on the red line.

If chNewPart = 1 Then
a = UBound(aryPartsCost, 2) + 1
Set rs1 = New ADODB.Recordset
    With rs1
        rs1.Open "MasterPriceList", conn, adOpenKeyset, adLockOptimistic, adCmdTable
            .AddNew
                !Partnumber = txtNewPart
                !cost = txtCost
            .Update
    End With
[red]ReDim Preserve aryPartsCost(3, a)[/red]
    aryPartsCost(1, a) = aryPartsCost(1, a - 1) + 1
    aryPartsCost(2, a) = txtNewPart
    aryPartsCost(3, a) = txtCost
End If

 

[gmmastros]

I recommend you use a User Defined Type. Here's an example to get you started.

In a new VB Project, add a module. Click Project -> Add Module

In the new module...

Option Explicit

Public Type Part
    Description As String
    Number As String
    Cost As Double
End Type

Public Parts() As Part

In the form, add a command button and this code.

Option Explicit

Private Sub Command1_Click()
    
    ReDim Parts(1)
    
    Parts(0).Description = "Tire"
    Parts(0).Number = "T 298-2"
    Parts(0).Cost = 43.25
    
    Parts(1).Description = "Head Light"
    Parts(1).Number = "HL 928"
    Parts(1).Cost = 12.43
    
End Sub

With this approach, you have an array (1-Dimensional) that has an element for each part.

Each Element is a PART type. A part has attributes (like Description, Number, Cost, etc...).

This approach will simplify your code and make it easier to add attributes for parts. For example, if you wanted to include supplier, you would only need to modify the User Defined Type (in the module) and change the way the array is built. You would then have access to the supplier from anywhere within your code.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jadams0173]

gmmastros, thanks for the reply and the great idea. I will certainly give that a try!

In the mean time I figured out my problem from above.

I changed this

        aryParts(L, 1) = rsCBO.Fields("partnumber")
        aryParts(L, 2) = FormatCurrency(rsCBO.Fields("cost"), 2)
        aryParts(L, 3) = rsCBO.Fields("partID")

to this

        aryParts(1, L) = rsCBO.Fields("partnumber")
        aryParts(2, L) = FormatCurrency(rsCBO.Fields("cost"), 2)
        aryParts(3, L) = rsCBO.Fields("partID")

and I changed this

ReDim Preserve aryPartsCost(3, a)

to this

ReDim Preserve aryPartsCost(3, 1 To a)

and now this way works!

 

[tonioJ]

instead of using array try creating a recordset in place of array. this will fasten and make your data manipulation easier. you can add, edit, delete data easier than using array. You can access data directly using loops, find method in recordset.

'---------- creation of Recordset that will replace array -
dim rsTmpArray

Public Sub CreateTmpRSArray()
Set rsTmpArray = New ADODB.Recordset
With rsTmpArray
.Fields.Append "FIELD1", adInteger
.Fields.Append "FIELD2", adChar, 10
.Fields.Append "FIELD3", adCurrency
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
End Sub