Multidimensional Arrays

[lemmiwinks30]

Hello,

In my program I run a query that returns a recordset that I'd like to store in an array called newReqs(). Each recordset consists of 3 fields: proj#, title and user. I'd like to display these rows to the user in 1 message box but I am having trouble loading the array since the actual size is not known at the time of creation.
The other problem would then be displaying them, I assume I'll have to loop through the array.

Any suggestions?
Thanks !!
Kathy

 

[lewds]

Hi..

Instead of a msgBox....

Why not a form(containing a subform,continuous form) based on your query that opens after your query runs , or a report?

 

[PJStephenson]

If you are going to put it in a msgbox you need to use Ubound(array,[dimension]) to find out how large the array is then loop through and append the contents to a string then use the string in the msgbox.

MsgString = ""
for LoopCount = 0 to Ubound(YourArray)
   MsgString = MsgString & YourArray(LoopCount) & vbCrLf
next
msgbox MsgString

HTH
Peter

 

[pullingteeth]

Make a user defined type which contains the major fields you'd like to work with, and perhaps some meta fields:

Private Type Element
periodtotal As Long
percentage As Double
avghour As Double
End Type

Now you can declare an array of your type:

Dim x() as Element

or

Dim x as Element(6)

 

[hwkranger]

If I'm reading this right, you're having trouble b/c you don't know the depth of your array, not the width.

You're having trouble defining the size of your array, since guessing is going to get you into trouble.

try using the recordcount and a redim. Also using Pulling Teeth's Type is also a good idea as a starter instead of using a multi dimensional array, using an array of a user defined datatype works as well.

Even with his suggestion you'll still need to redim your array.

eg.

Private type MyADT
lng_Project as long
str_Title as string * 25
str_User as string * 25
end type

Private function DoSomething()
dim myData as new myadt()
dim rst as new adodb.recordset
rst.open "Select [proj#], title, user from YourtableName where [whatever you want to be true is true]", currentproject.connection, adOpenKeyset, adLockReadOnly
if rst.recordcount then
redim mydata(6)
else
exit function
end if

[Do whatever you want with your data]
end function

this was hand-keyed, forgive me if there's typo's or small mistakes, but this should work.







Randall Vollen
National City Bank Corp.

 

[hwkranger]

I have no idea why I typed:

redim mydata(6)

It should read

redim mydata(rst.recordcount)

slip of the mind, i was actually thinking of a number for some reason. LoL

Randall Vollen
National City Bank Corp.

 

[lemmiwinks30]

Great thanks for your help! Everything is working now!

 

[lemmiwinks30]

I used parts of just about everyone's suggestions and it all makes sense now.

Thanks everyone for replying!