check box to update text box

[Rachel]

On my form I have a text box (txtPrice) which currently has a control source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to override
the value already in txtPrice and make it $0.00. Then if its unchecked the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like this:
If me.cbxdeal = true then
me.price.value = "0"
End if

Doesn't work

Any help is much appreciated
Thanks.

 

[Ken Snell]

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows more
than one record at a time (Continuous Forms view).

 

[Paul Shapiro]

Expanding on Ken's note, you need to call the same code anytime either the
current record changes or the check box value changes, assuming the checkbox
is bound to a field in your data. I would put the code to update the textbox
display in it's own routine and then call that routine both from
cbxDeal_AfterUpdate and in a FormCurrent event handler. And Ken is also
correct that the whole approach fails if you're using a continuous form
view.

If you don't have the check box bound to a data field, this won't work
because there's no way to retain the appropriate display once you leave the
current record.

Maybe a better approach is to change the txtPrice control source so the
control source accounts for the checkbox value. It would be something like:
= iif(Me.isFree, 0, [quantity]*[unitprice])
assuming the checkbox control is bound to a data field named isFree (which
should have a default value of false [0] and not allow nulls). Then the text
box always displays the correct value, without any additional code, and
continous forms would work correctly.

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows more
than one record at a time (Continuous Forms view).
--

Ken Snell
http://www.accessmvp.com/KDSnell/

On my form I have a text box (txtPrice) which currently has a control
source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to
override
the value already in txtPrice and make it $0.00. Then if its unchecked
the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like this:
If me.cbxdeal = true then
me.price.value = "0"
End if

Doesn't work

 

[Ken Snell]

Good additional info and suggested change to Control Source, Paul. Thanks.

However, don't use Me in the ControlSource directly; instead, just use the
checkbox control's name:

=IIf([isFree], 0, [quantity]*[unitprice])
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Expanding on Ken's note, you need to call the same code anytime either the
current record changes or the check box value changes, assuming the
checkbox is bound to a field in your data. I would put the code to update
the textbox display in it's own routine and then call that routine both
from cbxDeal_AfterUpdate and in a FormCurrent event handler. And Ken is
also correct that the whole approach fails if you're using a continuous
form view.

If you don't have the check box bound to a data field, this won't work
because there's no way to retain the appropriate display once you leave
the current record.

Maybe a better approach is to change the txtPrice control source so the
control source accounts for the checkbox value. It would be something
like:
= iif(Me.isFree, 0, [quantity]*[unitprice])
assuming the checkbox control is bound to a data field named isFree (which
should have a default value of false [0] and not allow nulls). Then the
text box always displays the correct value, without any additional code,
and continous forms would work correctly.

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows
more than one record at a time (Continuous Forms view).
--

Ken Snell
http://www.accessmvp.com/KDSnell/

On my form I have a text box (txtPrice) which currently has a control
source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to
override
the value already in txtPrice and make it $0.00. Then if its unchecked
the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like this:
If me.cbxdeal = true then
me.price.value = "0"
End if

Doesn't work

 

[Rachel]

Hi - I tried both your suggestions but have hit quite a few snags.
I need txtprice to be bound to my orderdetails table so I can't change the
control source to either [quantity]*[unitprice] (which is what I originally
had) nor your suggestions of =IIf([isFree], 0, [quantity]*[unitprice]).
I think I must be missing something.

I have an OrderDetails table with OrderID (PK), CategoryID, SizeID,
ProductID, Quantity, Unitprice, Price, Discount, Deal (checkbox) and DealType.
From that I have created a form in datasheet format where CategoryID, SizeID
and ProductID are all synchronised using queries. The UnitPrice text box
autofills based on the item selected in the combo boxes.
I then want to be able to enter a quantity and have the Price calculate.
Then I want to be able to tick the Deal check box and the Price is overridden
and changed to $0.00, then I select a DealType (which eventually will pull
the dealprice from the Deals table, to be used in the Grand Total field on
the Orders table, but thats a long way off yet!).
So I want all this information to be bound and recorded back to the
OrderDetails table.
When I use your suggestions, the amount entered into Price doesn't update in
the Ordersdetails table and i can't then use this to generate a subtotal
textbox in the subform footer and hence cannot generate the GrandTotal on the
main Orders Form/Table.

Have I lost you yet?!
I definately think I am missing something, my brain just won't click into
gear.
Any help is, as always, much appreciated.
Thanks,
Rachel

