Loop then string form responses

[tuxalot]

Greeting all -

I have 36 controls on a form, arranged like so:

chkA1 chkA2 txtA3
chkB1 chkB2 txtB3
chkC1 chkC2 txtC3

The functionality of the checkboxes (columns 1 & 2) and textbox (column 3) is like this. A double-click in a textbox on my main form opens this form. All column 2 & 3 controls are visible = false on form load. If a user checks the checkbox in column A1, the checkbox in the same row, column A2 becomes visible. If checkbox A2 is checked, testbox A3 is visible, and so on. There is a default value in the column 3 textboxes, but a user can override this text. This part is working.

So depending on a user's selections, I need a string to be placed in the calling textbox that would show:

If column 2 is not checked:

The string would consist of A1; B1; C1 etc.

If column 2 is checked:

The string would consist of A1, A3; B1, B3; C1, C3 etc.

Most likely there will be a mix of columns 1 & 2 being checked and not checked. So a possible scenario could be:

A1; B1, B3; D1, F1, G1, G3; H1.

So the thinking in my pea-brain is that I use a tag value to interate through the controls on the form (first row would share the same tag value, etc.) and build the string using if then statements. The resulting string should be placed back into the calling textbox. Would this be the best approach? Please help me understand the preferred method to use to get where I need to go with this. Any sample code is very much appreciated.

Thanks again for the assistance.

Tux

[NeoPa]

Although this is a pretty decent job of explaining a quite complex situation, you've made a mistake somewhere, it seems to me, and what we have doesn't make logical sense. As it's well explained otherwise though, let's see if we can recover it somehow.

You tell me which of the statements below are true and which are false and I'm sure we can help you :

1. There are 36 unbound controls on your form arranged as three columns and twelve rows.
2. Columns one and two are both CheckBox controls and start as False.
3. Column three is a TextBox control.
4. By default the column three TextBox contains a value that we don't know (please provide - even a dummy one just to work with would be fine) but this can be overridden if made visible.
5. Columns two and three start as invisible when the form is opened.
6. The CheckBox in column two is visible when the CheckBox in column one is True.
7. The TextBox in column three is visible when the CheckBox in column two is True (which can only be the case if the CheckBox in column one is also True).
8. In the result string the row values are separated by "; " (semi-colon).
9. In the result string multiple values within a row are separated by ", " (comma).
10. Resultant string required (just using three rows for simplicity) :
    1. If the reference is required rather than the value :
       

       Control Values                  Resultant String
       A1=False, A2=False, A3=""
       B1=False, B2=False, B3=""
       C1=False, C2=False, C3=""       "A1; B1; C1"
       A1=False, A2=False, A3=""
       B1=True,  B2=False, B3=""
       C1=True,  C2=True,  C3="Dummy"  "A1; B1; C1, C3"
       A1=True,  A2=True,  A3="Dummy"
       B1=True,  B2=True,  B3="Dummy"
       C1=True,  C2=True,  C3="Dummy"  "A1, A3; B1, B3; C1, C3"

    2. If the value is required rather than the reference (For now any visible TextBox contains "Dummy") :
       

       Control Values                  Resultant String
       A1=False, A2=False, A3=""
       B1=False, B2=False, B3=""
       C1=False, C2=False, C3=""       "False; False; False"
       A1=False, A2=False, A3=""
       B1=True,  B2=False, B3=""
       C1=True,  C2=True,  C3="Dummy"  "False; True; True, Dummy"
       A1=True,  A2=True,  A3="Dummy"
       B1=True,  B2=True,  B3="Dummy"
       C1=True,  C2=True,  C3="Dummy"  "True, Dummy; True, Dummy; True, Dummy"

11. There is no reason to have column 2 in this scenario (The only possible reason it makes a difference is in the second example of the set where values are shown where it allows B1 to be True without displaying B3).

[tuxalot]

Thanks NeoPa for your response. I will try to clarify. About your list being true or false; A - I are all true. Let me step back to give you an idea about the purpose of this form. I've attached a screenshot. The form will be used to select staff at a hotel that have taken part in a recent survey. Per the choices selected in the example screenshot, the resulting string would be:

Tom Smith; Bob Jones, Director of Food and Beverage; Tim Allan, Asst Director of Food and Beverage.

Default values in the textbox represent the staff members' title at the hotel. In the case of Tim Allan, the default value would be "What is this person's title?"

I apologize for not providing full details earlier. I hope this makes sense.

[ADezii]

@tuxalot: A rather interesting and perplexing problem. Here is how I approached this scenario, at least partially.

