Storing values from a Query in a variable

[abhisheknag89]

Hey all !

I had a little problem. I have a query that has 2 fields in it and 6 records. I want to create something like an array ideally that stores the 6 values from one of the fields.

I tried this:

Set rst = dbs.OpenRecordset("testquery")
testval = rst3.Fields("minofmin breaker rating")

I expected access to dynamically create an array called "testval" and store the values in it, but of course, that was not the case.

Any idea on how I might be able to achieve this? Thanks!

 

[vbaInet]

Look into GetRows of the recordset object. Have a look in the help files for its use.

 

[gemma-the-husky]

of course the other question is

"why do you want to move your data from a table to an array"

what are you trying to achieve by this process?

 

[abhisheknag89]

I have a query whose results i want to show on a report in a textbox using a function. I figured the best way would be to store the values I want into an array so that I can pass the array values I want back to the textbox on the report. Is there a better way to do this?

I tried getrows. Based on the documentation it seems like it should do the trick but it's really giving me a migraine. It doesn't seem to store anything from the query called "test query" into my array. This is what I have
-----------------------------------------------------------------------------------
Dim avardata as Variant

Set rst3 = dbs.OpenRecordset("test Query")

avardata = rst3.GetRows
-----------------------------------------------------------------------------------

I also tried avardata=rst.getrows(2) which should store 2 rows as opposed to the default 1, but that didn't make any change either. When I add a watch on 'avardata' the value shows blank, though apparently a 2 element array is created consisting of avardata(0) and avardata(1) but again both values are blank.

GAH!

 

[vbaInet]

Two dimensional, not two elements. By that I mean: avardata(0,0) for example. There's an explanation on multi dimensional arrays in the Access FAQs section of the forum or you can do some Googling.

But I don't see why you would want to get the results of ALL fields in the query and display ALL of them in a textbox. It would expand out of proportion if you had lots of records.

 

[abhisheknag89]

Eureka, it works! I didn't know it was a 2-dimensional array! VbaInet strikes again.

Yeah, if I had a lot of records it could get messy but I'll largely be limited to just a few. Thanks again! Hopefully I won't need to bother you with this again.

 

[vbaInet]

Great! Glad it's working for you.

I would imagine in the future you may need to change your approach once your db starts to grow.

Good luck!

 

[abhisheknag89]

Surprise surprise here I am again.

Any ideas on how I can return multiple values from the function? Right now it looks like:

functionname()

functionname=value

Which of course returns 'value'. How can I make it return multiple values?

 

[vbaInet]

Multiple values in what way? Per group?

Have you thought about concatenating the results into a string?

 

[abhisheknag89]

Yup, I changed the return type to string and concatenated and that does the trick. Hehe yeah hopefully this database doesn't get too big and flood these reports.

 

[vbaInet]

If you were going to concatenate then you wouldn't have used GetRows because that's double work. You should have just read each line in the recordset and concatenated.

If it gets big (which I bet it would) then it would be back to the drawing board for you

 

[vbaInet]

You might find this link useful:

http://allenbrowne.com/func-concat.html