MS Access Option Groups - How to Use Radio Buttons

i'm your host sean mckenzie thank you for joining me once again on my channel on data analytics and data engineering in this episode we're going to return to our microsoft access playlist and we're going to talk about option groups and how handy option groups are but also how they're a little bit tricky to use and so that's one of the reasons why i did this video is that they're pretty tricky so what are option groups well option groups are radio buttons when you have mutually exclusive choices inside of a box with little radio buttons that you can choose and when you choose one the other ones become deselected and and so it gives you a nice sort of easy way to to get selections from the user and so what we're going to do this time is we're going to create an option group and we're going to show how to get the values of the selected items or selected item out of the option group so that we can use it inside of our queries or inside of our code so without further ado let's get to our option groups okay to get started we're going to use an access file that we used already a couple of times and we're going to open a form in design view and then we're we're going to go to the toolbox and we'll click on the zed there that indicates the option group and then when the wizard comes up if it does you can hit cancel and we're going to learn how to use the option group in a much more realistic way that will teach you how it works so i'll go ahead and you can see in our property sheet there you can right click on the option group and go properties to get this window on the right side and you can see that the i'm changing the name to something that makes sense we'll call it ogrp main for option group main and we can go back over and re rename our label for that as well and uh and that'll give us our option group itself and this is important to to see that we have the group which is kind of like a frame and and then we can go ahead and start adding our options to it it's important to note that you need to grab the option separately so you'll click on the on the option and then you'll see that the option group will go black and you need to drop the radio button in this case onto the option group when it turns black and that'll allow you to have your first option and so what we'll do here is we'll we'll give our radio button a name we'll call it opt and we'll we'll use colors for this i think so what i'll do is i'll call it opt green and that'll allow us to have our first option which says that our is our value is or our selection is green but the option value is going to be one you can see that option groups they typically work with a a number in the background and so our first one says green when when we click it it'll have it'll make the option group have a value of one for each of our following options we can do the same thing we can click on the radio button uh and drop it on to our option group uh in our in our form and that's going to give us another option so we'll we'll drag that one into into place underneath green and we'll call this one opt red and we'll and you can see that it has a value of 2 for the for the option value so that's what's actually going to display in the background whenever this option is selected and then we can do the same thing for let's say yellow we'll grab an option group and we'll drop it on there and we'll call this one yellow and uh so we'll change the label to yellow as we're doing right here and then we'll change the name to yellow after we drag it into place there we can you always want to change your names so that you can troubleshoot later and it'll be very obvious if there's some kind of error or something it'll say it'll actually tell you instead of option six is got a problem it'll say option yellow has a problem and that means a lot more to you so you can see that one has value of one two and three and we've got it set for green uh red and yellow and then uh we could do say one more so we'll grab a radial button and drop that on there as well for blue and uh so we'll rename the label and we'll call that blue and then we'll click on the the uh the option uh itself and we'll we'll change the name on that one as well we'll call it opt blue and you can see that has a value of four so whenever the option group changes to blue it'll return a four and i'll show you after we get this ready i'll show you how to to see that in various different ways and once i've resized our option group to look kinda normal with our selections inside i'll save our form and then we can move on to the next step and then after i save that i'll drop a text box onto the form just so that we can see it so we'll go up to our toolbox click on text box and then we'll just drop that onto the onto our form and this is just to to see our output value for uh when we click on our option group and then from there once i drop the text box on there change the name of the label i can add forms exclamation option form exclamation and then ogrp main which is the name of our option group and you put an equal sign at the front so that it looks for that as as the form is running so you can go to the form view and then you can click on each of the options you can see that it changes the value in the text box now this is very handy this is looking up the value just on your text box but you can also look at look up the value in several ways so that's just a text box look up you could use it for various things on your form and and things like that so from here what we could do is we could say well how could i use that value maybe in a query and you could do that as follows so from here we've got our option group set up nicely we've got our text box here that is collecting the value if i extend that you can see the expression that i'm using to look up the uh the the value in the option group and we can use that almost exactly the same expression in a query that that'll look up our option group value and and so what we'll do from there is we'll go into our create and we'll go query design and you can just close this window here and then drag your table in this case i created a table called option test and it has the color stored as the numeric value as one of the fields and so we'll go select star and then for a criteria we'll select the color field and then we'll paste in the expression that we had on our form but we're going to remove the equal sign from the criteria and then from there we can see that it's going to look up the value whatever's on the option form and it's going to to get that in our query so if i select green and i go to our query and i run it or look at it in datasheet view you can see that the color is set to number one which is green if i go back and change it to red and then go to my query that we created and i look at datasheet view you can see it's set to 2. and so the query will change with that parameter in there with the criteria it will look at our option group each time so that you can i retrieve the value for what whatever it is that you want to look up so we can look at green items or blue items if i choose the last one which i created a couple of blue items in that table as well so jeans and socks were blue and so they've got option color number four which is blue and that's how you can use the option group in your query as a parameter and that's very handy for all kinds of different uses [Music] to be able to use your expression in multiple different places whether it's in a text box on a form or in a query you can look up that option group anywhere and you can also look it up using your if you're writing some code so in this case we can use the immediate window and i can just put the expression right into the immediate window and remove the equal sign instead we'll put the question mark and hit enter and that's going to return the value that's currently selected on that form which is useful for your code so you could actually put that into a subroutine or a function to look up uh the value that's been selected by the user on that form when you go to run something so that's a very useful uh technique for uh for using code and so what would that look like if i wanted to write some code well you can use the after update event if you click on the properties and go to after update of your option group not the options with the option group click on the ellipsis and then select code editor and then that will give you the after update event of the option group itself which is very very handy so it means i can do something or run some code or or whatever after the when when the user changes the value in my option group and that's very handy for you because you can have all kinds of things that you can do you can open a report or or you can update some data or whatever so in this case what i'll do is i'll select case and then i'll say me and then the ogrp main which is uh the option group and i'll say if it's if it's number one then let's do a message box that says the color is green if i do case 2 which is yellow then i'll say the color is yellow and and then i'll do the same for yellow and red and blue and so you can see that after the user clicks the option group it's going to evaluate what the value in the option group is and then it's going to do some different commands depending on on what was selected in the option group itself so this gives you a nice idea of how you can use the after update event with your option group in order to do useful things you could also load that value of me exclamation or grp main you could load that into a variable and then you could use it in a bigger procedure and things like that so once we get all our case statements in there we'll put a case else just in case something funky happens we'll say not selected we'll put our end select on there and then now what we have is we have uh a bunch of different cases of what happens after we use our option group so i'll save those i'll save those items and then we can go try it out and see how it works so as you can see we can go to our form and we'll open it in form view when i click on red now you can see it says the color is red in a pop-up message if i say yellow it says the color is yellow if i say blue it says the color is blue or green the color is green and so what that does is it allows us to to attach some code to our option group that makes it very handy and this is a really nice way of implementing some solutions using option groups in microsoft access i hope you enjoyed today's discussion on option groups and microsoft access and i hope that you can use this technique in your in your project if you like what you saw today please give the video a thumbs up and make sure to subscribe to the channel and also click the bell when you see the bell so that you'll be notified of any new content that i put up on on the channel if you have any questions or comments make sure to leave those in the comment section below and i'll make sure to try and answer any questions that you might have about about the content that we put up today have a great day have a safe day and i'll catch you next time you

Channel: Sean MacKenzie Data Engineering

Views: 2,696

Rating: 4.9111109 out of 5

Keywords: ms access option groups, ms access radio buttons, vba radio buttons, ms access forms, ms access expressions, radio buttons, option group, microsoft access, sean mackenzie data analytics

Id: eg4RUEsPPiw

Length: 14min 33sec (873 seconds)

Published: Fri Nov 27 2020