Filtering report and subreport from form

hi,

i have a form on which a user can choose specific criteria such as dates
etc, in order to filter the report that is called from the form.

i do this by using the Where section of the docmd.openrepor t as follows

DoCmd.OpenRepor t stDocName, acPreview, , strWhere

where strWhere is a string dependant on the choices the user makes in the
form.

this works fine.

my problem is that the report also contains a subreport and i would like to
be able to give the user some options to filter the subreport aswell. But i
just cant figure out how to do this. any suggestions would be much
appreciated.

thanks in advance

[Salad]

jim wrote:

hi,

i have a form on which a user can choose specific criteria such as dates
etc, in order to filter the report that is called from the form.

i do this by using the Where section of the docmd.openrepor t as follows

DoCmd.OpenRepor t stDocName, acPreview, , strWhere

where strWhere is a string dependant on the choices the user makes in the
form.

this works fine.

my problem is that the report also contains a subreport and i would like to
be able to give the user some options to filter the subreport aswell. But i
just cant figure out how to do this. any suggestions would be much
appreciated.

thanks in advance

Maybe in the OnOpen event of the form create a SQL string for the
rowsource of the subreport and make the rowsource for the subreport the
SQL string.

Perhaps on the OnOpen event of the form enter
Me.Filter = ...whatever your filter string is
Me.FilterOn = True

Perhaps in the rowsource query, filter on info from the form that opened
the report.
Select * from table where id = Forms!CallingRe port!ID

Maybe in the OnOpen event of the form create a SQL string for the
rowsource of the subreport and make the rowsource for the subreport the
SQL string.

Perhaps on the OnOpen event of the form enter
Me.Filter = ...whatever your filter string is
Me.FilterOn = True

Perhaps in the rowsource query, filter on info from the form that opened
the report.
Select * from table where id = Forms!CallingRe port!ID

Hi there, many thanks for your reply

i originally intended using the me.filter as you specified above and this is
what i had :

Private Sub Report_Open(Can cel As Integer)
Me.Filter = "YearOfPublicat ion=1926"
Me.FilterOn = True

End Sub

This works fine when i open the subreport on its own

but when i open the main form i get the following error message

Run-time error '2101':
The setting you entered isn't valid for this property

any help much appreciated

cheers
jim

[Salad]

jim wrote:

Maybe in the OnOpen event of the form create a SQL string for the
rowsource of the subreport and make the rowsource for the subreport the
SQL string.

Perhaps on the OnOpen event of the form enter
Me.Filter = ...whatever your filter string is
Me.FilterOn = True

Perhaps in the rowsource query, filter on info from the form that opened
the report.
Select * from table where id = Forms!CallingRe port!ID

Hi there, many thanks for your reply

i originally intended using the me.filter as you specified above and this is
what i had :

Private Sub Report_Open(Can cel As Integer)
Me.Filter = "YearOfPublicat ion=1926"
Me.FilterOn = True

End Sub

This works fine when i open the subreport on its own

but when i open the main form i get the following error message

Run-time error '2101':
The setting you entered isn't valid for this property

any help much appreciated

cheers
jim

Method 1: I'll assume the form that calls the report is called
MainForm. In the subreports recordsource, you have a column field
called YearOfPublicati on. In the criteria row for that field enter
=Forms!MainForm !YearOfPublicat ion Or IsNull()
Method 2: I'm not testing this...I'll leave this up to you.
I don't know what the recordsource is. Let's say
Select * From Employee

You don't need an orderby clause since that is performed in the
Groupings and Sortings

