View Single Post
 
Old April 21st, 2009, 05:10 PM
Purple_Khoi Purple_Khoi is offline
Registered User
 
Join Date: Apr 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Provide parameter to query for dynamic report

Please help.

How do I use selection from Combo box as query criteria? That is, how do I dynamically change report by changing criteria (e.g. Staff Name) after WHERE in query qrylatesthistory based on combo box cboStaff? Staff Name is one selection option for cboStaff.

I’ve been creating new queries and copying and pasting the following lines to reflect the correct query for the Filtername argument. It’s just a lot of maintenance when Staff Name is added or deleted.

Here’s the VBA Code I’ve used:

Dim stDocName As String

stDocName = "RptAdhereToPlan"
DoCmd.OpenReport stDocName, acPreview, Filtername:="qrylatesthistory"

Here’s my scenario:


Given:
WinXP Pro
MS Access 2002

cboStaff is a combo box for selecting different names; located on it own form.

SQL statement in query qrylatesthistory:

SELECT *
FROM qsellatest INNER JOIN QSelHistory ON qsellatest.MaxOfUpdateKey = QSelHistory.UpdateKey
WHERE (((QSelHistory.Resource) Like "StaffName") OR ((QSelHistory.UpdatedBy) Like "StaffName") AND ((QSelHistory.Complete)=No));

So I've been going in and changing "StaffName" to something else and saving query as another name. Then adding the new query to Filtername argument.

Any help is appreciated. Thanks in advance!