Create a distinct query then make another query for each result. (1 Viewer)

[qupe]

Hi,
As in the title: the main goal is to create a report for each subject and save it as PDF.
this code do the job.

Private Sub Command15_Click()
Dim sql As String

subcol = Array(14, 16, 30, 74, 289, 200548, 201103, 200594, 200569, 402031, 201105, 157)
For i = 0 To 11
sql = "SELECT * FROM Y7M WHERE(((Y7M.[Student Id]) Is Not Null) AND ((Y7M.[Subject Id])=" & subcol(i) & "))"
'Debug.Print sql
Me.RecordSource = sql


DoCmd.OutputTo acOutputReport, "Query1", acFormatPDF, "C:\Desktop\Year 7 " & Me.Subject & ".pdf"
Next i
End Sub

But I would like to have a distinct query to find all subjects' ids, then store its values as "subcol" array instead of writing it manually.
I appreciate your help.

 

[theDBguy]

Hi. Check out the GetRows() method of the ADO Recordset.

 

[Ranman256]

I've never used an array in 20 years using Access. (it's hardcoding)

make a listbox on a form with the list of key items you want to report. (fed from table)
cycle thru the list exporting the data.

the export query uses the listbox as criteria:
select * from table where [StudentID]=forms!fMyform!lstbox

 

[qupe]

Hi. Check out the GetRows() method of the ADO Recordset.

I looked for it, and its not clear. Please write any example or reference page.

 

[qupe]

I've never used an array in 20 years using Access. (it's hardcoding)

make a listbox on a form with the list of key items you want to report. (fed from table)
cycle thru the list exporting the data.

the export query uses the listbox as criteria:
select * from table where [StudentID]=forms!fMyform!lstbox

I am not limited to array, I am opened to any other solution. "I made a query and grouped it to get the distinct value," if this may give another way?

 

[Pat Hartman]

Queries ARE arrays. Open a recordset to read the query ot unique subjects you want to report on. As you loop through the query, use the WHER argument of the OpenReport method to tell the report which subject to select for the report. There are at least two active threads on this very topic. Look at the ones selected at the bottom of this page.

This is such a requested topic that I'm going to make a sample database but it won't be ready for at least a week or two depending on how much time I get to spend on it so start with the threads below.