Add values to a text field

[Guest]

Hi all,

I got a text field called "Distribution" where i input names of personnel -
usually it is 1-3 persons out of 5-6 from a list, but sometimes it could be
somebody who is not in list.

I would like to introduce 5-6 unbound check-boxes on my form, clicking each
of them would add up a name into the text field. (i want to be able as well
to add something manually)

I know how to replace a value of a text field with other value, but i want
to be able to ADD to existing value.

Can somebody help me with a script?

Thank you.
Lana

 

[Allen Browne]

Lana, this is the wrong design.

If several people can be in a distribution, you need another table, with a
one-to-many relation to the first table.

That's how relational databases work.
If it's a new concept for you, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

 

[Guest]

hi Allen,

this is not the only case where i want to use such function as adding some
more values to the existing ones in the text-box. And it has nothing to do
with relationships.

I have my reasons to do it that way.
Can anybody help me with the code please?

Lana

 

[Allen Browne]

If you are determined to do it that way, you will need to use the
AfterUpdate event procedure of each text box.

If the box now has the value True, you can add a piece of text and the
separator character (semicolon in this example) to the end of the value in
Text1 like this:
Me.Text1 = Me.Text1 & "My new word;"

If the box is now False, you presumably want to remove the word from Text1,
so use Instr() to locate the word, Len() to figure out how many characters
to chop out, and Left(), Mid() and Right() to parse the text.

In the VBA code window, you can get help for each of those functions.

 

[Guest]

Thank you Allen,

I guess i'll place this on each check-box for the True condition, and i can
delete manually for false (although it would be nice if it was done
automatically on false as well, but i am not sure i can compile the code
myself)

by the way, should there be any "save" action placed before the
Me.Text1 = Me.Text1 & "My new word;" ??
otherwise would it know which was the value of the Text1 before?
how do i do it?

Lana

 

[John Vinson]

Hi all,

I got a text field called "Distribution" where i input names of personnel -
usually it is 1-3 persons out of 5-6 from a list, but sometimes it could be
somebody who is not in list.

I would like to introduce 5-6 unbound check-boxes on my form, clicking each
of them would add up a name into the text field. (i want to be able as well
to add something manually)

I know how to replace a value of a text field with other value, but i want
to be able to ADD to existing value.

Can somebody help me with a script?

I quite agree with Allen that this is A Very Bad Idea; you're storing
data redundantly, making it hard to search, making it hard to edit,
and all sorts of other problems.

But... that said... if you really want to do this you can use code
like this in the checkbox AfterUpdate event:

Private Sub chkFred_AfterUpdate()
If Me.chkFred Then ' did the user check it?
Me.txtNames = (Me.txtNames + ", ") & "Fred"
End If
End Sub

This will take a text box containing "Joe, Janet, Bill" and change it
to "Joe, Janet, Bill, Fred"; the trick with the + and parentheses will
ensure that you don't put an unwanted comma into an empty textbox.

John W. Vinson[MVP]

 

[Allen Browne]

Try it Lana

The saving of the value to a table will happen when the recor is saved
(assuming that Text1 is bound to a field.)

 

[Guest]

Thank you guys very much!

I finally decided to make unbound fields with names instead of check boxes,
so double click on the field will add the name to my "Distribution" text box.
(because the check boxes wouldnt clear themselves as i moved to another
record)

Lana