Help Recordset field data in to an Array

[buckau]

Hi I am trying to select a set of fields from a record set and read the data into an array.
First off this is to be used with several table recoredsets all having the same naming structure but different qty of fields. Eg
Table 1 has fields named "Number_1,Number_2,Number_3
Table 2 has fields named Number_1,Number_2,Number_3,Number_4,Number_5
Table 3 has fields named Number_1,Number_2,Number_3,Number_4
The number of fields to read is an unknown qty until run time
At run time the data table gives me the total number of numbers in the table.
I have created a recordset of the table and an array to hold the data I have created a string to hold the field name and a for next loop to increment the trailing number but I keep getting an error message telling me item not found in record set. Is it possible to step through the field names in this manner?
I would appreciate any help on this matter.

this is a sample of the code

dim db as databace
dim Rst as recordset
dim ArrayNum()
dim x as integer
dim NumInSet
dim SrtFieldName as string

Set db = CurrentDb
Set Rst= db.OpenRecordset("tblNumData", dbOpenSnapshot)

with rst
.movelast
NumInSet = !totalNumbers
redim ArrayNum(1,NumInSet)
SrtFieldName = "!Number_" & x
for x = 1 to NumInSet
SrtFieldName = "!Number_" & x
ArrayNum(1,X) = SrtFieldName
next x

 

[DCrake]

What you need to do is something on the lines of..

Dim Rs As DAO.Recordset

For n = 0 to Rs.Fields.Count -1
ArrayNum(1,n) = Rs.Name
Next

 

[buckau]

thanks for that but that gives me the name of the field not the data stored inthe feild and its the data i am trying to extraxt into the array as i know wat the name of the field is i just do not now how neany os that type of field ther is eg
Number_1 to Number-3
or Number_1 to Number_ 10
all the fields are called Number_ and then a diget starting with 1

 

[ByteMyzer]

In that case, you might try changing:

SrtFieldName = "!Number_" & x
ArrayNum(1,X) = SrtFieldName

...to:

SrtFieldName = "Number_" & CStr(x)
ArrayNum(1,X) = .Fields(SrtFieldName).Value

 

[buckau]

thanks a lot that was it but what dos CStr do?

 

[gemma-the-husky]

its possible that concatenating a number to a string might fail in some circumstances (although vba does seem to resolve this itself) but cstr(x) EXPLICITLY casts a number into a string format.

so
SrtFieldName = "Number_" & CStr(x) or
SrtFieldName = "Number_" & x

would probably both work because of how vba works, but the former is more rigorous in general

 

[buckau]

thaks for that info now i know were i whent wrong!
do you know a quik way to sort data in an array
the array has over 100 lines of numeric data and i need to be able to sort it quikly in Decending or ascending order on the ast element in each array line?

arrayData (1 to 100, 1)

 

[gemma-the-husky]

look up algorithms for bubble sort

easiest to understand, and ok for modest sizes

--------
but why sort it at all

instead of using tblnumdata, have a query based on tblnumdata - called qrynumdata, and sort this in whatever order you need with sort parameters - now your data is already sorted for you. and you dont need to put it in an array

 

[buckau]

thanks i will give it a try. however the data is already in a multy dimantioned array.
also do you know how to empty an array and free up the men when you are finished with the array?

 

[gemma-the-husky]

i wouldnt worry about memory management

but if its really an issue just declare it in a sub, rather than global - then it will be recovered when the sub closes

 

[buckau]

thanks thats what i have don for the sorting i have used a veriation of the quick sort method it seems to work ok and is resnobaly fast.

 

[buckau]

is it possible to have an or multipul pop up text windows to display the text results of the array in.
at the moment i am using an activ x list box but can only display one result at a time.

 

[gemma-the-husky]

put them into a string, and popup a msgbox - if the text is too big (msgbox has a limit to no of chars - not sure how many 256 or 1024 or something like). put the string into a textbox - then you get scroll bars - good for 64K characters

 

[buckau]

hi thanks for that have tryed a msgbox but it is too small i also need to be able to print the results as formatted and layed out in the Active X list box.

 

[gemma-the-husky]

not sure about that

you can easily put the results in a text box on a form - then manually copy and paste into notepad etc