Using VBA to set the parameters of a query

Question

So I have a query, as shown below, which runs on a parameter.

I was wondering if there is a way to use VBA to set the query parameters. I currently have about 40 queries all exactly the same except for the product ID, and as such I have 40 reports as well... if i have the one query, I will still have to write out the code for the productid in the VBA, but i can use one report instead of that many.

Life would be much easier with just one query and one report and have the VBA set the parameters, as the user does not know the product ID

SELECT TblTotalSale.ProductID, TblProduct.Description, TblTotalSale.Size, TblTotalSale.SalePrice, TblTotalSale.Day
FROM TblProduct INNER JOIN TblTotalSale ON TblProduct.ProductID = TblTotalSale.ProductID
WHERE (((TblTotalSale.ProductID)=[]));

The VBA

Private Sub IPA_Click()

DoCmd.OpenReport "RptProduct", acViewReport, , [TblTotalSale.ProductID="5"]

End Sub

I have also tried tblTotalSale.ProductID = "5" I know I am using SQL where I should be using VBA...

Thanks

Sam

Fionnuala · Accepted Answer · 2012-04-09 16:08:36Z

6

Do not include a parameter for the sql statement for reports and forms. Use the Where argument of the OpenForm or OpenReport method of the DoCmd object.

DoCmd.OpenReport Method

answered Apr 9, 2012 at 16:08

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sam 'Evilsam' Spiller Over a year ago

so remove the where in the SQL and in the VBA write DoCmd.OpenReport "RptProduct", acViewReport, , TblTotalSale.ProductID=5 ?

Fionnuala Over a year ago

With quotes "TblTotalSale.ProductID=5" or for text, "AText='ABC'"

Collectives™ on Stack Overflow

Using VBA to set the parameters of a query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related