Add code with a comma following it to text field when checkbox is checked

I have a form that has three fields on it.  

One of the fields is a checkbox named "Checkbox".  Next to it is a field named "Code".  The other field ins named "SelectedCheckboxes".  The form is a datasheet form.  that has 20 possible checkbox each of the have their own code.  Like:

When the checkbox named ABC is checked for example I want the field named SelectedCheckboxes to be populated with "ABC,"  (note the comma).  Then if for example CBA123 is checked I want that to be added to the "ABC," or in other words, "ABC, CBA123".  Etc.  But I don't want the comma after the last selection.

What would the after update event of the checkbox be?

Also note if a checkbox was previously checked and then Unchecked, I want that code to be removed from the SelectedCheckboxes field.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric FlammOwnerCommented:
It would help if you explain what your ultimate goal is. It's not really meaningful to refer to the "last selection" in a datasheet, since the ordering of the rows is subject to change. Why do you want to populate a database field with the concatenated code(s)? If you just create a textbox on the form, then you can have the following after update code (pseudo code - not tested in VBA) for each checkbox:
for iRow=0 to Rowcount-1
  if checkbox(iRow).value then
     strSelected=strSelected & code(iRow) & ","
  end if
if len(strSelected.0 then
txtSelectedCodes.Value=left(strSelected,len(strSelected)-1) 'strip off the last comma

Open in new window

You might need to use column index references - I can never remember the best way to iterate through a dataform. You might also just get a recordset based on the source query and iterate through that - the field bound to the checkboxes will have updated before the AfterUpdate fires.
This should perform OK with 20 rows - but would get laggy with 2000 rows.
Eric FlammOwnerCommented:
Private Sub Select_AfterUpdate()
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String, strSelected As String
    DoCmd.RunCommand acCmdSaveRecord
    strSelected = ""
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Codes")
    While Not rst.EOF
        If rst.Fields("Select") Then
            strSelected = strSelected & rst.Fields("Code") & ", "
        End If
    If Len(strSelected) > 0 Then
        strSelected = Left(strSelected, Len(strSelected) - 2)
    End If
    Me.txtSelected.Value = strSelected
    MsgBox txtSelected.Value
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I mistyped.  The form is not a datasheet form.  It is a single view form.

I want to populate the database field with the concatenated code(s) because I need to do so.

I cannot get the above code to work.
Jeffrey CoachmanMIS LiasonCommented:
Clearly define:
cannot get the above code to work.

I created similar code,
(only I put mine on a button click event, and I am displaying the fully concatenated string in a textbox)
...and my code is almost exactly the same as Eric's, ...and mine works fine for me as well...

Private Sub btnUpdate_Click()
Dim rst As DAO.Recordset
Dim strFullCode As String
DoCmd.RunCommand acCmdSaveRecord
Set rst = CurrentDb.OpenRecordset("SELECT ID,Chk,Code FROM YourTable WHERE chk=True")
Do Until rst.EOF
    strFullCode = strFullCode & ", " & rst!Code
strFullCode = Right(strFullCode, Len(strFullCode) - 2)
Me.txtFullCode = strFullCode
Set rst = Nothing
End Sub

Open in new window

This is why posting a sample db  (along with your question) is almost always helpful...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.