Good additional info and suggested change to Control Source, Paul. Thanks.

However, don't use Me in the ControlSource directly; instead, just use the
checkbox control's name:

=IIf([isFree], 0, [quantity]*[unitprice])
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Expanding on Ken's note, you need to call the same code anytime either the
current record changes or the check box value changes, assuming the
checkbox is bound to a field in your data. I would put the code to update
the textbox display in it's own routine and then call that routine both
from cbxDeal_AfterUpdate and in a FormCurrent event handler. And Ken is
also correct that the whole approach fails if you're using a continuous
form view.

If you don't have the check box bound to a data field, this won't work
because there's no way to retain the appropriate display once you leave
the current record.

Maybe a better approach is to change the txtPrice control source so the
control source accounts for the checkbox value. It would be something
like:
= iif(Me.isFree, 0, [quantity]*[unitprice])
assuming the checkbox control is bound to a data field named isFree (which
should have a default value of false [0] and not allow nulls). Then the
text box always displays the correct value, without any additional code,
and continous forms would work correctly.

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows
more than one record at a time (Continuous Forms view).
--

Ken Snell
http://www.accessmvp.com/KDSnell/


On my form I have a text box (txtPrice) which currently has a control
source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to
override
the value already in txtPrice and make it $0.00. Then if its unchecked
the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like this:
If me.cbxdeal = true then
me.price.value = "0"
End if

Doesn't work

.

 

[Paul Shapiro]

First, I'm guessing OrderID is the PK of the Orders table, so unless each
Order can only contain 1 OrderDetail, there should be another attribute in
the OrderDetail PK, perhaps ProductID?

Second, you didn't explain earlier that price is a stored data field. Should
it be stored? It seems that if you have quantity, unit price and discount
(is that an amount, a percent, ???) you can calculate total price. If that's
the case it should not be stored in the data, but should always be
calculated. If Discount is always known from the DealType, then Discount
should not be stored either.

If for some reason the price is not calculable from the other data, then
indeed you can't use an expression for the control source. You would have to
add code in the appropriate form and control event handlers to manage the
default value for that data. As a rough guess from what you've said below,
you could fill in the price value once the quantity and unit price are
known. If the Deal box is checked, you would clear the assigned price value
and re-assign it after the DealType is selected. If the Deal box is
unchecked you would clear the DealType and reassign the total price. Etc.
This sounds pretty messy and makes me think you might want to re-evaluate
your requirements and data design. Do you need a Deal checkbox, or should
you add a No-Deal entry to the DealType? It is generally problematic to have
a design where the meaning of a field depends on the value of any other
field.

Hi - I tried both your suggestions but have hit quite a few snags.
I need txtprice to be bound to my orderdetails table so I can't change the
control source to either [quantity]*[unitprice] (which is what I
originally
had) nor your suggestions of =IIf([isFree], 0, [quantity]*[unitprice]).
I think I must be missing something.

I have an OrderDetails table with OrderID (PK), CategoryID, SizeID,
ProductID, Quantity, Unitprice, Price, Discount, Deal (checkbox) and
DealType.
From that I have created a form in datasheet format where CategoryID,
SizeID
and ProductID are all synchronised using queries. The UnitPrice text box
autofills based on the item selected in the combo boxes.
I then want to be able to enter a quantity and have the Price calculate.
Then I want to be able to tick the Deal check box and the Price is
overridden
and changed to $0.00, then I select a DealType (which eventually will pull
the dealprice from the Deals table, to be used in the Grand Total field on
the Orders table, but thats a long way off yet!).
So I want all this information to be bound and recorded back to the
OrderDetails table.
When I use your suggestions, the amount entered into Price doesn't update
in
the Ordersdetails table and i can't then use this to generate a subtotal
textbox in the subform footer and hence cannot generate the GrandTotal on
the
main Orders Form/Table.

Have I lost you yet?!
I definately think I am missing something, my brain just won't click into
gear.
Any help is, as always, much appreciated.
Thanks,
Rachel

Good additional info and suggested change to Control Source, Paul.
Thanks.

However, don't use Me in the ControlSource directly; instead, just use
the
checkbox control's name:

=IIf([isFree], 0, [quantity]*[unitprice])
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Expanding on Ken's note, you need to call the same code anytime either
the
current record changes or the check box value changes, assuming the
checkbox is bound to a field in your data. I would put the code to
update
the textbox display in it's own routine and then call that routine both
from cbxDeal_AfterUpdate and in a FormCurrent event handler. And Ken is
also correct that the whole approach fails if you're using a continuous
form view.

