Is there a way to bound a calculate field

[Guest]

I have a form made up by a table call retail sales. On the form and the table
there are one column for US$ sales, one column for Exchange Rate, and one
column for local sales$. The local sales$ in the form is a text field with
"=[US$ sales] * [Exchange Rate]". The result of this calculate field shown
itself successfully in the form's local sales$ box (form view); but it
doesn't show in the table's local sales$ column at all. What can I do to
bound it to the table's local sales$ column? Please advise!

Thank you

 

[Ken Snell [MVP]]

You cannot bind a control that has a calculation for the ControlSource.
Setting aside the question of whether you really need to store a calculated
value (a query can calculate that value for you whenever you need it), you'd
need to bind the textbox to the field and then calculate the value in VBA
code and write the value into that textbox.

 

[Guest]

Dear Ken Snell:
Can you please show me how to calculate the value in VBA
code and write the value into the textbox?

Thanks

You cannot bind a control that has a calculation for the ControlSource.
Setting aside the question of whether you really need to store a calculated
value (a query can calculate that value for you whenever you need it), you'd
need to bind the textbox to the field and then calculate the value in VBA
code and write the value into that textbox.

--

Ken Snell
<MS ACCESS MVP>

I have a form made up by a table call retail sales. On the form and the
table
there are one column for US$ sales, one column for Exchange Rate, and one
column for local sales$. The local sales$ in the form is a text field with
"=[US$ sales] * [Exchange Rate]". The result of this calculate field shown
itself successfully in the form's local sales$ box (form view); but it
doesn't show in the table's local sales$ column at all. What can I do to
bound it to the table's local sales$ column? Please advise!

Thank you

 

[Ken Snell [MVP]]

Use the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[local sales$].Value = Me.[US$ sales].Value * Me.[Exchange Rate].Value
End Sub

--

Ken Snell
<MS ACCESS MVP>

Dear Ken Snell:
Can you please show me how to calculate the value in VBA
code and write the value into the textbox?

Thanks

You cannot bind a control that has a calculation for the ControlSource.
Setting aside the question of whether you really need to store a
calculated
value (a query can calculate that value for you whenever you need it),
you'd
need to bind the textbox to the field and then calculate the value in VBA
code and write the value into that textbox.

--

Ken Snell
<MS ACCESS MVP>

I have a form made up by a table call retail sales. On the form and the
table
there are one column for US$ sales, one column for Exchange Rate, and
one
column for local sales$. The local sales$ in the form is a text field
with
"=[US$ sales] * [Exchange Rate]". The result of this calculate field
shown
itself successfully in the form's local sales$ box (form view); but it
doesn't show in the table's local sales$ column at all. What can I do
to
bound it to the table's local sales$ column? Please advise!

Thank you

 

[Guest]

It works! Thanks a lot!

Use the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[local sales$].Value = Me.[US$ sales].Value * Me.[Exchange Rate].Value
End Sub

--

Ken Snell
<MS ACCESS MVP>

Dear Ken Snell:
Can you please show me how to calculate the value in VBA
code and write the value into the textbox?

Thanks

You cannot bind a control that has a calculation for the ControlSource.
Setting aside the question of whether you really need to store a
calculated
value (a query can calculate that value for you whenever you need it),
you'd
need to bind the textbox to the field and then calculate the value in VBA
code and write the value into that textbox.

--

Ken Snell
<MS ACCESS MVP>



I have a form made up by a table call retail sales. On the form and the
table
there are one column for US$ sales, one column for Exchange Rate, and
one
column for local sales$. The local sales$ in the form is a text field
with
"=[US$ sales] * [Exchange Rate]". The result of this calculate field
shown
itself successfully in the form's local sales$ box (form view); but it
doesn't show in the table's local sales$ column at all. What can I do
to
bound it to the table's local sales$ column? Please advise!

Thank you