Using record in table for parameter

[KeythStone]

Hey all,

I would like to know if I could use a recordset in a table to fill in a parameter for a query. If so I would like to use Do Until .EOF. MoveNext.

I would also like to be able to use the recordset for part of a filename, 'ReportName'


Function test()
On Error GoTo test_Err
Set db = CurrentDb()
Set ReportName = db.OpenRecordset("ReportList", dbOpenDynaset)
Do Until ReportName.EOF
Dim StrClient As String

StrClient = "F:\OP_CS\Client Reporting\ReportName\AIM\" & Format(Date, "yyyymmdd") & ".pdf"

DoCmd.OpenReport "AIM (Assignment,DB): ALL OPEN (AIM by DB: ALL OPEN)", acViewPreview, "", "", acNormal

DoCmd.OutputTo acOutputReport, "AIM (Assignment,DB): ALL OPEN (AIM by DB: ALL OPEN)", "PDFFormat(*.pdf)", StrDocClient, False, "", 0, acExportQualityPrint

DoCmd.Close acReport, "AIM (Assignment,DB): ALL OPEN (AIM by DB: ALL OPEN)"


Any help is much appreciated.
Stay Smooth,
KeythStone

 

[Joe4]

I would like to know if I could use a recordset in a table to fill in a parameter for a query

Why not just include that table in your query?

If it is just a one-record table, then you don't even need to link it to any of the other objects in your query.
Then you can use any of the fields you want in the query (in calculations or criteria).

 

[KeythStone]

Thanks for your response.

So more specifically. It's not just one record being pulled but many. And the query would be using one at a time. For e.g. Query1.AssignedTo = "IPC." That query makes a report with the data and then I publish the report on a shared drive. Next, the same process but using the second record, "STG." And on till the end of the list.

I'm thinking loop statements. But my first concern is being able to pull from the table for each record. Then, I'd like to pull from the same table for a segment of a filename where i'd be publishing the report. (see filename segment above in blue).

Is this possible? Thanks,
KeythStone

 

[Joe4]

I see. Yes, you can loop through a recordset and pull off the field values you need. Here is a link to a thread where I did this:
http://www.mrexcel.com/forum/showpost.php?p=373507&postcount=7