Let's say the form that opens the report is named MainForm. Let's
assume you have a method to determine it the form is open (IsOpen(),
IsLoaded()...se e Google if you don't)...then store the "filter" in a
invisible field. Lets call it HiddenFilter. In this case
Me.HiddenFilter = "YearOfPublicat ion=1926"
Docmd.OpenRepor t.

Now in the OnOpen event of the subreport enter
If IsLoaded("MainF orm") Then
If Not IsNull(Forms!Ma inForm!HiddenFi lter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm! HiddenFilter

Me.Recordsource = strSQL
Endif
Endif

If that doesn't work, you could try it from the OnOpen event of the Main
report.
If IsLoaded("MainF orm") Then
If Not IsNull(Forms!Ma inForm!HiddenFi lter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm! HiddenFilter

Me("SubReportNa me").Report.Rec ordsource = strSQL
Endif
Endif

> Method 1: I'll assume the form that calls the report is called MainForm.

In the subreports recordsource, you have a column field called
YearOfPublicati on. In the criteria row for that field enter
=Forms!MainForm !YearOfPublicat ion Or IsNull()
Method 2: I'm not testing this...I'll leave this up to you.
I don't know what the recordsource is. Let's say
Select * From Employee

You don't need an orderby clause since that is performed in the Groupings
and Sortings

Let's say the form that opens the report is named MainForm. Let's assume
you have a method to determine it the form is open (IsOpen(),
IsLoaded()...se e Google if you don't)...then store the "filter" in a
invisible field. Lets call it HiddenFilter. In this case
Me.HiddenFilter = "YearOfPublicat ion=1926"
Docmd.OpenRepor t.

Now in the OnOpen event of the subreport enter
If IsLoaded("MainF orm") Then
If Not IsNull(Forms!Ma inForm!HiddenFi lter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm! HiddenFilter

Me.Recordsource = strSQL
Endif
Endif

If that doesn't work, you could try it from the OnOpen event of the Main
report.
If IsLoaded("MainF orm") Then
If Not IsNull(Forms!Ma inForm!HiddenFi lter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm! HiddenFilter

Me("SubReportNa me").Report.Rec ordsource = strSQL
Endif
Endif

Many thanks for you reply,

Method 1 works well, i have used qryBooks as the record source for my
subreport rptBooks and in the criteria of YearOfPublicati on have simply put
the following:

[Forms]![MainForm]![cboYear]

cboYear being the combo box on the main form which is used to select the
year of publication.
This gives the following SQL:

SELECT tblBooks.BookID , tblBooks.Title, tblBooks.Author ID,
tblBooks.YearOf Publication
FROM tblBooks
WHERE (((tblBooks.Yea rOfPublication) =[Forms]![MainForm]![cboYear]));
This works excellently and is nice and simple for me to understand.

The only problem is if cboYear is blank it returns no books; is there any
way that it can be made to return all the books if it is blank?

Below i have detailed the problems i encountered with method 2 for the sake
of interest.

Method2
Doesn't seem to work so good. Trying to chang the recordsource of the
subreport by putting the following in the OnOpen event of the subreport:

Private Sub Report_Open(Can cel As Integer)
Me.RecordSource = "Select * from qryBooks"
End Sub

gives me the following error:

Run-time error '2191':
You can't set Record Source propery in print preview or after printing has
started.
Strangely this works fine if the subreport is opened on its own. the error
only occurs when it is opened as part of the main form. This seems similar
to the problem of setting the filter in the OnOpen - it works fine when the
subreport is opened on its own but causes an error when opened form the main
form.

Whilst trying to change the recordsource of the subreport from the OnOpen
event of the main report:

Private Sub Report_Open(Can cel As Integer)
Me("Books").Rep ort.RecordSourc e = "select * from qryBooks"
End Sub

gives me the following error:

Run-time error '2455':
You entered an expression that has an invalid reference to the property
Form/Report

[Salad]

jim wrote:

Method 1: I'll assume the form that calls the report is called MainForm.
In the subreports recordsource, you have a column field called
YearOfPublica tion. In the criteria row for that field enter
=Forms!MainFo rm!YearOfPublic ation Or IsNull()
Method 2: I'm not testing this...I'll leave this up to you.
I don't know what the recordsource is. Let's say
Select * From Employee

You don't need an orderby clause since that is performed in the Groupings
and Sortings

Let's say the form that opens the report is named MainForm. Let's assume
you have a method to determine it the form is open (IsOpen(),
IsLoaded()... see Google if you don't)...then store the "filter" in a
invisible field. Lets call it HiddenFilter. In this case
Me.HiddenFilt er = "YearOfPublicat ion=1926"
Docmd.OpenRep ort.

Now in the OnOpen event of the subreport enter
If IsLoaded("MainF orm") Then
If Not IsNull(Forms!Ma inForm!HiddenFi lter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm! HiddenFilter

Me.Recordsour ce = strSQL
Endif
Endif

If that doesn't work, you could try it from the OnOpen event of the Main
report.
If IsLoaded("MainF orm") Then
If Not IsNull(Forms!Ma inForm!HiddenFi lter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm! HiddenFilter

Me("SubReport Name").Report.R ecordsource = strSQL
Endif
Endif
Many thanks for you reply,

Method 1 works well, i have used qryBooks as the record source for my
subreport rptBooks and in the criteria of YearOfPublicati on have simply put
the following:

[Forms]![MainForm]![cboYear]

cboYear being the combo box on the main form which is used to select the
year of publication.
This gives the following SQL:

SELECT tblBooks.BookID , tblBooks.Title, tblBooks.Author ID,
tblBooks.YearOf Publication
FROM tblBooks
WHERE (((tblBooks.Yea rOfPublication) =[Forms]![MainForm]![cboYear]));
This works excellently and is nice and simple for me to understand.

The only problem is if cboYear is blank it returns no books; is there any
way that it can be made to return all the books if it is blank?

Yes. In the query create a new column. Enter
[Forms]![MainForm]![cboYear]
Now, in the SECOND Criteria row enter
Is Null

Why the second row? Because you are creating an OR condition. If you
view the SQL...view/sql from the menu, you should see
Where YearOfPublicati on = [Forms]![MainForm]![cboYear] Or _
IsNull([Forms]![MainForm]![cboYear])
or something similar.

Remember...if you have criteria on the first row, all of the criteria
must exist on the second row (excluding the columns that make up the OR)

Ex: If you filter on EmpName and Year on criteria row 1, then in
criteria row2 you must also filter on EmpName besides checking for the
null condition. This creates an And/Or filter.
Where (Empname = 'Joe' And Year = 2005) Or )
(Empname = 'Joe' And IsNull(Year))

Below i have detailed the problems i encountered with method 2 for the sake
of interest.

Method2
Doesn't seem to work so good. Trying to chang the recordsource of the
subreport by putting the following in the OnOpen event of the subreport:

Private Sub Report_Open(Can cel As Integer)
Me.RecordSource = "Select * from qryBooks"
End Sub

gives me the following error:

Run-time error '2191':
You can't set Record Source propery in print preview or after printing has
started.
Strangely this works fine if the subreport is opened on its own. the error
only occurs when it is opened as part of the main form. This seems similar
to the problem of setting the filter in the OnOpen - it works fine when the
subreport is opened on its own but causes an error when opened form the main
form.

Whilst trying to change the recordsource of the subreport from the OnOpen
event of the main report:

Private Sub Report_Open(Can cel As Integer)
Me("Books").Rep ort.RecordSourc e = "select * from qryBooks"
End Sub

gives me the following error:

Run-time error '2455':
You entered an expression that has an invalid reference to the property
Form/Report

Yes. In the query create a new column. Enter
[Forms]![MainForm]![cboYear]
Now, in the SECOND Criteria row enter
Is Null

Why the second row? Because you are creating an OR condition. If you
view the SQL...view/sql from the menu, you should see
Where YearOfPublicati on = [Forms]![MainForm]![cboYear] Or _
IsNull([Forms]![MainForm]![cboYear])
or something similar.

Remember...if you have criteria on the first row, all of the criteria must
exist on the second row (excluding the columns that make up the OR)

Ex: If you filter on EmpName and Year on criteria row 1, then in criteria
row2 you must also filter on EmpName besides checking for the null
condition. This creates an And/Or filter.
Where (Empname = 'Joe' And Year = 2005) Or )
(Empname = 'Joe' And IsNull(Year))

many thanks again for replying & sorry for being so slow.

i've got two fields im filtering on YearOfPublicati on with cboYear and
Author with cboAuthorID.
and so i have basically got 4 scenarios:

cboYear blank & cboAuthorID blank : all books in database.
cboYear not blank & cboAuthorID blank: all books for year specified.
cboYear blank & cboAuthorID not blank: all books for specified author
cboYear not blank & cboAuthorID not blank: only books published in specified
year by specified author

i have built the following query which works:

SELECT tblBooks.BookID , tblBooks.Title, tblBooks.YearOf Publication,
tblBooks.Author ID, tblBooks.YearOf Publication
FROM tblBooks
WHERE (((tblBooks.Yea rOfPublication) =Forms!MainForm !cboYear) And
((tblBooks.Auth orID)=Forms!Mai nForm!cboAuthor ID))
Or (((Forms!MainFo rm!cboAuthorID) Is Null) And
((tblBooks.Year OfPublication)= Forms!MainForm! cboYear)
Or (((tblBooks.Aut horID)=Forms!Ma inForm!cboAutho rID)) And
((Forms!MainFor m!cboYear) Is Null))
Or (((Forms!MainFo rm!cboAuthorID) Is Null) And ((Forms!MainFor m!cboYear) Is
Null));

It seems to be getting really complicated, if i add another field to filter
eg, publisher i'll have 8 different OR statements.

is this the easiest way to do it?

[Salad]

jim wrote:

Yes. In the query create a new column. Enter
[Forms]![MainForm]![cboYear]
Now, in the SECOND Criteria row enter
Is Null

Why the second row? Because you are creating an OR condition. If you
view the SQL...view/sql from the menu, you should see
Where YearOfPublicati on = [Forms]![MainForm]![cboYear] Or _
IsNull([Forms]![MainForm]![cboYear])
or something similar.

Remember... if you have criteria on the first row, all of the criteria must
exist on the second row (excluding the columns that make up the OR)

Ex: If you filter on EmpName and Year on criteria row 1, then in criteria
row2 you must also filter on EmpName besides checking for the null
condition. This creates an And/Or filter.
Where (Empname = 'Joe' And Year = 2005) Or )
(Empname = 'Joe' And IsNull(Year))

many thanks again for replying & sorry for being so slow.

i've got two fields im filtering on YearOfPublicati on with cboYear and
Author with cboAuthorID.
and so i have basically got 4 scenarios:

cboYear blank & cboAuthorID blank : all books in database.
cboYear not blank & cboAuthorID blank: all books for year specified.
cboYear blank & cboAuthorID not blank: all books for specified author
cboYear not blank & cboAuthorID not blank: only books published in specified
year by specified author

i have built the following query which works:

SELECT tblBooks.BookID , tblBooks.Title, tblBooks.YearOf Publication,
tblBooks.Author ID, tblBooks.YearOf Publication
FROM tblBooks
WHERE (((tblBooks.Yea rOfPublication) =Forms!MainForm !cboYear) And
((tblBooks.Auth orID)=Forms!Mai nForm!cboAuthor ID))
Or (((Forms!MainFo rm!cboAuthorID) Is Null) And
((tblBooks.Year OfPublication)= Forms!MainForm! cboYear)
Or (((tblBooks.Aut horID)=Forms!Ma inForm!cboAutho rID)) And
((Forms!MainFor m!cboYear) Is Null))
Or (((Forms!MainFo rm!cboAuthorID) Is Null) And ((Forms!MainFor m!cboYear) Is
Null));

It seems to be getting really complicated, if i add another field to filter
eg, publisher i'll have 8 different OR statements.

is this the easiest way to do it?

The easiest way to do this may be to create a function. aircode

Enter something like this as a field row. In criteria, enter True
FilterSubReport Rec([YearOfPublicati on],[AuthorID])

Public Function FilterSubReport Rec(lngYear As Long, lngAuthor As Long)
As Boolean

Dim blnYear As Boolean
Dim blnAuthor As Boolean

If Isloaded("MainF orm") Then
If (Isnull(Forms!M ainForm!cboYear ) or _
Forms!MainForm! cboYear = lngYear) then _
blnYear = True

If (Isnull(Forms!M ainForm!authorI D) or _
Forms!MainForm! AuthorID = lngAuthor) then _
blnAuthor = True

FilterSubReport Rec = (blnYear And blnAuthor)
else
'calling report form not open. Use all, no filter
FilterSubReport = True
endif
end function

In your subreports recordsource query it will call a function that
returns a true or false. You then need to filter for only records that
are true by entering True on the criteria line.

I don't know if you are a programmer. If you can follow the above
logic, you should be able to filter the records.

Many thanks for your help.... it is much appreciated.

cheers
j