Toggle Buttions multiple selections

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?

Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
move them outside of the option group.  By definition, an OG can only have one valid value.
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.
Chuck LoweAuthor 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) ?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rey Obrero (Capricorn1)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.value
        end if
   end if

Rey Obrero (Capricorn1)Commented:
you can also use the click event of the toggle button

private sub toggle1_click()
if me.toggle1=-1 then
   'other code here
end if
end sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Chuck LoweAuthor Commented:
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.
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
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.
Chuck LoweAuthor Commented:
This was the easiest one to implement per company standards and user specs.
Thanks Rey
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.