Concatenate multiple check boxes into textbox with commas/ands

[Vethra]

Hi Guys,

I'm a little stuck. The attached link will show you what I am trying to do. https://www.accessforums.net/forms/c...mas-52817.html

Unfortunately I can't input anymore data in my array line. Can I extend this array to a second line?, or add another array and combine them? or just simplify this array some how?

Below is what I currently have.

Option Compare Database
Option Explicit
Private Sub Checkbox1_Click()    
Call CreateString
End Sub
Private Sub Checkbox2_Click()
    Call CreateString
End Sub
Private Sub Checkbox3_Click()
    Call CreateString
End Sub
Private Sub Checkbox4_Click()
    Call CreateString
End Sub
Private Sub Checkbox5_Click()
    Call CreateString
End Sub
Private Sub Checkbox6_Click()
    Call CreateString
End Sub
Private Sub Checkbox7_Click()
    Call CreateString
End Sub
Private Sub Checkbox8_Click()
    Call CreateString
End Sub
Private Sub Checkbox9_Click()
    Call CreateString
End Sub
Private Sub Checkbox10_Click()
    Call CreateString
End Sub
Private Sub Checkbox11_Click()
    Call CreateString
End Sub
Private Sub Checkbox12_Click()
    Call CreateString
End Sub
Private Sub Checkbox13_Click()
    Call CreateString
End Sub
Private Sub Checkbox14_Click()
    Call CreateString
End Sub
Private Sub Checkbox15_Click()
    Call CreateString
End Sub
Private Sub Checkbox16_Click()
    Call CreateString
End Sub
Private Sub Checkbox17_Click()
    Call CreateString
End Sub
Private Sub Checkbox18_Click()
    Call CreateString
End Sub
Private Sub Checkbox19_Click()
    Call CreateString
End Sub
Private Sub Checkbox20_Click()
    Call CreateString
End Sub
Private Sub Checkbox21_Click()
    Call CreateString
End Sub
Private Sub Checkbox22_Click()
    Call CreateString
End Sub
Private Sub Checkbox23_Click()
    Call CreateString
End Sub
Private Sub Checkbox24_Click()
    Call CreateString
End Sub
Private Sub Checkbox25_Click()
    Call CreateString
End Sub
Private Sub Checkbox26_Click()
    Call CreateString
End Sub
Private Sub Checkbox27_Click()
    Call CreateString
End Sub
Private Sub Checkbox28_Click()
    Call CreateString
End Sub
Private Sub Checkbox29_Click()
    Call CreateString
End Sub
Private Sub Checkbox30_Click()
    Call CreateString
End Sub
Private Sub Checkbox31_Click()
    Call CreateString
End Sub
Private Sub Checkbox32_Click()
    Call CreateString
End Sub
Private Sub Checkbox33_Click()
    Call CreateString
End Sub
Private Sub Checkbox34_Click()
    Call CreateString
End Sub
Private Sub Checkbox35_Click()
    Call CreateString
End Sub
Private Sub Checkbox36_Click()
    Call CreateString
End Sub
Private Sub Checkbox37_Click()
    Call CreateString
End Sub
Private Sub Checkbox38_Click()
    Call CreateString
End Sub
Private Sub Checkbox39_Click()
    Call CreateString
End Sub
Private Sub Checkbox40_Click()
    Call CreateString
End Sub
Private Sub Checkbox41_Click()
    Call CreateString
End Sub
Private Sub Checkbox42_Click()
    Call CreateString
End Sub
Private Sub Checkbox43_Click()
    Call CreateString
End Sub
Private Sub Checkbox44_Click()
    Call CreateString
End Sub
Private Sub Checkbox45_Click()
    Call CreateString
End Sub
Private Sub Checkbox46_Click()
    Call CreateString
End Sub
Private Sub Checkbox47_Click()
    Call CreateString
End Sub
Private Sub Checkbox48_Click()
    Call CreateString
End Sub
Private Sub Checkbox49_Click()
    Call CreateString
End Sub
Private Sub Checkbox50_Click()
    Call CreateString
End Sub
Private Sub Checkbox51_Click()
    Call CreateString
End Sub
Private Sub Checkbox52_Click()
    Call CreateString
