List Box Move Item 2
By Richard Rost
2 years ago
Move Items Up and Down. Part 2: Bound List Box In Part 2 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. A bound list box is a box that gets its values from a table or query (not to be confused with a box that's bound to a field in the form). Also known as a "Table/Query List Box." I like my term better. MembersThere 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!
PrerequisitesLinksRecommended CoursesSeries Links
Keywords TechHelp Access, list box items rearrange, up and down buttons Access, bound list box control, VBA sort order code, Access list box tutorial, custom sort Access, Employee sorting Access form, list box item movement, move list items Access, Access VBA coding tutorial
Intro In this video, we will learn how to move items up and down in a bound ListBox in Microsoft Access, where the ListBox is getting its data from a table or query instead of a manually entered value list. We will set up a sample employee table, discuss best practices for reserved words, create a sort order field, and design a ListBox that displays data from the table so that its items can be reordered using up and down buttons. This is part 2.Transcript Today is part two of my ListBox MoveItem series. We are removing items up and down in a ListBox using little up and down buttons.
Today we are going to learn how to move items up and down in a bound ListBox. This is a ListBox that is bound to a table or query. In other words, it is getting its data from a table or query. Not to be confused with bound to a field in this form. I mean that this is getting its data from a table or query as opposed to what we did in part one, which was a value ListBox where we just type in the values in the ListBox. These do not come from anywhere. These are easy to shuffle around. We just move them up and down inside the box itself.
If you have not watched this video yet, go watch it first and then come on back. Today we are going to deal with a list of items that are coming out of a table. We have to actually change the values in the table as we move these things up and down. That is what we are going to do today.
Here I am in the video that we built on Friday. This is a value ListBox. You click on it, you can move the items down, you can move the items up. This data only exists in this ListBox. It does not go anywhere. It is not bound to a table.
Let's set up a table of items that we want to control the sort order of. Let's do employees. Let's create an employee table. Create table design. I know I have other tables I can use. We will just make an employee table real quick.
We have got our employee ID. That is our auto number. We have got the employee name. You can do first name last name if you want. This is a simple example.
Remember again, we do not just use the word name as a field. Because that is a reserved word. It is special. Do not use name. My main man, Alex, has been keeping a big long list of reserved words to avoid in Access. I have a video on this coming up soon. I will put a link to this page down below.
Here is my big no no list. These are the words that you should definitely avoid. Any of the ones that are in bold, seriously avoid them. Name or image format. Those kinds of things.
Employee list is going to need to be sorted. You can sort by name if you want to. That does not really give you the option to move things up and down in the ListBox. We want to manually control the sort order.
Cannot go by ID because you cannot really be sure. Usually, auto numbers come in in order that the record is added to the table, but that is not always the case. It is possible to get items in an earlier auto number. It is a long story, but you cannot rely on them, basically. So we want to control the sort order ourselves.
So we are going to make our own sort order field. I am going to make that a number. Let's save this as my employee table. Employee table primary key. Yep, that is going to be our auto number. Save it. We will close this. Close this. Open her up. Let's throw some data in it.
Now, for this example, we are just going to put the sort order in here manually for now. If you have a big long list, a big table of existing records, and you want to put the sort order in there, you can do it yourself or you can use an update query or any number of techniques to start the seed of the sort order. After this, after we put the initial list of records in, then we will use our little form to change them.
So let's do Kirk. He will be zero. We are going to start with zero. You can start with one if you want to, but I figure zero is just as easy. We have Spock. He will be one. We have McCoy, two. We have Scotty, three. And Uhura will be four. Okay, so there are our employees.
And yes, I can already see the emails in the comments I am going to get about people who want to take a list of existing, like 500 employees, and automatically add the start seeding number. So sure, I will put this on the list for another video. Give me a couple days. I will put it on the list.
So the table is all set. Let's save that. Close it. Now let's put it on the main menu. Let's make some room here. We will put it next to the other one, right over here.
Let's grab a ListBox. Where are you? Right there. Now this one is going to be bound to a table. So we are going to get the values from a table or query.
Next, I think the official terminology for Access is a table query list box, but I think that sounds dumb. So I call it a bound ListBox. It is bound to a table. What table is it getting its data from? EmployeeT, of course.
Next, I am going to bring over all three fields, but I am going to hide the employee ID and the sort order. I do not want to see them. I am going to bring employee ID over first, then I am going to bring the sort order over next, and then last I will bring over the employee name because that is going to be the only visible field. We do not need to see the sort order.
Next, what field do you want to sort by? Well, the sort order.
Next, this is what it looks like in the ListBox right now. The key column is hidden. We are going to keep hiding that. We are also going to hide this column. How do we do that? We grab the border right there, we drag it all the way to zero. Do not leave a little tiny bit there. Sometimes people do that, and they cannot understand why stuff is not working. Because you can still kind of see it. Just take it and go past the beginning like there, and then now you are definitely sure it is zero length.
Resize it if you want to. Next, what label do you want? It does not matter. We are going to delete it anyway. Hit finish. There is the label. Delete.
Save the main menu. Close it. I am going to open it back up again, and there we go. It is sorted in our sort order.
Let's go back to design view. Let's open this guy's properties up and give it a good name. List21 is not a good name. EmployeeList we will call it. I am going to slide this over just a little bit. Slide you guys over just a little bit.
Now I need up and down buttons. I am going to just copy these ones. Copy paste. We will slide them over here. Now these guys are named up button and down button. So let's just call this one up button 2. And this guy can be down button 2.
I just recommend you do not start any field names with a number. Putting a number on the end is not a big deal. Starting with it adds some problems to your VBA code. Just trust me.
We are ready to start the code, the tough part, the stuff that goes in the buttons. We are going to do that in tomorrow's lesson. So tune in tomorrow, same bat time, same bat channel. Or if you are a member, you can watch it right now. Because members get to watch all the stuff as soon as it is posted, and I am going to be posting it in about five minutes. The rest of you, you can tune back tomorrow.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you tomorrow for part three.Quiz Q1. What is the main difference between a value ListBox and a bound ListBox in Access?
A. A value ListBox gets its data from a table or query, while a bound ListBox does not
B. A value ListBox allows sorting, but a bound ListBox does not
C. A value ListBox contains values entered directly, while a bound ListBox gets its data from a table or query
D. A value ListBox is always read-only, while a bound ListBox allows editing
Q2. When manually controlling the order of items in a bound ListBox, why should you add a custom sort order field to your table?
A. Access automatically sorts records in the correct order
B. The auto number field cannot be relied on for consistent ordering
C. Field names in Access cannot contain numbers
D. Only text fields can be used for sorting in Access
Q3. Why should you avoid using reserved words like "Name" as field names in Access tables?
A. Reserved words cannot be displayed in ListBoxes
B. Reserved words can cause errors and conflicts in queries and code
C. Reserved words are invisible in forms and reports
D. Reserved words must be used in every table
Q4. In order to hide columns like ID and SortOrder in a ListBox, what should you do in the property sheet?
A. Set the column width to 0
B. Delete the fields from the table
C. Change the font color to white
D. Rename the columns
Q5. Why is it important to bring over the employee ID and sort order columns to the ListBox even if you will hide them?
A. Access only allows one field in a ListBox
B. Hidden columns still provide necessary data for sorting and identification
C. The ListBox cannot display names without IDs
D. Access requires all columns to be visible
Q6. If you need to add a sort order to an existing table with many records, which method is recommended?
A. Enter the sort order manually for each record
B. Use an update query or another automated technique
C. Delete all records and re-enter them in the desired order
D. Ignore sort order and let Access decide the order
Q7. Why should you avoid starting field or control names with a number in Access?
A. Access will sort them incorrectly
B. Names starting with numbers can cause issues in VBA code
C. Such names are not allowed in forms
D. Field names must always be in uppercase
Q8. In the setup process, after creating the employee table, what should you do to control the display order in the ListBox?
A. Sort by employee name
B. Sort by the custom sort order field
C. Sort by employee ID
D. Access sorts automatically regardless of settings
Q9. What is the correct way to change the visible column in the ListBox so only employee names are visible?
A. Hide the other columns by resizing their width to zero
B. Delete the unused fields from the table
C. Make the non-name fields read-only
D. Move the name field to the first position only
Q10. When building an employee management ListBox feature that allows manual up and down sorting, what must be adjusted each time a change is made?
A. The table must be re-created
B. Only the ListBox display needs updating
C. The underlying sort order field values in the table
D. Nothing, Access handles updates automatically
Answers: 1-C; 2-B; 3-B; 4-A; 5-B; 6-B; 7-B; 8-B; 9-A; 10-C
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 is part two of my series on moving items up and down in a ListBox. This lesson focuses on working with a bound ListBox, which means the ListBox gets its data from a table or a query in your database. This is different from the value ListBox we built in part one, where the items are typed directly into the ListBox and are not linked to any data source. If you have not seen that first lesson, I recommend starting there before continuing with this one.
In part one, we worked with a simple ListBox whose data only existed inside the box itself. Moving items up and down was a matter of just rearranging the values in the ListBox. However, when dealing with a bound ListBox, the process is different. Here, every time we move an item up or down, we have to actually change the records in the underlying table to update their positions.
Let's start by building a table to store the items we will be managing. In this example, I will use an employee list. I will create a basic employee table with three fields: an EmployeeID field set as an AutoNumber for the primary key, an EmployeeName field to store the name, and a SortOrder field as a number to control how the list will be sorted. It's important not to use reserved words like 'Name' as your field name, because reserved words can cause issues in Access databases. Instead, use something more specific, like EmployeeName. There is a long list of reserved words I recommend you avoid, and a video about that is coming soon, so keep an eye out for the link.
The key to allowing manual control over the order of your list is the SortOrder field. Sorting by names or by ID won't give you the flexibility to move items up and down as you need. While AutoNumbers generally reflect the order in which records were added to the table, you cannot always rely on them to sort your data exactly how you want. That's why having your own SortOrder field is so important.
Once the table is created, I go ahead and enter sample employee names manually, setting their initial sort order as 0 for Kirk, 1 for Spock, 2 for McCoy, 3 for Scotty, and 4 for Uhura. If you are working with a large existing table and need to populate the sort order for many records, you can either do it manually, use an update query, or several other techniques to get those numbers established. I'll cover automatically seeding sort order in another video for those who need it.
Now that our EmployeeT table is ready, I head over to the main menu form and add a new ListBox to it. This ListBox is set to get its data from a table, specifically from EmployeeT. Even though Access sometimes refers to this as a table/query ListBox, I call it a bound ListBox because it is bound to a data source.
I add all three fields to the ListBox, but I only want EmployeeName to be visible. The EmployeeID and SortOrder columns should be hidden, as they are not necessary for the user to see. To hide those columns, I adjust their widths to zero in the ListBox properties, taking care to make sure they are completely hidden. Even a small visible edge can cause confusion or display issues, so dragging the border completely past zero is important.
After setting up the ListBox and making sure it is sorted by the SortOrder field, I resize it and remove any unnecessary labels. I save and reopen the main menu form to verify that the employees are shown in the intended sort order.
Next, I rename the ListBox to something meaningful, like EmployeeList, instead of leaving it with the default name Access provides. I then set up up and down buttons next to the ListBox for moving the selected employee up or down in the list. I rename these buttons accordingly, avoiding starting any control names with a number, since that can cause trouble in VBA code.
At this stage, we have the visual part set up. The next part will involve writing the VBA code behind the buttons to update the SortOrder field in the underlying table when moving records up or down. That's the more challenging part, and I will cover that in detail in the next lesson. Members can watch the next part immediately, while everyone else can catch it tomorrow.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a table to control ListBox sort order
Adding a custom sort order field to the table
Populating the table with initial data and sort values
Setting up a bound ListBox to a table in Access
Configuring the ListBox to display only desired fields
Hiding columns in a ListBox by adjusting column widths
Sorting a ListBox by a custom sort order field
Renaming controls in Access forms for clarity
Adding up and down buttons to a form for ListBox items |
