How to use a report field to populate a table field

[Nicholas Scarpinato]

I have a report I'm running that has a set of formulas in it for various data
calculations that I don't need to store anywhere. However, I *DO* need to
store the end result of those formulas in a table field. I wrote a bit of
code to do this when the report is closed, but it only stores the value shown
on the current page of the report, not the value for every page. (There are
34 records to update and each record has it's own page on the report.) The
code I'm using is as follows:

Private Sub Report_Close()
Dim db, rs, sql, CMCarryforward
sql = "SELECT * FROM [Carryforward]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
CMCarryforward = [Profit/Loss]
.Edit
rs.Fields("Current Month Carryforward") = Me![Profit/Loss]
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
End Sub


How do I get my report to populate the table fields on a record-by-record
basis, rather than using the number on the page of the report I happen to be
viewing when I close the report?

 

[Carl Rapson]

I have a report I'm running that has a set of formulas in it for various
data
calculations that I don't need to store anywhere. However, I *DO* need to
store the end result of those formulas in a table field. I wrote a bit of
code to do this when the report is closed, but it only stores the value
shown
on the current page of the report, not the value for every page. (There
are
34 records to update and each record has it's own page on the report.) The
code I'm using is as follows:

Private Sub Report_Close()
Dim db, rs, sql, CMCarryforward
sql = "SELECT * FROM [Carryforward]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
CMCarryforward = [Profit/Loss]
.Edit
rs.Fields("Current Month Carryforward") = Me![Profit/Loss]
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
End Sub


How do I get my report to populate the table fields on a record-by-record
basis, rather than using the number on the page of the report I happen to
be
viewing when I close the report?

It sounds to me like you're using a report to do what an Update query should
be doing. Since reports aren't interactive, every calculation you're wanting
to store is being (or can be) calculated in a query. I'd recommend copying
the Record Source of the report (I assume it's a query) and modifying it to
do the updates you're wanting.

Carl Rapson

 

[Nicholas Scarpinato]

I realize that an Update query can do what I'm trying to do; however, my
query I'm using to get the data for the report is not calculating the field I
need to export out to the table. Here's the basic flow of the process:

1. Enter the data into the appropriate tables.
2. Run the report, which runs a query to gather all the data from the tables
together, by store.
3. The report displays the appropriate data, and then figures a sum for each
set of data displayed (purchases, expenses, etc.).

The problem I'm having is, the Profit/Loss figure is a calculated field
based on the values of other calculated fields on the report, not on the
query the report is based on. (Now that I think about it, the number I need
isn't actually the Profit/Loss number, but the variance of last month's P&L
to this month's. This is the number I need to store in the table.) The main
thing I'm trying to avoid is having to redo my query to figure out all the
sums, but it looks like that's what I'll end up having to do. (This is what
happens when your supervisor tells you how something should be done three
days after you started the project, lol.)

I have a report I'm running that has a set of formulas in it for various
data
calculations that I don't need to store anywhere. However, I *DO* need to
store the end result of those formulas in a table field. I wrote a bit of
code to do this when the report is closed, but it only stores the value
shown
on the current page of the report, not the value for every page. (There
are
34 records to update and each record has it's own page on the report.) The
code I'm using is as follows:

Private Sub Report_Close()
Dim db, rs, sql, CMCarryforward
sql = "SELECT * FROM [Carryforward]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
CMCarryforward = [Profit/Loss]
.Edit
rs.Fields("Current Month Carryforward") = Me![Profit/Loss]
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
End Sub


How do I get my report to populate the table fields on a record-by-record
basis, rather than using the number on the page of the report I happen to
be
viewing when I close the report?

It sounds to me like you're using a report to do what an Update query should
be doing. Since reports aren't interactive, every calculation you're wanting
to store is being (or can be) calculated in a query. I'd recommend copying
the Record Source of the report (I assume it's a query) and modifying it to
do the updates you're wanting.

Carl Rapson

 

[Pat Hartman \(MVP\)]

Storing calculated results is poor practice. Even worse is having a report
that updates saved results. You can do it but as soon as the update is
done, there is the potential for the calculated values to be out of date.
It is far better to calculate the numbers in queries as they are needed or
in reports. What is the purpose for storing the numbers?

To do what you are asking - even though I don't recommend it, you need to
create an update query that takes values from calculated report controls
and updates table fields. Run the update query from the report section that
contains the values you need to store. So the update query will end up
running 34 times. Once for each break.

I realize that an Update query can do what I'm trying to do; however, my
query I'm using to get the data for the report is not calculating the
field I
need to export out to the table. Here's the basic flow of the process:

1. Enter the data into the appropriate tables.
2. Run the report, which runs a query to gather all the data from the
tables
together, by store.
3. The report displays the appropriate data, and then figures a sum for
each
set of data displayed (purchases, expenses, etc.).

The problem I'm having is, the Profit/Loss figure is a calculated field
based on the values of other calculated fields on the report, not on the
query the report is based on. (Now that I think about it, the number I
need
isn't actually the Profit/Loss number, but the variance of last month's
P&L
to this month's. This is the number I need to store in the table.) The
main
thing I'm trying to avoid is having to redo my query to figure out all the
sums, but it looks like that's what I'll end up having to do. (This is
what
happens when your supervisor tells you how something should be done three
days after you started the project, lol.)

"Nicholas Scarpinato" <[email protected]>
wrote
in message news:[email protected]...

I have a report I'm running that has a set of formulas in it for various
data
calculations that I don't need to store anywhere. However, I *DO* need
to
store the end result of those formulas in a table field. I wrote a bit
of
code to do this when the report is closed, but it only stores the value
shown
on the current page of the report, not the value for every page. (There
are
34 records to update and each record has it's own page on the report.)
The
code I'm using is as follows:

Private Sub Report_Close()
Dim db, rs, sql, CMCarryforward
sql = "SELECT * FROM [Carryforward]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
CMCarryforward = [Profit/Loss]
.Edit
rs.Fields("Current Month Carryforward") = Me![Profit/Loss]
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
End Sub


How do I get my report to populate the table fields on a
record-by-record
basis, rather than using the number on the page of the report I happen
to
be
viewing when I close the report?

It sounds to me like you're using a report to do what an Update query
should
be doing. Since reports aren't interactive, every calculation you're
wanting
to store is being (or can be) calculated in a query. I'd recommend
copying
the Record Source of the report (I assume it's a query) and modifying it
to
do the updates you're wanting.

Carl Rapson

 

[Nicholas Scarpinato]

I realize that I have somewhat worked myself into a bind with this method.
However, there is a method to the madness, as they say. The reason I need to
store a calculated field is simple. I'm comparing the profit and loss balance
from last month to the current profit and loss numbers for the current month.
When this balance is less than zero (i.e., the store is losing money), the
manager doesn't get his bonus, and he loses his discount for the month. If
it's greater than zero, he gets a 10% bonus based on the total amount of
profit. Since this is a monthly report, that number is not going to change
once the reporting is finalized for that month. For the next month, that
store's starting P&L balance is the balance from the end of the previous
month. So what I want to do is, calculate the rollover balance, store that to
a table, then move that number from current month to previous month for the
starting balance on the next month's sheet. I know when I export my report to
a spreadsheet, it has all the correct numbers for the calculated field. I set
this up with the calculations in the report because I don't need to store
most of the numbers I calculate in the report to get to this final number. I
just need the final number stored for the next month's reporting run.

This all started because I didn't know I needed to store this number until
three days into creating this report. Had I known that I would have made all
my calculations in quieries from the start.

Storing calculated results is poor practice. Even worse is having a report
that updates saved results. You can do it but as soon as the update is
done, there is the potential for the calculated values to be out of date.
It is far better to calculate the numbers in queries as they are needed or
in reports. What is the purpose for storing the numbers?

To do what you are asking - even though I don't recommend it, you need to
create an update query that takes values from calculated report controls
and updates table fields. Run the update query from the report section that
contains the values you need to store. So the update query will end up
running 34 times. Once for each break.

I realize that an Update query can do what I'm trying to do; however, my
query I'm using to get the data for the report is not calculating the
field I
need to export out to the table. Here's the basic flow of the process:

1. Enter the data into the appropriate tables.
2. Run the report, which runs a query to gather all the data from the
tables
together, by store.
3. The report displays the appropriate data, and then figures a sum for
each
set of data displayed (purchases, expenses, etc.).

The problem I'm having is, the Profit/Loss figure is a calculated field
based on the values of other calculated fields on the report, not on the
query the report is based on. (Now that I think about it, the number I
need
isn't actually the Profit/Loss number, but the variance of last month's
P&L
to this month's. This is the number I need to store in the table.) The
main
thing I'm trying to avoid is having to redo my query to figure out all the
sums, but it looks like that's what I'll end up having to do. (This is
what
happens when your supervisor tells you how something should be done three
days after you started the project, lol.)

"Nicholas Scarpinato" <[email protected]>
wrote
in message I have a report I'm running that has a set of formulas in it for various
data
calculations that I don't need to store anywhere. However, I *DO* need
to
store the end result of those formulas in a table field. I wrote a bit
of
code to do this when the report is closed, but it only stores the value
shown
on the current page of the report, not the value for every page. (There
are
34 records to update and each record has it's own page on the report.)
The
code I'm using is as follows:

Private Sub Report_Close()
Dim db, rs, sql, CMCarryforward
sql = "SELECT * FROM [Carryforward]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
CMCarryforward = [Profit/Loss]
.Edit
rs.Fields("Current Month Carryforward") = Me![Profit/Loss]
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
End Sub


How do I get my report to populate the table fields on a
record-by-record
basis, rather than using the number on the page of the report I happen
to
be
viewing when I close the report?

It sounds to me like you're using a report to do what an Update query
should
be doing. Since reports aren't interactive, every calculation you're
wanting
to store is being (or can be) calculated in a query. I'd recommend
copying
the Record Source of the report (I assume it's a query) and modifying it
to
do the updates you're wanting.

Carl Rapson

 

[Pat Hartman \(MVP\)]

Let me try one more time to get you on the path of righteousness . Create a
query that sums prior month values (inception to prior month). That gives
you your starting value. You can use dLookup to get it or union this query
to the query you are currently using. I prefer the union method. Just make
sure that both queries have the same number of columns in the same order.
That means that you will need to add a dummy date, for the union query so
that it sorts first in your report so it can be used as the starting
balance.

I realize that I have somewhat worked myself into a bind with this method.
However, there is a method to the madness, as they say. The reason I need
to
store a calculated field is simple. I'm comparing the profit and loss
balance
from last month to the current profit and loss numbers for the current
month.
When this balance is less than zero (i.e., the store is losing money), the
manager doesn't get his bonus, and he loses his discount for the month. If
it's greater than zero, he gets a 10% bonus based on the total amount of
profit. Since this is a monthly report, that number is not going to change
once the reporting is finalized for that month. For the next month, that
store's starting P&L balance is the balance from the end of the previous
month. So what I want to do is, calculate the rollover balance, store that
to
a table, then move that number from current month to previous month for
the
starting balance on the next month's sheet. I know when I export my report
to
a spreadsheet, it has all the correct numbers for the calculated field. I
set
this up with the calculations in the report because I don't need to store
most of the numbers I calculate in the report to get to this final number.
I
just need the final number stored for the next month's reporting run.

This all started because I didn't know I needed to store this number until
three days into creating this report. Had I known that I would have made
all
my calculations in quieries from the start.

Storing calculated results is poor practice. Even worse is having a
report
that updates saved results. You can do it but as soon as the update is
done, there is the potential for the calculated values to be out of date.
It is far better to calculate the numbers in queries as they are needed
or
in reports. What is the purpose for storing the numbers?

To do what you are asking - even though I don't recommend it, you need to
create an update query that takes values from calculated report controls
and updates table fields. Run the update query from the report section
that
contains the values you need to store. So the update query will end up
running 34 times. Once for each break.

"Nicholas Scarpinato" <[email protected]>
wrote
in message news[email protected]...

I realize that an Update query can do what I'm trying to do; however, my
query I'm using to get the data for the report is not calculating the
field I
need to export out to the table. Here's the basic flow of the process:

1. Enter the data into the appropriate tables.
2. Run the report, which runs a query to gather all the data from the
tables
together, by store.
3. The report displays the appropriate data, and then figures a sum for
each
set of data displayed (purchases, expenses, etc.).

The problem I'm having is, the Profit/Loss figure is a calculated field
based on the values of other calculated fields on the report, not on
the
query the report is based on. (Now that I think about it, the number I
need
isn't actually the Profit/Loss number, but the variance of last month's
P&L
to this month's. This is the number I need to store in the table.) The
main
thing I'm trying to avoid is having to redo my query to figure out all
the
sums, but it looks like that's what I'll end up having to do. (This is
what
happens when your supervisor tells you how something should be done
three
days after you started the project, lol.)


:

"Nicholas Scarpinato" <[email protected]>
wrote
in message I have a report I'm running that has a set of formulas in it for
various
data
calculations that I don't need to store anywhere. However, I *DO*
need
to
store the end result of those formulas in a table field. I wrote a
bit
of
code to do this when the report is closed, but it only stores the
value
shown
on the current page of the report, not the value for every page.
(There
are
34 records to update and each record has it's own page on the
report.)
The
code I'm using is as follows:

Private Sub Report_Close()
Dim db, rs, sql, CMCarryforward
sql = "SELECT * FROM [Carryforward]"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
.MoveFirst
Do Until .EOF
CMCarryforward = [Profit/Loss]
.Edit
rs.Fields("Current Month Carryforward") = Me![Profit/Loss]
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
End Sub


How do I get my report to populate the table fields on a
record-by-record
basis, rather than using the number on the page of the report I
happen
to
be
viewing when I close the report?

It sounds to me like you're using a report to do what an Update query
should
be doing. Since reports aren't interactive, every calculation you're
wanting
to store is being (or can be) calculated in a query. I'd recommend
copying
the Record Source of the report (I assume it's a query) and modifying
it
to
do the updates you're wanting.

Carl Rapson