Set up a checkbox or other control for "do this" or "do that"?

[Ed from AZ]

I am storing a value and need some kind of Boolean control to indicate
whether this value should be added or subtracted from another number.
I put a Yes/No data field in the table, with
;"Add"[Blue];"Subtract"[Red]
as a custom format. Somehow, I kind of expected to see "Add" or
"Subtract" by the checkbox when the wizard created the form!!?

What would be the best way to (1) store a Boolean that I can use to
indicate in a formula whter that value is added or subtracted, and (2)
indicate to the user which option is selected on the form?

Ed

 

[Dirk Goldgar]

I am storing a value and need some kind of Boolean control to indicate
whether this value should be added or subtracted from another number.
I put a Yes/No data field in the table, with
;"Add"[Blue];"Subtract"[Red]
as a custom format. Somehow, I kind of expected to see "Add" or
"Subtract" by the checkbox when the wizard created the form!!?

What would be the best way to (1) store a Boolean that I can use to
indicate in a formula whter that value is added or subtracted, and (2)
indicate to the user which option is selected on the form?

Interesting notion. Check boxes don't have the same sort of format options
as text boxes, but you could certainly have a text box bound to your yes/no
field, and set that control's Format property to the format string you
posted. I just tried it, and it worked.

I'd be inclined to have a check box for the user to check or uncheck, and
also have a locked, disabled text box beside it just to show "Add" or
"Subtract". In other words, I'd use the text box just to explain the
meaning of the check box. However, it would be perfectly possible to use
the text box alone, and program it to respond to a click the way a check box
would. If I were going to do that, I'd probably still lock and disable the
text box (because it will show -1 or 0, not "Add" or "Subtract", if it the
user ever clicks on it), but I'd put a transparent command button over it
and use the command button's Click event to toggle the value of the yes/no
field.

 

[Ed from AZ]

Hi, Dirk. I'm not sure I followed all of what you were saying, but
here's what I did (Access 2003, BTW):

-- drug the Add_Sub_KM field from the table field list onto the form
to get a checkbox, then deleted the label and kept the checkbox.
-- drew an unbound textbox on the form and named it txtKM.
-- set textbox Enabled "No" and Locked "Yes".
-- set the checkbox On Click to Event Procedure, and put the following
code in the module:

Private Sub Add_Sub_KM_Click()

If Me.Add_Sub_KM.Value = -1 Then
DoCmd.GoToControl "txtKM"
Me.txtKM.Text = "Add this value"
Else
DoCmd.GoToControl "txtKM"
Me.txtKM.Text = "Subtract this value"
End If

End Sub

I get the error"
"Access can't move the focus to the control txtKM."

Any further help is greatly appreciated.
Ed

I am storing a value and need some kind of Boolean control to indicate
whether this value should be added or subtracted from another number.
I put a Yes/No data field in the table, with
  ;"Add"[Blue];"Subtract"[Red]
as a custom format.  Somehow, I kind of expected to see "Add" or
"Subtract" by the checkbox when the wizard created the form!!?

What would be the best way to (1) store a Boolean that I can use to
indicate in a formula whter that value is added or subtracted, and (2)
indicate to the user which option is selected on the form?

Interesting notion.  Check boxes don't have the same sort of format options
as text boxes, but you could certainly have a text box bound to your yes/no
field, and set that control's Format property to the format string you
posted.  I just tried it, and it worked.

I'd be inclined to have a check box for the user to check or uncheck, and
also have a locked, disabled text box beside it just to show "Add" or
"Subtract".  In other words, I'd use the text box just to explain the
meaning of the check box.  However, it would be perfectly possible to use
the text box alone, and program it to respond to a click the way a check box
would.  If I were going to do that, I'd probably still lock and disable the
text box (because it will show -1 or 0, not "Add" or "Subtract", if it the
user ever clicks on it), but I'd put a transparent command button over it
and use the command button's Click event to toggle the value of the yes/no
field.

 

[Dirk Goldgar]

Hi, Dirk. I'm not sure I followed all of what you were saying, but
here's what I did (Access 2003, BTW):

-- drug the Add_Sub_KM field from the table field list onto the form
to get a checkbox, then deleted the label and kept the checkbox.
-- drew an unbound textbox on the form and named it txtKM.
-- set textbox Enabled "No" and Locked "Yes".
-- set the checkbox On Click to Event Procedure, and put the following
code in the module:

Private Sub Add_Sub_KM_Click()

