Report Generation Code problem

[Brennan]

Hello:

I have attached the follwoing code to a command button on
a form:

Dim strProjectManager As String
strProjectManager = "tblEstimators_EstimatorID = Me!
lstProjectManager"


If IsNull(Me!lstProjectManager) Then

DoCmd.OpenReport "rptPipeline_byProjectManager",
acViewPreview

Else

DoCmd.OpenReport "rptPipeline_byProjectManager",
acViewPreview, , strProjectManager

End If

Forms!Switchboard.Visible = False
Forms!frmReports.Visible = False

End Sub


The code should look at a list box containing project
managers and if null, return records for all the managers
or for a specific project manager if one is selected


Whe I run the report, the debugger opens and I get the
following error:

Run-time error 3070
The Microsoft Jet database engine does not recognize 'Me!
lstProjectManager' as a valid field name or expression.

If I remove the strProjectManager from the respective
open report command, the code runs.

Any ideas on what is wrong with my code would be
appreciated.

Thanks
Brennan

 

[Dave Cousineau]

this line is what is not right:

strProjectManager = "tblEstimators_EstimatorID = Me!
lstProjectManager"

you are passing strProjectManager as the Where Clause
argument, but the Where Clause must be in valid SQL, you
are typing your Where Clause in VB

your report rptPipeline_byProjectManager should have a
RecordSource like a table or more likely a query. the
Where Clause argument must be a valid SQL statement in the
context of the Record Source

IE:
given a table MyTable, with two fields ID and Description
if your record source is a query: "SELECT * FROM MyTable"

then your Where Clause must be in the context of your
record source:
(WHERE) "ID BETWEEN 2000 AND 4000"