Callins a report from a form in MS Access 2013 and passing multiple ids 1

[InspectorHound]

Hi,

I have an expense report based on a complex query. The report is called from a form that displays a list of people. How do I call the report and pass to it all of the people shown in the form. I am passing a person id so the report will show the expenses related to that person -- but I would like to pass more than one person id.

Thanks

 

[MajP]

The recordsource of the report could be something like

Select Field1, Field2, .... where PersonID in (select PersonID from yourComplexFormQuery)

 

[InspectorHound]

Ok, but that means I have to run my original query twice

 

[dhookom]

form that displays a list of people" how are you doing this? Is this a table or query or list box or subform? How is this "list of people" created?

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

I use a query that selects handful of people (from thousands) based on search criteria entered by the user. I then want the report to use the ten or so IDs displayed on the form to show expense data for these people from another table.

 

[dhookom]

Join your report's record source to "query that selects handful of people (from thousands) based on search criteria entered by the user" so you can limit your report results to that group of people.

You can also set a filter like

WHERE PeopleID IN (SELECT PeopleID FROM [query that selects handful of people (from thousands) based on search criteria entered by the user])

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Ok.. Can I do that in the where clause of the DoCmd Open Report statement

 

[dhookom]

Your where clause could be

"PeopleID IN (SELECT PeopleID FROM YourQuery)"

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Ok. Will try that tomorrow

 

[MajP]

isn't that what I said.

 

[dhookom]

MajP it's the same concept but instead of in the saved query or report's record source, it is applied to the Filter property of the report using the WHERE CONDITION of the DoCmd.OpenReport method.

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Ok, but now the where clause is not being passed to the report.
Here is my code:

/********************************************************************
Public Sub cmdReport_Click()
reportSQL = "Select AuthorizationID from (" & strSQL & ")"
MsgBox reportSQL
strWhere = "[Trvl_plng_athrztn.AuthorizationId] in (" & reportSQL & ")"
MsgBox strWhere
DoCmd.OpenReport "Expense Report", acViewPreview, , strWhere
/***********************************************************
What is wrong with this? The record source for the report is a query and the where cluase is not being passed. The report always pulls 14,000 records.

 

[dhookom]

Does your code compile?
What is the value of strSQL?
What does the MsgBox display?
Can you place a large text box in your report and set its control source to:
=[Filter]


Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Yes, the code compiles.

The value of strSQL is
*~*~*~*~*~*~*~*~*


Yes, the code compiles.


The value of strSQL is
*~*~*~*~*~*~*~*~*
SELECT * FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*"
~*~*~*~*~*~*~~*

The Filter box is empty

I am now getting the message that the specdified field "[Trvl_plng_athrztn.AuthorizationId] can refer to more than one table in your from clause.

 

[dhookom]

When you stated you wanted to filter for people in a list, it actually meant AuthorizationIDs in a list?

I don't understand what you mean by

The value of strSQL is
*~*~*~*~*~*~*~*~*

Instead of MsgBox strWhere can you use Debug.Print strWhere so you can copy the results for us to see?

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Fisrt Query that populates form:
SELECT * FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*"

Second query to get the id number of people displayed on form:
Select AuthID from (SELECT * FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*" )

The where clause for the report given as strWhere:
[Trvl_plng_athrztn.AuthorizationId] in (Select AuthID from (SELECT * FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*" ))

 

[dhookom]

As I posted in my example you would select one field not *. I'm not sure why you have so many nested queries. I expect your where condition might be something like:

[AuthorizationId] in (Select AuthID FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*" )

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Something must be wrong with the report. Even when I try to pass a single variable, the where clause of the report does not change and all records are returned:
DoCmd.OpenReport "Expense Report", acViewPreview, , "[A.AuthorizationId] = " & Me.VoucherID

 

[dhookom]

I'm not sure why you prefix your field name with "A.". Is there more than one field in your report's record source named AuthorizationID? If not, just use:

DoCmd.OpenReport "Expense Report", acViewPreview, , "[AuthorizationId] = " & Me.VoucherID

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

I get a sytax error in query expression <expression>. (Error 3075)
DoCmd.OpenReport "Expense Report", acViewPreview, , "[AuthorizationId] = " & Me.VoucherID