If you don't have the check box bound to a data field, this won't work
because there's no way to retain the appropriate display once you leave
the current record.

Maybe a better approach is to change the txtPrice control source so the
control source accounts for the checkbox value. It would be something
like:
= iif(Me.isFree, 0, [quantity]*[unitprice])
assuming the checkbox control is bound to a data field named isFree
(which
should have a default value of false [0] and not allow nulls). Then the
text box always displays the correct value, without any additional
code,
and continous forms would work correctly.

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows
more than one record at a time (Continuous Forms view).
--

Ken Snell
http://www.accessmvp.com/KDSnell/


On my form I have a text box (txtPrice) which currently has a control
source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to
override
the value already in txtPrice and make it $0.00. Then if its
unchecked
the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like
this:
If me.cbxdeal = true then
me.price.value = "0"
End if

 

[Ken Snell]

Going back to my original code suggestion, I've modified it (and added a
Current event procedure example) to write the desired value into the
textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.Value = Me.[quantity] * Me.[unitprice]
End Select
End Sub

Private Sub Form_Current()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.Value = Me.[quantity] * Me.[unitprice]
End Select
End Sub

Using the above allows you to keep the ControlSource for txtPrice as you
have it.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Hi - I tried both your suggestions but have hit quite a few snags.
I need txtprice to be bound to my orderdetails table so I can't change the
control source to either [quantity]*[unitprice] (which is what I
originally
had) nor your suggestions of =IIf([isFree], 0, [quantity]*[unitprice]).
I think I must be missing something.

I have an OrderDetails table with OrderID (PK), CategoryID, SizeID,
ProductID, Quantity, Unitprice, Price, Discount, Deal (checkbox) and
DealType.
From that I have created a form in datasheet format where CategoryID,
SizeID
and ProductID are all synchronised using queries. The UnitPrice text box
autofills based on the item selected in the combo boxes.
I then want to be able to enter a quantity and have the Price calculate.
Then I want to be able to tick the Deal check box and the Price is
overridden
and changed to $0.00, then I select a DealType (which eventually will pull
the dealprice from the Deals table, to be used in the Grand Total field on
the Orders table, but thats a long way off yet!).
So I want all this information to be bound and recorded back to the
OrderDetails table.
When I use your suggestions, the amount entered into Price doesn't update
in
the Ordersdetails table and i can't then use this to generate a subtotal
textbox in the subform footer and hence cannot generate the GrandTotal on
the
main Orders Form/Table.

Have I lost you yet?!
I definately think I am missing something, my brain just won't click into
gear.
Any help is, as always, much appreciated.
Thanks,
Rachel

Good additional info and suggested change to Control Source, Paul.
Thanks.

However, don't use Me in the ControlSource directly; instead, just use
the
checkbox control's name:

=IIf([isFree], 0, [quantity]*[unitprice])
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Expanding on Ken's note, you need to call the same code anytime either
the
current record changes or the check box value changes, assuming the
checkbox is bound to a field in your data. I would put the code to
update
the textbox display in it's own routine and then call that routine both
from cbxDeal_AfterUpdate and in a FormCurrent event handler. And Ken is
also correct that the whole approach fails if you're using a continuous
form view.

If you don't have the check box bound to a data field, this won't work
because there's no way to retain the appropriate display once you leave
the current record.

Maybe a better approach is to change the txtPrice control source so the
control source accounts for the checkbox value. It would be something
like:
= iif(Me.isFree, 0, [quantity]*[unitprice])
assuming the checkbox control is bound to a data field named isFree
(which
should have a default value of false [0] and not allow nulls). Then the
text box always displays the correct value, without any additional
code,
and continous forms would work correctly.

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows
more than one record at a time (Continuous Forms view).
--

Ken Snell
http://www.accessmvp.com/KDSnell/


On my form I have a text box (txtPrice) which currently has a control
source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to
override
the value already in txtPrice and make it $0.00. Then if its
unchecked
the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like
this:
If me.cbxdeal = true then
me.price.value = "0"
End if

Doesn't work

.

 

[Rachel]

Thanks again for your time Ken.
I have managed after a lot of fiddling to get this to work. I needed to add
a few extra things but it seems to be ok.... for the first record.
When I move to the next record and check or uncheck cbxDeal all records
change.
How can I avoid this?
Thanks again,
Rachel

