Dynamically filtering a report

[Guest]

Happy New Year 2005 to you all!
I am building an app to manage transport vehicles. I have among others
tables tblVehicles (LicensePlateNo, ChassisNo, AcquisitionDate, HorsePower),
tblRevenue (IDRevenue, DateRev, AmountRev, LicensePlate, DriverRev,
CommentsRev) and tblExpenses (IDExpenses, DateExp, AmountExp, LicensePlate,
CommentsExp). I have created a report, rptRevenue (LicensePlateNo, DateRev,
AmountRev, CommentsRev) that displays the details of income for each vehicle.
I also used the technique explained here:
http://fontstuff.com/access/acctut19.htm to dynamically filter my report to
display only records for a selected vehicle and dates between selected start
and end dates. It works great and I like the feature.
Now I want to get it to work for a report rptTransactions that has 2
subreports, rsubRevenue and rsubExpenses. It display the detailed revenue and
expenses of each vehicle.
I changed this piece of code which applies the filter to the report and
switches it on
*************************************
With Reports![rptRevenue]
.filter = strFilter
.filterOn = True
End with
**********************************
Into this one
*****************************************
With Reports![rptTransaction]![rsubRevenue]
.filter = strFilter
.filterOn = True
End with
******************************************
When testing, I get a message stating that object does not support or
method. When I hit Debug the 2nd line of the code above gets highlighted.
I don't where to go from here. Can someone help? I would really appreciate.
My system runs Access 2000 and Win XP Home.

 

[Marshall Barton]

Happy New Year 2005 to you all!
I am building an app to manage transport vehicles. I have among others
tables tblVehicles (LicensePlateNo, ChassisNo, AcquisitionDate, HorsePower),
tblRevenue (IDRevenue, DateRev, AmountRev, LicensePlate, DriverRev,
CommentsRev) and tblExpenses (IDExpenses, DateExp, AmountExp, LicensePlate,
CommentsExp). I have created a report, rptRevenue (LicensePlateNo, DateRev,
AmountRev, CommentsRev) that displays the details of income for each vehicle.
I also used the technique explained here:
http://fontstuff.com/access/acctut19.htm to dynamically filter my report to
display only records for a selected vehicle and dates between selected start
and end dates. It works great and I like the feature.
Now I want to get it to work for a report rptTransactions that has 2
subreports, rsubRevenue and rsubExpenses. It display the detailed revenue and
expenses of each vehicle.
I changed this piece of code which applies the filter to the report and
switches it on
*************************************
With Reports![rptRevenue]
.filter = strFilter
.filterOn = True
End with
**********************************
Into this one
*****************************************
With Reports![rptTransaction]![rsubRevenue]
.filter = strFilter
.filterOn = True
End with
******************************************
When testing, I get a message stating that object does not support or
method. When I hit Debug the 2nd line of the code above gets highlighted.
I don't where to go from here. Can someone help? I would really appreciate.
My system runs Access 2000 and Win XP Home.

You left out the Report property in that reference, it
should be:

With Reports![rptTransaction].REPORT![rsubRevenue]

but that looks like you have the code in the main report's
Open event. I don't think that will work, it should be in
the subreport's Open event, in which case, all you need is:

With Me

 

[Guest]

Happy New Year 2005 to you all!
I am building an app to manage transport vehicles. I have among others
tables tblVehicles (LicensePlateNo, ChassisNo, AcquisitionDate, HorsePower),
tblRevenue (IDRevenue, DateRev, AmountRev, LicensePlate, DriverRev,
CommentsRev) and tblExpenses (IDExpenses, DateExp, AmountExp, LicensePlate,
CommentsExp). I have created a report, rptRevenue (LicensePlateNo, DateRev,
AmountRev, CommentsRev) that displays the details of income for each vehicle.
I also used the technique explained here:
http://fontstuff.com/access/acctut19.htm to dynamically filter my report to
display only records for a selected vehicle and dates between selected start
and end dates. It works great and I like the feature.
Now I want to get it to work for a report rptTransactions that has 2
subreports, rsubRevenue and rsubExpenses. It display the detailed revenue and
expenses of each vehicle.
I changed this piece of code which applies the filter to the report and
switches it on
*************************************
With Reports![rptRevenue]
.filter = strFilter
.filterOn = True
End with
**********************************
Into this one
*****************************************
With Reports![rptTransaction]![rsubRevenue]
.filter = strFilter
.filterOn = True
End with
******************************************
When testing, I get a message stating that object does not support or
method. When I hit Debug the 2nd line of the code above gets highlighted.
I don't where to go from here. Can someone help? I would really appreciate.
My system runs Access 2000 and Win XP Home.