If Me.Add_Sub_KM.Value = -1 Then
DoCmd.GoToControl "txtKM"
Me.txtKM.Text = "Add this value"
Else
DoCmd.GoToControl "txtKM"
Me.txtKM.Text = "Subtract this value"
End If

End Sub

I get the error"
"Access can't move the focus to the control txtKM."

Any further help is greatly appreciated.

So you decided to take yet a different approach. It's no mystery why you
get the error message: the text box is disabled, so it can't receive the
focus. Yet you're trying to put the focus there via DoCmd.GoToControl.
There's no need to do that; while you can't set the Text property of a
control unless it has the focus, you can set its Value property any time,
and that's what you would normally use. In Access, you only use the Text
property of the control for very special purposes, and this wouldn't be one
of them.

But may I suggest that you're going about this in an unnecessarily elaborate
way? Rather than using code to change the value of txtKM -- which could
work with the correct code, don't get me wrong -- why not just set the
controlsource propertry of the text box to an expression which automatically
evaluates the value of the yes/no field? If you set the ControlSource
property of txtKM to this expression:

=IIf([Add_Sub_KM], "Add this value", "Subtract this value")

.... you won't need any code to make the text box reflect the state of the
check box.

 

[Ed from AZ]

Hi, Dirk. I'm not sure I followed all of what you were saying,

So you decided to take yet a different approach.

Well, I guess I _didn't_ understand what you were saying!!

evaluates the value of the yes/no field? If you set the ControlSource
property of txtKM to this expression:

=IIf([Add_Sub_KM], "Add this value", "Subtract this value")

I tried that, but got a #Name error in the text box and another
message that the ControlSource referenced an invalid control.

you can set its Value property any time,
and that's what you would normally use.

I didn't know that - I'm used to working with forms in Word and Excel
VBA.
So I changed my existing code from
Me.txtKM.Text =
to
Me.txtKM.Value =
and took out the
DoCmd.GoToControl
and it works fine.

Thanks for the boost!!

Ed

Hi, Dirk.  I'm not sure I followed all of what you were saying, but
here's what I did (Access 2003, BTW):

-- drug the Add_Sub_KM field from the table field list onto the form
to get a checkbox, then deleted the label and kept the checkbox.
-- drew an unbound textbox on the form and named it txtKM.
-- set textbox  Enabled "No" and Locked "Yes".
-- set the checkbox On Click to Event Procedure, and put the following
code in the module:

Private Sub Add_Sub_KM_Click()

 If Me.Add_Sub_KM.Value = -1 Then
   DoCmd.GoToControl "txtKM"
   Me.txtKM.Text = "Add this value"
 Else
   DoCmd.GoToControl "txtKM"
   Me.txtKM.Text = "Subtract this value"
 End If

End Sub
I get the error"
"Access can't move the focus to the control txtKM."

Any further help is greatly appreciated.

So you decided to take yet a different approach.  It's no mystery why you
get the error message:  the text box is disabled, so it can't receive the
focus.  Yet you're trying to put the focus there via DoCmd.GoToControl.
There's no need to do that;  while you can't set the Text property of a
control unless it has the focus, you can set its Value property any time,
and that's what you would normally use.  In Access, you only use the Text
property of the control for very special purposes, and this wouldn't be one
of them.

But may I suggest that you're going about this in an unnecessarily elaborate
way?  Rather than using code to change the value of txtKM -- which could
work with the correct code, don't get me wrong -- why not just set the
controlsource propertry of the text box to an expression which automatically
evaluates the value of the yes/no field?  If you set the ControlSource
property of txtKM to this expression:

    =IIf([Add_Sub_KM], "Add this value", "Subtract this value")

... you won't need any code to make the text box reflect the state of the
check box.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

 

[Dirk Goldgar]

=IIf([Add_Sub_KM], "Add this value", "Subtract this value")

I tried that, but got a #Name error in the text box and another message
that the ControlSource referenced an invalid control.

That suggests that one or both of two things is wrong:

1. The name of the text box may be the same as the name of one of the fields
in the form's recordsource. That's only allowed when the text box is bound
directly to the field in question. If that's the problem, just rename the
text box.

2. "Add_Sub_KM" may not be the correct name of the yes/no field or the check
box that is bound to it. If the name is wrong, that would explain both the
#Name error and ther message you got. If that's the case, you have to
correct the name used in the controlsource expression.

I can tell you that, in principle, this approach works. So if you want to
use it, you just have to figure out where the error is coming from and fix
it.

On the other hand, if you've got a different working solution, you can
ignore this.