Loop through multiple Toggle Buttons on a Form to create Query string

[ItsMe]

When I looked at your sample DB I did not see anything that is outside of a basic search form. What is it that you are trying to accomplish, other than filtering data?

[RPACDN]

Yes, it is a basic filtering. But as I mentioned, I am looping through 40 plus toggle buttons using the loop that you have suggested and that is also working fine. As you can see in the example, all I am trying to accomplish is to filter the data and then display exactly as I have demonstrated in the example. The real problem is on how to efficiently handle the 40 plus toggle buttons.

The only piece missing is that once we determine that a button is "selected" by the User based on it's tag, how do we then determine/associate the two characteristics for each button, that is the associated "field name" and the associated "search string"?

Example:
- if tglGermany is selected, the associated fieldname in the table will be "Country" and the associated search string for the sql search will be "Germany".
- if tglSalesRep is selected, the associated fieldname in the table will be "ContactTitle" and the associated search string for the sql search will be "Sales Representative".

Hope this helps.

Apologize for the confusion, but I think the example gives you a better understanding of exactly what I am trying to do. I already have the code that you suggested in there, so now it is just a matter of modifying it to determine the "field name" and the "search string" for each button pressed.

[ItsMe]

Review post 10 and 12 then
Start here...

if tglGermany is selected, the associated fieldname in the table will be "Country" and the associated search string for the sql search will be "Germany".

Do not name any control tglGermany. Instead, name it Germany. Pass the name of the control to your strWHERE, as criteria.

Pass the name of the control to the criteria, only, if the control's value is -1

                If ctl.value = -1 then
                     strWHERE = strWHERE & "(Customers.[Country]= '" & ctl.Name & "') AND "
                End if

[RPACDN]

Yes, I suppose I can take that approach to pass the "search string" to strWHERE, but still the "field name" (such as Country, ContactTitle etc) remains to be updated depending on the selected button!

 If ctl.value = -1 then 
            strWHERE = strWHERE & "(Customers.[Country]= '" & ctl.Name & "') AND "
         End if

[ItsMe]

but still the "field name" (such as Country, ContactTitle etc) remains to be updated depending on the selected button

There is not any need to update field names or make any edits to tables. Use different tags for the controls associated to different field names.

[RPACDN]

Okay, I made some changes to my approach and I think I have a way forward on this now. For anyone who is interested in the solution, see the code changes below that I made to the sample database.

The only drawback or limitation is that you can not use the tag control of your toggle buttons for anything else in the design. In some cases, designers use that for displaying hints to the User etc., but that won't be possible, because this script solely depends on the value in the tag field. Again, another reason why I would've preferred to use an array.

I changed the on event click for each button as follows:

Private Sub tglOwner_Click()
If [tglOwner].Value = -1 Then  ' If Owner Selected (pressed)
    Me.tglOwner.Tag = "include_ContactTitle"
Else
    Me.tglOwner.Tag = ""
End If
End Sub
Private Sub tglGermany_Click()
If [tglGermany].Value = -1 Then  ' If Owner Selected (pressed)
    Me.tglGermany.Tag = "include_Country"
Else
    Me.tglGermany.Tag = ""
End If
End Sub

Then updated the search on click event as follows:

Private Sub tglSearch_Click()    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Dim strFieldName As String                    
    Dim strSearchString As String

    Dim ctl As Control
        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "include_") <> 0 Then
            strFieldName = Right(ctl.Tag, Len(ctl.Tag) - 8)  ' strip out include_ from the string
            strSearchString = Right(ctl.Name, Len(ctl.Name) - 3) ' strip out tgl from the string
                strWHERE = strWHERE & "(Customers.[" & strFieldName & "]= '" & strSearchString & "') AND "
            End If
        Next ctl

    lngLen = Len(strWHERE) - 5
    If lngLen <= 0 Then     
        MsgBox "No Filter Criteria Provided", vbInformation, "Nothing to do."
    Else                   
        strWHERE = Left$(strWHERE, lngLen)

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWHERE
        Me.FilterOn = True
    End If
    strWHERE = ""
End Sub

Thanks to "ItsMe" for the time and patience to work with me on this solution.

If I come up with a different solution, I will post it here. I can upload the updated database, if anybody is interested.

Thank you to everyone who replied. I know I wasn't very good in explaining my problem originally!

[RPACDN]

If anyone is interested in seeing how arrays can be used to cycle through the toggle buttons, take a look at my other post
https://www.accessforums.net/program...tml#post278354

Just one of many ways to process multiple buttons.