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

[RPACDN]

I have a split form with over 40 toggle buttons for Users to choose their options.

I am wondering if there is a way to loop through all the toggle buttons in that form to determine which ones have been selected by the user so that I can create a Query string based on their selections.

The toggle buttons as an example: (My actual design is for a different design and have over 40 different toggle options. So please ignore if you see this is not the right approach for the example)

Level 1: Ford, Chrysler, GM
Level 2: Engine, Transmission, Interior
Level 3: Turbo, Automatic, Manual, Leather, Fabric
Level 4: <so on for more sub-systems>

I have named the toggle buttons as tglFord, tglChrysler, tglGM, tglEngine, tglAutomatic etc.

When the User press the Search button, I want to loop through these toggle buttons and select the ones that are set to -1.

So, in this example, the loop will yield
[tglFord] = -1
[tglTransmission] = -1
[tglAutomatic] = -1

Based on that result, I will have to create the Query string.

Ideally I would like to use an Array that would establish the relationship between the toggle button name and the fieldname. (I may not be explaining the array definition below accurately)


Array myArray [0] [tglFord, Ford]
Array myArray [1] [tglChrysler, Chrysler]
Array myArray [2] [tglGM, GM]... and so on.

So, when I loop through the buttons, if I see that tglFord is pressed, then I will add the fieldname "Ford" to the SQL query string and keep adding the other ones until I have collected all the selected buttons.

Any help would be appreciated.

Thank you.

[ItsMe]

I use a For Each statement to assess the .Tag property of the controls.

Something like this snippet...

Dim ctl As Control

        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "ImportantControl") <> 0 Then
                'Do Something here!
            End If
        Next ctl

[June7]

Over 40 toggle buttons? Could probably be reduced to 5 combo or list boxes.

[RPACDN]

Thanks for the reply. I am bit of a novice in this field.

So, the "ImportantControl" in your snippet - is that an access control name or is that where I put in my toggle button names?

I am currently using -

if ("Forms![frmStructureSplit].[Form]![tglFord] = -1) then

to determine if tglFord is selected, as an example.

If you could create a sample loop using my sample fields, that would clarify it better for me.

Thank you.

I use a For Each statement to assess the .Tag property of the controls.

Something like this snippet...

Dim ctl As Control

        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "ImportantControl") <> 0 Then
                'Do Something here!
            End If
        Next ctl

[RPACDN]

Possibly, but with toggle buttons, it seemed easier to display the choices and with color change, easier to highlight the ones selected etc.

I am displaying the screen to a group of people who are not necessarily computer friendly. Besides, for my programming issue, whether it is combo box or list box, the selection options and the choices that I will have to process remains the same.

Over 40 toggle buttons? Could probably be reduced to 5 combo or list boxes.

[ItsMe]

...So, the "ImportantControl" in your snippet - is that an access control name or is that where I put in my toggle button names?

Most controls, if not all of them, have a Tag property. You can view the Tag property from within the Property Sheet while in Design View. The Tag property accepts literal text. You could place "ImportantControl" in the Tag property or something else, like "NotImportatnt" or "Cake".

The code I provided will loop through all of the controls within the form, looking for a tag that has ImportantControl within. There may be an issue with the split form but I would give it a try, as is. All you need to do is add some code where the comment is. Example code that would affect the current control would be.

ctl.value = -1

or

ctl.forecolor = 255

[RPACDN]

OK, so you are suggesting that I set a tag property for every toggle button that the user selects like "userSelected" or something like that and then loop through all items to select the ones that has the tag property set to "userSelected".

Okay, I can try that. Secondly, is there an easy way to relate the button names to fieldnames in the table. I would prefer not to standardize names based on what is in the table, because the table fieldnames might change. But if I can use an array to establish the relationship between, say toggle button "tglFord" to fieldname "FordCars", "tglAutomatic" to fieldname "Automatic Transmission" etc., that would be good. Not familiar with how to define a two dimensional array.

Once that array is established, I can then loop through the controls, find out which ones have the tag "userSelected" and then keep adding the related fieldnames to the sql string in the same loop.

If you are trying something out, if you could kind of put this idea in to your loop, that would be helpful.

Thank you for your time.

[ItsMe]

I am having a hard time understanding what it is you are building. You do not have to relate the Control Names to a Field name in a table if you do not want to. Are you using the toggle button's control source to bind the control to a field? I am having a hard time imagining a scenario where a dynamic control source for a Toggle would be beneficial.

To dynamically bind a control to a field would require an intermediate level understanding of VBA. You would have to go after the Table Defs via DAO. I don't think a multidimensional array would be of any benefit there. I will take a look at your other posts to see if I can glean some insight. If I had to place a bet right now, I would bet you do not have a normalized data structure and you are working harder than need be.

EDIT:
Like June mentioned, you are probably using the wrong control. Instead of using toggle button controls to build a multidimensional array. Use a Combo or a List Box control. The only caveat is you should have data in a table to support your combo/list control. The multidimensional part is seamless and not really something the developer or user interacts with. The data in the table is what builds the combo ...

[RPACDN]

I agree with both of you that there may be other ways to provide the filter functionality. But in my case, I am required to design the function in this manner, because there is more to the story than what I can explain here (and you wouldn't want to know)! So ignoring the part about the inefficiency of my approach (sorry!), I thought it would be best to explain the problem by showing an example. So I quickly put together a sample database. I don't have all the buttons working, but you will get the idea.

In the code, you will see that since I have to deal with 40 plus different toggle buttons, you will see that it would be desirable to reduce the redundancy (although I do not have the right to seek optimization, given the convoluted approach that I am taking :-)

Please see the attached example (.accdb file). Hope this helps.

Having said that I have special requirements to create it in the way it is created, I am certainly open to your recommendations on a better approach as well. The goal is to ensure that I can clearly present the Search options to the User and be able to clearly display what they have selected.

[ItsMe]

You could give a special name to each control that is a Boolean (Toggles). Each of these Toggle Controls would have a special Tag, "IncludeInString". A For Each Statemnt would evaluated the value of each control with the IncludeInString Tag. If the value was = to -1 then the .Name would be added to the string.


Untested...

Dim ctl As Control

        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "IncludeInString") <> 0 Then
                strWHERE = strWHERE & "(Customers.[Country]= '" & ctl.Name & "') AND "
            End If
        Next ctl

