473,747 Members | 2,298 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 13 '05 #1
8 13526
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
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!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
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!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

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
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


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
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

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 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?


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 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.
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
3215
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 one child and the master (inner join), which works properly and loads to preview in less than a second. Created the second report using the third table, with a select
4
7058
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, "frmReportOptions" <== popup form This is the error I get when I try to move to the last page of the report *after* closing the popup: "This expression is typed incorectly, or is too complex to be evaluate...."
2
2206
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 shrink" properties to 'yes' but I'm having trouble with the syntax referencing the text boxes on the sub- report. I get run-time error 2445 (you entered an expression that has an invalid reference to the property form/report) on the first line...
3
7465
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 report prints dental and hygenist appointments for the date (one subreport for each). The user wants to enter a date range and have one page for each date in the date range. I'm wondering how to modify the report. The only way I see is to create...
3
3597
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 'dynamic crosstab query' but I don't know how to!! I've read the "How to..." on the Microsoft site but it mainly gives an example rather than explain the basics, which I can't work out. My context is:
1
3775
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 report grouped by part group that lists the part numbers needed from that group. I have force new page after each group. This gives me a list of parts on each page. I want to print the needed drawings after the groups/list of parts. There are...
3
7402
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 narrowed down this error to the Link Master Fields property setting, when I tried the same scenario with form/subform. It gave me error: 'The Link Master fields property setting has produced this error: The object doesn't contain the Automation object...
1
2238
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 for main report (i.e. Purchases) with the following two parameters; PurchasesType and Date Range (i.e. Between PurchasesStartDate and PurchasesEndDate)
3
2385
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 report. =nz(!)+nz()+nz()+nz()+nz()+nz()+nz()+nz()+nz()+nz()+nz() This expression works perfectly as long as there is a value for the !) If !) has no value, that subreport is blank on the report and the total expression results in "Error" I...
0
8979
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8818
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9355
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6069
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4589
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3296
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2773
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.