By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Getting data from a field to use in VBA code

P: n/a
Hi,

I am using Access 2000. I have a table full of records. Each record
holds a value that I want to use in VBA code. Does anyone know how to
take the value from the field, put it into an array and then move onto
the next record in the table?

Ollie

May 11 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
do******@aol.com wrote:
Hi,

I am using Access 2000. I have a table full of records. Each record
holds a value that I want to use in VBA code. Does anyone know how to
take the value from the field, put it into an array and then move onto
the next record in the table?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a Recordset:

dim db as dao.database
dim rs as dao.recordset
dim f() as string
dim i as integer

set db = currentdb
set rs = db.openrecordset("SELECT columnA FROM tableZ")

do while not rs.eof
i = i + 1
redim preserve f(i)
f(i) = rs!columnA
rs.movenext
loop
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRGOnToechKqOuFEgEQKLpACg/BwnWrzxA21weKtCftAULvmFypwAnjHI
bVkRuZgIwy3EnXucj8RS2DRg
=ZlnU
-----END PGP SIGNATURE-----
May 11 '06 #2

P: n/a
Cheers for the reply, I did actually find a function that someone put
on the Microsoft site that I could adjust to make work (using forms),
but maybe you could help me with my next problem, as I am not an
experienced programmer, but I can sort of muddle through.

I want to construct an array containing 6 other arrays. These will then
be populated with the values I take from the database. There will
always be 6 parts to the array, but each part will have a number of
values in it. I know about the redim and preserving values when you
redim, but how do I construct this and fill it up?

It has been many years since I programmed at university, and I remember
things about multidimensional arrays, but the finer details have gone.

Any ideas?

May 14 '06 #3

P: n/a
rkc
do******@aol.com wrote:
Cheers for the reply, I did actually find a function that someone put
on the Microsoft site that I could adjust to make work (using forms),
but maybe you could help me with my next problem, as I am not an
experienced programmer, but I can sort of muddle through.

I want to construct an array containing 6 other arrays. These will then
be populated with the values I take from the database. There will
always be 6 parts to the array, but each part will have a number of
values in it. I know about the redim and preserving values when you
redim, but how do I construct this and fill it up?

It has been many years since I programmed at university, and I remember
things about multidimensional arrays, but the finer details have gone.


Why do you want to do this? Both ADO and DAO have a getRows method
of the recordset object that will store some or all the rows of a
recordset in a two-dimensional array.

You can also do it yourself and store the fields of each row in
it's own array and then store the resulting arrays in another array.
That has the advantage of being able to easily pass single rows or
the whole mess to other methods.

How depends greatly on why.





May 15 '06 #4

P: n/a
I have done it by looping through each array in turn. I decided to go
for individual arrays, there is a lot of code, but it works. Thanks
for all you help.

Ollie.

May 15 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.