Toggle Buttions multiple selections

Chuck Lowe
Chuck Lowe used Ask the Experts™
on
I have 3 Toggle Buttons in an option group. I.E. only one button is enable and the .Value is used in a query.
Now the use wants to be able to pick 1, 2 or all 3 buttons and do the query selections off of the .Value each button has.
I can do the query.
What is the best way to allow multiple toggle buttons to be picked?

-Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
move them outside of the option group.  By definition, an OG can only have one valid value.
Distinguished Expert 2017

Commented:
You need to change the "look" of the control also.  Option groups, which are usually rendered as radio buttons are intended to make you think of a radio.  When you push one button, you get that station.  It doesn't make sense to be able to press multiple buttons at once.  If you want to go with multiple selections, you need multiple controls.  In this case, I would use checkboxes because people understand that checkboxes stand alone and so in a properly designed form/document multiple checkboxes may be checked at the same time.

Alternatively, you could use a multi-select list box.  I would recommend the listbox if you forsee the choices being expanded.  Using individual checkboxes would require changes to the form and query if you wanted to add/change the options.  However, with a listbox, you can simply add a new choice to the RowSource table and no programming is required.  The downside is that they are a little harder to set up initially since you would need a code loop that goes through the listbox collection and assembles an In() clause with all the selected values.  They you would build the SQL to include this In() clause.  Remember, that due to the way querydefs work, you can't simply use the In() clause as an argument since changing the In() clause would be considered a physical change to the queyr and so would require the query to be re "compiled".  So, to use the In() clause, you need to build the SQL on the fly.  You can then save it as a querydef or pass it into a form or report and the form/report can take the SQL String and save it to its RecordSource in the Load event.

Author

Commented:
So I cut and past the buttons outside the group. Delete the group. The .values go away.
So I need to check if the button is clicked (True) and set a named value now for the vba instead or 1 2 or 3  (data value) ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
you have to iterate to all controls to check ifor the value of the toggle button that is outside of an Option Group

dim ctl as control
for each ctl in me.controls
     if ctl.controltype=actogglebutton then
        if ctl.value=-1 then
            msgbox ctl.name, ctl.value
        end if
   end if

next
Top Expert 2016
Commented:
you can also use the click event of the toggle button

private sub toggle1_click()
if me.toggle1=-1 then
   'codehere
  else
   'other code here
end if
end sub
Distinguished Expert 2017

Commented:
You are going from an option group (concept 1) to individual checkboxes (concept 2)  --- start from scratch.  Everything is changing and it will be easier.  Cut and paste isn't going to save you any work.  Checkboxes are true/false rather than a numeric value so you don't need checkbox 1 to return a 1 and checkbox 2 to return a 2, etc.  Now that you have three controls rather than one, your code will refer to each control separately.  
If Me.chkbox1 = True Then
    'do something
End If
If Me.chkbox2 = True Then
    ' do something
End If
If Me.chkbox3 = True Then
    'do something
End If

Open in new window

Or in a query
Select ... From ... Where somefield = Forms!yourform!chkbox1 OR somefield2 = Forms!yourform!chkbox2 or somefield3 = Forms!yourform!chkbox3;
If the data is bound, you also need to add additional columns to your table in which it would be saved.

Author

Commented:
@Pat
I do need to return the 1,2 and 3. These coincided with DocumentType values on my table. I understand I will need to recode.
@Rey
I will try this option. This allows me to keep the toggles which the users like and are used to and allow me to set my values or even booleans and send as parameters to my queries.

-Thanks all
Distinguished Expert 2017

Commented:
Select ... From ... Where
(somefield = 1 AND Forms!yourform!chkbox1 = True)
OR (somefield = 2 AND Forms!yourform!chkbox2 = True)
OR  (somefield = 3 AND Forms!yourform!chkbox3 = True);

If you use Toggle buttons Then
Select ... From ... Where
(somefield =  Forms!yourform!Toggle1)
OR (somefield =  Forms!yourform!Toggle2)
OR  (somefield =  Forms!yourform!Toggle);

I do not recommend using radio buttons when multiple selections are allowed even if that was how the options were previously presented.  In the previous presentation, the options were mutually exclusive which is implied by the radio button.  Checkboxes  imply multiple selections.  Toggle buttons are ambiguous so a user might try to press more than one, but he might not.  There is a great deal of written material on user interface creation and expectations.

Author

Commented:
This was the easiest one to implement per company standards and user specs.
Thanks Rey

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial