Passing parameters to a report based on a query

MrTibbs · Nov 20, 2001

Access '97.
I've built a report based on nested queries which require 5 parameters.
I can run the report and have it ask for each parameter in turn or run the query with parameters from the form.

How can I pass the form values into the report so I just have to call the report with the right parameters?

Could I run the query to a temporary table and then just report based on the table?

To run a query I:
Set db = CurrentDb
Set qdf = db.QueryDefs("qryreport")

' Set parameters for query based on values entered
qdf.Parameters![lowdate] = var_Low_Date
qdf.Parameters![highdate] = var_High_Date
qdf.Parameters![shiftcolour1] = var_Shift1
qdf.Parameters![shiftcolour2] = var_Shift2
qdf.Parameters![shiftcolour3] = var_Shift3

' execute the query
qdf.Execute

This works for make-table and append queries but doesn't appear to work for select queries.

AlanS · Nov 20, 2001

To pass the value of text box Text1 on form Form1 directly into either a select query or a report field, simply include the following expression at the appropriate place in the query or report:

Forms!Form1!Text1

MrTibbs · Nov 21, 2001

I wanted to put variables into a query which was imported.
I'll re-work the queries to use the different parameter names based on the form values rather than try and put the form values into the parameters the query expects.

pajamasw · Nov 23, 2001

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>


To pass vb parameters to any type of query, here is what I have developed. 
First create a global variable in heading of any module such as:
Public strFirstName as String
Then in the same module create a function:
Public Function get_strFirstName() as String 
 get_strFirstName
= strFirstName 
End Function
Now in the query use the function in the criteria box like:
=get_strFirstName()
repeat as needed for each parameter.
p.


</body>

</html>

pajamasw · Nov 23, 2001

Sorry, I misunderstood the HTML/UBB info on the page. Here is a plain text version.

To pass vb parameters to any type of query, here is what I have developed.
First create a global variable in heading of any module such as:

Public strFirstName as String

Then in the same module create a function:

Public Function get_strFirstName() as String
get_strFirstName = strFirstName
End Function

Now in the query use the function in the criteria box like:

=get_strFirstName()

repeat as needed for each parameter.

p.

MrTibbs · Oct 4, 2004

Use form variables on export .. parameters on select

I eventually used form variables on my export queries and parameters on my select queries. The function approach does shield the queries from the variables required but I prefer to see the variables being used.

Thank you for your suggestions.

Tony

Passing parameters to a report based on a query

MrTibbs

Registered User.

AlanS

Registered User.

MrTibbs

Registered User.

pajamasw

New member

pajamasw

New member

MrTibbs

Registered User.

Similar threads

Users who are viewing this thread