By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,220 Members | 1,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,220 IT Pros & Developers. It's quick & easy.

Filtering report and subreport from form

P: n/a
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.openreport as follows

DoCmd.OpenReport 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

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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.openreport as follows

DoCmd.OpenReport 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!CallingReport!ID
Nov 13 '05 #2

P: n/a

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!CallingReport!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(Cancel As Integer)
Me.Filter = "YearOfPublication=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
Nov 13 '05 #3

P: n/a
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!CallingReport!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(Cancel As Integer)
Me.Filter = "YearOfPublication=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 YearOfPublication. In the criteria row for that field enter
=Forms!MainForm!YearOfPublication 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.HiddenFilter = "YearOfPublication=1926"
Docmd.OpenReport.

Now in the OnOpen event of the subreport enter
If IsLoaded("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) 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("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm!HiddenFilter

Me("SubReportName").Report.Recordsource = strSQL
Endif
Endif

Nov 13 '05 #4

P: n/a
> Method 1: I'll assume the form that calls the report is called MainForm.
In the subreports recordsource, you have a column field called
YearOfPublication. In the criteria row for that field enter
=Forms!MainForm!YearOfPublication 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.HiddenFilter = "YearOfPublication=1926"
Docmd.OpenReport.

Now in the OnOpen event of the subreport enter
If IsLoaded("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) 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("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm!HiddenFilter

Me("SubReportName").Report.Recordsource = 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 YearOfPublication 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.AuthorID,
tblBooks.YearOfPublication
FROM tblBooks
WHERE (((tblBooks.YearOfPublication)=[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(Cancel 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(Cancel As Integer)
Me("Books").Report.RecordSource = "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


Nov 13 '05 #5

P: n/a
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
YearOfPublication. In the criteria row for that field enter
=Forms!MainForm!YearOfPublication 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.HiddenFilter = "YearOfPublication=1926"
Docmd.OpenReport.

Now in the OnOpen event of the subreport enter
If IsLoaded("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) 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("MainForm") Then
If Not IsNull(Forms!MainForm!HiddenFilter) Then
strSQL = "Select * From Employee " & _
"Where " & Forms!MainForm!HiddenFilter

Me("SubReportName").Report.Recordsource = 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 YearOfPublication 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.AuthorID,
tblBooks.YearOfPublication
FROM tblBooks
WHERE (((tblBooks.YearOfPublication)=[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 YearOfPublication = [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(Cancel 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(Cancel As Integer)
Me("Books").Report.RecordSource = "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

Nov 13 '05 #6

P: n/a
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 YearOfPublication = [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 YearOfPublication 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.YearOfPublication,
tblBooks.AuthorID, tblBooks.YearOfPublication
FROM tblBooks
WHERE (((tblBooks.YearOfPublication)=Forms!MainForm!cboY ear) And
((tblBooks.AuthorID)=Forms!MainForm!cboAuthorID))
Or (((Forms!MainForm!cboAuthorID) Is Null) And
((tblBooks.YearOfPublication)=Forms!MainForm!cboYe ar)
Or (((tblBooks.AuthorID)=Forms!MainForm!cboAuthorID)) And
((Forms!MainForm!cboYear) Is Null))
Or (((Forms!MainForm!cboAuthorID) Is Null) And ((Forms!MainForm!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?


Nov 13 '05 #7

P: n/a
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 YearOfPublication = [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 YearOfPublication 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.YearOfPublication,
tblBooks.AuthorID, tblBooks.YearOfPublication
FROM tblBooks
WHERE (((tblBooks.YearOfPublication)=Forms!MainForm!cboY ear) And
((tblBooks.AuthorID)=Forms!MainForm!cboAuthorID))
Or (((Forms!MainForm!cboAuthorID) Is Null) And
((tblBooks.YearOfPublication)=Forms!MainForm!cboYe ar)
Or (((tblBooks.AuthorID)=Forms!MainForm!cboAuthorID)) And
((Forms!MainForm!cboYear) Is Null))
Or (((Forms!MainForm!cboAuthorID) Is Null) And ((Forms!MainForm!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
FilterSubReportRec([YearOfPublication],[AuthorID])

Public Function FilterSubReportRec(lngYear As Long, lngAuthor As Long)
As Boolean

Dim blnYear As Boolean
Dim blnAuthor As Boolean

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

If (Isnull(Forms!MainForm!authorID) or _
Forms!MainForm!AuthorID = lngAuthor) then _
blnAuthor = True

FilterSubReportRec = (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.
Nov 13 '05 #8

P: n/a
Many thanks for your help.... it is much appreciated.

cheers
j
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.