Creating If statements using a for loop for an array of check box controls

[JGalletta]

I think this post will be an exciting learning experience for me.. so here it goes:

I have a form which opens a report based on criteria selected on the form. (This form is used solely to filter, sort, group a report prior to opening it.)

The array of check boxes on the right are Named from top to bottom G1-G12, S1-S12, and D1-D12. G,S,D for Group, Sort, Descending respectively.

What I'm trying to accomplish might be obvious to some of you at this point. I'd like to systematically create the If statements for each check box that creates/appends grouping and/or ordering criteria for the report this form opens when the user clicks "OK".

For example in plain English:

When the user clicks "OK", if a "G" check box is selected, create the grouping criteria for the report (if this needs to be done in an openargs statement, so be it, that's not my main concern at this point). The same would follow subsequently for the sorting and descending check boxes.

I'm thinking the basic code might look something like this:

Dim intcount As Integer
intcount = 1
For intcount = 1 To 12
    If Me.G[B][U](Append/concatenate value of intcount here? - How?)[/U][/B] = True Then
        '[I]Add grouping, sorting, descending criteria to report openargs or whatever method works here..[/I]
    End If
Next intcount

I'm open to any suggestions about different ways to do this, but I'd like to keep the design of this form the way it is. What are your thoughts about what I'm trying to do, and how can I accomplish this?

 

[mdlueck]

    If Me.G[B][U](Append/concatenate value of intcount here? - How?)[/U][/B] = True Then

For this bit I would suggest...

"Using a Variable or Number to Refer to a Field or Control"
http://wiki.lessthandot.com/index.php/Using_a_Variable_or_Number_to_Refer_to_a_Field_or_Control

And for building the WHERE clause, concatenate with an IF statement checking the existing WHERE clause based on if something is in it already (inject the AND SQL keyword) or simply append the first condition.

air code...

If strSQLWhereClause = vbNullString Then
  strSQLWhereCluase = "ColA = 'xyz'"
Else
  strSQLWhereClause = strSQLWhereClause & " AND ColA = 'xyz'"
End If

Once all of the way through the column list, use the strSQLWhereCluase variable to build the complete SQL query.

*** Oops, I hard coded ColA... use the same dynamic field name look-up code that the link I pasted above suggested.

Obviously if you are dealing with mixed string / numeric data, then you need to step through conditionally wrapping the value in quotes or not. etc...

 

[JGalletta]

Perfect. Thanks a million!

 

[NickHa]

For generic control handling, you could use something like this:
Dim ctlItem As Control, bytNo As Byte
For Each ctlItem In Me.Controls
If ctlItem.ControlType = acCheckBox Then
Rem following assumes the remainder of the checkbox name is numeric
bytNo = Val(right(ctlItem.Name, Len(ctlItem.Name) - 1))
Select Case left(ctlItem.Name, 1)
Case "G"
Rem do whatever you need for each numeric label value for G in bytNo
Case "S"
Rem do whatever you need for each numeric label value for S in bytNo
Case "D"
Rem do whatever you need for each numeric label value for D in bytNo
Case Else ' not interested
End Select
End If
Next

You could add a unique tag value (control.Tag) to each control you want to handle programmatically and then use that value to direct your logic (instead of relying on the control name).

 

[mdlueck]

Thanks a million!

@JGalletta, glad to have been able to provide assistance.

 

[JGalletta]

I'm liking the Case solution as well. I'll combine the two.

 

[JGalletta]

This is what I ended up using.

Dim ctlItem As Control
Dim MyGroup As String
Dim MySort As String
MyGroup = ""
MySort = ""
For Each ctlItem In Me.Controls
    With ctlItem
        If .ControlType = acCheckBox Then
            Select Case ctlItem.Tag
                Case "G"
                    If .Value = True Then
                    MyGroup = MyGroup & C(Right(.Name, Len(.Name) - 1))
                    'Following line shows message box to ensure code is processing correct values during testing.
                    'MsgBox Right(.Name, Len(.Name) - 1)
                    End If
                Case "S"
                    If .Value = True Then
                        If Len(MySort) > 0 Then
                            MySort = MySort & ", "
                        End If
                        MySort = MySort & C(Right(.Name, Len(.Name) - 1))
                        'Following line shows message box to ensure
                        'code is processing correct values during testing.
                        'MsgBox Right(.Name, Len(.Name) - 1)
                    End If
                Case "D"
                    If .Value = True Then
                        If Len(MySort) > 0 Then
                            MySort = MySort & ", "
                        End If
                        MySort = MySort & C(Right(.Name, Len(.Name) - 1)) & " DESC"
                        'Following line shows message box to ensure code is processing correct values during testing.
                        'MsgBox Right(.Name, Len(.Name) - 1)
                    End If
                Case Else
            End Select
        End If
    End With
Next

The group column only allows one selection so MyGroup doesn't need concatenation with 'and'.

Hope this makes sense.

 

[JGalletta]

And this is the code used to only allow one selection per column or row of controls.

Private Sub Form_Load()
    For Each Control In Me.Controls
        With Control
            If .ControlType = acCheckBox Then
                Select Case .Tag
                    Case "G", "S", "D"
                        .OnClick = "=SetChecks(" & .Name & ")"
                    Case Else
                        .OnClick = "[Event Procedure]"
                End Select
            End If
        End With
    Next
End Sub

Function SetChecks(ChkName As Control)
If ChkName.Value = True Then
    For Each Control In Me.Controls
        Select Case Control.Tag
            Case "G", "S", "D"
                If Left(Control.Name, 1) = Left(ChkName.Name, 1) Then
                    If Right(Control.Name, Len(Control.Name) - 1) <> Right(ChkName.Name, Len(ChkName.Name) - 1) Then
                        Control.Value = False
                    End If
                Else
                    If Right(Control.Name, Len(Control.Name) - 1) = Right(ChkName.Name, Len(ChkName.Name) - 1) Then
                        Control.Value = False
                    End If
                End If
            Case Else 'Ignore
        End Select
    Next
End If
End Function

 

[vbaInet]

Few points:

1. If I were you I would change S and D to simply S and use a Radio button grouped in a frame. Sorting or Descending should actuallly be Ascending or Descending and because there can only be one option two radio buttons in an Option Frame is more logical. It's either that you enforce it progamatically, but why bother if you have a control that does that for you already.

2. The code in your last post is heavy. Why loop through all the controls everytime a control is clicked? This kind of form should not be real-time. I would put a "Confirm" or "OK" button on the form which would run the function when clicked.

 

[JGalletta]

vbaInet,

Thanks. That's part of why I posted the code up as well. Thanks for being somewhat of a guardian for me on my venture through these forums. I'm still a bit of a rookie, but I'm making do with everyone's help.

Thanks again.

 

[vbaInet]

No problemo! Lots of good guys (and girls) on here helping out when they can.