Specify parameter to reports based on queries, then print

[CurtisFBuck]

I have a series of forms based on queries. Each of these queries have the same parameter ("Product ID"), but pulls from different tables. The purpose of these different forms is to pull product specifications from their respective tables.

In a userform, I prompt the user for the parameter "Product ID" to run on a check on the last time the specs were reviewed. If the review date is recent enough, it allows the specs to be printed.

The problem I have, is that each time I got to print one of the reports, it asks for the parameters again. I would like to pass the parameter the user input when opening the form to each of the reports when they open. This would cut out about 5 pop up boxes.

Help!

[twinnyfo]

Curtis,

First, it sounds like the Query supporting the Report is looking for Product ID, but that value is not actually available, so, you are simply telling the Report what to look for. Try to drill down to the query first.

Along those same lines, if you want to limit the query based on the value entered by the User, as one of the criteria in your query, you can use the value entered on the form like such:

WHERE [Product_ID] = Forms![FormName]![txtProduct_ID]

This will allow the Report to print just the records associated with that Product ID.

Hope this hepps!

[CurtisFBuck]

Hi Twinnyfo,

Thanks for your answer.

So the Form (frmPrintCheck) pulls in a query (qryMMO) to get a value (ReviewDate) from that query to check when the most recent review was. Because of this, opening the form requires providing the parameter (ProductID) to the query (qryMMO).

After the review check, a series of Forms are opened the reference a series of queries, including qryMMO. Despite this, you have to re-enter the parameter.

I've tried making relationships, etc. to tie these queries together.


So i've tried to provide the WHERE criteria in the open report code:

DoCmd.OpenReport "rptSpecs", acViewPreview,,[Product ID]=Me.[Product ID]

but that doesn't seem to change anything. Thoughts?

[CurtisFBuck]

I think I figured out what you meant just now twinnyfo...

I went to the query and changed the criteria for [Product ID] to pull directly from the form itself.

Thank you so much!

[twinnyfo]

Here's an idea, which I think will work:

In a separate, stand-alone module, establish a Global Variable and a quickie Function to use that variable in Queries:

Option Compare Database
Option Explicit
 
Public gProductID as [whatever data type the Product ID is]
 
Public Function ProductID() as [whatever data type the Product ID is]
    ProductID = gProductID
End Function

When the Form opens, ask the user to select the Product ID, in whatever method you desire. After the Product ID is selected or identified, establish the value of the Global Variable:

gRpoductID = me.txtProductID [or however you have established to set the Product ID]

Now, go back to the Query used for the Record Source for your Report. In the Product ID Field, in the Criteria, use this:

ProductID()

This should limit the records to only that Product ID.

Hope you understand this and that it hepps.