End Sub
Private Sub Checkbox53_Click()
    Call CreateString
End Sub
Sub CreateString()
    Dim k As Integer, i As Integer, pos As Integer
    Dim tmpString As String
    Dim arChecks
    
    arChecks = Array("", "Adaptor", "Adaptors", "Anchor Plate", "Anchor Plates", "Female Anchor Plate", "Female Anchor Plates", "Flue Extension", "Flue Extensions", "Female Flue Extension", "Female Flue Extensions", "Clamping Adaptor", "Clamping Adaptors", "Draft Hood Connector", "Draft Hood Connectors", "Draw Band", "Draw Bands", "Boiler Adaptor", "Boiler Adaptors", "Single to Double Wall Adaptor", "Single to Double Wall Adaptors", "Double to Single Wall Adaptor", "Double to Single Wall Adaptors", Textbox1, "Length", "Lengths", "Adjustable Length", "Adjustable Lengths", "6"" Length", "6"" Lengths", "9"" Length", "9"" Lengths", "12"" Length", "12"" Lengths", "18"" Length", "18"" Lengths", "24"" Length", "24"" Lengths", "36"" Length", "36"" Lengths", "48"" Length", "48"" Lengths", "60"" Length", "60"" Lengths", "Test Port Section", "Test Port Sections", "Horizontal Drain Section", " Horizontal Drain Sections", "Vertical Drain Section", "Vertical Drain Sections", Textbox2, "Elbow", "Elbows", "15° Elbow")
    
    i = 0


    For k = 1 To UBound(arChecks)
        If Me("Checkbox" & k) = True Then
            tmpString = tmpString & arChecks(k) & ", "
            i = i + 1
        End If


    Next


    If Len(tmpString) > 0 Then
        'remove the trailing comma
        tmpString = Left(tmpString, Len(tmpString) - 2)
    End If


    If Len(tmpString) > 0 And i > 1 Then
        pos = InStrRev(tmpString, ", ")
        tmpString = Left(tmpString, pos - 1) & " and " & Right(tmpString, Len(tmpString) - pos)
    End If


    Me.Text1582 = tmpString
End Sub

Any help would be awesome

Thanks,
Dilan

[pbaldy]

This worked in a brief test:

  arChecks = Array("", "Adaptor", "Adaptors", _
                   "Anchor Plate", "Anchor Plates", "Female Anchor")

[pbaldy]

I should have added as an option, you could put the text into the Tag property of the checkboxes and simply loop the checkboxes.

[ranman256]

I think you made this over complicated.
Access does everything via queries. Rarely a need for coding, (or arrays). Access has all this build in via queries.
Maybe a better example (not code) of what you need.
A user clicks a check box and you want it to.....???

[ssanfu]

@Dilan,

I didn't realize you would have so many check boxes. I thought about using a table and a recordset to fill the array.

But Paul's suggestion is the way to go with this. Clean and fast.....

So attached is another example...

[pbaldy]

I would loop controls rather than having to use numbered checkboxes and having to remember to change the For/Next counter value any time I added a checkbox. That would also let me have meaningful names like "chkAdaptor" instead of "Checkbox1". Like:

  Dim ctl As Control

  For Each ctl In Me.Controls    
    Select Case ctl.ControlType
      Case acCheckBox
        If ctl.Value = True Then
          tmpString = tmpString & ctl.Tag & ", "
        End If
    End Select
  Next ctl

Not saying it's better or faster, just my preference.

[Vethra]

Thanks for the input guys. I will simplify it tomorrow morning and report back.

[Vethra]

And you're correct Steve originally I was going to have around 20 check boxes. Now I'm at around 100 check boxes.

[Vethra]

Alright its all finished and working like a charm. Thank you very much for your help gents.

[ssanfu]

One thing to watch for is that a text field in a table has a 255 character limit. If you exceed that, you might want to change to a memo field.

[ssanfu]

Update:
This will save you from typing lots of code.

Try this in a COPY of your dB.

1) Delete all of the "Click" event code for all of the check boxes.
2) Change the Sub to a Function

Function CreateString()

3) In the click event of each check box, enter

=CreateString()

4) Compile the code and test.

No more writing code for click events when you add more check boxes......

[Vethra]

This makes life a lot easier! Thank you