Refer to a Recordset Field as array of record values

[Tim]

It would be nice to be able to refer to a particular field in a recordset as
an array so that we would not have to iterate through the records of the
recordset to fill an array.

For instance, let's say that I have a form of Computers linked to a subform
(master/child fields) on ComputerName. The subform shows the various software
packages installed on the Computers. I'd like to limit the Computers that I
show in the main form to those that have software package "SoftwareA"
installed. If you filter the subform, you are still going to show all
computers in the main form; you will just be limiting the
displayed-installed-software packages to "SoftwareA".

Instead, to limit the Computers displayed, we need to pick a software
package and filter the Computers on the main form. So, through some process
(a separate pop-up form, or an unbound listbox on the mainform, whatever), we
arrive at a set of software packages that we want to filter for. The filter
we apply is where the ComputerName of the main formhas a sub-table record
matching one of those software packages.

To get that filter, we get a list of ComputerNames (from the
sub-table/subform) that have a matching software type:

set rs = CurrentDb.OpenRecordset("SELECT ComputerName From SubTable WHERE
SoftwarePackage In (" & strSoftwares & ") GROUP BY ComputerName")

We now have a list of ComputerNames with the software we are looking for.
Our Filter for the main form needs to be something like:

"ComputerName In ('Computer1','Computer2','Computer15','Computer43')"

So, it would be nice to be able to refer to the ComputerName field in the rs
recordset as an array that could be put through a Join() function:

Me.Filter = "ComputerName In ('" & Join(rs.Fields("ComputerName"),"','") &
"')"
Me.FilterOn = True

Otherwise, we have to iterate through the original recordset object to get
the string of information.

Or is this functionality already available, and I don't know how to do it?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...c9c9&dg=microsoft.public.access.modulesdaovba

 

[Dirk Goldgar]

It would be nice to be able to refer to a particular field in a
recordset as an array so that we would not have to iterate through
the records of the recordset to fill an array.

For instance, let's say that I have a form of Computers linked to a
subform (master/child fields) on ComputerName. The subform shows the
various software packages installed on the Computers. I'd like to
limit the Computers that I show in the main form to those that have
software package "SoftwareA" installed. If you filter the subform,
you are still going to show all computers in the main form; you will
just be limiting the displayed-installed-software packages to
"SoftwareA".

Instead, to limit the Computers displayed, we need to pick a software
package and filter the Computers on the main form. So, through some
process (a separate pop-up form, or an unbound listbox on the
mainform, whatever), we arrive at a set of software packages that we
want to filter for. The filter we apply is where the ComputerName of
the main formhas a sub-table record matching one of those software
packages.

To get that filter, we get a list of ComputerNames (from the
sub-table/subform) that have a matching software type:

set rs = CurrentDb.OpenRecordset("SELECT ComputerName From SubTable
WHERE SoftwarePackage In (" & strSoftwares & ") GROUP BY
ComputerName")

We now have a list of ComputerNames with the software we are looking
for. Our Filter for the main form needs to be something like:

"ComputerName In ('Computer1','Computer2','Computer15','Computer43')"

So, it would be nice to be able to refer to the ComputerName field in
the rs recordset as an array that could be put through a Join()
function:

Me.Filter = "ComputerName In ('" &
Join(rs.Fields("ComputerName"),"','") & "')"
Me.FilterOn = True

Otherwise, we have to iterate through the original recordset object
to get the string of information.

Or is this functionality already available, and I don't know how to
do it?

It seems to me that, in the specific example you gave, the problem could
most simply be solved by applying a filter like this:

Me.Filter = _
"ComputerName In (" & _
"SELECT ComputerName From SubTable " & _
"WHERE SoftwarePackage In (" & strSoftwares & ")" & _
")"

There'd be no need for either an array or a recordset.

If you have some other reason for wanting to transform a recordset into
an array, you can use the GetRows method of the Recordset object. Both
DAO and ADO recordsets have a GetRows method that returns a specified
number of the rows of the recordset as an array. The two DAO and ADO
methods are slightly different, so check the help file for the details,
but either can be used to do what you want.