Using a query parameter for a report

  • Thread starter Thread starter Access::Student
  • Start date Start date
A

Access::Student

Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do. Thanks
for any help.
 
Access said:
Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
 
Access said:
Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
 
Marshall Barton said:
Access said:
Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
Click to expand...

Hey, thanks, that was a mistake on my part, you can disregard that.

So I used what you put:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

And I'm still have problems. I still have to enter the parameter. The query
Common BF's is as follows:

SELECT *
FROM BF
WHERE (((BF.kf_from_id)=[kp_people_id])) OR
(((BF.kf_assigned_id)=[kp_people_id])) OR
(((BF.kf_assigned_sec_id)=[kp_people_id]));

but that works when I enter the required parameter., so I didn't think that
was the problem. am I still missing something?
 
Marshall Barton said:
Access said:
Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
Click to expand...

Hey, thanks, that was a mistake on my part, you can disregard that.

So I used what you put:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

And I'm still have problems. I still have to enter the parameter. The query
Common BF's is as follows:

SELECT *
FROM BF
WHERE (((BF.kf_from_id)=[kp_people_id])) OR
(((BF.kf_assigned_id)=[kp_people_id])) OR
(((BF.kf_assigned_sec_id)=[kp_people_id]));

but that works when I enter the required parameter., so I didn't think that
was the problem. am I still missing something?
 
Access::Student said:
Marshall Barton said:
Access said:
Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
Click to expand...

Hey, thanks, that was a mistake on my part, you can disregard that.

So I used what you put:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

And I'm still have problems. I still have to enter the parameter. The query
Common BF's is as follows:

SELECT *
FROM BF
WHERE (((BF.kf_from_id)=[kp_people_id])) OR
(((BF.kf_assigned_id)=[kp_people_id])) OR
(((BF.kf_assigned_sec_id)=[kp_people_id]));

but that works when I enter the required parameter., so I didn't think that
was the problem. am I still missing something?
Click to expand...


One extra thing. It now only works if I press the button and enter the ID
from the record the button was pressed from. Does that mean anything?
 
Access::Student said:
Marshall Barton said:
Access said:
Hello,

I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
Click to expand...

Hey, thanks, that was a mistake on my part, you can disregard that.

So I used what you put:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

And I'm still have problems. I still have to enter the parameter. The query
Common BF's is as follows:

SELECT *
FROM BF
WHERE (((BF.kf_from_id)=[kp_people_id])) OR
(((BF.kf_assigned_id)=[kp_people_id])) OR
(((BF.kf_assigned_sec_id)=[kp_people_id]));

but that works when I enter the required parameter., so I didn't think that
was the problem. am I still missing something?
Click to expand...


One extra thing. It now only works if I press the button and enter the ID
from the record the button was pressed from. Does that mean anything?
 
Access said:
Marshall Barton said:
Access said:
I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
Click to expand...

So I used what you put:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

And I'm still have problems. I still have to enter the parameter. The query
Common BF's is as follows:

SELECT *
FROM BF
WHERE (((BF.kf_from_id)=[kp_people_id])) OR
(((BF.kf_assigned_id)=[kp_people_id])) OR
(((BF.kf_assigned_sec_id)=[kp_people_id]));

but that works when I enter the required parameter., so I didn't think that
was the problem. am I still missing something?
Click to expand...


You need to remove the parameterized criteria from the query
to avoid being prompted for it. Gange the query to just:
SELECT *
FROM BF
or change the report's record source property to the BF
table.

Since you are filtering multiple fields, the code needs to
use all the filtered fields:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id] & _
" OR [kf_assigned_id] = " & Me.[kp_people_id] & _
" OR [kf_assigned_sec_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

If you inspect the resulting stWhere string carefully, you
will see that it is your old query's Where clause with the
value filled in and without the word Where.
 
Access said:
Marshall Barton said:
Access said:
I'm trying to create a button to generate a report dependent on the current
record.

So I have two tables, "tasks" and "employees". I have a query that return
"tasks" based on a parameter inputted, which is the primary key of
"employees". This works so that I can input the ID number and get the correct
report, including only "tasks" that are related to that "employee". What I
want to do is put a button on the form for "employees" so that I can run the
report without having to input the ID.

I currently have the following code to open the report:

DoCmd.OpenReport "Common Tasks's", acViewReport, "Common Tasks's"

I can't figure out how to pass the value, so I'm not sure what to do.
Click to expand...


What is the "Common Tasks's" in your OpenReport line
supposed to do? That argument is rarely useful so it is
highly suspicious.

The usual way to filter a report is to use the
WhereCondition argument:

Dim stWhere As String
stWhere = "[employee ID field] = " & Me.[employee ID field]
DoCmd.OpenReport "Common Tasks's", acViewReport, , stWhere
Click to expand...

So I used what you put:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

And I'm still have problems. I still have to enter the parameter. The query
Common BF's is as follows:

SELECT *
FROM BF
WHERE (((BF.kf_from_id)=[kp_people_id])) OR
(((BF.kf_assigned_id)=[kp_people_id])) OR
(((BF.kf_assigned_sec_id)=[kp_people_id]));

but that works when I enter the required parameter., so I didn't think that
was the problem. am I still missing something?
Click to expand...


You need to remove the parameterized criteria from the query
to avoid being prompted for it. Gange the query to just:
SELECT *
FROM BF
or change the report's record source property to the BF
table.

Since you are filtering multiple fields, the code needs to
use all the filtered fields:

Dim stWhere As String
stWhere = "[kp_people_id] = " & Me.[kp_people_id] & _
" OR [kf_assigned_id] = " & Me.[kp_people_id] & _
" OR [kf_assigned_sec_id] = " & Me.[kp_people_id]
DoCmd.OpenReport "Common BF's", acViewReport, , stWhere

If you inspect the resulting stWhere string carefully, you
will see that it is your old query's Where clause with the
value filled in and without the word Where.
 
Back
Top