Pass parameters to Access 2010 report from DoCmd.OpenReport

Question

I have a form, which has 2 text boxes and 1 button. The text boxes are Date From and Date To. When the button is clicked, some VBA code runs which loops through query results (qryInvoicesBetweenDates), gets the Invoice ID and generates a print preview of the invoice. The problem is, I cant work out how to pass the report the current ID within the loop. I simply need to give the DoCmd.OpenReport he invoice_number variable.

VBA Code:

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qryInvoicesBetweenDates")
Dim rs As DAO.Recordset

qdf.Parameters(0) = tbFrom.Value
qdf.Parameters(1) = tbTo.Value

Set rs = qdf.OpenRecordset()
Do Until rs.EOF = True
        ' Set the invoice number to the current row
        'invoice_number = rs.N
    invoice_number = rs.Fields(0).Value


    ' Preview the invoice
    Dim stDocName As String
    stDocName = "InvoiceForWork"
    DoCmd.OpenReport stDocName, acPreview
Loop

Many thanks.

Fionnuala Fionnuala · Accepted Answer · 2013-02-18T13:57:17

You can use a where statement with OpenReport:

DoCmd.OpenReport stDocName, acPreview,,"ID=" & Rs!invoice_number

Where ID is the name of the field in the report that corresponds to Rs!invoice_number. The above example is for a numeric data type, you would need quotes for a text data type.

Pass parameters to Access 2010 report from DoCmd.OpenReport

1 Answers