Going back to my original code suggestion, I've modified it (and added a
Current event procedure example) to write the desired value into the
textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.Value = Me.[quantity] * Me.[unitprice]
End Select
End Sub

Private Sub Form_Current()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.Value = Me.[quantity] * Me.[unitprice]
End Select
End Sub

Using the above allows you to keep the ControlSource for txtPrice as you
have it.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Hi - I tried both your suggestions but have hit quite a few snags.
I need txtprice to be bound to my orderdetails table so I can't change the
control source to either [quantity]*[unitprice] (which is what I
originally
had) nor your suggestions of =IIf([isFree], 0, [quantity]*[unitprice]).
I think I must be missing something.

I have an OrderDetails table with OrderID (PK), CategoryID, SizeID,
ProductID, Quantity, Unitprice, Price, Discount, Deal (checkbox) and
DealType.
From that I have created a form in datasheet format where CategoryID,
SizeID
and ProductID are all synchronised using queries. The UnitPrice text box
autofills based on the item selected in the combo boxes.
I then want to be able to enter a quantity and have the Price calculate.
Then I want to be able to tick the Deal check box and the Price is
overridden
and changed to $0.00, then I select a DealType (which eventually will pull
the dealprice from the Deals table, to be used in the Grand Total field on
the Orders table, but thats a long way off yet!).
So I want all this information to be bound and recorded back to the
OrderDetails table.
When I use your suggestions, the amount entered into Price doesn't update
in
the Ordersdetails table and i can't then use this to generate a subtotal
textbox in the subform footer and hence cannot generate the GrandTotal on
the
main Orders Form/Table.

Have I lost you yet?!
I definately think I am missing something, my brain just won't click into
gear.
Any help is, as always, much appreciated.
Thanks,
Rachel

Good additional info and suggested change to Control Source, Paul.
Thanks.

However, don't use Me in the ControlSource directly; instead, just use
the
checkbox control's name:

=IIf([isFree], 0, [quantity]*[unitprice])
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Expanding on Ken's note, you need to call the same code anytime either
the
current record changes or the check box value changes, assuming the
checkbox is bound to a field in your data. I would put the code to
update
the textbox display in it's own routine and then call that routine both
from cbxDeal_AfterUpdate and in a FormCurrent event handler. And Ken is
also correct that the whole approach fails if you're using a continuous
form view.

If you don't have the check box bound to a data field, this won't work
because there's no way to retain the appropriate display once you leave
the current record.

Maybe a better approach is to change the txtPrice control source so the
control source accounts for the checkbox value. It would be something
like:
= iif(Me.isFree, 0, [quantity]*[unitprice])
assuming the checkbox control is bound to a data field named isFree
(which
should have a default value of false [0] and not allow nulls). Then the
text box always displays the correct value, without any additional
code,
and continous forms would work correctly.

You can use the AfterUpdate event of the checkbox to change the
ControlSource of the textbox:

Private Sub cbxDeal_AfterUpdate()
Select Case Me.cbxDeal.Value
Case True ' checkbox is checked
Me.txtPrice.ControlSource = ""
Me.txtPrice.Value = 0
Case False ' checkbox is unchecked
Me.txtPrice.ControlSource = "=[quantity]*[unitprice]"
End Select
End Sub


Note that this will affect all records on the form if your form shows
more than one record at a time (Continuous Forms view).
--

Ken Snell
http://www.accessmvp.com/KDSnell/


On my form I have a text box (txtPrice) which currently has a control
source
as =[quantity]*[unitprice] and a currency format.
I also have a check box (cbxDeal) which when checked I want it to
override
the value already in txtPrice and make it $0.00. Then if its
unchecked
the
calculated value (=[quantity]*[unitprice]) returns.
I have tried putting a code in the on click event something like
this:
If me.cbxdeal = true then
me.price.value = "0"
End if

Doesn't work



.

.

 

[Ken Snell]

You cannot change that behavior where all records "change" in appearance
based on the current record's conditions. That is the nature of the
continuous forms view in ACCESS. Although the screen shows the different
records, actually there is just one record that exists and the others are
"copies" of the visible representation of that one record; and then the data
for those other records are "shown" in those other records.

It's just a visual aesthetic issue; nothing is happening to your data in the
other records.

 

[Rachel]

Not sure what the actual difference is but is this still the case if I am
using datasheet view and not continuous from view.
Thanks again!

 

[Ken Snell]

Datasheet will behave the same way as continuous forms view with regard to
the appearance of all the records changing based on what you do on the
active record.