Kanban Board
By Richard Rost
9 hours ago
Build a Kanban Board to Track Sales, Customers & Tasks In this lesson, we will introduce how to create a Kanban board in Microsoft Access to visually track customers or projects as they move through different stages. We will discuss the basics of what a Kanban board is, set up the necessary status tables, add a status field to customer records, build queries to show status information, and design a basic unbound form with list boxes representing each workflow stage. I will show you how to configure the data sources for the Kanban board so that each list box displays customers at a specific stage, preparing the foundation to add interactive features in the next lesson. Chris from Matapanai, Virginia (a Gold Member) asks: I did a Google search to see if a Kanban system can be made in Access. The only video of yours that came up was about a Gantt chart. Google says it can be done and that it's actually well suited for Access since it's a relational database. Would you consider putting together a video on how to do this? (Not that I won't keep trying to figure it out myself.) MembersThere is no extended cut, but here is the file 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, Kanban board Access, visual pipeline Access, customer tracking Access, project tracking Access, workflow management Access, move records VBA, list box Access, status table Access, combo box Access, relational combo box, status query Access, customer form Access, unbound form Access
Subscribe to Kanban Board
Get notifications when this page is updated
Intro In this lesson, we will introduce how to create a Kanban board in Microsoft Access to visually track customers or projects as they move through different stages. We will discuss the basics of what a Kanban board is, set up the necessary status tables, add a status field to customer records, build queries to show status information, and design a basic unbound form with list boxes representing each workflow stage. I will show you how to configure the data sources for the Kanban board so that each list box displays customers at a specific stage, preparing the foundation to add interactive features in the next lesson.Transcript What if you could track your customers or projects in Microsoft Access like a visual pipeline and move them from stage to stage with a single click?
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today we're going to build a Kanban board in Microsoft Access using simple columns to represent each stage.
We'll set up buttons that let you move records from column to column automatically, and you can manage your workflow quickly and easily.
Today's question comes from Chris in Matapanai, Virginia. One of my Gold Members.
Chris says, "I did a Google search to see if a Kanban system can be made in Access. The only video of yours that came up was about a Gantt chart. Google says it can be done, and that's actually well suited for Access since it's a relational database. Would you consider putting together a video on how to do this? Not that I won't keep trying to figure it out myself."
First of all, Chris, yes, keep trying to figure it out yourself. That's the best way to learn. But I'm also going to show you some tips and tricks that you might not have thought of. So we're going to get into it.
Also, a Gantt chart is good for tracking projects that have overlapping phases. But when you want to track when you move a project from one phase directly to another, I think a Kanban system is better for that.
Yes, in my preview video, if you guys watched it, I was calling it a Kanban system, but I had to look up the correct pronunciation. Yes, it is Kanban, so I'm happy about that.
Now, what is a Kanban board? If you're not familiar with the term, don't feel bad. I had to honestly look it up myself the first time someone asked me about it. I've built databases that do this exact thing in the past, but I didn't know they were called Kanban boards.
But it's basically a visual way to track work as it moves through different stages. Think of it like a signboard. We can see everything at a glance. Instead of digging through reports or scrolling through lists, you just lay things out in columns like lead, prospect, customer, done. Each item moves from left to right as progress is made, or backwards sometimes.
This works great for all kinds of things. You can use it to track sales leads as they move from initial contact to closed deal. You can use it for project management where tasks go from planned, in progress, completed. You can use it for support tickets, job applications, content creation, really anything that moves through a series of steps.
The big advantage is you can instantly see where everything stands without having to read through a bunch of data.
Now, in Microsoft Access, we absolutely can build a Kanban board. We'll use multiple list boxes, one for each stage, load records into the appropriate list based on their status, and then use buttons to move items from one column to another.
You're still getting that visual workflow, but with the power of Access behind it.
And you can even track Khan on the Kanban board. First, he's picked up by the original Enterprise. Then he's marooned on Ceti Alpha Five. Then he takes over the Reliant. Then he goes boom. So, see, you can track the progression easily using the Kanban board.
Before we get started, this will be a developer level video. You should be familiar with a little VBA. If not, go watch this video. It's about 20 minutes long. It will teach you everything you need to know to get started programming in VBA with Microsoft Access.
We're also going to use a little SQL, not a ton, just a little bit. But go watch this video if you're not familiar with or comfortable using basic SQL. These are free videos. They're on my website. They're on my YouTube channel. Go watch those, and then come on back.
There may be a couple of others down the line, but these are the two big ones for now.
Here I am in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to, but you can use any database that you want.
First, we're going to create a status table. Create, table design. This will be what actually contains the statuses for us. For example, we're going to do customers. So we got the statuses: lead, then prospect, then customer, then follow up.
Status ID is going to be my primary key.
A sort order, because you might want to change this in the future, will be a number.
A description. You could put whatever else you want that describes that status. A note field in here tells the user exactly what the status means, but we're just going to keep it simple. Save it, statusT.
Then let's put some status, some statuses. One will be lead, and they're just going to happen to match the autonumbers, but that might not always be the case because you might add ones in the future, delete ones, move them around. It just so happens that they match up right now.
Three will be customer. They've actually paid and they're now a customer, but they still have to be followed up upon.
This is the simplest example that I can think of that everybody can relate to. You could use it. You can do project status. You can do all kinds of stuff in here.
So there's our status table.
Now we need a place to store that status in the customer table. So let's go to the customer table design view.
What is this customer's Status ID? That'll be a number. That's your foreign key. Save it. Close it.
Before I put it on the customer form, I would actually like to see the status number and their description together. But if you do it in the combo box, you'll see two columns, sure, but you only see one of them when it's closed. So we're going to make a query for that.
Go to create, query design. Close all this stuff. Bring in the status table. Let's call it Status Text:. I'm going to zoom in for you. Shift F2.
The status text is going to be basically the sort order and a period and the description. So it will be like "1. Prospect". Or lead or whatever one was.
Run that. There you go. Now we got this, which we will see in our combo box, just like we did last name and first name before.
Save this. This will be my StatusQ.
Now we got something we can stick on the customer form right here.
Design view. Open it up. Put it right down here at the bottom. Go to combo boxes.
If you don't know how to do this, this is called a relational combo box. Go search for it on my website.
Choose to look up the values in a table or query. We're getting our values from StatusQ.
Next. We want the Status ID, which is the bound field. I also want to see the status text that we just made.
Next, we're going to sort by status text because that actually includes the sort order in it.
Next, we're going to hide this column because that's the bound value. We need that in there, but this is what we want to see. Later on, if you do decide to add more and you get over nine, just go back and change these to 01, 02, 03, just for a little bit of formatting. It will still sort properly.
Next, Status ID is the bound field.
Next, store that value in the Status ID on the customer record.
Next, what do you want to call it? Status is fine.
There we go.
Format Painter. Make the label look pretty. Slide it into place.
There you go. Now we're good to go.
I'm also going to drop this on the customer list form on this guy because then you can see the status right here and that makes it easy too. Since we got this guy already built, we can just copy and paste it.
Watch this. Let's save this one. Let's make sure this one works first. Looks good. I'm a lead, James Kirk is a prospect, Diana is a follow up.
That's working great. Now, let's go back to design mode on this one. Let's come over here. Let's go to design mode on you.
Just to keep things easy for class, I'm going to delete this stuff. We don't need all this. Delete these.
Let's take the status box, copy and paste. Then we'll slide it over here. Your label came in. Put that right there. Size it up a little bit. Do this. Change this to Status.
Save it. Close it. Save it again. Close it. Open it up. Now there you go. Now you can just come in here and go 1, 2, 2, 1, 3, 4, 4, 4. You see how easy it is now? You can come in here and just change stuff.
You could sort this if you wanted to. All the nulls are up top. There they are. You can move things, but it's not as easy as moving things around visually on a Kanban board.
So that's what we're going to build. Now in my database here, I've got a couple of blank forms that I keep around. I got a single form. Let's use this guy.
We're going to copy, paste, Ctrl+C, Ctrl+V. I'm going to call this KanbanF. It's down here. Right-click and Design View. There we go.
The form itself is going to be unbound like it is right now. It's not going to have any data in the form itself. There's going to be no single record associated with this form.
We're going to have list boxes on it that will have their own data, but that data is going to be controlled through VBA code and SQL.
We can get rid of these, and let's turn off some things for formatting purposes here. Go to Format. Turn off Record Selectors. We can turn off Navigation Buttons. We're not going to need scroll bars.
Close that. Save this. Close it and reopen it.
Looks good. Move it over here. Maybe while I'm thinking about it, let's hijack the Hello World button and make that open up our Kanban board. So come over here, Kanban.
Right-click, Build Event, Code Builder loads up.
Let me resize it.
Where's our button? So we're not going to Hello World. We're going to do Command OpenForm, KanbanF.
Save it. Debug Compile on some. Make sure you don't have any compile errors and close that.
Open it. Kanban. Here we go.
Now, what do I want to see in each of these list boxes? I just basically want to see the customer first name and last name. You could put anything you want in here.
In fact, when I announced I was doing this, people were asking, "How do we get more information to show up here?" You can put whatever you want in each list box.
I'm just going to show first name and last name for now. You can put their phone number. You can make it so when you click on one of them, you see more information on the bottom. We'll do that maybe later on.
Let's make a query that we can use to fill each one of these list boxes. We can do it with one query: Create, query design. We're going to bring in the customer table. Where are you, CustomerT?
What I'm going to want in here is the CustomerID. That way, when I click on that customer, I can do things like open up that customer's record.
I want first name and last name, but, just like we did a minute ago, I want to see them as one field. I don't want two separate columns. So let's call this CustomerName: FirstName & " " & LastName. A little string concatenation there for you.
Finally, I will need the StatusID so I can break them up into different boxes.
Run this real quick. See what we got. Looks good. CustomerID (that'll be hidden), CustomerName, and their StatusID.
Save this as CustomerStatusQ.
That's our CustomerStatusQ and we can use this to build the list boxes.
Let's make a list box. Go to design view, form design. Find the list box. There it is right there. List box. Click and drag it out here. Look up the values from a table or query. I want that CustomerStatusQ.
Next. Let's bring over all three of these. Next.
How do you want to sort these? That's up to you. I'm going to sort it by the customer name. How do you want each box sorted? They're all in the same status group, so might as well sort by customer name.
Next. What do you want it to look like? Well, I don't want to see the CustomerID. That's got to be hidden. And I don't want to see the StatusID either. We're going to break them up into individual boxes, so just hide this one. Set its width to zero as well.
Make this guy as wide as you want it to be.
Next. CustomerID is the bound field, even though it's not being saved anywhere. We're still going to use its value, so CustomerID is fine for that. What label would you want? We'll change the label in a minute, and finish.
Let's put the label up top here. That looks good.
Save it. Close it. Open it. It's a little narrow. Let's make it a little bigger.
I don't like Layout Mode. I know there's a Layout Mode where you can still see the data in there. I say this in a lot of my videos. I hate Layout Mode, probably because when it first came out, it was really buggy. If you had VBA in your form at all, it would mess things up on you. I just haven't liked Layout Mode since day one.
So I always go to Design Mode and then I just make my changes.
That looks good.
Part of the problem, though, as I noticed, I'm seeing everybody in here. I never gave an opportunity to change the criteria, so I could see, like, just people in status one. Because I want four different boxes for each of the statuses.
What we could do now, if you wanted a non-programming solution or a non-SQL solution, is you could take this and make CustomerStatus1Q, CustomerStatus2Q, CustomerStatus3Q, and so on. Make four of them, and then use that to fill that box. You'd only get status one. But this is not a beginner video, so in here, we're not going to put a criterion. What we're going to do is switch to SQL view.
Copy this SQL, go back to our Kanban board design view. Open up this guy's properties, find the data. Right here where it says Row Source, Shift F2. Paste that in here.
Look at that. Now we're only getting data from the Customer table, so we can simplify this by getting rid of all the CustomerT's. Get rid of CustomerT there, get rid of CustomerT there.
Leave CustomerT here. You need that. We can get rid of the brackets since we're good little programmers and we don't have spaces in our field names. This makes it a lot easier to read.
CustomerID, FirstName, LastName as CustomerName (that's an alias), comma StatusID from CustomerT where StatusID = 1. If you want to put your ORDER BY in here, ORDER BY FirstName, LastName, because at this point it doesn't know what CustomerName is. I know it's weird.
Hit OK.
Save it. Close it. Close it. Open it and look at that. There's all of our customers in status one. You can verify that by going over here. Take a peek. There's all the ones: The Card, Crusher, Me, and O'Brien.
Now we just copy and paste that for the other groups. Copy, paste, paste, paste. One, two, three, four.
I got one too many. There's a copy bug that is going around. A lot of people have complained about it. It's been hitting me too. If you don't copy and paste fast enough, that happens. But I'm not too worried about it.
It has to do with the Windows clipboard manager. If you turn the clipboard manager off (that lets you have multiple things on your clipboard and you can go back through them), then it goes away. I'm going to do a whole video about it later on, but it's a weird bug.
So there they are. Now we just have to change the SQL in each of these boxes. This will be box two, so two. This will be box three. This one will be box sixteen, just kidding. This will be box four.
Now, what about their names? List4, List6. This is one of those rare circumstances where I actually like List1, List2, List3, but I don't want four because it goes by how many total controls are on here. So let's change this guy. The name of it, which we'll use later, we're going to make this List1.
This one is going to be List2, List3, and List4.
We'll do the labels. For the labels now, it's just 1, 2, 3, 4. Let's make them readable--format, dark black background with white foreground, bold, and centered.
Then we'll do this. How's that? Much better.
We're getting there. Save it. Close it. Open it. Progress, progress. We got someone in each. For now, you can click on multiple values. We'll deal with that later too.
Now we're ready to start putting buttons down here to move these guys from box to box.
We'll cover that in tomorrow's video.
Tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it in just a few minutes because I'm going to keep recording today and I'll probably finish the whole series tonight, so it'll be online if you're a member.
For everybody else, that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.Quiz Q1. What is the primary purpose of a Kanban board as discussed in the video?
A. To store large amounts of unrelated data
B. To visualize and manage workflow through different stages
C. To automate email responses to customers
D. To create complex financial reports
Q2. Which Microsoft Access control type is primarily used for displaying each stage of the Kanban board?
A. Combo Boxes
B. Text Boxes
C. List Boxes
D. Option Groups
Q3. According to the example in the video, what is the table that stores each possible status called?
A. CustomerT
B. StatusQ
C. StatusT
D. StatusF
Q4. What is the purpose of the SortOrder field in the StatusT table?
A. Maintain unique values for each status
B. Track the foreign key relationship
C. Allow for future changes in the order statuses are displayed
D. Count the number of statuses
Q5. What is the recommended data type for the StatusID field in the CustomerT table?
A. Text
B. Autonumber
C. Number
D. Date/Time
Q6. How are the status and its description combined for use in a combo box?
A. By concatenating StatusID and Description with a period
B. By listing them on separate lines
C. By using nested queries
D. By displaying only the StatusID
Q7. What is the primary key in the StatusT table?
A. StatusOrder
B. StatusID
C. CustomerID
D. StatusDescription
Q8. What is the intended function of buttons in the Kanban board form?
A. Generate printable reports
B. Move records between stages/columns
C. Sort list boxes alphabetically
D. Delete customer records
Q9. Why does Richard recommend using a query (StatusQ) for the combo box on the customer form?
A. It helps display both status number and description together
B. It hides the status completely from the user
C. It sorts customers by last name only
D. It makes combo boxes multi-selectable
Q10. How does the video suggest constructing separate list boxes for each Kanban status stage?
A. Use one large list box with filtering
B. Use individual list boxes, each with a different SQL criterion for StatusID
C. Use continuous forms embedded in a form
D. Use a macro to cycle through records
Q11. When constructing the CustomerStatusQ, what field combination creates the CustomerName?
A. StatusID & LastName
B. FirstName & " " & LastName
C. StatusDescription & LastName
D. CustomerID & StatusID
Q12. Why would you use a sort order that includes a two-digit format like 01, 02, 03 for statuses?
A. It allows hiding fields in queries
B. It ensures correct alphabetical and numerical sorting as numbers reach double digits
C. It is required by Access for all primary keys
D. It is necessary for using macros
Q13. In the Kanban board form, why is the form left unbound?
A. To increase performance
B. To display only summary data
C. To avoid associating the form with a single record and allow multiple list box controls to be managed with VBA and SQL
D. To make the design simpler for beginners
Q14. If someone wanted to add more information to each entry in the list box, what is suggested?
A. Only use first and last name, nothing else
B. Add additional fields to the query used by the list box
C. Create new tables for each stage
D. Use only one list box for all data
Q15. What language skills are recommended prerequisites before building this Kanban board system in Access?
A. HTML and CSS
B. VBA and basic SQL
C. C++
D. Python
Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-A; 7-B; 8-B; 9-A; 10-B; 11-B; 12-B; 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 video from Access Learning Zone focuses on creating a Kanban board using Microsoft Access. Many people have wondered if it's possible to visually track customers, projects, or tasks through various workflow stages within Access, and the answer is yes, we can build a simple and effective Kanban board inside Access.
I start out by talking about the usefulness of a Kanban board. If you are not familiar with it, a Kanban board is a visual tool that lets you monitor work as it moves through a sequence of stages. It is commonly used in sales, project management, support tickets, and similar systems where you want to quickly see what stage each item is in, without digging through endless lists or reports. For example, you can have stages such as "Lead," "Prospect," "Customer," and "Follow-Up" and move records from one stage to the next as their status changes.
Building a Kanban board is a little different from using a Gantt chart in Access. While a Gantt chart helps with tracking overlapping project phases, a Kanban board is more about moving items straight from one stage to the next, and it gives you a much clearer overview of what's happening at a glance.
Now, let me explain how to set this up in Access. We begin by creating a status table. This table stores the different stages your records can be in. For the example in this video, I used customer statuses. The table contains a primary key (Status ID), a sort order (which lets you control the display order of statuses), and a description. You can always expand this later with more fields if necessary.
Once your status table is ready, you need to link these statuses to your customer records. To do that, add a Status ID field to your customer table; this serves as a foreign key. On your customer form, you'll want to display both the status number and its description. However, combo boxes in Access only show one column when closed, so the next step is to create a query that combines the sort order and status description into a single text string, such as "1. Prospect." This query feeds your combo box so users see both the order and name of the status in one line.
After updating your customer form to show and select statuses using this combo box, it is a good idea to add the same control to your customer list form. This way, you can quickly update statuses across multiple customers, making your workflow more efficient.
While this lets you change statuses, it's not yet a full Kanban experience. To get that, we switch to building a board-style form. You need to start with a new, unbound form - meaning the form itself isn't tied directly to one record. Instead, you will create several list boxes on this form, one for each status or stage. Each list box will display all customers currently in that stage.
You'll use a single query that pulls customer ID, the combined customer name, and the Status ID. This query forms the basis for each list box. In each list box's row source, you modify the criteria so it only shows customers in a specific status. For example, in one list box's SQL statement, you specify that StatusID equals one. In the next, StatusID equals two, and so on. You repeat this for each workflow column.
Next, organize the form so each list box represents a column (like "Lead," "Prospect," "Customer," and "Follow-Up") and format the labels so it's easy to see each stage at a glance. Assign clear names to all list boxes (List1, List2, etc.) since you will reference them later.
With this setup, you now have a board where you can see all your records grouped by their workflow stage. It's already a major improvement over just scrolling through a long list.
The next step, which I will cover in the next video, is adding buttons that let you move records from one column to the next, updating their status with a click. This final touch makes your Kanban board interactive, allowing you to quickly manage the flow of your customers, projects, or other data.
This process uses a bit of VBA and SQL, but you don't need advanced programming knowledge - just a basic familiarity with Access VBA and some simple SQL concepts. If you're new to those, I recommend checking out my introductory videos on both topics, which are available for free on my website and YouTube channel.
If you want to follow along, you can use the free Access template available on my website, or work in any database of your choice.
For complete step-by-step instructions and to see the entire tutorial in action, visit my website at the link below. Live long and prosper, my friends.Topic List Introduction to Kanban boards and use cases in Access
Creating a status table for pipeline stages
Setting up status IDs and sort order
Adding status descriptions for each pipeline stage
Establishing a foreign key in the customer table for status
Building a StatusQ query for display and sorting
Adding a status combo box to the customer form
Copying and customizing the status combo box on the customer list form
Demonstrating status assignment and sorting in forms
Designing an unbound form for the Kanban board layout
Configuring form properties for the Kanban view
Linking a button to open the Kanban board form
Creating a query to combine customer name and status
Building a list box sourced from a query for Kanban columns
Writing SQL to filter list box contents by status ID
Duplicating and customizing list boxes for each Kanban stage
Renaming and formatting list boxes and labels for clarity
Adjusting form design for visual workflow managementArticle If you have ever wanted a way to track your customers or projects in Microsoft Access using a visual pipeline - where you can instantly see what stage each item is in and move them from stage to stage with a click - then building a Kanban board in Access is a great approach. Let me walk you through how you can build your own Kanban board right inside Access, even if you only have basic VBA and SQL skills.
First, let's clarify what a Kanban board is. In simple terms, it is a visual layout that helps you manage workflow by dividing tasks, customers, or projects across columns, each representing a stage of a process. For example, in sales, your columns might be Lead, Prospect, Customer, and Follow-Up. As your contact moves through the sales funnel, you move the card from one column to the next. This kind of view can also be used for tracking project tasks, customer support tickets, job applications, and just about anything that progresses through a series of stages. The big benefit is that you can quickly get a bird's-eye view of where everything stands by stage, which saves you from having to comb through lists or reports.
In Access, you can achieve this look and feel by using several list boxes - one for each stage - and then setting up queries and code to tie those stages to your underlying data. Let's build a simple Kanban board for tracking customers across four stages: Lead, Prospect, Customer, and Follow-Up.
To get started, you first need a way to store your different statuses. Create a table called StatusT. The table should include a StatusID as the primary key (an autonumber), a SortOrder field (a number) to specify the order statuses appear, and a Description field with the name of the status, like Lead or Customer. You might also want a Note or more detailed Description, but to keep things simple, just stick with the main fields.
Once your StatusT table is created, enter your stages. For example, record 1: Lead, 2: Prospect, 3: Customer, 4: Follow-Up. The actual numbers don't have to match the order you want; that's what the SortOrder field is for.
Now, go to your CustomerT table (or whatever your main data table is - it could be Projects, Tasks, etc.) and add a StatusID field. This should be a Number field and will act as a foreign key linking back to your StatusT table. Save your changes.
Ideally, when you are looking at your data, you want to display the status in a simple and readable way, for example, "1. Lead" or "2. Prospect," not just a number or a separate status description. To do this, create a query called StatusQ. This query should include a calculated field to combine the SortOrder with the Description, like so: StatusText: [SortOrder] & ". " & [Description].
Using this query, you can now add a combo box to your customer form that allows users to choose a status. When setting up the combo box wizard, point it to StatusQ as its data source. Include both StatusID and StatusText, make StatusID the bound column, and hide the key column so users only see the readable text. Sort by StatusText so they display in the right order. Assign this combo box to save its value in the StatusID field of your customer record.
With this combo box on both your single-customer and customer-list forms, you will easily be able to update customer status. However, simply changing the status through a drop-down is not as intuitive as seeing all your records in a visual pipeline and moving them between stages with a click. That is the real goal of our Kanban board.
For the Kanban board itself, start by making a new unbound form in your database. You can use a blank form or copy an existing one. Name it something like KanbanF. Make sure it does not have a record source, navigation buttons, or record selectors. This form will be used solely for your visual board.
Next, you need a source for the data in your list boxes. Create a query called CustomerStatusQ with at least CustomerID, a calculated CustomerName field (FirstName & " " & LastName), and StatusID. That way, each item you display on your Kanban board will show the name, and you can use the ID in the background for your code.
Now, add a list box to the Kanban form. Use the wizard to pull data from CustomerStatusQ and include the fields CustomerID, CustomerName, and StatusID in that order. Hide the bound field (CustomerID), display CustomerName, and hide StatusID since each list box will only display items for a specific stage. Sort the list by CustomerName.
Next, instead of just using one list box, you will want one for each stage. To do that, switch the row source in each list box to pull customers for a particular status. You can do this by editing the Row Source property of the list box and writing an SQL statement like this:
SELECT CustomerID, FirstName & " " & LastName AS CustomerName, StatusID FROM CustomerT WHERE StatusID=1 ORDER BY FirstName, LastName;
Change StatusID=1 to StatusID=2, 3, or 4 for your other columns. Copy and paste the list box control for each status, changing the Row Source SQL as needed. This will result in four parallel list boxes, one for each board stage, showing just the customers in that stage.
It is important to name your list boxes clearly - List1, List2, List3, and List4, for example - matching each Kanban stage, so that later you can easily move items between them with code. Label each list box with the name of its status, such as Lead, Prospect, Customer, and Follow-Up, making the interface clear and user-friendly.
As you build out the rest of your board, arrange the list boxes horizontally, ensure their labels stand out with formatting, and size them so that you can read all the entries easily.
At this stage, every list box will show the customers in that particular status. It's already a huge step forward: you can now open the Kanban form and instantly see your pipeline, how many customers are in each stage, and who they are. Of course, all this is running from your database's live data.
To make this fully functional as a Kanban board, you need to add buttons or methods to move customers between stages with a click. This is handled with a bit of VBA code, which you will add next. You will set up buttons below each list box to "move selected to next stage," which will update the underlying record's StatusID and refresh the list boxes. While the code for that is covered in the next part, the setup described so far is the backbone of your Access Kanban board.
With this approach, you can apply the same technique to customers, projects, tickets, orders, or any process that moves linearly through defined stages. You can also customize the fields displayed, arrange the form to your liking, and extend the logic as needed for your workflow.
Access does not support drag-and-drop natively out of the box, but even with simple multi-column list boxes and some VBA buttons, you get nearly all the advantages of a Kanban view: instant, clear visual feedback about your pipeline, and one-click movement between stages.
Once you have this basic Kanban board functioning, you can expand it: add more information to each row, show record details on selection, or implement better navigation - whatever your process needs. With Access handling your data and VBA managing updates, you have a lightweight but powerful DIY Kanban system that works with all of your current records and forms. |
