Kanban Board 2
By Richard Rost
9 hours ago
Build a Kanban Board in Microsoft Access - Part 2
In this lesson, we continue building our Kanban board in Microsoft Access by creating buttons to move items between stages and updating the statuses in the underlying table. I will show you how to handle selection logic in list boxes, highlight moved items, update status labels dynamically with DLookup, and display extra information for selected items. We will also discuss how to allow single selection across different list boxes and enable opening customer details with a double-click event.
Members
In the extended cut, we will clean up the duplicated code by creating a reusable routine that can move a customer left or right between stages using passed control names. I will show you how to build one set of move buttons that update customer status automatically, along with visual enhancements like adding colors to columns and other customizations so your Kanban board is easier to manage and more visually appealing.
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
Keywords
TechHelp Access, Kanban board, move listbox item, command button, VBA code builder, update statusID, requery listbox, highlight selected item, DLookup label caption, on-click event clear selection, display customer info, open customer form, dynamic stages, drag and drop, customizable columns
Subscribe to Kanban Board 2
Get notifications when this page is updated
Intro In this lesson, we continue building our Kanban board in Microsoft Access by creating buttons to move items between stages and updating the statuses in the underlying table. I will show you how to handle selection logic in list boxes, highlight moved items, update status labels dynamically with DLookup, and display extra information for selected items. We will also discuss how to allow single selection across different list boxes and enable opening customer details with a double-click event.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This is part two of my Kanban board in Microsoft Access series. If you have not watched part one yet, go watch that first. You will find a link down below and then come on back.
Alright, so here we are. We have got our Kanban board looking pretty good. Now we are going to make some buttons to move something from box one to box two, then box two to box three, three to four, and so on, and then backwards if we need to. So let us start off with moving an item from one to two.
Alright, let us go into design mode. I am going to throw a command button down here. So, form design, find command button. Where are you? There you are right there. Drop it down here. Cancel the wizard because unfortunately there is no move item from one list box to another wizard. I am going to make the caption look like that. Alright, and then let us make it bigger. So let us go, I do not know, like 18 point bold and I am just going to double-click so that it resizes. There we go. Maybe make it a little bit bigger than that. Alright, there is my button to move whatever is in that box to the right.
OK, let us open up its properties. Let us give it a good name so Alex does not yell at us. Let us call it moveOneToTwoBTN. Move one to two button. Alright, and in here we are going to put something in the click event. So right-click, build event. As our code builder comes right back up, OK, first thing we have to do is make sure that there is a value in list one. So if the user did not pick anything, if is null(list1) then exit sub. You can give them a warning message if you want to. You could say message box, hey, pick something in list one first, you can do all that. That is the easy stuff, you know how to do that.
Alright, the next thing to do is to move whatever the selected item is to box two. So how do we do that? We update the record in customerT, because that is where our records live, and change the statusID to two for this customerID. Here is how it looks as an SQL statement. CurrentDB.execute - you can also use RunSQL. I have a whole different video on why Execute is better than RunSQL. Either one is fine.
UPDATE customerT SET statusID = 2 WHERE customerID = & list1.
OK, that is all you need. We are going to update the customer table, set the statusID equal to two, where the customerID equals whatever customer is selected in list one. OK, now that happens behind the scenes, so nothing appears to happen on the form. Now we need to requery list one and two.
list1.requery
list2.requery
Then let the user know something happened - beep. That is it. That is all you have to do. Alright, debug compile once in a while to make sure that we got it right.
Alright, let us go back over to our Kanban board. Close it, save it, open it. Now if I just hit the button, nothing happens. But if I pick Wesley and hit go - there, Wesley is over here now. That is pretty cool. How about Miles? Yeah, well, now Wesley is selected. Now let us do that next.
Alright, it would be nice if that person became highlighted over here. That would be handy. So let us go back to our code. Now in order to do that, we have to remember what that ID was. So we will need a variable in here. Dim ID as Long. You can call it customerID if you want to. ID is fine. Right here, we are going to say ID = list1. I want to remember what customer is selected in list one. Because after I add it to list two, now I am going to say list2 = ID. I am going to set that listbox equal to the value of the customer that I just moved over.
Alright, save it. Debug compile. Come back over here. Now let us move Jean-Luc. Ready? Go. There is Jean-Luc. And what about me? Now everybody is over here. It is a party.
OK, now let us make the other buttons. It is basically at this point going to be just copy and paste. Yes, there are better ways to do it, and we are going to talk about some better ways in the extended cut. But we are going to keep this one simple. Copy, paste. Alright, paste, paste. See what I did there? I moved this guy over here, then I hit paste, paste, and Access remembers where it was.
OK, so open it up. This is move one to two button. This is going to be move two to three button. This one is going to be move three to four. And this one is going to be, guess what? Move - oh wait, we cannot go from there, can we? This guy is going to go backwards. So let us hold on to this guy for a minute. He is going to go this way. This is going to be move four to three. Alright, we are going to line him up over on this side. We will get to that.
Ready? Right-click, build event. Now we are going two to three. So again, copy this stuff, paste it down here, and now we just have to change some stuff. If is null(list2), then exit sub. ID = list2. We are going to three, so set the status equal to three from list two. list2.requery, list3.requery, and then list3 = ID. Good.
Alright, let us test it. Close it, open it. Let us move Jean-Luc over there. Let us move me over there. Riker, all over there. Beautiful.
Alright, let us do the other ones. Right-click, build event. Copy this stuff. Three to four. Same thing. list3, set status=4, from list3, requery list3 and list4, list4 = ID. All right.
Do I like having all this duplicated code? No, not really, and yes, there are better ways to do this. We will talk about them again in the extended cut. But today we are just going to get this to work. Alright, let us test this. This one is working. Beautiful.
Now we have to go backwards, because you might get something that is in stage four, and realize you messed up, so you have to go back to stage three. OK.
Now we are in the move four to three button. Now we have to do a little thinking again. It is not just copy and paste. We can copy this though. If is null(list4) then exit sub. ID = list4. We are going to set the status to three, where the customer is in list four. We are still going to requery three and four, and then set list3 = ID.
OK, save that. Let us test it. Let us send Geordi back down. He is going back down to the little leagues. OK, good. Now we just have to make buttons for three to two and two to one.
Design, copy, paste, slide you over here, paste, slide you over here. Did not remember it that time. Sometimes it remembers it, sometimes it does not. OK, this guy is going to be move three to two, and this one is going to be move two to one.
Alright, so this guy here, let us copy and paste. We are going three to two. OK, so we have to check list three, set status=2 from list three, requery two and three, and then set list2 = ID.
Last but not least, this guy. Copy, paste, we are going two to one. OK, so list two, set status=1 from list two, requery one and two, and then set list1 = ID.
OK, all right, I think I got them all right. Let us test. Let us send someone back. Now let us send Sisko all the way back and forward. OK, go that way, there, there, and then back down again. Beautiful, huzzah.
Alright, that is the basics of it, that is the mechanics of it. Now it is just some prettying and sprucing things up. For example, when you click on one of these, you might want to blank the other ones, so only one person at a time can be selected. That is pretty easy. We can do that with an on-click event.
So if they click on list one, go to the events, go to on-click. We are going to say list2 = null, list3 = null, list4 = null. I cannot type today. Then we are going to copy this and do the same thing in the other lists.
So in list two, if they click on that one, we are going to set list1, list3, and list4 to null. In list three, if they click on this one, we are going to set list1, list2, and list4 to null. Then list four, same thing here.
Debug compile. So now it is not confusing if you have multiple items. Only one at a time can be selected. That makes it a little more sensible. That way you do not click on this and then go, see? And then the other buttons do not work, too, which is a good thing. I only want one record to be able to be selected.
Could you do multiple records in here? Yes, you could. You could do a multi-select list box. I have a whole separate video on that. That is a little more complicated, but it is definitely doable. If you want to be able to select multiple people and move them all at once, absolutely. That is covered in my Access Developer 15 class, lesson three. I will put a link to that down below.
What about these labels up here? Instead of just one, two, three, four, well, we have statuses in here. Why not just look up that description? Now you can change these to text boxes and put DLookups right in here, or you can change the caption properties and leave them labels. That is up to you.
Let us call these guys label1, label2, label3, and label4. Let us go into the form's events and go into the on-load event for the form. This is where we are going to put it. Now you can do four separate DLookups here. That is fine. Something this quick, I do not mind doing four separate DLookups. It is going to run once when the form loads and it is almost instant. So you just say
label1.caption = Nz(DLookup("description", "statusT", "statusID=1"), "1")
And yeah, you could do a loop and you could do all kinds of other things here, but you know what? This is so simple, there is only four of them, there is nothing wrong with this once in a while. Are there better ways to do this? Yes. If I had a hundred of them, I would absolutely make this a loop. But just four of them, OK. We can get away with this.
We could use a recordset, we could do all kinds of stuff, but in this case, simple sometimes works well. You could change colors for these. If you want different colors, put the color in here, put a color field in here. Those are the little embellishments you can do. You can change the background colors of these, all kinds of stuff.
Do you want to be able to click on one of these guys and see more information down here on the bottom? Let us say you want to see a preview of what their note is or their phone number or whatever. Whatever other information you want, you could put down in a note box down here, let us say.
So, design view, I will just put an unbound text box across the bottom. Form design, text box, we will drop this down here. I am going to make this about yay wide, like that. Let us make it transparent - shape fill OK. Let us call this the moreInfo.
Now in our click event, we have an on-click event. Right here we will say
moreInfo = Nz(DLookup("phoneNumber", "customerT", "customerID=" & list1), "")
Come on. Save it. Now when you click on this, you see the number show up. They might not have phone numbers. Let me see here. Well, that is the next thing. We can make a double-click event so you can open up that customer.
Let us see, Julian Bashir, where are you? Sort by first name. Where is Julian? And Julian does not have a phone number. That is what I figured. I do not know. Where is me? Click. There is my phone number. How about phone number and notes? You could put that in there too.
You could say
moreInfo = Nz(DLookup("phoneNumber", "customerT", "customerID=" & list1), "")
moreInfo = moreInfo & " " & Nz(DLookup("notes", "customerT", "customerID=" & list1), "")
Well, we can make this multi-line, put a space between them, etc. Save it. Now if I click on me, there is all the information you want down here. Obviously, it is more than fits in there, but you can do whatever you want.
You want to open up these customers? OK, not a problem. Design view, make a double-click event. On double-click, open the form:
DoCmd.OpenForm "customerF", , , "customerID=" & list1
Close it, open it, click on me, double-click, and there I am. Then you just add the same event to these other boxes. Not hard to do.
Now, if you want to go further in the extended cut for the members, we are going to clean up a lot of that duplicated code. Remember I said between the different boxes, we have all this copied code? Well, I am going to show you how to do it properly, by passing the control names and such in VBA code. Then we will build one set of move left and right buttons that update the customer status automatically.
So instead of having separate code for each and every single button, we will write one reusable routine that moves the selected customer forward or backward through the process. Then we will do a little more visual cleanup and stuff. We will add colors to the columns and similar enhancements so it is not just functional, it is customizable too.
If you really want to take this to the next level, that is exactly what we are going to do in my Access Developer Level 58 class. We are going to make this entire system fully dynamic. Instead of hard coding for columns, we will build it so you can have anywhere from two to nine stages, all controlled from a table, and all customizable. We will store the position, size, colors, and labels for each column in that table. Then you can have the form build itself dynamically when it loads.
So if you want to add a new stage or move a stage, rearrange them, you can do it all without redesigning the form. In other words, your users can customize this at runtime. It will be really cool. We will also take it a step further and implement real drag and drop behavior. You will be able to click on a customer and drag them from one column to another and then drop them exactly where you want them.
Let us take Ben Sisko, click, and drag him over here to completed. Look at that. Or how about we take Dr. Bashir and move him over here at first. Boom, look at that. Real click and drag. We will handle the mouse events, calculate where the drop happens, and update the data accordingly.
On top of that, we will add things like automatic form resizing based on how many columns are visible, centering the navigation buttons automatically, hiding unused controls, and even building a developer utility that lets you pop the form into a design-friendly layout so you can easily make changes.
So if you want a fully customizable, dynamic Kanban system with drag and drop built right into Microsoft Access, that is what is going to be built in Developer 58, available on my website. I will put a link down below.
But now you have a fully functional Kanban system for Microsoft Access, and 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 next time, and members, I will see you in the extended cut.Quiz Q1. What is the purpose of the command buttons added to the Kanban board in Microsoft Access?
A. To add new records to the database
B. To move items between different status columns
C. To delete selected customers
D. To sort the records alphabetically
Q2. When moving an item from one list box to another, what needs to be updated in the database?
A. The customer's name
B. The statusID field in the customerT table
C. The primary key of the statusT table
D. The form's caption property
Q3. Which method is preferred by the instructor for running an SQL UPDATE statement in VBA?
A. RunSQL only
B. Use of Macros
C. CurrentDB.Execute
D. Exporting to Excel first
Q4. What needs to be done on the form after updating a customer's status in the database?
A. Compact and repair the database
B. Manually refresh Access
C. Requery the relevant list boxes
D. Delete and recreate the list boxes
Q5. How can the code ensure only one customer at a time is selected across all Kanban columns?
A. Use a macro to disable controls
B. Set other list box selections to null in the on-click event
C. Delete all other records
D. Hide all other list boxes
Q6. How does the instructor suggest displaying the status description at the top of each column instead of just numbers?
A. Typing them in manually
B. Using DLookup to retrieve the description from statusT
C. Adding new fields to customerT
D. Sorting the list by status
Q7. How can you show additional customer information, such as phone number or notes, on the form?
A. By opening a report in print preview
B. By using a text box with DLookup in the on-click event of a list box
C. By editing the table directly
D. By exporting data to Excel
Q8. What functionality can be added to allow users to open a customer record directly from the Kanban board?
A. Adding a hyperlink field
B. Using a double-click event on the list box to open a form filtered by the selected customerID
C. Creating a macro to print the record
D. Placing a checkbox next to each name
Q9. What suggestion is made for future code improvement regarding the movement buttons?
A. Use separate routines with unique code for each move action
B. Use a reusable VBA function passing control names to avoid duplicated code
C. Use Excel macros instead of Access VBA
D. Delete the buttons after each use
Q10. In the advanced version (Developer 58), what dynamic feature is emphasized?
A. Hard coding the columns in VBA
B. Allowing for a fully customizable number of Kanban columns, with properties stored in a table
C. Limiting customization to only color changes
D. Only allowing two status columns
Q11. What advanced user-friendly feature is demonstrated for moving records in the Kanban board?
A. Prompting for confirmation for every move
B. Implementing real drag and drop behavior between columns
C. Copying the record to all columns
D. Locking records from being moved
Q12. Why does the instructor suggest it is acceptable to use four individual DLookups for setting the column labels in this case?
A. Because DLookup is always the fastest option
B. Because there are only four columns, so the performance impact is minimal
C. Because form load events cannot handle loops
D. Because Access cannot use variables in label captions
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-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 video from Access Learning Zone continues the Kanban board project in Microsoft Access. If you have not watched the first part yet, I recommend doing that before going further, as this lesson builds on the foundation established previously.
At this stage, the Kanban board interface is set up and visually organized. The next step is to add functionality that allows users to move items - represented here by customer records - from one stage to another on the board. To achieve this, I begin by designing command buttons that move selected items between adjacent list boxes. First, I demonstrate how to move a customer from the first stage to the second stage by adding a command button, adjusting its appearance, and naming it clearly so the code remains organized.
The key to moving an item is updating the relevant customer record in the database so that its status reflects the new stage. This update is performed by running an SQL statement that changes the status ID of the selected customer. After making the update, both the source and destination list boxes are refreshed, ensuring the interface visually reflects the change. I also include a small alert to the user - a beep - to confirm that something has happened.
Testing this interaction, I show that picking a name from the first list and clicking the move button shifts the person to the second stage. To enhance the interface, I adjust the code so that after the move, the transferred person is highlighted in the destination list. This involves storing the selected customer's ID before the update and then setting the appropriate list box to highlight that same record after the lists refresh.
Once the first move function works, adding the remaining move buttons becomes a matter of copying, pasting, and adjusting code for the other direction and stages. Buttons are added to handle moving items forward and backward through all stages, from one through four and back. Even though this method involves some repetitive code, it keeps the example simple and easy to follow. I mention that better solutions for reducing repetitive code will be explored further in the Extended Cut for members.
For interface usability, I address the issue of selecting multiple items at once, which can be confusing. By adding simple code in each list's click event, I ensure only one item is ever selected at a time by clearing the selections in the other lists whenever a new list receives focus. While multi-selecting is possible, it requires a more advanced approach which I teach in another class.
Next, I discuss replacing the basic numerical stage labels with more descriptive captions. Using DLookup in the form's On Load event, I dynamically set the label text for each stage to match the description stored in the database. Even though this could be made more efficient with a loop or recordset for larger boards, given there are just four stages, the simple approach is perfectly acceptable here.
Additional interface improvements include showing more detailed information about the selected customer, such as phone number and notes, in a dedicated area at the bottom of the form. This is accomplished by adding an unbound text box that is populated using DLookup when a list item is clicked. For even more advanced functionality, double-clicking a customer opens their record in a detailed form, providing a seamless workflow.
For those interested in taking this solution to the next level, I explain that today's Extended Cut for members will focus on eliminating duplicated code by developing a reusable routine for moving customers between stages. This approach involves passing control names and dynamically handling movements with a single set of move-left and move-right buttons. We'll also implement visual enhancements such as color coding columns and making the interface more visually appealing and customizable.
Looking ahead, my Developer Level 58 class will guide you through creating a fully dynamic and customizable Kanban system in Access. It will support any number of stages, drag-and-drop functionality, dynamic form resizing, and runtime customization of stages and appearances, all stored and managed in a backend table. You'll even learn how to implement true click-and-drag interactions for moving records between columns.
With today's lesson, you now have a functional Kanban-style progress tracker in Microsoft Access. If you want to work through these steps in detail, you can find the 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 move buttons for Kanban columns in Access
Writing VBA code for button click events
Updating statusID field to move records between columns
Requerying listboxes to reflect changes
Highlighting the moved item in the destination listbox
Creating reverse move buttons for moving items backward
Clearing other listbox selections on item click
Using DLookup to set label captions dynamically
Displaying additional customer info in an unbound text box
Showing multiple customer details using DLookup
Opening a customer form on listbox double-clickArticle In this tutorial, I'll walk you through building a functional Kanban board in Microsoft Access. We'll set up buttons that let you move items between columns, both forward and backward, and I'll show you ways to improve the usability and look of your board. The overall goal is to manage records - such as customers - by moving them through a process, changing their status as they progress.
Starting off, suppose you already have your basic Kanban form set up with four ListBox controls, each representing a different stage in your process (for example, New, In Progress, Review, Completed). The records in each ListBox are filtered by a statusID field in your table - here, I'll call that table customerT. Each ListBox (list1, list2, list3, and list4) displays items with statusIDs of 1 through 4, respectively.
Next, you'll need buttons that can move the selected item from one ListBox to the next, and also backward if necessary. Let's start by adding a button to move an item from box one to box two. In Design View, add a Command Button below the first ListBox. Cancel the Command Button Wizard if it appears, since Access doesn't provide a built-in wizard for moving items between ListBoxes. Set the button's caption to your liking - for instance, use a right arrow symbol or the words Move to Next. Increase the font size and make it bold if you want to make it more prominent. Give this button a meaningful name in its properties window, such as moveOneToTwoBTN, so it's clear what it does.
Now, let's write the code for the button. Open the Click event for moveOneToTwoBTN and enter the following VBA code:
If IsNull(list1) Then Exit Sub
You can add a message box if you want to alert the user that they must select an item in list1 first. The main job of this button is to update the statusID for the selected customer from 1 to 2. You can do this with an SQL UPDATE command:
Dim ID As Long
ID = list1
CurrentDb.Execute "UPDATE customerT SET statusID = 2 WHERE customerID = " & ID
After updating, you need to refresh the affected lists so the change appears immediately:
list1.Requery
list2.Requery
Optionally, you might want to highlight the moved record in its new box. You can do this by setting:
list2 = ID
This will make the same customer selected in list2. You can also add a beep or confirmation message if desired.
With the code compiled and tested, try moving a record using the button. Select a customer in list1, click the Move to Next button, and they should appear in list2, now highlighted.
You will want to add similar buttons for moving items from list2 to list3, and list3 to list4, and also to allow moving backward (from 4 to 3, 3 to 2, and 2 to 1). Each button's code closely resembles the code above, only updating the statusID value and referencing the appropriate ListBox controls. For example, to move from list2 to list3:
If IsNull(list2) Then Exit Sub
ID = list2
CurrentDb.Execute "UPDATE customerT SET statusID = 3 WHERE customerID = " & ID
list2.Requery
list3.Requery
list3 = ID
Repeat similar steps for the other transitions, simply adjusting the statusID and ListBox names to match the direction you're moving.
Although this approach results in some repeated code, it is simple and effective for a small number of stages. If you find yourself repeating very similar blocks of code, you can eventually refactor your solution to make it more dynamic and reusable, but for now, this will get you a working Kanban board.
To enhance usability, it's a good idea to enforce that only one record can be selected at a time across all ListBoxes. To do this, add an On Click event for each ListBox that clears the selection from the others. For list1, for example:
list2 = Null
list3 = Null
list4 = Null
Repeat similar logic for the On Click events of list2, list3, and list4, so that whenever you click on one, the others are deselected. This prevents confusion and ensures the move buttons only act on a single item.
You might wish to improve the labels above each column. Instead of just displaying "1", "2", "3", and "4", you can pull the actual status names from your statusT table using DLookup in the form's On Load event. Suppose your labels are named label1 through label4. In the form's On Load event, use:
label1.Caption = Nz(DLookup("description", "statusT", "statusID=1"), "1")
label2.Caption = Nz(DLookup("description", "statusT", "statusID=2"), "2")
label3.Caption = Nz(DLookup("description", "statusT", "statusID=3"), "3")
label4.Caption = Nz(DLookup("description", "statusT", "statusID=4"), "4")
This change displays the real status description for each column.
You can also add a text box at the bottom of your form to show more details about the selected record, such as their phone number or notes. Add a text box named moreInfo. In the Click event for each ListBox, use code like this to update the box based on who is selected:
moreInfo = Nz(DLookup("phoneNumber", "customerT", "customerID=" & list1), "") & vbCrLf & Nz(DLookup("notes", "customerT", "customerID=" & list1), "")
Adjust the lookup field names as needed for your specific table fields.
If you want quick access to editing a customer's full record, set up a double-click event on each ListBox. When a record is double-clicked, use:
DoCmd.OpenForm "customerF", , , "customerID=" & list1
(Replace customerF with the actual form name for editing customers.) Add similar events for list2, list3, and list4 as needed.
You can further polish your board by customizing colors for columns, dynamically filling in labels, or allowing your users to configure the stages and their order. If you want to enable moving multiple records at once, look into enabling multi-select on the ListBoxes and writing code that loops through all selected items.
For more advanced features, such as a fully dynamic number of stages, drag-and-drop functionality to move records between columns, and a responsive layout, you would need significantly more VBA. These enhancements allow users to modify the process stages at runtime and update the Kanban board's appearance and behavior on the fly. You would store information about stages and their properties (such as position, color, name) in a table, and build the form dynamically, including real click-and-drag drop actions.
But with the steps above, you now have a functioning Kanban board in Microsoft Access - users can move items forward and backward, only one is selected at a time, columns are labeled based on your data, and you can display more details or open a record directly from your board. You can further customize this approach to match your business workflow and make as many visual tweaks as you desire. |
