Using WHERE clause when launching Reports from Code

[Stephen @ ZennHAUS]

Hi Guys and Gals

I have a number of reports that are all launched from the same dialog box,
which is used to specify filter criteria for the report. I have used code
to create a WHERE clause which is stored in a String variable and then
passed to the form as part of the DoCmd.OpenReport line of code.

It works a treat EXCEPT ... If I have a report that has one or more
subreports, it does not pass the WHERE clause to the subreport(s). Since
all of my main reports that have subreports do not have any data bindings,
there is no data to filter.

How do I get the subreports to use the WHERE clause (or filter property) of
the parent report?

As additional information, you can refer to my post "Specifying conditional
Criteria in a Query or Report" which was posted on 23/4/2009 in which I
explain that I have changed to using code to create a WHERE clause for the
reports instead of having the queries directly access the content of the
textbox on the criteria form because I now need to supply more criteria than
just the date.

Cheers

Stephen @ ZennHAUS

 

[Bob Barrows]

Hi Guys and Gals
line of code.

It works a treat EXCEPT ... If I have a report that has one or more
subreports, it does not pass the WHERE clause to the subreport(s).
Since all of my main reports that have subreports do not have any
data bindings, there is no data to filter.

It's been years since I've used Access reports, but I suspect you would
need to do this in the Load events of the subreports.

 

[Stephen @ ZennHAUS]

Thanks for that Bob. I've tried that using some simple code in the Open
event of the report that sets the Me.Filter and Me.FilterOn properties to be
the same as the parent report because I noticed that the DoCmd.OpenReport
command sets those properties (MeFilter=rptProductionFilter). But I got
errors saying it wasn't a valid way to set the property. SO, now I have
used a global variable to store the WHERE clause and the following code.

Private Sub Report_Open(Cancel As Integer)
If MyWhereClause <> "" Then
Me.Filter = MyWhereClause
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub

But I still get an error: "Run time error 2101: The Setting you entered
isn't valid for this property."

When I look at the code, it stops on Me.Filter = MyWhereClause.

The Value of both MyWhereClause and Me,Filter in code is "((ProdDate)
Between #03/31/2009# And #03/01/2009#)"

If I manually set the properties of the report and save it before it is run,
it works just fine.

Cheers

Stephen @ ZennHAUS

 

[Paul E. Schoen]

Thanks for that Bob. I've tried that using some simple code in the Open
event of the report that sets the Me.Filter and Me.FilterOn properties to
be the same as the parent report because I noticed that the
DoCmd.OpenReport command sets those properties
(MeFilter=rptProductionFilter). But I got errors saying it wasn't a
valid way to set the property. SO, now I have used a global variable to
store the WHERE clause and the following code.

Private Sub Report_Open(Cancel As Integer)
If MyWhereClause <> "" Then
Me.Filter = MyWhereClause
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub

But I still get an error: "Run time error 2101: The Setting you entered
isn't valid for this property."

When I look at the code, it stops on Me.Filter = MyWhereClause.

The Value of both MyWhereClause and Me,Filter in code is "((ProdDate)
Between #03/31/2009# And #03/01/2009#)"

If I manually set the properties of the report and save it before it is
run, it works just fine.

Cheers

Stephen @ ZennHAUS

I don't know if this is related or not, but I wanted to generate a report
based on a single "Horse" in the Horses table as shown on a form, along
with multiple "Records" with the same ID. What worked was to add a
criterion in the query in the report:

SELECT HORSE.ID AS HORSE_ID, HORSE.*, Records.ID AS Records_ID,
Records.Type, Records.RecordDate, Records.Provider, Records.Details FROM
HORSE INNER JOIN Records ON HORSE.ID=Records.ID WHERE
(((HORSE.ID)=[Forms]![HorseInfo].[tbID]));

I tried using a macro in which I used a Where clause that was essentially
the same but IIRC perhaps with not so many parentheses, and it was
converted to the form [HORSE]![ID]=[Forms]![HorseInfo].[tbID]. It seems
that I had to create the bound text box tbID in the form rather than use ID
directly. And it also seems that there were references to HORSE_ID and
HorseInfo_ID. I'm not sure I understand what they mean, as it appears there
are at least three different ways to refer to the same field.

It also appeared that the Where clause became the Filter in the properties
of the report.

I was trying a lot of different things and this just seemed to be what
worked, but I still don't know the right way to do various things.

Paul

 

[Paul E. Schoen]

I don't know if this is related or not, but I wanted to generate a report
based on a single "Horse" in the Horses table as shown on a form, along
with multiple "Records" with the same ID. What worked was to add a
criterion in the query in the report:

SELECT HORSE.ID AS HORSE_ID, HORSE.*, Records.ID AS Records_ID,
Records.Type, Records.RecordDate, Records.Provider, Records.Details FROM
HORSE INNER JOIN Records ON HORSE.ID=Records.ID WHERE
(((HORSE.ID)=[Forms]![HorseInfo].[tbID]));

Actually I had to use this (LEFT JOIN) to make it work properly if there
are no matching "Records":

SELECT HORSE.ID AS HORSE_ID, HORSE.*, Records.ID AS Records_ID,
Records.Type, Records.RecordDate, Records.Provider, Records.Details FROM
HORSE LEFT JOIN Records ON HORSE.ID=Records.ID WHERE
(((HORSE.ID)=[Forms]![HorseInfo].[tbID]));

Paul