How do I use an array for a report recordsource in Access?

[Pete B.]

I have an array that I need to use as the recordsource for a recordset. I
want to print section headers for groups of data and print records from the
array within each group. I do not know how to tell Access to start a new
group, nor do I know how to get data to print to a report when the data is an
array. I cannot use a query for this data.

 

[Douglas J. Steele]

AFAIK, you can't use an array as a report's recordsource.

 

[Larry Linson]

I have an array that I need to use as the record-
source for a recordset. I want to print section
headers for groups of data and print records from
the array within each group. I do not know how
to tell Access to start a new group, nor do I know
how to get data to print to a report when the data
is an array. I cannot use a query for this data.

As Doug has already explained, Access does not support using arrays as the
RecordSource for a report. Just as a matter of interest, if the data in the
array does not itself come from tables or queries in the database, where
_does_ it originate?

Consider writing the data from the array to a temporary table and running
the report using that table data (or a query against it) as the record
source. If you decide to do this, I strongly suggest you look at MVP Tony
Toews site for the example of creating a temporary database to contain the
temporary tables, then deleting the temporary database when done, to avoid
bloating of your own database.

Larry Linson
Microsoft Access MVP

 

[Pete B.]

Larry,

I have a form where the customer uses a listbox to select the topics for
their report. The topics become a query filter used to extract the data from
a table.
The problem is printing the topic with "No events this month" under the
topic on the report.

For a vague example, lets say the customer is looking for information on
events involving aircraft hydraulic pumps, landing gear struts, and VHF
Radios. They select these in the listbox on frmPrintReport. The form
populates an array with the customer's request. I then populate the "WHERE
[tblPARTS].[PART_NOMENCLATURE] =" clause of my query using the data from the
array. The query, of course, does not give any indication of not finding one
of the members of the "WHERE" clause. Consequently, there is no indication on
the report of a null search result for the respective part.

I'm probably being as clear as mud, but this is my challenge.

Regards,

Pete

 

[Larry Linson]

Create a Query based on the selection. Create another Query with one data
source being the list of all the topics then join to the Query based on the
selection. Use an outer join, that is, all records from the tblTopicNames
and only those that match from the qrySelectedInformation. That should be a
start on what you need to get the data for your report... in a form that you
can use as RecordSource...

Larry Linson
Microsoft Access MVP

Larry,

I have a form where the customer uses a listbox to select the topics for
their report. The topics become a query filter used to extract the data
from
a table.
The problem is printing the topic with "No events this month" under the
topic on the report.

For a vague example, lets say the customer is looking for information on
events involving aircraft hydraulic pumps, landing gear struts, and VHF
Radios. They select these in the listbox on frmPrintReport. The form
populates an array with the customer's request. I then populate the "WHERE
[tblPARTS].[PART_NOMENCLATURE] =" clause of my query using the data from
the
array. The query, of course, does not give any indication of not finding
one
of the members of the "WHERE" clause. Consequently, there is no indication
on
the report of a null search result for the respective part.

I'm probably being as clear as mud, but this is my challenge.

Regards,

Pete

As Doug has already explained, Access does not support using arrays as
the
RecordSource for a report. Just as a matter of interest, if the data in
the
array does not itself come from tables or queries in the database, where
_does_ it originate?

Consider writing the data from the array to a temporary table and running
the report using that table data (or a query against it) as the record
source. If you decide to do this, I strongly suggest you look at MVP Tony
Toews site for the example of creating a temporary database to contain
the
temporary tables, then deleting the temporary database when done, to
avoid
bloating of your own database.

Larry Linson
Microsoft Access MVP