M
maud
I am working in Access 2007. I have a standard form that allows my
users to generate a report based on various customizable parameters.
This is implemented by having a few generic reports and some basic vba
code that generates (based on the user's selections) a string that is
passed as the WhereCondition into the function DoCmd.OpenReport that
opens the generic report.
Now I need to have a header in the report that displays various
statistics on the data displayed in the report. The statistics I need
to display in the header are the number of distinct occurrences of
certain fields in the record set. For example, if my records where
auto parts and one of the attributes for the parts is manufacture,
then I need to display in the header the number of distinct
manufactures of all of the parts in the report.
As has been discussed on here before counting distinct records in a
report is not easy in access (of course, "count distinct" doesn't do
what you, or at least I, would like it to!), and impossible (?) if you
need the stats to be in the header (I've read a few work-arounds that
can get them in the footer). From my online research, it seems there
is no good way to simply generate this state within the user friendly
access controls for the report. A standard solution to this problem
has been to make a text box to display the state, and set the source
for this text box to be a query that calculates the value. This is a
bit tricky for me as the recordsource for the report depends on the
user selections on the form that generates the report (as described
above).
My approach to this was to create a table that would store the stats
for the header. I would then, in the vba code that generates the
wherecondition that is passed to DoCmd.OpenReport when opening the
report, have a few lines of code that would update the table with the
correct states based on the users parameters for the report. To do
this I made a query identical to the recordsource of the "generic
report" and then created a string that updated the table holding the
statistics for the report based on this querry and the wherecondition
that I was going to pass to DoCmd.OpenReport. For example:
Dim strQry As String
strQry = "Update tblReportStats Set ABCstats=(Select count(*) from
qryReportStats where " & strWhereCondition & ")"
Set db = CurrentDb()
db.Execute strQry
However when this code tries to run it gives me an error: "Operation
must use an updatable query."
So I have two questions:
(1) Is there a better way to do what I'm trying to do? (I'm sure there
is)
(2) Aside from the better ways for a second, what is wrong with the
snippet of code I've posted?
Thanks!
users to generate a report based on various customizable parameters.
This is implemented by having a few generic reports and some basic vba
code that generates (based on the user's selections) a string that is
passed as the WhereCondition into the function DoCmd.OpenReport that
opens the generic report.
Now I need to have a header in the report that displays various
statistics on the data displayed in the report. The statistics I need
to display in the header are the number of distinct occurrences of
certain fields in the record set. For example, if my records where
auto parts and one of the attributes for the parts is manufacture,
then I need to display in the header the number of distinct
manufactures of all of the parts in the report.
As has been discussed on here before counting distinct records in a
report is not easy in access (of course, "count distinct" doesn't do
what you, or at least I, would like it to!), and impossible (?) if you
need the stats to be in the header (I've read a few work-arounds that
can get them in the footer). From my online research, it seems there
is no good way to simply generate this state within the user friendly
access controls for the report. A standard solution to this problem
has been to make a text box to display the state, and set the source
for this text box to be a query that calculates the value. This is a
bit tricky for me as the recordsource for the report depends on the
user selections on the form that generates the report (as described
above).
My approach to this was to create a table that would store the stats
for the header. I would then, in the vba code that generates the
wherecondition that is passed to DoCmd.OpenReport when opening the
report, have a few lines of code that would update the table with the
correct states based on the users parameters for the report. To do
this I made a query identical to the recordsource of the "generic
report" and then created a string that updated the table holding the
statistics for the report based on this querry and the wherecondition
that I was going to pass to DoCmd.OpenReport. For example:
Dim strQry As String
strQry = "Update tblReportStats Set ABCstats=(Select count(*) from
qryReportStats where " & strWhereCondition & ")"
Set db = CurrentDb()
db.Execute strQry
However when this code tries to run it gives me an error: "Operation
must use an updatable query."
So I have two questions:
(1) Is there a better way to do what I'm trying to do? (I'm sure there
is)
(2) Aside from the better ways for a second, what is wrong with the
snippet of code I've posted?
Thanks!