Report Generation Code problem

  • Thread starter Thread starter Brennan
  • Start date Start date
B

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
 
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"
 
Back
Top