Access & VB question

[pe18ekt]

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.

 

[CameronM]

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