Passing parameters to a report...

[Joao]

Hello everybody,
Let's assume I have this coded SQL in the Report_Open property:

Private Sub Report_Open(Cancel As Integer)
RecordSource = "SELECT People, Address" _
& " FROM Lisbon " _
& " WHERE Year= " & intYear
End Sub

If I call this report thru a FORM, how can I pass the intYear variable as a
parameter?

 

[Paolo]

Hi Joao,
You can use a global variable. You declare it in a module i.e.
Global intYear As Integer

and then in your form you initialize it with your value before opening the
report e.g.

intyear=2008
docmd.openreport "Idunno",acviewpreview

HTH Paolo

 

[Klatuu]

A better solution is to use the Where argument of the OpenReport method and
let it do the filtering.

Also, change the name of the field Year to something else. Year is an
Access reserved word. Using reserved words, particularly one that is a
function name, can cause problems.

 

[pietlinden]

Hello everybody,
Let's assume I have this coded SQL in the Report_Open property:

Private Sub Report_Open(Cancel As Integer)
RecordSource = "SELECT People, Address" _
& " FROM Lisbon " _
& " WHERE Year= " & intYear
End Sub

If I call this report thru a FORM, how can I pass the intYear variable as a
parameter?

Why not just pass the filter in when you open the report?
just leave off the WHERE clause, and then use the Filter argument when
you call OpenReport? Using a public variable sounds dodgy... what if
you want a complex filter?

 

[Paolo]

Hi Klatuu,
I've already told him in a precedent post to use the where argument of the
openreport method... but perhaps he prefer do that in this way...

Cheers Paolo

 

[Joao]

Can't do that Paolo (Global Variable)

Hi Joao,
You can use a global variable. You declare it in a module i.e.
Global intYear As Integer

and then in your form you initialize it with your value before opening the
report e.g.

intyear=2008
docmd.openreport "Idunno",acviewpreview

HTH Paolo

 

[Joao]

Dave, That's a simple SQL that I wrote to help u guys more quickly to
understand my problem, also the SQL is irrelevant. I have all in portuguese,
so, don't worry about the reserved words. How I use the Where argument to
filter?

 

[Joao]

Sorry Paolo, but I don't remember any precedent post of u. But if u say so I
believe in u.

 

[Joao]

Yeap, i don't want to use the public variable, will ruin other code runnin
thru. but how do I use the filter? I must pass 2 arguments "intYear" and
"intMonth"

 

[Klatuu]

Remove the filtering from your report's record source. The Where argument of
the OpenReport method works exactly like the Where argument of the OpenForm
method.
The syntax is like an SQL WHERE statement without the word where. For
example:

strWhere = "[Year] = " & intYear
Docmd.OpenReport "MyReportName", , , strWhere