Create a distinct query then make another query for each result.

[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?