Can't save calculated field

  • Thread starter Thread starter Rolf Rosenquist
  • Start date Start date
R

Rolf Rosenquist

I have a form with a subform, showing a datalist of items. The client will
provide the number of every sold item and the final price is calculated with
deduction of rebate, if any. This final price is also shown in the subform.

My problem is that I cannot save this final price into the order
specification table. (I know that I should normally not save a calculated
price, but think I have to, as the normal price of the item could later be
changed. If so, the statistics of sold items will not be right.)

Can anyone help?
/ Rolf
 
Hi Rolf

I suggest that you save, in your OrderItems table, not the *final* price,
but the *current* base price. You can then have additional "modifying"
fields, such as Quantity, TaxPercent and Discount, and a calculated final
price, but you will be protected from future price changes modifying your
data by storing the base price *at the time of the sale*. This is called
"point-in-time" data, and it is perfectly valid to store it in this way.

The way to populate this field is to load it at the time the product is
selected. One simple way to do this is to use a combo box to select the
product, and include an extra, hidden column in the combo rowsource which
contains the price. For example, your combo's rowsource could be:
Select ProductID, ProductName, ProductPrice from Products
order by ProductName;
Set the combo's other properties:
Columns: 3
BoundColumn: 1
ColumnWidths: 0;;0

Now, in your combo's AfterUpdate event procedure, fill in the price field:
Me.BasePrice = Me.cboProduct.Column(2)
 
Rolf said:
I have a form with a subform, showing a datalist of items. The client will
provide the number of every sold item and the final price is calculated with
deduction of rebate, if any. This final price is also shown in the subform.

My problem is that I cannot save this final price into the order
specification table. (I know that I should normally not save a calculated
price, but think I have to, as the normal price of the item could later be
changed. If so, the statistics of sold items will not be right.)
Click to expand...


Don't use a control source expression to calculate the final
price. Instead use a little VBA in the Form's BeforeUpdate
event procedure. or in the AfterUpdate event of each of the
controls involved in the expression.
 
Hi Graham,

Tried very thorough after your description, but it seem that I have no link
to the Products table, as no products show up in the combo. I put the Select
statement in the source. I think it is called so in english version of
Access. It is the first row of the data part of the properties of the combo
box.

/ Rolf


Graham Mandeno said:
Hi Rolf

I suggest that you save, in your OrderItems table, not the *final* price,
but the *current* base price. You can then have additional "modifying"
fields, such as Quantity, TaxPercent and Discount, and a calculated final
price, but you will be protected from future price changes modifying your
data by storing the base price *at the time of the sale*. This is called
"point-in-time" data, and it is perfectly valid to store it in this way.

The way to populate this field is to load it at the time the product is
selected. One simple way to do this is to use a combo box to select the
product, and include an extra, hidden column in the combo rowsource which
contains the price. For example, your combo's rowsource could be:
Select ProductID, ProductName, ProductPrice from Products
order by ProductName;
Set the combo's other properties:
Columns: 3
BoundColumn: 1
ColumnWidths: 0;;0

Now, in your combo's AfterUpdate event procedure, fill in the price field:
Me.BasePrice = Me.cboProduct.Column(2)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Rolf Rosenquist said:
I have a form with a subform, showing a datalist of items. The client will
provide the number of every sold item and the final price is calculated
with
deduction of rebate, if any. This final price is also shown in the
subform.

My problem is that I cannot save this final price into the order
specification table. (I know that I should normally not save a calculated
price, but think I have to, as the normal price of the item could later
be
changed. If so, the statistics of sold items will not be right.)

Can anyone help?
/ Rolf
Click to expand...
Click to expand...
 
Rolf said:
I have a form with a subform, showing a datalist of items. The client will
provide the number of every sold item and the final price is calculated with
deduction of rebate, if any. This final price is also shown in the subform.

My problem is that I cannot save this final price into the order
specification table. (I know that I should normally not save a calculated
price, but think I have to, as the normal price of the item could later be
changed. If so, the statistics of sold items will not be right.)
Click to expand...


Listen to Graham. I just answered the question as asked, he
gave you a better way.

Hey, Graham, tag, you're it ;-)
 
Hi Rolf

It sounds like you have put the Select statement in the ControlSource of the
combobox, not the RowSource. (I don't know what language version you are
using, and even if I did, I probably wouldn't know what the property names
were, sorry!)

The ControlSource for any editable control specifes the name of the
RecordSource field to which that control is bound (or could contain an
expression starting with "=" for a calculated control).

The ControlSource for *your* combobox should be the name of the field in
your OrderItems table wuich contains the ProductID of the item which is
being sold.