You left out the Report property in that reference, it
should be:

With Reports![rptTransaction].REPORT![rsubRevenue]

but that looks like you have the code in the main report's
Open event. I don't think that will work, it should be in
the subreport's Open event, in which case, all you need is:

With Me

Thanks Marshal for your quick response.
In fact the code runs from a form where I choose the vehicle and the dates;
it is associated to the Onclick event of a ApplyFilter command button in the
form. The program verifies that the report is opened prior to executing.
Otherwise it ends.
I followed your advice, I changed the first line to
*** With Reports![rptTransaction].Reports![rsubRevenue] ***
It resulted in a Run-time error '2465'-Application defined or object defined
error.
Many thanks for coming back with your help.

Jean-Marie

 

[Marshall Barton]

Happy New Year 2005 to you all!
I am building an app to manage transport vehicles. I have among others
tables tblVehicles (LicensePlateNo, ChassisNo, AcquisitionDate, HorsePower),
tblRevenue (IDRevenue, DateRev, AmountRev, LicensePlate, DriverRev,
CommentsRev) and tblExpenses (IDExpenses, DateExp, AmountExp, LicensePlate,
CommentsExp). I have created a report, rptRevenue (LicensePlateNo, DateRev,
AmountRev, CommentsRev) that displays the details of income for each vehicle.
I also used the technique explained here:
http://fontstuff.com/access/acctut19.htm to dynamically filter my report to
display only records for a selected vehicle and dates between selected start
and end dates. It works great and I like the feature.
Now I want to get it to work for a report rptTransactions that has 2
subreports, rsubRevenue and rsubExpenses. It display the detailed revenue and
expenses of each vehicle.
I changed this piece of code which applies the filter to the report and
switches it on
*************************************
With Reports![rptRevenue]
.filter = strFilter
.filterOn = True
End with
**********************************
Into this one
*****************************************
With Reports![rptTransaction]![rsubRevenue]
.filter = strFilter
.filterOn = True
End with
******************************************
When testing, I get a message stating that object does not support or
method. When I hit Debug the 2nd line of the code above gets highlighted.
I don't where to go from here. Can someone help? I would really appreciate.
My system runs Access 2000 and Win XP Home.

You left out the Report property in that reference, it
should be:

With Reports![rptTransaction].REPORT![rsubRevenue]

but that looks like you have the code in the main report's
Open event. I don't think that will work, it should be in
the subreport's Open event, in which case, all you need is:

With Me

Thanks Marshal for your quick response.
In fact the code runs from a form where I choose the vehicle and the dates;
it is associated to the Onclick event of a ApplyFilter command button in the
form. The program verifies that the report is opened prior to executing.
Otherwise it ends.
I followed your advice, I changed the first line to
*** With Reports![rptTransaction].Reports![rsubRevenue] ***
It resulted in a Run-time error '2465'-Application defined or object defined
error.

You're trying to filter a report from outside the report???

I was unaware that that could work reliably under any
circumstances. Even if you're happy doing this for a main
report, I can't imagine ever using this technique for
subreports.

There are two approaches that are commonly used for this
type of problem. One, the easiest, is to use the form
controls as parameter criteria in the subreport's record
source query. The other is to use the subreport's Open
event to construct the subreport's record source SQL
statement.

If you want to pursue one of those and need help with it,
post back with more details about the form controls and the
subreport's record source tab/query.

 

[Guest]

Jean-Marie wrote:
Happy New Year 2005 to you all!
I am building an app to manage transport vehicles. I have among others
tables tblVehicles (LicensePlateNo, ChassisNo, AcquisitionDate, HorsePower),
tblRevenue (IDRevenue, DateRev, AmountRev, LicensePlate, DriverRev,
CommentsRev) and tblExpenses (IDExpenses, DateExp, AmountExp, LicensePlate,
CommentsExp). I have created a report, rptRevenue (LicensePlateNo, DateRev,
AmountRev, CommentsRev) that displays the details of income for each vehicle.
I also used the technique explained here:
http://fontstuff.com/access/acctut19.htm to dynamically filter my report to
display only records for a selected vehicle and dates between selected start
and end dates. It works great and I like the feature.
Now I want to get it to work for a report rptTransactions that has 2
subreports, rsubRevenue and rsubExpenses. It display the detailed revenue and
expenses of each vehicle.
I changed this piece of code which applies the filter to the report and
switches it on
*************************************
With Reports![rptRevenue]
.filter = strFilter
.filterOn = True
End with
**********************************
Into this one
*****************************************
With Reports![rptTransaction]![rsubRevenue]
.filter = strFilter
.filterOn = True
End with
******************************************
When testing, I get a message stating that object does not support or
method. When I hit Debug the 2nd line of the code above gets highlighted.
I don't where to go from here. Can someone help? I would really appreciate.
My system runs Access 2000 and Win XP Home.

