Filter a sub report on open (1 Viewer)

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
Hello again,

Im trying to filter a subreport on open.

in my report I have a field called "Packages" and my subreport consists of a table, one of the columns is "Packages" so I want to use the value that I have in "Packages" to filter for only those items in the sub report.

Is this possible?
 

KenHigg

Registered User
Local time
Today, 05:41
Joined
Jun 9, 2004
Messages
13,327
Sounds like you simply need to set up the parent/child link between the report and the sub report in the forms subreport object properties... ?
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
hmm, I am not sure...
I want to show a report based on all a collection of packages, so I filter the report by the collection name... so there is multiple packages.

When I set up the Parent/Child relationship to packages It asks me to input the package name... which cant be done as I want to show all packages under the collection
 

KenHigg

Registered User
Local time
Today, 05:41
Joined
Jun 9, 2004
Messages
13,327
You're going to have to post the table schema so we know how this is all set up, I'm getting confused...
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
You're going to have to post the table schema so we know how this is all set up, I'm getting confused...

Table1
Package - Unique Identifier
Type - the type of package

Table2
contains a list of things which appears in the subreport table, the link between this table and table2 is Package. In this table, Package is not unique so I am assigning lots of records to the information that is kept in table 1
Table1.Package = Table2.Package


Main report is filtered by type, so the number of reports I want = the number of records that have the selected type.
Sub report is filtered by package, so each page of the report contains a table of the information found in Table2

I hope this was good enough... otherwise I will have to do it at home as I cannot upload anything at work.
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
ok... here is what I am trying to achieve...
View attachment Database11.accdb
if you select "BMW" you should see 3 pages of the report.
Page 1, contains a list of owners of M3.
Page 2, contains a list of owners of M5.
Page 3, contains a list of owners of Z3.

If you select "Mercedes" you should see 3 pages of the report.
Page 1, contains a list of owners of B-Class.
Page 2, contains a list of owners of C-Class.
Page 3, contains a list of owners of E-Class.

for an additional step, I only want to display the Owners that appear on the latest date.
For "Ford" - "Fiesta" I should see 1 line in the subreport.
For "BMW" - "Z3" I should see 3 lines in the sub report.

Give it a go and let me know how you get on =)

Thank you!
 

JHB

Have been here a while
Local time
Today, 10:41
Joined
Jun 17, 2012
Messages
7,732
Is this what you want, database attached?
 

Attachments

  • Database11.accdb
    516 KB · Views: 104

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
Is this what you want, database attached?

this is EXACTLY what I want, could you please explain what you have done/changed so I can see how you did this!
it appears that you have done:
1. Change the subform to link Manufacture and Model
2. Do some magic with the "query1" report that I dont understand:D

Could you explain?

Thanks very much
 

JHB

Have been here a while
Local time
Today, 10:41
Joined
Jun 17, 2012
Messages
7,732
..
1. Change the subform to link Manufacture and Model
Exactly
..
2. Do some magic with the "query1" report that I dont understand:D
Not with the report, but created 2 new queries:
No. 1 to get the max date, no. 2 to link the owner together with the max date. And ofcause change the recordsource of the sub report.

By the way, did you read my signature? :)
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
Exactly

Not with the report, but created 2 new queries:
No. 1 to get the max date, no. 2 to link the owner together with the max date. And ofcause change the recordsource of the sub report.

By the way, did you read my signature? :)

is it possible to condense these two queries into one?

Code:
select [Model],[Owner],[Date] from [Data]
where [Date] in(select [Model],max([Date] from [Data])
Something like this would work but if there was entries in multiple models on the same date that happens to be the max date for one model, it will display multiple models from that date (right?)
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
I have tried to copy this method to my database and... surprise surprise, something isnt working.

I have been trying to do it at home but the connection is bad, it takes 10 minutes to even load up the "Query Wizard" that ive just given up.

The error I get is a "parameters" box that wants me to enter a value for "Master.Model"...
I dont know why I am getting this error but can anyone offer suggestions as to what it might be?
 

JHB

Have been here a while
Local time
Today, 10:41
Joined
Jun 17, 2012
Messages
7,732
...
The error I get is a "parameters" box that wants me to enter a value for "Master.Model"...
I dont know why I am getting this error but can anyone offer suggestions as to what it might be?
Mostly this error occurs when a field's name isn't the table/query, (usually a typo).
So try to compare the tables, the one from the sample database and the other from your real database, also the field type.
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
Mostly this error occurs when a field's name isn't the table/query, (usually a typo).
So try to compare the tables, the one from the sample database and the other from your real database, also the field type.

The problem was that the tables were not properly linked in ALL fields on the form (and subform) so thank you for helping me clear that up!
 

JHB

Have been here a while
Local time
Today, 10:41
Joined
Jun 17, 2012
Messages
7,732
Good you got the problem solved! :)
 

penfold1992

Registered User.
Local time
Today, 09:41
Joined
Nov 22, 2012
Messages
169
Good you got the problem solved! :)

uh... new problem...
now some people are requesting that they dont always want the most up to date one and instead they want to enter in the date value to retrieve...

so how can I filter based on a passed in date? (can I use vba to modify a queries definition? or use QueryDef to create like... a temporary table that I can then use in the report?
 

JHB

Have been here a while
Local time
Today, 10:41
Joined
Jun 17, 2012
Messages
7,732
I've made a sample for you.
If you don't select a date then the "max date" report open, if you select a date then another report open.
 

Attachments

  • Database11New.accdb
    592 KB · Views: 79

Users who are viewing this thread

Top Bottom