There are several ways of running a parameter query from code without needing to enter a value manually or without getting an error. This is probably the most flexible as it allows you to use Docmd.OutputTo as well as Docmd.OpenReport methods.
Create a new general module, and on the declarations page dimension a variant array. (I've used a 10 element array but it can be any size, the smaller the better to efficiently use memory.) Use a variant array because you need it to hold any type of data.
Dim arrParameter(10)
Create a new Sub Procedure which allows you to set the values of the array such as:
Public Sub SetParam(ByVal InputVal, ByVal ParamID)
arrParameter(ParamID) = InputVal
End Sub
Where the InputVal is the value of the parameter, and ID is the parameter number you will be setting.
Create a similar Function to retreive the value of the parameter from the array:
Public Function GetParam(ByVal ParamID)
GetParam = arrParameter(ParamID)
End Function
Then in your query for the report where you would normally set a parameter in the criteria section simply place a call to the function to get a parameter as in:
=GetParam(X)
Where X = 1 or 2 or 3 and so on for the number of parameters for the query.
Then in your VB code, prior to opening the report, simply call the SetParam subprocedure for each parameter of the report with the values of the parameters you want to the report to use, as in:
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.