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!

Actually, I would not do it this way only because it seems like a lot of hoops to do something quite simple.

I would set up the query with the proper fields that I want to come out of it, including UpdatedBy, without any criteria. The make the query the record source for the report. If you open the report by itself, it would give you all the records. However, if you open the report via the form, the code would look like this.

DoCmd.OpenReport stDocName, acPreview, , "[UpdatedBy] = '" & Me.cboStaff & "'"

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division