The RowSource is (I think) the fourth property in the "Data" sublist, and
only applies to comboboxes and listboxes. It contains either a SQL
statement or the name of a table or query, which lists the rows and columns
you want to appear in the combo/listbox's list.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Rolf Rosenquist said:
Hi Graham,

Tried very thorough after your description, but it seem that I have no
link
to the Products table, as no products show up in the combo. I put the
Select
statement in the source. I think it is called so in english version of
Access. It is the first row of the data part of the properties of the
combo
box.

/ Rolf


Graham Mandeno said:
Hi Rolf

I suggest that you save, in your OrderItems table, not the *final* price,
but the *current* base price. You can then have additional "modifying"
fields, such as Quantity, TaxPercent and Discount, and a calculated final
price, but you will be protected from future price changes modifying your
data by storing the base price *at the time of the sale*. This is called
"point-in-time" data, and it is perfectly valid to store it in this way.

The way to populate this field is to load it at the time the product is
selected. One simple way to do this is to use a combo box to select the
product, and include an extra, hidden column in the combo rowsource which
contains the price. For example, your combo's rowsource could be:
Select ProductID, ProductName, ProductPrice from Products
order by ProductName;
Set the combo's other properties:
Columns: 3
BoundColumn: 1
ColumnWidths: 0;;0

Now, in your combo's AfterUpdate event procedure, fill in the price
field:
Me.BasePrice = Me.cboProduct.Column(2)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Rolf Rosenquist said:
I have a form with a subform, showing a datalist of items. The client will
provide the number of every sold item and the final price is calculated
with
deduction of rebate, if any. This final price is also shown in the
subform.

My problem is that I cannot save this final price into the order
specification table. (I know that I should normally not save a calculated
price, but think I have to, as the normal price of the item could
later
be
changed. If so, the statistics of sold items will not be right.)

Can anyone help?
/ Rolf
Click to expand...
Click to expand...
Click to expand...
 
Hi again Graham,

WOW !! A very big Thank You, for helping me!

As you pointed out, I did mess up between ControlSource and RowSource. By
the way, these two have names that are direct translated in the Swedish
version. I did change the AfterUpdate code to react on the field with a
bar-code that shall be put in from a reader and got it work like I hoped
from the beginning. :-))

/ Rolf


Graham Mandeno said:
Hi Rolf

It sounds like you have put the Select statement in the ControlSource of the
combobox, not the RowSource. (I don't know what language version you are
using, and even if I did, I probably wouldn't know what the property names
were, sorry!)

The ControlSource for any editable control specifes the name of the
RecordSource field to which that control is bound (or could contain an
expression starting with "=" for a calculated control).

The ControlSource for *your* combobox should be the name of the field in
your OrderItems table wuich contains the ProductID of the item which is
being sold.

The RowSource is (I think) the fourth property in the "Data" sublist, and
only applies to comboboxes and listboxes. It contains either a SQL
statement or the name of a table or query, which lists the rows and columns
you want to appear in the combo/listbox's list.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Rolf Rosenquist said:
Hi Graham,

Tried very thorough after your description, but it seem that I have no
link
to the Products table, as no products show up in the combo. I put the
Select
statement in the source. I think it is called so in english version of
Access. It is the first row of the data part of the properties of the
combo
box.

/ Rolf


Graham Mandeno said:
Hi Rolf

I suggest that you save, in your OrderItems table, not the *final* price,
but the *current* base price. You can then have additional "modifying"
fields, such as Quantity, TaxPercent and Discount, and a calculated final
price, but you will be protected from future price changes modifying your
data by storing the base price *at the time of the sale*. This is called
"point-in-time" data, and it is perfectly valid to store it in this way.

The way to populate this field is to load it at the time the product is
selected. One simple way to do this is to use a combo box to select the
product, and include an extra, hidden column in the combo rowsource which
contains the price. For example, your combo's rowsource could be:
Select ProductID, ProductName, ProductPrice from Products
order by ProductName;
Set the combo's other properties:
Columns: 3
BoundColumn: 1
ColumnWidths: 0;;0

Now, in your combo's AfterUpdate event procedure, fill in the price
field:
Me.BasePrice = Me.cboProduct.Column(2)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with a subform, showing a datalist of items. The client will
provide the number of every sold item and the final price is calculated
with
deduction of rebate, if any. This final price is also shown in the
subform.

My problem is that I cannot save this final price into the order
specification table. (I know that I should normally not save a calculated
price, but think I have to, as the normal price of the item could
later
be
changed. If so, the statistics of sold items will not be right.)

Can anyone help?
/ Rolf
Click to expand...
Click to expand...
Click to expand...
 
Back
Top