Create string from selected controls on a form

[tuxalot]

I have a form with five columns and 16 rows of controls. From left to right each row consists of a label, a bound and locked textbox populated from a query, two checkboxes and another unbound textbox. I've attached a screenshot (staff-form.jpg) of the form's state On Load. As you can see the right checkbox as well as the right textbox are visible = false. For explanation, I will use the following as control names:

Name checkbox = chkName (located under the label Name)
Title checkbox = chkTitle (located under the label Title, currently hidden in this example)
Title textbox = txtTitle (located under the label Please Include Title, currently hidden in this example)

This form is opened by double clicking a textbox on another form and is used to select the persons who have taken part in a recent survey at a hotel. If chkName for Row 1 is ticked, chkTitle for Row 1 becomes visible. If chkTitle is ticked, the textbox under the label "Please include Title" becomes visible for Row 1. Unticking these reverses their visibility. txtTitle has a default value that can be overwritten. In the case of Tom Smith the default value for txtTitle is General Manager. In the case of Tim Allan, who works in the same Department as Bob Jones, the default value for txtTitle is "What is this person's title?" in which case the appropriate title would be keyed in by a user. This functionality is currently working.

I would like the items selected to create a string that will be placed in the calling textbox. A partially completed form will help explain what I would like to have happen next. In the attached screenshot, Bob, Tim and Stan all participated in the survey. The user wants to show Bob and Tim's titles but not Stan's. Tim's title was keyed in as Assistant Director. Please see the attached screenshot (staff-form-filled.jpg).

The resulting string for this screenshot should be:

Bob Jones, Director of Food and Beverage; Tim Allan, Assistant Director; Stan Laurel

Is this possible? Please help me understand how I can achieve this.

Thanks to all for your assistance with my project.

** Edit **
Staff-Form.jpg

Staff-Form-Filled.jpg

[ADezii]

1. Iterate thru all the Controls on the Form, using strict Naming Conventions to identify Controls specific to each Row, as in:
   

   Row 1: lblPos1, txtStaff1, chkName1, chkTitle1, txtTitle1

   Row 2:          txtStaff2, chkName2, etc.
    

2. For each Row, if both Name and Title are checked, start building a String concatenating the Staff Text Box & "," & Title Check Box & ";".
3. If just the Name Check Box is selected, build the String using the Staff Text Box only & ";".
4. If the Name Check Box is not selected, do not Add to the existing String.
5. In a Related Thread, I previously indicated to you how this can be accomplished via an Attachment.

[tuxalot]

Hi ADezii,

Arghh! I cannot seem to get my head around this but I persist. I am interpreting your comment here to mean build a series of if then statements for each row and build the string from that? That makes sense, but I thought there would be a way to loop through the controls using a combination of control names and tag values. While your attachment did just that, it used a common portion of the control name to build the string:

strBuild = strBuild & Mid$(ctl.Name, 4, 1) & "1," & Mid$(ctl.Name, 4, 1) & "3;"

What I need is the value of the name and title textbox depending on the checkbox selections. Please explain how I can modify the attachment provided to yield this.

Thanks again.

[ADezii]

I should be a little slow in work today, if that is the case, I'll construct a simple Demo to precisely illustrate how this can be done.

[ADezii]

The following Code, using little more than a Looping Structure and very strict Control Naming Conventions, should precisely illustrate how you can generate the requested String. All extraneous elements have been ignored so as to clarify the question at hand. I'll post the Code, but I strongly suggest that you Download the Demo (Attachment) that I have created for you, so that you may see exactly what is going on. Good Luck.

Const conNUM_OF_ROWS As Byte = 8        'Change to 16
Dim bytRowCtr As Byte
Dim strBuild As String
 
strBuild = ""       'RESET
 
For bytRowCtr = 1 To conNUM_OF_ROWS
  If Me.Controls("chkName" & CStr(bytRowCtr)).Value And Me.Controls("chkTitle" & _
                  CStr(bytRowCtr)).Value Then
    strBuild = strBuild & Me.Controls("txtStaff" & CStr(bytRowCtr)) & "," & _
                          Me.Controls("txtTitle" & CStr(bytRowCtr)) & ";"
  ElseIf Me.Controls("chkName" & CStr(bytRowCtr)).Value Then
    strBuild = strBuild & Me.Controls("txtStaff" & CStr(bytRowCtr)) & ","
  End If
Next
 
'Strip Trailing ',' or ";"
Me![txtResults] = Left$(strBuild, Len(strBuild) - 1)

P.S. - There are other approaches to solving this problem, but I personally feel that the one I have chosen is a good and efficient Option.

[tuxalot]

ADezii,

Thank you so much for taking the time to help me. I really appreciate it. You always go above and beyond. I am looking forward to studying your solution and it looks like it will give me exactly what I need.

Thanks again

[ADezii]

You are quite welcome, tuxalot. Let me know if the last Attachment solves the problem.

[tuxalot]

The solution works great. Now I need to get this string to be placed back to the calling textbox. There are a couple forms that will use this solution. I'm sure I can figure this one out.

I want to understand *why* your coding works. What would I search for to learn about this coding?

Thanks so much for your efforts on this.

[ADezii]

I want to understand *why* your coding works

The three elements that make this Code work are:

1. The Looping Structure for which the number of Iterations is indicated by the Number of Rows
   

   Const conNUM_OF_ROWS As Byte = 8      'Change to 16 

2. Very strict Naming Conventions, which specifically names Controls by a combination of Type/Row.
3. An understanding of how the Form's Controls Collection works.

[NeoPa]

I'm sorry I'm late to the party here boys. I actually got busy with proper work today so that held me right up :-D

Tuxalot:
I want to understand *why* your coding works. What would I search for to learn about this coding?

Great attitude Tux. I'm getting more impressed by the day :-)

The best answer I can give to that is to study it; Follow it through in your head; Run through scenarios of set data to see what you would expect to happen. That's what I generally do, and if I come across anything I can't follow then ask for help. I actually check with the Help System first as I find answers to nearly everything I don't know there, but failing that post a question. If it's directly related to code in this thread it can even be included here, but there is always the option of posting a new question to ask what a piece of code means (I'm talking about specific lines rather than whole swathes of code as some members seem to like to do).

I haven't had a chance to interpret the question yet as I'll need to see the pics first and read the question in relation to those, but I'm just editing your post now to do that (show the pics in the post). BTW You can do that for yourself too by using the [ IMGNOTHUMB ] tags (No embedded spaces). Other possible tags that you can use can be found at BB Code in case of interest.

[NeoPa]

Tuxalot:
Now I need to get this string to be placed back to the calling textbox.

I generally handle that by passing a string value to the form as it's opened (in the OpenArgs parameter) which contains two values separated by a comma. The first is the name of the calling form, and the second is the name of the control you want the value returned into. With that information available the called form can determine the value (as you've already seen code handle) then populate the referenced control and set its value. Let us know if you need more pointers to handle this bit of coding.

As for your overall question, I still find I'm in the dark about certain items :

1. Why are there only some rows with Name CheckBoxes by them (They only appear to exist for the top five rows)?
2. It seems from ADezii's code that you report works, that the full names of the various controls are simply what you've stated as a stub followed by the value of the number of the row they appear in (chkName1, chkTitle1, txtTitle1, chkName2, chkTitle2, txtTitle2, etc). How come the code doesn't crash when it reaches row six then, as it appears from your pictures that no such controls exist on the form. I would expect the code to crash in such circumstances.

It all seems much clearer overall mind. Certainly a question thread we can work with.