Using a query parameter for a report

[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.

 

[Marshall Barton]

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.

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::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.

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

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]

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.

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

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?

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?

 

[Marshall Barton]

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.

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

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?

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.