Passing parameter into a report from a form

Question

I'm relative new to Access 2010. I built a small CRM/Invoicing database as practice and to use for a while. I used a lot of examples from Northwind to learn from but I don't quite understand passing parameters.

I have two tables for invoice data (index and data), with the PK in the index table being the actual invoice number the customer would see then it's also a FK in the data table. I setup a query and have my report working with all items and totals and have it set on a report. If you call the Report directly, a parameter is asked for the invoice number (the PK in the index table)

What I'm looking to do now is place a button on my invoice form to "View Invoice" and have the parameter of "InvoiceNumber" from the "Invoice_Index" table passed to the report so I'm not prompted to enter it each time I want to view a Invoice/Report from clicking on the "View Invoice" button on my form.

Answer 1

Consider the DoCmd.OpenReport Method. It will allow you to specify a WhereCondition when you open the report. And the WhereCondition will operate like a WHERE clause in a query.

So if your form has a text box named txtInvoiceNumber which contains the InvoiceNumber to filter the report on, and if the name of the corresponding field in the report's record source is InvoiceNumber, you could use code like this in the click event of the command button on your invoice form.

Dim strWhereCondition As String
strWhereCondition = "InvoiceNumber = " & Me.txtInvoiceNumber
DoCmd.OpenReport "YourReportName", , , strWhereCondition

That should work if InvoiceNumber is numeric data type. If it's actually text data type, add quotes around Me.txtInvoiceNumber when you build the WhereCondition.

strWhereCondition = "InvoiceNumber = '" & Me.txtInvoiceNumber & "'"

(This suggestion assumes you will revise the query used by the report to eliminate the existing parameter.)