Dynamic multifunctional arrays?

[Access9001]

I want to be able to have an array defined in this way:

nameofarray(,2)

where I then iterate through a recordset that I get via a query, and for each new entry, insert a name into the first index, and their email in the second index. For instance:

nameofarray(0,0)="James"
nameofarray(0,1)="james@email.com"
nameofarray(1,0)="Tim"
nameofarray(1,1)="Tim@email.com"

and so on and so forth. I've tried a variety of things with redim preserves and so forth but I always run into index errors and incorrect array syntax.

 

[boblarson]

It should be:

Dim nameofarray(1,2)

And then you redim it to

ReDim Preserve nameofarray(i,2)

where i might be an integer as you are working through them.

You can redimension the first part of a multidimensional array but not any following dimensions.

 

[Access9001]

I seem to be able to redim the last dimension but none of the first

 

[boblarson]

My bad - I had it reversed.

So, I would use the second part of the array for the ID and the first Part for the details.

 

[boblarson]

This might also be useful info.

 

[boblarson]

My bad - I had it reversed.

So, I would use the second part of the array for the ID and the first Part for the details.

Never mind - I'm not thinking straight. That doesn't help because you don't know how many records you will have.

I would just write it to a table and then load it.

 

[Access9001]

Thanks for the help -- I think I'm on the right track now

 

[ChrisO]

Something else to try: -

Option Explicit
Option Compare Text

Private Type Contact
    Name As String
    Email As String
End Type

Public Type Contacts
    Data() As Contact
End Type


Sub TestIt()
    Dim ContactList  As Contacts
    Dim lngSubscript As Long
    
    ReDim ContactList.Data(0)
    
    ContactList.Data(UBound(ContactList.Data())).Name = "James"
    ContactList.Data(UBound(ContactList.Data())).Email = "james@email.com"
    
    ReDim Preserve ContactList.Data(UBound(ContactList.Data()) + 1)
    ContactList.Data(UBound(ContactList.Data())).Name = "Tim"
    ContactList.Data(UBound(ContactList.Data())).Email = "Tim@email.com"
    
    For lngSubscript = LBound(ContactList.Data()) To UBound(ContactList.Data())
        MsgBox ContactList.Data(lngSubscript).Name & vbTab & ContactList.Data(lngSubscript).Email
    Next lngSubscript

End Sub

Hope that helps.

Regards,
Chris.