Calculated Field Working Properly on Form but is not being updated in table-HELP

[Jessika]

I have created a fruit table on which I have the unitprice of the fruit
I have another table name as Fruit sales where I have the sales code , the
fruitcode from the fruit table and another field as Price
From that same FruitSales Table I have created a form.In the Price Control
Source I have inserted the following formula-=(DLookUp("[uprice]","[fruits]",
"[fcode] = fruits![fcode]")*1.15)*[Qty]
( I want to look for the unitprice from the fruit table and multiply it by 1.
15 and then multiply by the Quantiy)
It is working properly but the answer in the Price field does not appear in
the Table (Fruit Sales -Price Field)

Can you please help
ThankYou

 

[Ken Snell \(MVP\)]

You cannot save a calculated control's results directly into a table's field
because the calculated control's ControlSource has an expression in it, and
it cannot also have a field name there to bind the control to a field.

You will need to use programming to write the value from that control into
the appropriate field when you're ready to save the record. The
Form_BeforeUpdate event procedure is likely the best place to do this.

But first you need to rename the control from Price to txtPrice, else ACCESS
is going to get confused by the double-naming of field and control with same
name but where the control is not bound to the field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Price.Value = Me!txtPrice.Value
End Sub

 

[Jessika]

how can i do it if i proceed by a query.. would it be more easy? is there a
way of updating the table without programming codes? please help

You cannot save a calculated control's results directly into a table's field
because the calculated control's ControlSource has an expression in it, and
it cannot also have a field name there to bind the control to a field.

You will need to use programming to write the value from that control into
the appropriate field when you're ready to save the record. The
Form_BeforeUpdate event procedure is likely the best place to do this.

But first you need to rename the control from Price to txtPrice, else ACCESS
is going to get confused by the double-naming of field and control with same
name but where the control is not bound to the field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Price.Value = Me!txtPrice.Value
End Sub

I have created a fruit table on which I have the unitprice of the fruit
I have another table name as Fruit sales where I have the sales code , the

[quoted text clipped - 12 lines]

Can you please help
ThankYou

 

[Jessika via AccessMonster.com]

Ive done exactly what ypu advised- that is Ive changed the Name of the
textbox Price on the form to TxtPrice.However I left the name as Price in my
table. And I inserted the code as you said. BUT ITS NOT WORKING!!! Can
anyboby help! PLEASE

You cannot save a calculated control's results directly into a table's field
because the calculated control's ControlSource has an expression in it, and
it cannot also have a field name there to bind the control to a field.

You will need to use programming to write the value from that control into
the appropriate field when you're ready to save the record. The
Form_BeforeUpdate event procedure is likely the best place to do this.

But first you need to rename the control from Price to txtPrice, else ACCESS
is going to get confused by the double-naming of field and control with same
name but where the control is not bound to the field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Price.Value = Me!txtPrice.Value
End Sub

I have created a fruit table on which I have the unitprice of the fruit
I have another table name as Fruit sales where I have the sales code , the

[quoted text clipped - 12 lines]

Can you please help
ThankYou

 

[Douglas J. Steele]

Are you sure that the BeforeUpdate event is firing?

Look at the Properties sheet for the form. Does it say [Event Procedure] as
the property for the BeforeUpdate event? If it does and you click on the
ellipsis (...) to the right of the property, does it take you into the
correct sub?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ive done exactly what ypu advised- that is Ive changed the Name of the
textbox Price on the form to TxtPrice.However I left the name as Price in
my
table. And I inserted the code as you said. BUT ITS NOT WORKING!!! Can
anyboby help! PLEASE

You cannot save a calculated control's results directly into a table's
field
because the calculated control's ControlSource has an expression in it,
and
it cannot also have a field name there to bind the control to a field.

You will need to use programming to write the value from that control into
the appropriate field when you're ready to save the record. The
Form_BeforeUpdate event procedure is likely the best place to do this.

But first you need to rename the control from Price to txtPrice, else
ACCESS
is going to get confused by the double-naming of field and control with
same
name but where the control is not bound to the field.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Price.Value = Me!txtPrice.Value
End Sub

I have created a fruit table on which I have the unitprice of the fruit
I have another table name as Fruit sales where I have the sales code ,
the

[quoted text clipped - 12 lines]

Can you please help
ThankYou

 

[Jessika via AccessMonster.com]

Yes did it- Have right click on the form properties, click on the(...) of
BeforeUpdate event and has typed in exactly the same thing -It's not working.
Tell me -Did i do the correct thing. I mean I've changed the Price in My Form
FruitSales to TxtPrice in the Name and Caption. However on my table it is
still Price? Is it correct?

Are you sure that the BeforeUpdate event is firing?

Look at the Properties sheet for the form. Does it say [Event Procedure] as
the property for the BeforeUpdate event? If it does and you click on the
ellipsis (...) to the right of the property, does it take you into the
correct sub?

Ive done exactly what ypu advised- that is Ive changed the Name of the
textbox Price on the form to TxtPrice.However I left the name as Price in

[quoted text clipped - 28 lines]

 

[Douglas J. Steele]

You've set it up correctly. Afraid I don't know what else to suggest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes did it- Have right click on the form properties, click on the(...) of
BeforeUpdate event and has typed in exactly the same thing -It's not
working.
Tell me -Did i do the correct thing. I mean I've changed the Price in My
Form
FruitSales to TxtPrice in the Name and Caption. However on my table it is
still Price? Is it correct?

Are you sure that the BeforeUpdate event is firing?

Look at the Properties sheet for the form. Does it say [Event Procedure]
as
the property for the BeforeUpdate event? If it does and you click on the
ellipsis (...) to the right of the property, does it take you into the
correct sub?

Ive done exactly what ypu advised- that is Ive changed the Name of the
textbox Price on the form to TxtPrice.However I left the name as Price
in

[quoted text clipped - 28 lines]

Can you please help
ThankYou

 

[Jessika via AccessMonster.com]

Thanks, it works. Actually I did a small mistake while typing!
Thanks Everybody.

You've set it up correctly. Afraid I don't know what else to suggest.

Yes did it- Have right click on the form properties, click on the(...) of
BeforeUpdate event and has typed in exactly the same thing -It's not

[quoted text clipped - 18 lines]