1. Set the Tag property of all the Check Boxes in Column 1 to 'Col1'.
2. Set the Tag property of all the Check Boxes in Column 2 to 'Col2'.
3. Create a Sub-Routine Procedure, called from the AfterUpdate() Event of the Check Boxes in Column 1 that will control the Visibility of Column 2 and Column 3 Controls.
   

   Private Sub MakeVisible()
   Dim ctl As Control
    
   For Each ctl In Me.Controls
     If ctl.Tag = "Col1" Then
       'Check the Value of the Check Boxes in Column 1, then Set the Visible
       'Property of Column 2 Check Boxes and Column 3 Text Boxes accordingly
       Me.Controls(Left$(ctl.Name, 4) & "2").Visible = Nz(ctl.Value)
       Me.Controls("txt" & Mid$(ctl.Name, 4, 1) & "3").Visible = Nz(ctl.Value)
     End If
   Next
   End Sub

   'Sample Call:
   Private Sub chkB1_AfterUpdate()
     Call MakeVisible
   End Sub

4. Create a Sub-Routine Procedure, called from the AfterUpdate() Event of the Column 2 Check Boxes, which will dynamically create the requested String:
   

   Private Sub BuildString()
   Dim ctl As Control
   Dim strBuild As String
    
   Me![txtResults] = ""
    
   For Each ctl In Me.Controls
     If ctl.Tag = "Col2" Then
       If ctl.Value Then       'Column 2 is Checked, build String accordingly
         strBuild = strBuild & Mid$(ctl.Name, 4, 1) & "1," & Mid$(ctl.Name, 4, 1) & "3;"
       Else                    'Column 2 is NOT Checked, build String accordingly
         strBuild = strBuild & Mid$(ctl.Name, 4, 1) & "1;"
       End If
     End If
   Next
    
   Me![txtResults] = Left$(strBuild, Len(strBuild) - 1)
   End Sub

   'Sample Call:
   Private Sub chkC2_AfterUpdate()
     Call BuildString
   End Sub

5. Download the Attachment to see first hand this approach. I'm sure that it is not comprehensive, but hopefully it is at least in the ballpark.

[NeoPa]

I'm disappointed. Your original description is not only different from the picture shown, but is also so incompatible that all the details of the logic previously worked out are not applicable any more. Sixteen rows instead of twelve is barely a problem. More controls per line doesn't need to be a problem, but as the Staff TextBox wasn't even mentioned and it seems clear (if I ignore your description and look at the picture instead) that having any content there triggers whether or not the Name CheckBox is even visible, this is such a fundamental change to the logic that trying to explain anything within that context impossible.

Simplifying is all well and good, and I don't generally have an issue with that, but when it's done inconsistently then it makes the whole question meaningless (or worse actually - impossible).

Is the picture posted what we should actually be concentrating on? If so, a description of the logic that makes sense in that situation would be useful.

PS. There's no longer any need to respond to point K as this is no longer relevant. The confusion there was part of the larger confusion as to why the question didn't make a lot of sense.

[tuxalot]

@NeoPa - Point taken. If I may, allow me to start from square one. The picture is far superior to my explanation so I will begin there. The textboxes under "Staff" are bound to textboxes on my main form and these controls are locked. The checkboxes under "Name" are visible on form load. The checkboxes under "Title", and the textboxes under "Please include title" are not visible on load. I've attached a screenshot of the main form. The lower left textbox on my main form is double clicked, opening the select staff form. Based on those selections,the string is returned to the calling textbox in the format previously mentioned.

** Edit **

[NeoPa]

As binding TextBoxes on one form to TextBoxes on another isn't possible, perhaps you could explain what you thought you were saying when you said they were so bound.

I think we're nearly at a point where we can start to look at this seriously.

[tuxalot]

I appreciate your patience. The textboxes in the select staff form are bound and get their data from a query. No, they are not bound to the main form and this was a poor choice of words.

What I am trying to avoid is having to type the same details twice in different areas of my main form; other forms have a similar requirement. Per the image in post #5, There is a name listed for General Manager, Engineering, etc. During the survey, some of the hotel staff participate in the survey and those individuals are named in the box at the lower left of the aforementioned image. In this textbox, the persons name and title are shown, on another form it will just be a listing of names separated by a semi-colon.

[NeoPa]

Tuxalot (From post #3):
A - I are all true.

Point A was that all controls were unbound. How can I possibly help you if I cannot rely on what you say. I feel like I'm drowning in quicksand. Every time you post anything you contradict one or more points from previous posts. I think we need to get focused here pretty soon or this thread will die a death. I don't think you're actually trying to mess me around, but I do think you're doing a pretty poor job of avoiding it.

[tuxalot]

I am trying to learn Access and I thought this was a place I could get some help. My last post, was that helpful?