Filter a sub report on open

[penfold1992]

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]

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]

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]

You're going to have to post the table schema so we know how this is all set up, I'm getting confused...

 

[penfold1992]

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]

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]

Is this what you want, database attached?

 

[penfold1992]

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

Could you explain?

Thanks very much

 

[JHB]

..
1. Change the subform to link Manufacture and Model

Exactly

..
2. Do some magic with the "query1" report that I dont understand

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]

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?

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]

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]

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

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]

Good you got the problem solved!

 

[penfold1992]

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]

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.