[RPACDN]

You could give a special name to each control that is a Boolean (Toggles). Each of these Toggle Controls would have a special Tag, "IncludeInString". A For Each Statemnt would evaluated the value of each control with the IncludeInString Tag. If the value was = to -1 then the .Name would be added to the string.


Untested...

Okay, I tried that and I think that brings me one step closer. So I made the following changes to the Click events.

Private Sub tglOwner_Click()If [tglOwner].Value = -1 Then  ' If Owner Selected (pressed)
    Me.tglOwner.Tag = "IncludeInString"
    strOwner = True
Else
    strOwner = False
    Me.tglOwner.Tag = ""
End If
End Sub

And then, added the loop that you suggested...

    Dim ctl As Control

        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "IncludeInString") <> 0 Then
                strWHERE = strWHERE & "(Customers.[Country]= '" & ctl.Name & "') AND "
            End If
        Next ctl

And that works fine, but only one problem - my search string strWHERE comes out as shown below.(For the example, I selected the buttons Owner & Germany)

(Customers.[Country]= 'tglOwner') AND (Customers.[Country]= 'tglGermany')

Here is where I thought an array might help. If I can setup an array that establish the relationship between the toggle button name and the field name, problem solved.

So for the example, I might need an array like

Array myArray [0] [tglOwner, 'ContactTitle', 'Owner']
Array myArray [1] [tglSalesRep, 'ContactTitle', 'Sales Representative']
Array myArray [2] [tglGermany, 'Country', 'Germany']... and so on.

So when I find tglOwner in the loop, I can insert "Owner" to the search string so that strWHERE will come out as

(Customers.[ContactTitle]= 'Owner') AND (Customers.[Country]= 'Germany')

I am not quite sure how to define such an Array and the loop to pick up the right values from the Array. But hope this clarifies your earlier doubt about my Array reference!

[ItsMe]

... If I can setup an array that establish the relationship between the toggle button name and the field name, problem solved....

So you are going to build an array and then do what with it? Then, you are going to loop through your array??? There is not any need for that.

The code I suggested will loop through the controls on a form. To make sure the code I provided does not loop through each and every control, assign tags to the controls you want to include.

The loop can determine the value and store the control name. My previous example does not determine the value. You will need to add an If Then statement to determine the value.

Dim ctl As Control

        For Each ctl In Me.Controls
            If InStr(ctl.Tag, "IncludeInString") <> 0 Then
                If ctl.value = -1 then
                     strWHERE = strWHERE & "(Customers.[Country]= '" & ctl.Name & "') AND "
                End if
            End If
        Next ctl

This code will negate the need for a Click Event Sub Routine for your 40+ Toggle Controls. You just need to make sure that each of the control names will work within your WHERE clause. So tglGermany is not going to be compatible with a field named Country. You might want to rename that control Germany.

[orange]

I recommend that you step back from trying to work with vba/Access and create:

- an English description of WHAT you are trying to do -- sort of the 30,000 foot overview of the business
- add more details to flush out the business rules
- review this tutorial on design
- create a logic diagram (flowchart) to show WHAT your "proposed toggles(40)" represent. My guess is that these break down into a few (4-7) topics where each has 5-8 choices from which you select 1. This seems to fit the cascading combo box construct, but it is too early (not enough business info yet) to decide that.
- create an ERD (model) and review it with others.

You really need a clear description of the problem, its scope and the associated business rules before designing the database. And you need a well designed and vetted database before jumping into Access and vba.
You may find the stump the model approach useful when reviewing your design.

Good luck with your project.

[RPACDN]

Appreciate your comments. I think the person who is trying to help me is getting the full picture of my issue now and we are talking the "same language" in terms of the problem and how to solve it. So I think within the next few more messages, we will be able to sort it out.

[RPACDN]

The code I suggested will loop through the controls on a form. To make sure the code I provided does not loop through each and every control, assign tags to the controls you want to include.

Yes, you are correct, that part works fine. I am assigning tags to the controls when the User press the button and I can pick them out using your loop.

The loop can determine the value and store the control name. My previous example does not determine the value. You will need to add an If Then statement to determine the value.

But here is where the problem is. As you see in my example below, there are three characteristics associated to each click -
- that is the button name (in this case "tglGermany")
- the field name in the table (in this case "Country")
- and search string for the SQL (in this case "Germany")

(Customers.[Country]= 'Germany')

You just need to make sure that each of the control names will work within your WHERE clause. So tglGermany is not going to be compatible with a field named Country. You might want to rename that control Germany.

Yes, but unfortunately as you can see the field name "Country" is applicable to more than one button, "Germany", "France", "Spain" so on. So I can not use the approach of renaming the toggle button to "tglCountry" and strip out "tgl" from it.

I guess I could assign names like "tglCountryGermany", "tglCountrySpain", "tglContactTitleOwner", "tglContactTitleSales Representative" and so on. But even that approach will be difficult, because some of the field names have spaces and some are really long like "Sales Representative" for example, and I do not have control over the field names (the data comes from another application).

So again, I can't think of an easy way to decipher those three characteristics for each button, other than something like an array. How else can we establish that relationship between the three characteristics of the button (ignoring whether this is the best approach or not)!