How to set Variable Criteria in a Query so I can run separate Reports for each Item.

[lferguison]

I have a Report based on a Query that retrieves data from several different Tables for a particular Item. The tables are linked by the Item Number. Currently, I have set the Criteria for the Item Number in my Query to this: Like "IDI 037115" I would like to be able to set the Criteria to a variable "list" of Item Numbers. My intent is to create a "text" file or something of that nature to be "input" into the Criteria as a variable. Then I would like a separate Report to be generated for each Item Number in the list. Is this even possible?

[June7]

Yes, can be done with code. Open a recordset of the desired records and loop through the recordset to open and close report. What do you want to do with each report - output to PDF?

SELECT * FROM table WHERE [Item Number] IN ("IDI 037115", "IDI 037120", "IDI 037290");

Review http://allenbrowne.com/ser-50.html

[lferguison]

I don't know what you mean by "loop through the recordset". Yes I want to output to PDF for each report.

[June7]

Example:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM table WHERE [Item Number] IN ('IDI 037115', 'IDI 037120', 'IDI 037290'")
While Not rs.EOF
DoCmd.OpenReport "report name", , , "[Item Number]='" & rs![Item Number]
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "C:\MyPDFs\" & rs![Item Number] & "_" & Format(Date(), "yyyymmdd"), False
DoCmd.Close "acReport", "report name", acSaveNo
rs.MoveNext
Wend

An alternative to looping recordset is to just loop through a listbox and do the report output on each selected item. You will probably want to loop through listbox anyway to build the IN filter string for the recordset. Could just do the output to pdf instead of building string. The referenced link shows how to loop through a listbox and act on selected items.