How to Make a Listbox in MS Access Part 2: Multi-select

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering and data analytics in this episode we're going to continue in our microsoft access playlist with part two of looking at how to use list boxes and in part one we showed how to create a list box and you bind it to some data and show some selections and then retrieve selections from the list box and in this episode we're going to show how to do multiple selections in a list box and how to get the values and retrieve those and store them back into our database so without further ado let's get to our microsoft access list boxes okay so to get started we're going to go back to this file that we worked with in our last episode where we created a candy table a candy order table with some orders in it and a form that allowed us to select some candy and as you can see we were able to bind it to our table and we had many items in there and we could select one of them at any given time and that worked really great so to continue on and look at our multi-select list box some things are going to change uh in the way that we handle uh the data and essentially what we're going to do is we're going to release the list box from its binding so we're going to unbind it and and we're going to change a few things so the first thing we're going to do is we're going to go in that other tab and under multi-select we'll select simple which means that items are just selected when or deselected as you click on them and the other multi-select style allows you to make it so that you can hold down the shift key and click on them but we're going to take the simple style and as you can see if we look at our table there in our orders there's a single field for candy id so we can only put one item in there because it's an integer and so that's not really going to work for us if we want to have you know like two or three items on one order and use a single list box for that so in our simple example here we're just going to store the candy id numbers as a string or as in a text field and we're going to put commas in between the numbers that's a very simple way of doing it there are many other ways you could create another table and you know store each item in a row but in this case we're going to do a very simple one we're going to go select our our list box and we're going to remove the field name from the control source and that's going to change it to unbound as you can see there and so what what we're going to do is now that we're unbound and we've got multi select in there we can go ahead and we can choose whatever candies we want from our list and as you can see in the when it's in simple mode uh you can just click on an item to select it and click on it again to deselect it and there are multiple selections in our list box which is what we want to see now just like we showed before if you use your control g you can open the immediate window and you can put in your question mark and you can ask you can request the value from from the list box just like we did last time but if we do it the same way as we did last time where we just asked for the value you can see it says null because the value of the list box itself is is now null but there are many selections in there and the way to find the value for those selections is to use the items selected property and so you can you can see we can get the count from that so that says there's three there's three items selected and so that means if we want to ask what those values are we can also we can also do that so i could say you know what is the the candy form uh list box for candy and i could say item selected and i could put a number in now we'll say 0 1 and 2 to make three items because that's zero based counting here so if i say zero that gives me number three i id3 which is the white chocolate and if i go to the table you can see that id number three is the white chocolate so it's picked up the first item as being selected if i ask for the other numbers i can find out those ones as well so i can find the second item selected is number one and that says that it's item five and two is number seven if i say three it gives an error so so we don't want to ask for items that are outside of of the range because we do know the range because we counted it and then we can say you know from this number to this number so that's great i've got a a nice form with a list box on it with multi selections and now i just need to be able to capture those selections and store them somewhere into our into our field that we created to store the numbers and uh and in order to do that i will go back to the properties of our list box and we're going to go to the after update event under the event tab and we'll click on the ellipsis just like i did there and that's going to give us our after update event now i'm going to come back to this after i just wanted to show you that but what we're going to do is we're going to create a function and it's a function that's going to allow us to just say at any time just give me give me the list of items that's stored in in the list box as opposed to having to go and you know get them from the list box manually each time so what we'll do is we're just going to create a little function that does that we'll say you know we'll give our uh we'll get an lbx uh variable there for as a list box and then we'll dim our return string so that we have something to return when we ask for the function and or when we call the function and we'll say set lbx equal to me lbxcandy because that and that'll just set a nice reference for us to work with that and then uh then from there uh we can um get some items uh because we're gonna get a sort of collection here and we're gonna loop through it so we'll need two integers and and one for the item that we're on one for all of the items then we'll set the lbx items equal to the list box and we'll say the item selected dot count and that's going to be really handy for us so now we know how many items we have just like we did when we were in the immediate window and then we once we have that then we can cycle through all of the items and uh and stop at the end just like we wanted so in order to do that we will do a for next loop we'll say 4 long item is equal to 0 to the count minus 1. remember as we showed down below that the count gives back the numeric count but the actual positions is zero based and so you have to say zero to long items minus one and then we'll go ahead and put this inside of a an if then so so that uh if there aren't any selected items in there we'll just go ahead and and uh skip the four next uh on our items here so we'll say if if we've got more than one item that's selected in the selected items then we'll we'll go ahead and evaluate those otherwise we'll just return sort of an empty empty string so so then we can say for each of those items zero to long items minus one we can say if if the item is the first one if long item equals zero then we'll say that our return string is just equal to the uh lvx.item selected and and that zero it'll be zero as long item uh otherwise um you know put a comma after it um if it's anyone that's greater than that we can say put a comma and then put the next item in there and uh and what that'll do is it's going to build us a nice little concatenated string or a delimited string that has a comma in between each number and we can use that later to put into our field but also when it's in the field we can read it and and split it out into an array in order to load the list box which i'll show um in a little bit here and then at the end of our function what we'll do is we'll say that our function lbx items is equal to our return string that we just put together so so that way we can just sort of say hey our function is equal to that we'll go back up to the top and we'll set uh we'll initialize that variable into an empty string so that if if it doesn't ever get anything put in it it'll return an empty string and that is very handy for us so i'll compile that debug compile just to make sure there aren't any glaring problems with that function and then we can move on and we can use our after update event which disappeared because i saved it and we didn't put anything into it so we need to go back and get it again so we'll do is we'll we'll go up here we'll minimize our code window there and we've still got our our list box selected there so we can go ahead and choose the after update event again and click on the ellipsis and then the code builder just like i showed before and now we've got our after update event for our list candy list box again so then we can we can sort of test out our function and get it to return a list of items so we'll say dimster items as string and then i get make our stir items equal to lbx items and then we'll message box that list just to to tell us that it's actually working so so we'll save that go to our view form view and that's perfect so now we've got each time i click on it it's going to return what's in the list box so it looks like that's working and uh and quite happy about that so our little function to return the value seems to be working great we can select and unselect as we want to and now we're going to go and remove that list box and we're going to put those items into our field instead so we can go back to our code window and we're going to remove that line that had the message box in it that we were using for testing and now we can just put in me exclamation candy id numbers is equal to str items and that's going to set the value of that field each time that the box is changed and so now i can go back to the form and we can go to the form form view and now we can see down below there we've got three records with orders on them so on order number one if i click on some of these guys here i should see if i go back to my table my orders table i should see those selections there now if i move to the second record you can see it stays selected up above but we haven't actually changed the values in in there yet so it won't do that update that we showed so now you can see on the first row it's two and five are the ones that are selected and that's exactly what we want to see if i add a couple more and and i go back to my order table you see it's not refreshed yet in order to refresh it i i do have to move off of that record and then back again and then you'll see that there's a there's a refresh in there you can actually throw a re-query or refresh command into the code and it'll do that for you but i left that out for now so it will get updated as you move off the record just like the rest of your fields do and we want to make sure that when we move off of the record and move to a new record the list gets cleared or gets cleared and then populated with the right values from the stored record and so in order to do that we'll go to our form event so you use that drop down at the top left there says it said lbx candy in it and now it says form because we selected form and then we're going to select current and what form current does is it fires an event when the record changed and you're on a new record and so we can start populating some variables here we'll say that our str items in this case at the moment that the form lands on the next record it's going to go and it's going to populate that with whatever is in the candy id numbers there and uh the field called candy id numbers and and then we'll do the same thing that we did before we can message box that and we'll put a couple of double quotes at the beginning of it with an ampersand so that if it happens to be null it'll concatenate that with an empty string and which will end up with an empty string that that does remain as a string and it's not a null value and that's going to work a bit better and not throw through that error and so now if i if i scroll off you can see oh it it did fire the the event but we did get an error and it looks like i spelled i put a one after candy there typo and so i'll remove that i hit go and you can see i came up with nothing as as the value in the message box and that's what we want to see so as we scroll through because those values that are there are actually the values that are on the very first record because we didn't clear the list box which is what we're going to do now and as you can see that one's empty there and it should have it should be empty when we scroll onto it from another record so this one's empty as you can see the second row and the third row are both empty but when we scroll to them they uh the box doesn't update and but the first one has four four items in it and we do want to show those so we're just getting a refresh problem on our list box that we're going to handle sort of uh with some coding here so in order to do that we'll bring up our code window and we're going to take that message box row there and we're going to remove that and that allows to start start coding so we're going to do some similar things to what we did before so we're going to have an items uh long integer as well as an item long integer we're going to cycle through sort of like the collection of of rows in in the list box and and then we're also going to cycle through the elements in in in an array which we're going to create when we uh when we finally get the the string from the row but to get started we've got our lng items equal to the list count of our candy list box and we'll go ahead and set up that loop and then we're going to go back up to our variables we're going to create a couple new ones we're going to create an array item which a simple array item which is kind of like a list if you come from other programming languages and uh we're going to create two more long integers one to hold the element count one to hold the current element and we'll use those in our loop here now just ahead of our loop there we are going to load that that array we could say our array for items is equal to split and split will take the delimited string and it'll split it into an array and remove the commas and then we can say our elements is equal to the upper bound of the um of the array item list which is a fancy way of saying that it's just the count of items in the list now from there we're going to loop through and what we're doing here is where this loop that we're creating is the loop of all of the sort of rows in our list box so we're going to as we loop through because we're doing kind of a refresh at first we're going to set the the selected so whether it's grayed out or whether it's grayed out or not we're going to set the selected item equal to false and that's going to sort of refresh it and then we're going to check and see if it if any item should be set to you know selected and we'll set it accordingly so it's just sort of a default value of unselected and then we'll go ahead and grab our our our id as we go through our list we don't we don't know what the actual like data id or candy id of the item is that's selected there uh in in the list or not selected um of that row so we don't know if it's uh what the id is and so we can go get that by saying that the id is equal to me lbxcandy.com and then three is the is the row which is the fourth row and then the item is the is the is the number of the row and so we're sort of grabbing it by coordinates there because this is one one of the only ways that you can do this uh and then we'll create another for next loop inside of that which will just cycle through the elements of the selected items that we got from the record and basically if the item happens to be one of the selected items for that for that record then we want to set the selected property equal to true and that's what we're going to do that's what we're going to do there then we'll put a end if on our statement there and we can scroll up and see uh what we've got here and i'll do a debug compile just to check for any glaring syntactical errors and that there does not seem to be any so i'll put a couple comments on here so first we're clearing the row with that statement so that it is unselected and then we will set it to true if the item is found in the in the array of of items that was taken from the current row and look that over and then we can minimize our code window there and then if i scroll to the next record it fires it oh and we did get an error so so we can see uh here we go so i think yeah so i'm trying to load an integer value but that the ro the id in the in the id column of the list box is not always an integer sometimes it's a string or something like that so i'm going to change it to a variant and a variant will allow it to use either an integer or a string or whatever and the program should like that a little bit nice a little bit better and that's really going to help us to to see the items that are in there so from there we can go ahead and minimize our code window again so we've sort of built our matching and i'll scroll to another row and you can see it clears all of the items from there on each of the rows that has no selections and when we go to the first row there we go now now we've got our selections have come from the record and they are populated into the multi-select list box and if i scroll forward off of there or i or if i change the items and scroll off and then go back you can see that the selections have changed for that one row and that's that is what we want to see i can select all three and of the top on the second row and the bottom three for the last row and i can scroll back through the records and you can see that the selections have been retained and stored just the way we want them to be and if i go to the order table open that up you can see the selections that have been made for that multi-select list box and if i change them or if i select all of them say and scroll off of that and then open up the table you can see all of the items in in the list box have been selected the id numbers are all in there and that is how you do multi-select list boxes in microsoft access i hope you enjoyed today's discussion on how to do multi-select list boxes in microsoft access if you like what you saw today please give the video a thumbs up and make sure to subscribe to the channel if you have not subscribed yet also and make sure to click the bell when you see the bell so that you'll be notified of any new content that i put up if you have any questions or comments about what you saw today please put those in the comment section below and i'll do my best to answer any questions that you might have have a great day have a safe day and i'll catch you next time you

Channel: Sean MacKenzie Data Engineering

Views: 1,529

Rating: 5 out of 5

Keywords: MS Access listbox multi-select, how to make a listbox in ms access part 2: multi-select, access listbox multi, multi-select listbox, vba listbox, ms access listbox implementation, sean mackenzie data analytics, Sean mackenzie data engineering

Id: CUQj0IUE4zU

Length: 25min 45sec (1545 seconds)

Published: Tue Mar 09 2021