Form footer equation

[Guest]

I have an OrderDetails subform and I am having trouble getting the correct
expressions/calculations to perform correctly.

The subform is called Order_Details_Subform and it has a cboProduct where I
choose the product. The Row Rource of the cbo has an 8 column query to put
relevant information into other controls (ie UnitType, and UnitPrice from
tblProduct).
The 8th column is an expression called ExtPrice:
CCur([Quantity]*[UnitPrice]*(1-[Discount]))

What I want to happen is when I select the product from cboProduct I want
the ExtendedPrice control to be filled with the result of the expression and
if I change any details for the result to change accordingly. (I have
Me.recalc in relevant other controls).
However, the only way I can get the correct answer in the ExtendedPrice
control is to put the equation it's control source? But this means that the
result does not appear in my tblOrderDetails.

Also I have a OrderTotal from in my main form that I want to display the
total of all the products selected. Would I put an equation n the footer of
my subform and relate the mainform control to display this total (what would
the equation be?)

 

[Rick Brandt]

I have an OrderDetails subform and I am having trouble getting the
correct expressions/calculations to perform correctly.

The subform is called Order_Details_Subform and it has a cboProduct
where I choose the product. The Row Rource of the cbo has an 8 column
query to put relevant information into other controls (ie UnitType,
and UnitPrice from tblProduct).
The 8th column is an expression called ExtPrice:
CCur([Quantity]*[UnitPrice]*(1-[Discount]))

What I want to happen is when I select the product from cboProduct I
want the ExtendedPrice control to be filled with the result of the
expression and if I change any details for the result to change
accordingly. (I have Me.recalc in relevant other controls).
However, the only way I can get the correct answer in the
ExtendedPrice control is to put the equation it's control source? But
this means that the result does not appear in my tblOrderDetails.

That's correct. A control either saves its data to the table OR contains an
expression. It cannot do both. The good news is that you don't need to save
that calculation result to the table nor should you. Saving derived data
violates a basic principle in database design theory. You have all of the
operands for that calculation being saved so just perform the calculation
whenever you need that value.

Also I have a OrderTotal from in my main form that I want to display
the total of all the products selected. Would I put an equation n the
footer of my subform and relate the mainform control to display this
total (what would the equation be?)

=Sum(CCur([Quantity]*[UnitPrice]*(1-[Discount])))

 

[Guest]

Thanks.
OK, have deleted the ExtendedPrice from my tblOrderDetails so there is no
violation of database design theory. But, do I have to relate the control
source to the expression in the cboProduct query (column7)

Or am I just putting the equation as the control source (therefore there
would be no need to have the equation in the cboProduct Row Souce, would
there?) (this way is displaying #Error on the new record of the
OrderDetailsSubform (Continous Form) and only changes when something is
selected form the cboProduct - can I just get this to be blank until
something is selected?)

How then now can I get the
--
Thanks
WoodyAccess

I have an OrderDetails subform and I am having trouble getting the
correct expressions/calculations to perform correctly.

The subform is called Order_Details_Subform and it has a cboProduct
where I choose the product. The Row Rource of the cbo has an 8 column
query to put relevant information into other controls (ie UnitType,
and UnitPrice from tblProduct).
The 8th column is an expression called ExtPrice:
CCur([Quantity]*[UnitPrice]*(1-[Discount]))

What I want to happen is when I select the product from cboProduct I
want the ExtendedPrice control to be filled with the result of the
expression and if I change any details for the result to change
accordingly. (I have Me.recalc in relevant other controls).
However, the only way I can get the correct answer in the
ExtendedPrice control is to put the equation it's control source? But
this means that the result does not appear in my tblOrderDetails.

That's correct. A control either saves its data to the table OR contains an
expression. It cannot do both. The good news is that you don't need to save
that calculation result to the table nor should you. Saving derived data
violates a basic principle in database design theory. You have all of the
operands for that calculation being saved so just perform the calculation
whenever you need that value.

Also I have a OrderTotal from in my main form that I want to display
the total of all the products selected. Would I put an equation n the
footer of my subform and relate the mainform control to display this
total (what would the equation be?)

=Sum(CCur([Quantity]*[UnitPrice]*(1-[Discount])))

 

[Rick Brandt]

Thanks.
OK, have deleted the ExtendedPrice from my tblOrderDetails so there
is no violation of database design theory. But, do I have to relate
the control source to the expression in the cboProduct query (column7)

Not sure I understand that question.

Or am I just putting the equation as the control source (therefore
there would be no need to have the equation in the cboProduct Row
Souce, would there?) (this way is displaying #Error on the new record
of the OrderDetailsSubform (Continous Form) and only changes when
something is selected form the cboProduct - can I just get this to be
blank until something is selected?)

To eliminate that you need to modify your expression to deal with possible Null
values...

CCur(Nz([Quantity],0) * Nz([UnitPrice],0) * 1-Nz([Discount],0)))

 

[Guest]

Thanks for that.

Maybe you can help me with another quick question?

I have a Received checkbox and a DateReceived textbox that link directly to
the tblOrderDetails.

What I want to happen is that if the checkbox is unchecked then the
DateReceived box is not enabled and if the checkbox is selected then the
DateReceived box is enabled to input information. What I have is an After
Update as follows

Private Sub Received_AfterUpdate()
If Me.Received = True Then Me.ctrl_DateReceived.Enabled = True Else
Me.ctrl_DateReceived.Enabled = False
End Sub

But when on a Continous form if any Received checkbox is checked all of the
DateReceived boxes become enabled - I only want it for that specific record.
How can i change the code?
--
Thanks
WoodyAccess

Thanks.
OK, have deleted the ExtendedPrice from my tblOrderDetails so there
is no violation of database design theory. But, do I have to relate
the control source to the expression in the cboProduct query (column7)

Not sure I understand that question.

Or am I just putting the equation as the control source (therefore
there would be no need to have the equation in the cboProduct Row
Souce, would there?) (this way is displaying #Error on the new record
of the OrderDetailsSubform (Continous Form) and only changes when
something is selected form the cboProduct - can I just get this to be
blank until something is selected?)

To eliminate that you need to modify your expression to deal with possible Null
values...

CCur(Nz([Quantity],0) * Nz([UnitPrice],0) * 1-Nz([Discount],0)))

 

[Rick Brandt]

Thanks for that.

Maybe you can help me with another quick question?

I have a Received checkbox and a DateReceived textbox that link
directly to the tblOrderDetails.

What I want to happen is that if the checkbox is unchecked then the
DateReceived box is not enabled and if the checkbox is selected then
the DateReceived box is enabled to input information. What I have is
an After Update as follows

Private Sub Received_AfterUpdate()
If Me.Received = True Then Me.ctrl_DateReceived.Enabled = True Else
Me.ctrl_DateReceived.Enabled = False
End Sub

But when on a Continous form if any Received checkbox is checked all
of the DateReceived boxes become enabled - I only want it for that
specific record. How can i change the code?

You would have to look at conditional formatting (something I'm not that
familiar with). I do know that you are limited to what you can do with it
and enabling/disabling might not be possible.