You left out the Report property in that reference, it
should be:

With Reports![rptTransaction].REPORT![rsubRevenue]

but that looks like you have the code in the main report's
Open event. I don't think that will work, it should be in
the subreport's Open event, in which case, all you need is:

With Me

Thanks Marshal for your quick response.
In fact the code runs from a form where I choose the vehicle and the dates;
it is associated to the Onclick event of a ApplyFilter command button in the
form. The program verifies that the report is opened prior to executing.
Otherwise it ends.
I followed your advice, I changed the first line to
*** With Reports![rptTransaction].Reports![rsubRevenue] ***
It resulted in a Run-time error '2465'-Application defined or object defined
error.

You're trying to filter a report from outside the report???

I was unaware that that could work reliably under any
circumstances. Even if you're happy doing this for a main
report, I can't imagine ever using this technique for
subreports.

There are two approaches that are commonly used for this
type of problem. One, the easiest, is to use the form
controls as parameter criteria in the subreport's record
source query. The other is to use the subreport's Open
event to construct the subreport's record source SQL
statement.

If you want to pursue one of those and need help with it,
post back with more details about the form controls and the
subreport's record source tab/query.

Thanks Marshall,

My apologies for not replying sooner. I will use the first approach and
come back if I need your help.

Thanks again

Jean-Marie

 

[Guest]

Hi!
I finally succeeded with a different approch. To avoid subreports I created
a union query that pulls from the tables tblRevenue and tblExpenses the
information LicennsePlate, Date, Amount and a string expression diplaying
"Revenue" or "Expense". Then I created a report based on the query that
dispalys: LicensePlate, Type of Transaction (Revenue or Expense), Date,
Amount. I can then use the filter and it works fine.
Thanks very much for your input.

Jean-Marie

Jean-Marie wrote:
Happy New Year 2005 to you all!
I am building an app to manage transport vehicles. I have among others
tables tblVehicles (LicensePlateNo, ChassisNo, AcquisitionDate, HorsePower),
tblRevenue (IDRevenue, DateRev, AmountRev, LicensePlate, DriverRev,
CommentsRev) and tblExpenses (IDExpenses, DateExp, AmountExp, LicensePlate,
CommentsExp). I have created a report, rptRevenue (LicensePlateNo, DateRev,
AmountRev, CommentsRev) that displays the details of income for each vehicle.
I also used the technique explained here:
http://fontstuff.com/access/acctut19.htm to dynamically filter my report to
display only records for a selected vehicle and dates between selected start
and end dates. It works great and I like the feature.
Now I want to get it to work for a report rptTransactions that has 2
subreports, rsubRevenue and rsubExpenses. It display the detailed revenue and
expenses of each vehicle.
I changed this piece of code which applies the filter to the report and
switches it on
*************************************
With Reports![rptRevenue]
.filter = strFilter
.filterOn = True
End with
**********************************
Into this one
*****************************************
With Reports![rptTransaction]![rsubRevenue]
.filter = strFilter
.filterOn = True
End with
******************************************
When testing, I get a message stating that object does not support or
method. When I hit Debug the 2nd line of the code above gets highlighted.
I don't where to go from here. Can someone help? I would really appreciate.
My system runs Access 2000 and Win XP Home.


:
You left out the Report property in that reference, it
should be:

With Reports![rptTransaction].REPORT![rsubRevenue]

but that looks like you have the code in the main report's
Open event. I don't think that will work, it should be in
the subreport's Open event, in which case, all you need is:

With Me

Thanks Marshal for your quick response.
In fact the code runs from a form where I choose the vehicle and the dates;
it is associated to the Onclick event of a ApplyFilter command button in the
form. The program verifies that the report is opened prior to executing.
Otherwise it ends.
I followed your advice, I changed the first line to
*** With Reports![rptTransaction].Reports![rsubRevenue] ***
It resulted in a Run-time error '2465'-Application defined or object defined
error.

You're trying to filter a report from outside the report???

I was unaware that that could work reliably under any
circumstances. Even if you're happy doing this for a main
report, I can't imagine ever using this technique for
subreports.

There are two approaches that are commonly used for this
type of problem. One, the easiest, is to use the form
controls as parameter criteria in the subreport's record
source query. The other is to use the subreport's Open
event to construct the subreport's record source SQL
statement.

If you want to pursue one of those and need help with it,
post back with more details about the form controls and the
subreport's record source tab/query.

Thanks Marshall,

My apologies for not replying sooner. I will use the first approach and
come back if I need your help.

Thanks again

Jean-Marie