[RESOLVED] Variable Length Array?

[bat711]

I want to use an array, but I don't know what the size will be until I read the number of fields from a DB. All of the fields have the same name with a number appended to them (lr_transaction1, lr_transaction2...) but I'd like to maintain the flexibility to add new fields without having to code and recompile. Any suggestions?

Dim txtTrans() As String
Dim z As Integer
Dim strField As String

z = 1

For z = 1 To gsNumTrans
    strField = "lr_transaction" & z
    If IsNull(rsScript.Fields(strField)) Then
        txtTrans(z) = ""
    Else
        txtTrans(z) = rsScript.Fields(strField)
    End If
Next

The gsNumTrans variable holds the number of "lr_transaction" fields and is read from an INI file.

And yes I know I'm using antique DAO technology, but it works and isn't worth the hassle to convert it at this point.

[techgnome]

two words... ReDim and Preserve...

Dim myArray() as Long

ReDim Preserve myArray(100)

The preserve will preserve any existing data in the array as you resize it. If you DON'T use it, it will ERASE ALL VALUES in the array.

-tg

[bat711]

Ahh...thanks! I didn't think there was any way to declare an array using a variable for the number of elements, but the below worked...

ReDim Preserve txtTrans(gsNumTrans)

[MartinLiss]

Mots likely you want to do

ReDim Preserve txtTrans(gsNumTrans - 1)

because arrays by default start at 0.