Concatenate Checkbox

[Stephen sjw_ost]

I have a form on which I've put in Check boxes.
Checkbox1 = N when true and null, blank, when false
Checkbox2 = 0 when true and null, blank, when false
Checkbox3 = 1 when true and null, blank, when false

What I need is to have the assigned results collected, concatenated and
entered into a textbox as the checkboxes are checked and unchecked.

If Checkbox1 and Checkbox3 are true then I want the textbox to reflect N, 1.
If Checkbox2 and Checkbox3 are true then I want the textbox to reflect 0, 1.
and so forth.
As the checkboxes are unchecked, their designation would be removed from the
textbox.

Can someone go about showing me how to do this?

Thanks for any help.

 

[Allen Browne]

Stephen, you can write code in the AfterUpdate event procedure to
concatenate the values and assign them to a field, but please don't. You
will also have to write code in Form_Current, so the value are correctly set
when you visit a record, and in Form_Undo so the OldValue is correctly reset
for each control. And in the end, you still have a bad design that's hard to
query, and breaks one of the most basic normalization rules, i.e. that
fields must be atomic (storing only one thing each field.) It's a waste of
effort.

Instead, use a related table to store the related values, so one record here
can have many options associated with it. Details in:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

 

[Stephen sjw_ost]

Allen,

Thank you for your reply, however the suggestions presented in the link you
provided was not helpful in getting me to my end result. I have however
changed a bit of how my form is put together. For the from I am needing to
put together I must use check boxes. Listboxes, subforms and relationships
will not help me with what my client wants.

I now have the following which will remain static;
8 checkboxs, visible
9 textboxes, hidden "Visible = no"
1 bound textbox, hidden

Each checkbox puts the result I want in to one of the hidden textboxs using
a formula in the textbox "Control Source =IIf([chk_Bkt00]=True,"0, ",Null).

The 9th textbox concatenates the results from the other 8 checkboxes,
"Control Source =[01] & [02] & [03] & [04] & [05] & [06] & [00] & [0N]"
and provides, in the 9th textbox, the result I want.

Now the problem I am having is getting the result of the 9th textbox into
the 1 bound textbox to where the full result needs to be.

So my question has evolved to, How can I make a bound textbox equal the
result of an unbound textbox.

Thanks for any help.

 

[Allen Browne]

As stated in the previous unhelpful reply, and detailed in the link, you can
use the AfterUpdate event of each control if you want to go that way.

The code will be something like this, assuming a hidden text box named
txtTarget bound to the field where you want to save it:
Me.txtTarget = Me.[01].Value & Me.[02].Value & Me.[03].Value &
Me.[04].Value & Me.[05].Value & Me.[06].Value & Me.[00].Value &
Me.[0N].Value

(I still believe storing non-normalized data like this will create you more
problems than it solves, but it's your call.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for your reply, however the suggestions presented in the link
you
provided was not helpful in getting me to my end result. I have however
changed a bit of how my form is put together. For the from I am needing to
put together I must use check boxes. Listboxes, subforms and relationships
will not help me with what my client wants.

I now have the following which will remain static;
8 checkboxs, visible
9 textboxes, hidden "Visible = no"
1 bound textbox, hidden

Each checkbox puts the result I want in to one of the hidden textboxs
using
a formula in the textbox "Control Source =IIf([chk_Bkt00]=True,"0,
",Null).

The 9th textbox concatenates the results from the other 8 checkboxes,
"Control Source =[01] & [02] & [03] & [04] & [05] & [06] & [00] & [0N]"
and provides, in the 9th textbox, the result I want.

Now the problem I am having is getting the result of the 9th textbox into
the 1 bound textbox to where the full result needs to be.

So my question has evolved to, How can I make a bound textbox equal the
result of an unbound textbox.

Thanks for any help.
--
Stephen

Stephen, you can write code in the AfterUpdate event procedure to
concatenate the values and assign them to a field, but please don't. You
will also have to write code in Form_Current, so the value are correctly
set
when you visit a record, and in Form_Undo so the OldValue is correctly
reset
for each control. And in the end, you still have a bad design that's hard
to
query, and breaks one of the most basic normalization rules, i.e. that
fields must be atomic (storing only one thing each field.) It's a waste
of
effort.

Instead, use a related table to store the related values, so one record
here
can have many options associated with it. Details in:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

message

 

[Stephen sjw_ost]

Please don't take my response personally. You gave good advice, it's just
that the first suggestion would not have worked for me entirely. I did figure
it out and your suggestions and input was invaluable.
The fix was;
I created 1 query that is tied to a temp table and then tie the form to the
query, including all fields on the form within the query.
Next, I made another query that updates the checkbox results to the temp
table.
Next, I made an append query to move the full results to the main table.
Next, I made a delete query to empty the temp table.
All tests have worked perfectly and the structure of the DB is still
normalized.

Thank you for all of your help.
--
Stephen

As stated in the previous unhelpful reply, and detailed in the link, you can
use the AfterUpdate event of each control if you want to go that way.

The code will be something like this, assuming a hidden text box named
txtTarget bound to the field where you want to save it:
Me.txtTarget = Me.[01].Value & Me.[02].Value & Me.[03].Value &
Me.[04].Value & Me.[05].Value & Me.[06].Value & Me.[00].Value &
Me.[0N].Value

(I still believe storing non-normalized data like this will create you more
problems than it solves, but it's your call.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for your reply, however the suggestions presented in the link
you
provided was not helpful in getting me to my end result. I have however
changed a bit of how my form is put together. For the from I am needing to
put together I must use check boxes. Listboxes, subforms and relationships
will not help me with what my client wants.

I now have the following which will remain static;
8 checkboxs, visible
9 textboxes, hidden "Visible = no"
1 bound textbox, hidden

Each checkbox puts the result I want in to one of the hidden textboxs
using
a formula in the textbox "Control Source =IIf([chk_Bkt00]=True,"0,
",Null).

The 9th textbox concatenates the results from the other 8 checkboxes,
"Control Source =[01] & [02] & [03] & [04] & [05] & [06] & [00] & [0N]"
and provides, in the 9th textbox, the result I want.

Now the problem I am having is getting the result of the 9th textbox into
the 1 bound textbox to where the full result needs to be.

So my question has evolved to, How can I make a bound textbox equal the
result of an unbound textbox.

Thanks for any help.
--
Stephen

Stephen, you can write code in the AfterUpdate event procedure to
concatenate the values and assign them to a field, but please don't. You
will also have to write code in Form_Current, so the value are correctly
set
when you visit a record, and in Form_Undo so the OldValue is correctly
reset
for each control. And in the end, you still have a bad design that's hard
to
query, and breaks one of the most basic normalization rules, i.e. that
fields must be atomic (storing only one thing each field.) It's a waste
of
effort.

Instead, use a related table to store the related values, so one record
here
can have many options associated with it. Details in:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

message
I have a form on which I've put in Check boxes.
Checkbox1 = N when true and null, blank, when false
Checkbox2 = 0 when true and null, blank, when false
Checkbox3 = 1 when true and null, blank, when false

What I need is to have the assigned results collected, concatenated and
entered into a textbox as the checkboxes are checked and unchecked.

If Checkbox1 and Checkbox3 are true then I want the textbox to reflect
N,
1.
If Checkbox2 and Checkbox3 are true then I want the textbox to reflect
0,
1.
and so forth.
As the checkboxes are unchecked, their designation would be removed
from the textbox.