473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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.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
8 13465
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

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
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
> 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
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
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
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
Many thanks for your help.... it is much appreciated.

cheers
j
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bob Quintal | last post by:
Hi all, Using Access 97 Front End, linked to SQL Server back end. Required data is spread across three tables, with one table linked one to many to the two others. I created a report based on...
4
by: deko | last post by:
I can't move a multi-page report to the last record unless I keep the popup form (that defined it's subreports) open. DoCmd.OpenReport "rptStandard", acViewNormal DoCmd.Close acForm,...
2
by: Keith Wilby | last post by:
A97 I have a report/sub-report setup and for some records in the main report, the sub-report is blank. I want to set the height of the sub-report to zero where it is blank. I've set all the "Can...
3
by: manning_news | last post by:
Using A2K. I've been asked to modify a report currently requiring only one date parameter to now accept a date range. The main report has 2 subreports and is not bound to a table or query. The...
3
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a...
1
by: shaqattack1992-newsgroups | last post by:
I know this is kind of a weird question, but is there anyway to give a subreport control of a main report? I posted my situation earlier about having drawings print out after a group. I have a...
3
by: lorirobn | last post by:
Hello, I have a report which uses a subreport. When I run the report, I get "Enter Parameter Value" error message for "tblGuestRoom". I click ok and the report seems to work fine. I...
1
by: princesteveis | last post by:
Actually I want a single report by the name "Sales and Purchases Summary Statement" which comprises of a main report name "Purchases" and a subreport name "Sales". I have also created a query...
3
by: Connell | last post by:
I am using a command button from a form (Access 2000) to produce a report. The form and the report each have a subform (subreport). Here is the expression that produces a total for one field on the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.