List Box Move Item 3
By Richard Rost
2 years ago
Move Items Up and Down. Part 3: Bound List Box
In Part 3 of this Microsoft Access tutorial series, I'm going to teach you how to move items up and down in a bound list box using little up and down buttons. We'll getting to writing the code behind our buttons in this video. A little VBA. A little SQL. A little sweet, sweet coding action!
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Series Links
Other Links
Keywords
TechHelp Access, list box item movement, moving items list box Access, Access VBA buttons, VBA list control, Access list item swap, coding list box interaction Access, Access sort order modification, VBA and SQL list updates, list index manipulation Access, list box requery, VBA list count, move last list item Access, list item sorting code Access.
Intro In this video, we will learn how to write the VBA code needed to move items up and down in a Microsoft Access list box using up and down buttons. I will show you how to check for selection errors, prevent users from moving the first or last items past their limits, and use SQL to swap the sort order between items directly in your table. You'll also see how to refresh the list box to keep things in sync with your data. This is part 3.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today is part three of my moving items around in a list box with little up and downy buttons series.
Today's video, we're going to actually build the code that goes behind our up and down buttons with a little SQL, a little VBA, and a little fun stuff. Of course, today's video follows yesterday's lesson, part two, and that video follows part one. If you haven't watched parts one and two, go watch those first. Here's a link down here. You'll find that one and then come on back.
We now join the program already in progress. Time for some code. Here's our algorithm. First, we're going to check for no selection. Then we're going to make sure that the user is not trying to move the item up if it's already the first item. Then we're going to get the ID of the item we're trying to swap with and then, using SQL, we're going to change their sort orders.
Here's how it works. Right click, build a map. Brings up my handy dandy code editor. We're going to need an ID because we have to look up the guy we're swapping with and get his ID. The ID of the one that we are swapping, that we picked, is going to be the value of the employee list. The first column, column zero, the bound column, is the ID of the item that we're swapping. We need to look up the other one. We need a place to save that.
Let's dim an ID, dim ID as long. That's ID2 basically.
Check for no selection. If IsNull(employee list), then beep, exit sub. You can put a message box and then you can do whatever you want. That just says, if they didn't pick anything, beep, get out of dot.
Now we're going to check to see if they're moving the first item. They're trying to move the first item up. Check for first item, in which case it's a no-no. So if employee list.column(1), what is .column(1)? Remember, column zero is the ID. The next column is column one. That's the sort order. If the sort order is already zero, then we're at the first one. So beep, exit sub.
We're going to get the ID of the record to swap with. The ID of the record to swap with, meaning we're moving up, is going to be the list index minus one. So ID equals employee list.column(0, employee list.listindex - 1). In other words, we're going to put it here, get the ID of the previous item.
So employee list.column(0), column zero is the first column, the ID column of which row. Well, list index is the selected item. And we know it's not the first one, so this is going to be one above it. We already checked for that. So list index minus one means the row above the row I'm in. Now I've got that item's employee ID.
Now we're going to swap the records. Here comes the tough part. This is the SQL. We have to execute two SQL statements. We're going to take the current item and subtract one from its sort order. So one becomes zero, two becomes one, and so on. And take the other guy, whose ID we have, and add one to it. So we're effectively swapping those two numbers.
And here's our update statement: currentdb.execute "UPDATE employee SET sortorder = sortorder - 1 WHERE employeeID = " & employee list. Update the employee table, set the sort order to sort order minus one, subtract one from it. So one becomes zero where the employee ID equals employee list, which is the ID of the currently selected item.
Now the next one is very similar. So we're going to copy and paste. The next one is going to set the sort order plus one where the employee ID equals the guy we looked up earlier, who's the one above it.
Make sense? When we're all done, requery the box. So employee list.requery. We didn't have to do this before with the value list one because we were moving items in the box. But here we're moving items in the table. So we have to refresh the box, reload the box, requery the box.
Debug, compile, save it. Back out. Let's close this. Close this. Open her up.
No item picked. That works. Pick the first item. Nothing happens. Let's move over up to the top of the list. Up. Up. Up. Up. Perfect.
So we're basically looking at this item. Looking at McCoy, getting its ID, which is the value of the box. Finding out who's above it, which is Spock. Then looking up Spock's ID from the list, and then swapping these two.
Down button. Very similar code. In fact, I'm going to take this. I like to keep this stuff together. I'm going to move it right down here. There's my down button. I'm going to copy all of this stuff. Again, a lot of it's the same.
Not similar enough to make these into one function because that gets kind of confusing, but here we're good. So this is the same. But here we're not checking for the first item, we're checking for the last item. So we have to say, this is going to be different right here.
EmployeeList.column(1), if it's greater than or equal to whatever the total list count is, minus one because it's zero based, then we're at the last item. So this is going to be: if employeeList.column(1) is greater than or equal to employeeList.listcount - 1. List count is how many items we have.
So if the sort order for this thing is three, which is greater than or equal to, let's say the list count is, well, we've got four items, or, I think in this case, we've got five items. Yeah, we've got five items. So if we're on the last one, employeeList.column(1), the sort order should be four because it goes zero, one, two, three, four. If that's greater than or equal to the list count minus one, which is four - list count five minus one is four - then we're on the last item. We can't move it down anymore.
Next, same thing. Get the ID of the next item. We know there's got to be one after it, so it's plus one. Otherwise, this code would have failed us. Now here, we're swapping the items again, but this time, we just reverse these signs. This one's going to go up one, and this one's going to go down one.
That's it. This is the tricky part here, checking to see if we're at the last item. Save it. You can back out here. Close it. Open it. Take Spock, move Spock up. Move Spock down. Got it in one pass, the end. That's it. There's your code. Pretty fancy.
Get typing. Or sign up and become a gold member. You can just download the database.
If you like learning this stuff with me, check out my developer lessons. I've got tons and tons of stuff on my website to teach you how to program in VBA in Microsoft Access.
If you want to learn SQL as it works in Microsoft Access, I've got a three-part seminar. Part one is all select queries, order by clauses, where conditions, all that stuff. Part two is all the action queries. It's all like we did today - update queries, delete queries, append queries, all that cool stuff. Then part three, if you want to go to part three, that's fine. That's about manipulating the structure of tables, like adding fields and indexes and such. You don't really need part three - that's for the super high-end people. Parts one and two are really good.
There's the link. Scan the little QR code. Check it out. That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, please give me a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can.
Make sure you subscribe to my channel, which is completely free. Click the bell icon and select all to receive notifications when new videos are posted.
Want to learn more? Click the Show More link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here? The name, the video is up here. There's a little Show More down there, right at the bottom. It's kind of hard to find, but once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted as they used to do. But if you'd like to get an email every time I post a video, click on the link to join my mailing list. You can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.
Now, if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the Join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member, and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I've got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.
Now, if you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long. You can find it on my website, or on my YouTube channel. I'll put a link down below and click on it. Did I mention it's completely free? The whole thing free, four hours. Go watch it.
A lot of you have told me that you don't have time to sit through a four-hour course. So I do now have a quicker Microsoft Access for beginners video that covers all the basics faster, in about 30 minutes, and no, I didn't just put the video on fast forward. I'll put a link to this down below as well.
If you like level one, level two is just a dollar. That's it, one dollar. That's another whole 90-minute course. Level two is also free for paid members of any level, including supporters. So if you're a member, go watch level two. It's free.
Want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page and you'll have a better chance of getting it answered.
While you're on my website, be sure to stop by my Access forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Wan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. I'm on Facebook too, but I don't like Facebook.
Now, let's talk more about those member perks if you do decide to join as a paid member.
There are different levels: Silver, Gold, Platinum, and Diamond. Silver members get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks plus access to download the sample databases that I build in my TechHelp videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions.
Now, answers are never guaranteed, but you do go higher in the list to meet the algorithm. If I like your question, you have a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions. You get access to all of my full beginner level courses for every subject. I cover lots of different subjects like Word, Excel, VBA, ASP, and more, not just Access. These are the full length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
Finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today.
Live long and prosper, my friends. I'll see you again soon. Bye.Quiz Q1. What is the primary function of the up and down buttons in the list box as discussed in the video?
A. To change the appearance of the list box items
B. To sort the items alphabetically
C. To move a selected item up or down in the order by modifying their sort order in the database
D. To delete items from the list
Q2. What is the first step in the algorithm when an up or down button is clicked?
A. Check if the form is open
B. Check if an item is selected in the list
C. Refresh the list box
D. Update the database immediately
Q3. Why does the code check if the selected item is the first or last in the list before proceeding?
A. To highlight the item
B. To prevent moving items outside the list boundaries
C. To sort the list
D. To reset the sort order
Q4. What VBA method is used to refresh the list box after items have been moved in the table?
A. RepaintObject
B. Requery
C. RefreshData
D. UpdateList
Q5. How does the code determine the item to swap with when moving up?
A. By adding one to the list index
B. By setting it to the last item in the list
C. By subtracting one from the list index of the selected item
D. By choosing a random item in the list
Q6. In the update statement for moving an item up, what happens to the sort order of the selected item?
A. It is set to zero
B. It is divided by two
C. It is increased by one
D. It is decreased by one
Q7. When moving an item down, how does the code check if the selected item is the last in the list?
A. If the sort order is equal to zero
B. If the sort order equals the list count minus one
C. If the employee ID is negative
D. If the item is selected
Q8. Why does the video emphasize the difference between moving items in a value list and moving items in a table?
A. Value lists cannot be sorted
B. Table changes require requerying the list box
C. Value lists are permanent
D. Moving items in tables does not affect data
Q9. Which function is used in VBA to check if an item has been selected in the list box?
A. IsEmpty
B. IsNothing
C. IsNull
D. IsSelected
Q10. According to the video, why are up and down button codes not merged into a single function?
A. Because VBA does not allow function reuse
B. Because the logic for up and down is too different and combining would lead to confusion
C. Because SQL cannot handle two operations
D. Because separate forms are required
Q11. What advantage do Gold members receive, according to the video?
A. Access to beginner courses only
B. Higher priority for TechHelp questions and access to downloadable sample databases
C. Access to just the forums
D. Free merchandise
Q12. What SQL action query is demonstrated in the video for moving list items?
A. Select query
B. Append query
C. Update query
D. Make-table query
Q13. What is 'employeeList.column(0)' referring to in the code?
A. The row number of the selected item
B. The sort order value
C. The ID of the selected item
D. The total number of items in the list
Q14. Why is it necessary to dim a second ID variable in the code?
A. To keep track of database errors
B. To store the ID of the item to swap with
C. To count the number of items
D. To store user messages
Q15. If a user tries to move the first item up, what happens according to the code?
A. The item moves to the bottom
B. A message displays and the function exits
C. The item stays in place without feedback
D. The item is duplicated
Answers: 1-C; 2-B; 3-B; 4-B; 5-C; 6-D; 7-B; 8-B; 9-C; 10-B; 11-B; 12-C; 13-C; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on building the logic that allows users to move items up and down within a list box in Microsoft Access using up and down buttons. This is part three in a series dedicated to rearranging items in a list box, so if you have not watched parts one and two, you should start there for a better understanding of the concepts being discussed.
The main goal in this lesson is to build the VBA code that goes behind the up and down buttons, making use of both SQL and VBA. The approach begins by defining an algorithm to handle the item movement. First, it checks if the user has made a selection in the list box. If nothing is selected, the procedure exits early. Next, it ensures that the user is not trying to move the topmost item up, which is not permitted. Then, the code retrieves the ID of the adjacent item (the one to be swapped with) and performs the necessary swap by adjusting their sort order values in the database.
The list box is set up so that the first column (column zero) contains the unique ID for each item; this is known as the bound column. The second column holds the sort order, which determines the position in the list. When moving an item up, the code retrieves the employee ID of the currently selected item, as well as the ID of the item just above it, by referencing their indices in the list box. The crucial logic involves checking the sort order to prevent invalid moves: if the current item's sort order is zero, it is already at the top, and no further move up is allowed.
To swap the orders, two SQL UPDATE statements are executed. One deducts one from the sort order of the selected item, and the other adds one to the sort order of the item above it. This effectively swaps their positions within both the underlying table and the list box. Once the updates run, the list box is requeried to show the new order, since changes have been made directly to the table.
The code structure for the down button is nearly identical, with some adjustments. Instead of checking for the first item, it now checks if the selected item is already at the bottom, using the list count property of the list box. If the selected item's sort order is greater than or equal to the total count of items minus one, then it is at the bottom and cannot be moved down. Otherwise, the code retrieves the ID of the item below the selection and swaps the sort orders, just as with the up button, only now the operations are reversed to move the selected item down.
With both up and down buttons working, the user can now seamlessly reorder items in the list box, with all changes reflected instantly in the list as well as the database. This kind of logic comes in handy if you need to manage ordered lists, such as priorities, rankings, or custom user arrangements.
If you are interested in learning more about programming in Microsoft Access with VBA, there are additional lessons available covering many topics, ranging from beginner to advanced. To build a solid foundation in SQL as it applies to Microsoft Access, I offer a three-part seminar. The first segment covers SELECT queries, sorting, and where clauses. The second part, which is relevant to what we have done today, handles action queries like UPDATE, DELETE, and APPEND. The third part deals with managing table structures and is more suitable for advanced users. Parts one and two provide a strong starting point for most users.
If you want to further expand your skills, take a look at my free Access Level 1 course covering the basics of Microsoft Access, including working with forms, queries, reports, and more. For those with limited time, I also have a quicker, introductory video running about 30 minutes that gives a fast overview of the essentials.
To enhance the learning experience, paid memberships are available and come with a range of benefits. Silver members receive my extended cut TechHelp videos and a free beginner class every month. Gold members also have access to downloadable sample databases and the VBA code vault, as well as priority in having questions answered. Platinum members add even more complete beginner courses and receive periodic developer classes. These memberships provide additional resources and access to more advanced content for those who want to take their skills further.
If you have questions you would like answered in a future video, you can submit them directly on my website where members receive priority. You can also take part in the Access forum, where a dedicated community discusses Access and related topics and helps each other out.
To stay updated, be sure to subscribe for notifications of new videos and follow my blog and social media accounts. For those who want to help support my work, you are welcome to use the tip jar or join as a paid member for extra perks, but my free tutorial videos will always continue.
For a complete video walkthrough and step-by-step demonstration of everything outlined in this tutorial, visit my website at the link below.
Live long and prosper, my friends.Topic List Checking for no selection in a list box
Preventing moving the first item up
Retrieving the ID of the item to swap with
Swapping sort order values using SQL
Executing SQL UPDATE statements with VBA
Requerying the list box to refresh data
Implementing the Move Up button logic
Implementing the Move Down button logic
Preventing moving the last item down
Using list index and column properties in VBA
Handling zero-based indexing in list boxes |
