check boxes

[mdelo]

I have form that is bound to a table. Data is entered through the form. On the form there are 4 check boxes (not an option group b/c more than one can be selected), however the user has requested that the values checked should be displayed in the table in a new column or field as "1, 2, 3" or "1, 4" along with the four separate fields showing check marks.

This is what it should look like:

Status CB1 CB2 CB3 CB4
1,2,3 x x x (no chk)

Is this possible?

 

[shacket]

It is possible. You have to obviously create the extra field in the table. Then, behind each check box's AfterUpdate event, put the following code (I would run it from a Function that each event points to):

Dim strBoxesChecked As String
strBoxesChecked = ""
If Me.chk1 = True Then strBoxesChecked = strBoxesChecked & "1,"
If Me.chk2 = True Then strBoxesChecked = strBoxesChecked & "2,"
If Me.chk3 = True Then strBoxesChecked = strBoxesChecked & "3,"
If Me.chk4 = True Then strBoxesChecked = strBoxesChecked & "4"
Me.FieldCreated = strBoxesChecked

You'll have to mess with the code above to work out the kinks in terms of spacing and commas, but that should give you enough to get started.

HTH,
Dave

 

[kaspi]

Hi
It could be better to use the "Before Update" event of the form. In that way the code can be only in one place.
It could be something like:
me.status = IIf(me.cb1, "1,",vbnullstring) & IIf(me.cb2,"2,",vbnullstring) & IIf(me.cb3, "3,",vbnullsting & IIf(me.cb4,"4,",vbnullstring)
'to get rid of the last comma
me.status = left(me.status, len(me.status) - 1)
Cheers

 

[Pat Hartman]

Users don't get to specify how data should be stored. That is what professional programmers get paid to do. They specify how they want to interact with the data and what needs to be stored but NOT how it is stored.

It is very poor practice to combine multiple values in a single table column. It will cause you nothing but trouble in the future. You can use either of the methods suggested (except that the comma should preceed the concatenated field rather than follow it unless you want a trailing comma) to format it for display. But don't store the data that way.

Dim strBoxesChecked As String
strBoxesChecked = ""
If Me.chk1 = True Then strBoxesChecked = strBoxesChecked & "1"
If Me.chk2 = True Then strBoxesChecked = strBoxesChecked & ",2"
If Me.chk3 = True Then strBoxesChecked = strBoxesChecked & ",3"
If Me.chk4 = True Then strBoxesChecked = strBoxesChecked & ",4"
Me.FieldCreated = strBoxesChecked

or

me.status = IIf(me.cb1, "1",vbnullstring) & IIf(me.cb2,",2",vbnullstring) & IIf(me.cb3, ",3",vbnullsting & IIf(me.cb4,",4",vbnullstring)

 

[kaspi]

Hi Pat
I fully agree what you are saying in regards to the way how the data should be stored.
I just would like to bring up that neither of our solutions fully addressed the problem of the comma.
Just imagine that the first check box is not checked (you would get something like ",2,3"). I prefer the trailing comma because in that case I do know that the comma is at the end and I can use left function to get rid of it.

 

[joeyreyma]

i believe that either way you put it (first or end of the character), you should check if the comma is existing.

using kaspi's example, if the user choses "3,". you'll still have to check if comma is the last character. what about "4".

using pat's example, if the user choses ",2". you'll still have to check if comma is the first character. what about "1".

which way that is you still have to check if the comma is existing.

 

[Pat Hartman]

I stand corrected. The comma has to be dealt with one way or another.

 

[mdelo]

Thanks for the help. I might have mis-stated my question, but I got it to work with your suggestions nonetheless. The user was not actually specifying how to store data (the check boxes are actually how the data for each status is stored), they were merely asking for a field (for display purposes only, i.e. on a report) that would just list the status rather than have 4 different check boxes.