Currently I have a a macro in access that accepts a number(user input) and assigns it to tempvar for use within the macro. The macro then runs several queries and creates a report. I would like to write code to read a numeric field in a table and use that as the variable for use in the macro and create the report, then print the report to pdf. Ideally this would repeat these steps until EOF. Any help would be greatly appreciated.

It's a fairly easy task to get values from a table using a Recordset. You can then run whatever queries are needed based on the value in your recordset. One way is to modify your stored queries to have a critieria such as [ID], which can be replaced at runtime with the value from your Recordset.

A sample query sould look something like INSERT INTO TableResults (RepID, Name) SELECT TableA.RepID, TableA.Name FROM TableA WHERE TableA.RepID=[ID]

You then create a function to put it all together and print/preview your report at the end


Public Sub CreateReport()
Dim strSQL As String
Dim rst As Recordset
Dim qdf As QueryDef

'create a recordset to retrieve a unique id field
strSQL = "SELECT Rep FROM TableA"
Set rst = CurrentDb.OpenRecordset(strSQL)

'loop through the recordset and run a number of queries
'based on the unique id field
While Not rst.EOF

'turn off warnings so user is not prompted to insert/update table
DoCmd.SetWarnings False

'call first query - perhaps a Append query
Set qdf = CurrentDb.QueryDefs("qryMakeTable1")
qdf.Parameters("[ID]") = rst!Rep
qdf.Execute

'call second query - perhaps an Update query
Set qdf = CurrentDb.QueryDefs("qryMakeTable2")
qdf.Parameters("[ID]") = rst!Rep
qdf.Execute

'turn warning back on
DoCmd.SetWarnings True

rst.MoveNext
Wend

'open the report to show the results
DoCmd.OpenReport "rptTableResults", acViewPreview
End Sub