Splitting a Text Field and uusing the data to change field properties

[mcaliebe]

I have a table with 20 records. I have a form that has 6 fields on it and depending on what record is selected determines the number of fields that are use.

For instance,

Type NbrConnections CallOuts

Elbow 2 1,3
Tee 3 1,2,3
Tapped Tee 4 1,2,3,6

I can probably nest 20 If statements to evaluate the "Type" field on the form and than tell it which of the 6 fields to make visible, however I was looking for a simpler solution.



I thought perhaps there was a way to use the text in the CallOuts fields to create the necessary VBA statements to change the field properties ie

Elbow 1,3
Split 1,3 into 1 and 3
Combine to create fields names "textbox"&"1" and "textbox"&"2"
Now set the properties for textbox1.visible and textbox2.visible

Is this possible or is there a better way of handling this?

[andy49]

How about setting the tags of your form fields as either 1,2,3,4,5,6



Then split the the1,3 into 1 and 3

Then Something like
For each ctl in me.controls

For I = 1 to 6
If ctl.tag=myarray(I-1) then ctl.visible=true
Else Ctl.visible = false
End if
Next
Next

[mcaliebe]

Andy

What do you mean split the 1,3 into 1 and 3?

1,3 are in a text field in the table and on the form. Is Myarray the text field data?

[ssanfu]

Maybe something like this. I used part of the code Andy49 proposed.

EDIT: BTW, "Type" is a reserved word and shouldn't be used as an object name. Note that I used "ConType".
"Type" isn't really descriptive either. "Type" of what??? Think of the person that might have to follow you.......

[mcaliebe]

Thanks Steve. Yes, I can make this work. I just wish I understood more why it worked. I'll have to do some research.

[ssanfu]

Does this help?? (I added comments)

Private Sub Form_Current()
    Dim ctl As Control
    Dim I As Integer
    Dim myarray() As String
    Dim tmp As Integer

    'if nothing in the control, exit the sub
    If IsNull(Me.tbCallout) Then
        Exit Sub
    End If

    ' loop through all of the controls on the form
    ' to hide the 6 controls
    For Each ctl In Me.Controls
        '109 is a text box. 
       ' Could also use the AcControlType Enumeration "acTextBox" instead of the value of 109
        'The control tag property has a number in it.
        If ctl.ControlType = 109 And ctl.Tag >= 1 And ctl.Tag < 7 Then
            ctl.Visible = False
        End If
    Next

    ' use the split function to populate the array
    myarray = Split(Me.tbCallout, ",")
    ' loop through the controls
    For Each ctl In Me.Controls 'only want to check text boxes (109 is a text box.)
        If ctl.ControlType = 109 Then
            tmp = Val(ctl.Tag) 'convert the tag string to a value (number)
            If tmp > 0 Then 'if tmp is a 0, there was not an entry in the tag property of the control
                For I = 0 To UBound(myarray)
                'check if an entry in the array matches whatins in tmp
                    If tmp = Val(myarray(I)) Then
                        ctl.Visible = True 'if they match, display the control
                    End If
                Next
            End If
        End If
    Next
End Sub

[mcaliebe]

That helps a lot! Thank you.

Now, if I have additional text boxes on the form, does it matter?

[andy49]

Shouldn't do as other text boxes won't have a tag so the line starting

if tmp>0

will return false so the txt box will effectively be ignored.