Followups 4
By Richard Rost
4 years ago
Customer Followups in Microsoft Access, Part 4
In this Microsoft Access tutorial, we're continuing work on our Followup database. We will put our followups into categories. We'll make a category table and a combo box for the followup form and contact form. We'll make a printable report grouped by category, for all followups coming up by tomorrow, along with the customer's name and phone number.
Pre-Requisites
Links
Up Next
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, How To Create A Customer Follow-Up System, organize your database followups, Database Follow up, follow-up template, Sales Follow-up System, customer follow-up system, Creating an Effective Follow Up System, show only followups, button from main menu, customer combo, double-click to open, contact management, crm, Categories, filter by category, printable report, add combo to contact form
Subscribe to Followups 4
Get notifications when this page is updated
Intro
In this video, we continue building our follow-up database in Microsoft Access by adding categories to effectively organize follow-ups such as sales, service, and pre-sales calls. I will show you how to create a category table, set up a one-to-many relationship with your follow-ups, add a combo box for selecting categories on your forms, and sort and filter contacts by category. You'll also learn how to create a printable report grouped by category and set up a menu button to open it, plus fix common issues like filter errors after renaming controls. This is part 4.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today we are carrying on, moving forward with our follow-up database. This is part four. If you have not watched parts one through three, go watch them right now. You will find links down below that you can click on to watch them.
In today's video, we are going to add categories to our follow-up. So if you want to sit down and do all your pre-sales follow-ups or all your service calls, we will add a filter. I will show you how to filter by category. Then we will do a printable report. If you want to print it out and take it home to make actual phone calls, you can. I guess people still do that. Then we will add that combo box to the contact form as well.
So, lots to do today. Welcome back, fans. This is part four.
I was just playing around with the numbers and data, and we have to fix the tab order in here. Let's go to design view, and then tab order, and then select the detail section, auto, and click OK. That should do it. Let's see.
Okay, much better. I do not think it is going over to the date. The date was not in the tab stop in the last form. So go to Other and make sure the tab stop is set to Yes. There we go, that will do it. Okay, good, everything is all right. I am just going to back some of these up a little bit so we can see them in the list without having to hit all.
All right, there they are. Next up: categories. It would be nice to put these into categories like sales, service, pre-sales, tech support, or whatever. Or you could group them based on different departments you know: hardware, accessories, or whatever categories you want to use. So let's make a category table.
Create - Table Design.
CategoryID and a description. That is all you really need. Save it as CategoryT. Primary key, yes, and let's put some stuff in.
I am going to have:
1. Pre Sales
2. Sales
3. Service
4. Follow Up
I am putting the number in there intentionally because you cannot rely on the autonumber here to always be in sequence. That autonumber is not for you, and I would like to have these in order. That kind of doubles as a sort order. You could make a separate column for sort order if you want to. In fact, yes, let's do that. That is a better design. Let's add a SortOrder, and that will be a number, so you can specify the sort order yourself without having to use the ID like this.
So we will do:
- Pre Sales as 1
- Sales as 2
- Service as 3
- Follow Up as 4
- Other as 5
Now I am going to set this up in a one-to-many relationship, which means that each follow-up can be in one category. If you need to brush up on your relationships, go watch this video.
Since I am going to use a one-to-many relationship, I am going to go to the contact table and add a foreign key in here: CategoryID, which is going to be a number, type long integer. I like to keep all my keys up at the top, so put it right there: ContactID, CategoryID, CustomerID. So this table now has two foreign keys. It is linked to the Category table and the Customer table. The primary key is the ContactID.
Because of this, you can only have one category for each follow-up. If you need to do a sales and a service follow-up, you will have to make two different follow-ups, one in sales and one in service.
If you wanted to have it so that each follow-up could have two or more categories assigned to it, you would have to set up a many-to-many relationship with a junction table. I cover that in this video. For the purposes of our database, I think we are fine with just a single category for each of these. If you need one for sales and one for service, you are going to have to make two follow-ups.
Now we are going to add the category as a combo box to our follow-up form. Right-click, Design View. Let's just drop a combo box right over here. Find the combo box - there, drop it there.
Look up the values from a table or query. What table has the values that we want in it? Our category table. Bring over all the fields. Now, we do not need SortOrder to see it, but I need it to do the sorting, so just bring it in. Next. What field do you want to sort by? The SortOrder field. You could make a query that uses the SortOrder and then it does not bring it into the box, but this is fine. We are just going to hide it anyway.
Our key column is hidden, then Description, and then we will just hide SortOrder. Next, now we are going to store that value. What are we picking? We are picking a category, so we are going to store that in the CategoryID of the Contact form, or table. We are picking a category, saving it as the foreign key in the Contact table. Next. We are going to delete that label anyway and hit finish.
There is our combo box. Put it over here – you can put it at the beginning or at the end. That is fine. Copy one of these labels, paste, put that there: "Category." Slide it up, slide to the left, and save it, close it, open it, and there we go.
Now we just have to put these things into categories. That will be sales, that will be service, that will be follow-ups, that will be other, more sales, more pre-sales, and a pre-sales.
You can sort and filter based on any of these things you want to. If you want to sort these based on the category, you can right-click, sort A-Z. If you want to filter for just a specific customer's contacts, right-click and then filter on Richard Rost. Turn the filters off, and you can see just what you want. If you want to do just your pre-sales calls today, right-click equals pre-sales.
So you can sort and filter based on any of these things using Access's built-in tools. If you want to make it spiffy for your users, I have another video where I show you how you can make this header clickable, so when they click on that header it will sort based on that column. That is sometimes easier for novice users.
I also have a template available called the Search and Sort Template, where I put these little boxes above each column. You can filter based on just typing data into these boxes. You could do a credit limit from/to, a date from/to, or you click on each of these column headers and it changes the color. That is my Search and Sort Template. I will put a link to this down below as well.
There are lots of add-ons you can use to build cool features like that into Access if you want to make it easier for your users. But that functionality is all in here if you just know how to use it. Right-click, sort Z to A, for example. Right-click over here, sort by the follow-up dates.
How about a quick report? Some people like to print this stuff out. Some people are old school, they want to print out a list of their contacts they have to make. We will include the customer's name and phone number, and we will make the report only for follow-ups up to tomorrow, because you might print this and take it home or on the road.
The first step is to make a query that has all the stuff you want in it. Create, Query Design. Then we are going to bring in the Contact table. We're going to want to see that category name, so I'm going to bring in CategoryT. We're going to make this an outer join, because I want to see all the contacts and include the category even if they are missing one, because you don't have to pick a category. You could put a follow-up in with no category.
Let's remember to put that category combo box on our contact form so we can specify a category when we add the contact. We are going to want some customer information, so bring over the customer table. Now, all follow-ups have to have a customer, so we can leave that as an inner join.
What do you want to see on your report? I am going to bring over the star from one table when I am building queries. Do not bring in all the stars from all tables. Then you are going to get ContactT.CustomerID and CustomerT.CustomerID. Do not try to duplicate your fields down here.
Now, this follow-up has to be equal to true, and FollowUpDate has to be less than or equal to tomorrow. Let's take a peek to make sure it is working. All right, looks good. Let's bring in other information that we need: category description, customer's first name, last name, and phone - since we are going to be making phone calls.
Let's take another peek at it. Okay, now we have all the related information for each contact.
You are probably saying to yourself, "We have duplicated information here." Yes, we do, but we are going to group this stuff. Do not worry about it.
Save this as FollowUpReportQ. This is going to be used for my follow-up report.
I did just notice I got ContactT.Description and CategoryT.Description - that is okay, sometimes that is going to happen when you have a Description field in both tables. Just be aware of it.
Go make a report now. In my blank database, I have a blank R report. Let's design this. Actually, let's copy this guy first: Ctrl+C, Ctrl+V. Let's call this MyFollowUpR, My Follow Up Report. I think it is already formatted properly. Close that.
This guy is already formatted for an 8.5 x 11 sheet. Let's specify where we are getting our data from – the data is coming from that FollowUpReportQ.
Now, I keep this guy here just so I can format stuff. Let's go to Report Design, Add Existing Fields.
What do we want to see on the report? I do not need any of these IDs. I want the contact description, notes, I am holding down the Control key. If it is on this list, it is going to be a follow-up so we do not need that. Follow Up Date, I want to see that. I want the category description, and I want the customer's first name, last name, and phone number.
Bring all these fields over. Looking good so far. Let's take a peek, save it, and I have a button for Print Preview right there.
All right, looks good. Got some work to do. Back to Design View.
There is a reason I keep this guy around – so I can use the format painter and do this: look, look, look, look, look, look, look, look. It sets all the colors, turns all those formatting features off, turns the borders off around there, and makes this guy black so you can actually read it.
Now I can give it a label and I am going to line these fields up one over the other. Take all these fields out, cut them out, and paste them in the Page Header. The ContactT.Description we will put here as Contact, contact description, and this will be the category, so we will just call that Category. Move it up here.
Notes will go below the contact description, so we can delete the label and maybe put the Follow Up Date over here, and then the first name, last name, and phone number over here.
Move this underneath it, take the description and make it a little bit wider. Notes is going to go underneath, Follow Up Date next to it, the category here, first name, last name, and phone number.
Now, shrink this up, save it, print preview again. Looking a lot better.
Let's clean up these labels a little more and maybe put a line under them. If you do not have these buttons up here in the quick launch toolbar, I think I showed how to do this in the Blank Template video, but you can right-click on the header. You can see them there, or you can right-click below. There are a million ways to do it, or you can click down here in the bottom right corner. All kinds of ways to switch.
I set them up in my quick launch toolbar; that is where I am used to looking at them. Or, I right-click on the title bar, design view.
Follow Up Date, Contact Description, First Name, Last Name, Phone Number - slide this down a bit. Let's add a horizontal line. There you go.
Maybe we will leave a little bit of extra space there and select everything. One of my pet peeves with Access is that these things do not snap right to the grid. Right-click Size to Grid, now they should all be snapped to the little grid dots in the background.
Let's see what you look like now. I do a lot of flipping back and forth between print preview.
Category does not have to be that big. Follow Up Date can come to the left. Looking good.
I think the next thing I want to do is group everybody in the same category together. We are going to put a grouping level on. If you have never done grouping levels in reports, go watch this video.
Right-click, go back to Design View, come up to Group and Sort, add a group. What do you want to group by? Let's group them alphabetically by the category description. Why not: CategoryT.Description. With A on top. There are a bunch of different options in here.
I do have a header. I do not need a footer for this one. Move this category up into the category header like that. Then we can make it bigger, bold, make the letters a little bigger if you want to.
Now let's take a look and see what we have. That looks a lot nicer. Follow Ups, Other, Pre Sales, Sales, doing Sales.
I am not a big fan of the alternating background color. That is up to you if you want to get rid of it. I am going to leave it for this one. I do not really mind it here. Maybe let's put a footer on with a horizontal line; that might look better too. Go back to design view, turn on a footer section. Copy this line, and paste it in the category footer. Move it off a little. Looks better. Save it, preview.
Now, let's get rid of that alternating background color. I do not really like that unless it is a very simple report where it is every other one, but with variable space in here, it just does not look right.
Open up the properties for Detail, go to Format, Background Color and Alternate Background Color. Now we have the category header as well: background color, no color for that as well. Save it. Now we should see... oh, we have the footer too. Do the footer: no color.
There we go. That looks a lot better. Now we have a report with everything grouped by category. You have them grouped here, right? There are two sales ones for today.
You could put totals on these, you could put dates, all kinds of header information. This is just a really brief report and I am way over time. I wanted to keep these videos to about 10 minutes a piece. What am I, almost 20 minutes now?
If you guys want to see more report stuff, let me know. Post a comment down below.
For now, I am going to close this, but we need to have a way to get to it. Let's put a button on the main menu. Design View, copy and paste this guy, right: Follow Up Report. Give it a name: FollowUpReportButton.
Right-click, build event. DoCmd.OpenReport "MyFollowUpR," [then the view]. Now this is important, if you leave it the default it is going to open it and send it directly to the printer. That is acViewNormal. So if you click a button and it goes right to the printer, that is fine if you want that, but you may be spitting out useless paper.
I like to always go into preview mode first. acViewPreview, so I can look at it, see if it looks good, and then print it.
If you want to actually print it, click the print button.
One more thing: when we create, and I mentioned this earlier in the video, when we create a new contact and want to set a follow-up category down here, we do not have a category combo box on here. But we already made a combo box so we can just copy it.
Design View, copy this combo box, then go to the contact form, Design View, and paste it down in the footer. Right there.
We also need to play around with the visible property like we did before. Oh, and it is Combo17, I did not name it. Make sure you give it a good name: CategoryCombo. Save it here and then this one is also CategoryCombo.
By default, Visible is No. Now we have to add it to that code we had earlier.
Go to the code behind this guy (the button that looks like three pieces of candy—I also put it up here on my quick launch toolbar—it's called View Code).
Then just do the same thing as before:
CategoryCombo.Visible = FollowUp
Now, when you go to create a contact, let's say Jean-Luc Picard, talked about the forge invasion (Star Trek reference). Set the follow-up with him, set a date. You can put those buttons here too, by the way. I put them on the big follow-up form because I like to just run down my follow-ups by date: this week, next week, etc. In here you can put them as well if you want, but now you can also change the category here.
It is going to bother me if we leave it right-justified. Change that to left.
Close and save. Test the contacts form. Looks good.
Oh, wait a minute: look up Combo17.Description - Uh-oh. I am going to leave this in the video. I changed the name of the combo from Combo17 to CategoryCombo, and anytime you see "Enter Parameter Value," I have a whole video on this. Go watch this video if you want to learn more.
Basically, something is spelled wrong, or you changed it and Access is looking for something that is no longer there because you either renamed it or typed it in wrong. Usually it is a spelling mistake.
So in this case, we have to figure out who is looking for Combo17.Description. Cancel here. Debug.
Open the form FollowUp. Close and reset the code so it is not doing that. Open up the FollowUp form manually. It is doing the same thing. Who is looking for this?
Right-click, Design View. I am going to bet it is a form filter. Go to Data - yep, there it is.
What happened before was (and this is interesting) — this came up in the forums a while back — when you filter or sort in the form like we did before, that actually gets saved in the Filter property and the OrderBy property. By default, FilterOnLoad is set to No. When you close the form and reopen it, the filter does not apply itself, but it is still there, and Access is still trying to evaluate "Hey, where is Lookup Combo17?" It does not exist, because I renamed it. That filter is no longer necessary, so just delete it. Same thing with the OrderBy. Delete.
I personally do not want OrderByOnLoad on; I usually turn that off. That means when you custom-resort the form, and you close and reopen it, it will retain that sort. That is what this is. I have a video on this, too.
But that is where that error was coming from: you applied a filter, then renamed the field, and the filter is still referencing the old name. This comes up once in a while in the forums. Now, if I go back in here, it opens up just fine because I took that filter out.
So there you go. There is your Fast Tip for today. That was about twice as long as I was planning on it being, but we did some formatting on that report and made it look pretty.
Anything else you guys want to see, let me know. I have a list of stuff too. We are not done. I have at least two or three more parts of this follow-up series to go. Hope you are enjoying it. If you are, let me know. I want to know your feedback.
If you like me doing things like this, I got feedback from a bunch of people on different kinds of databases: church membership, donations, accounting, etc. Whatever kind of database you are looking to have built or build yourself, I am always looking for ideas to start things like this, and I will do a little multi-part series on how to set that stuff up.
Okay, there is your Fast Tip for today. Bye bye!
Quiz
Q1. What is the primary purpose of adding categories to the follow-up database in this video?
A. To allow filtering and grouping of follow-ups by category such as sales or service
B. To increase the database file size for security
C. To link follow-ups to invoices automatically
D. To replace customer IDs with categories
Q2. Why does the instructor create a separate SortOrder field in the CategoryT table?
A. To allow custom ordering of categories regardless of the autonumber sequence
B. To speed up query execution
C. To store category descriptions
D. To track how many contacts exist in each category
Q3. What is the relationship type between ContactT and CategoryT in this setup?
A. One-to-many
B. Many-to-many
C. One-to-one
D. Self-referencing
Q4. What is the function of the foreign key CategoryID added to the ContactT table?
A. It identifies which category each follow-up belongs to
B. It keeps track of customer phone numbers
C. It is used for autonumbering contacts
D. It stores the customer's address
Q5. What would you need to set up if you wanted each follow-up to have multiple categories?
A. A junction table for a many-to-many relationship
B. An additional report
C. More combo boxes on the form
D. A SortOrder field in ContactT
Q6. When creating the combo box for categories on the follow-up form, which field is used to sort the categories?
A. SortOrder
B. CategoryID
C. CustomerID
D. Description
Q7. Why does the instructor suggest hiding the SortOrder column in the combo box?
A. Because it is only needed for sorting, not display
B. Because it contains sensitive information
C. Because users must manually change it
D. Because it is not related to contacts
Q8. What is the benefit of using Access's built-in right-click features for sorting and filtering?
A. It allows users to sort and filter data quickly without extra programming
B. It automates follow-ups with AI
C. It prevents any changes to the database
D. It changes the structure of the table permanently
Q9. What is a quick way the instructor adds a combo box for categories onto the contacts form?
A. Copy the existing combo box from another form and paste it
B. Create a text box and type values manually
C. Use a macro to generate it automatically
D. Use a calculated field
Q10. What is the purpose of creating a query (FollowUpReportQ) before designing the follow-up report?
A. To collect all necessary fields and join related tables for the report
B. To fix tab order issues
C. To generate random data for the report
D. To replace the data source of the form
Q11. Why does the instructor use an outer join between ContactT and CategoryT in the report query?
A. To include all follow-ups, even those without a category
B. To display only follow-ups assigned to a category
C. To speed up the report
D. To group by customer
Q12. What does the instructor do to remove the "Enter Parameter Value" error related to Combo17.Description?
A. Deletes the stored filter and order by settings referencing the old name
B. Reinstalls Microsoft Access
C. Changes the Description field name in all tables
D. Restarts the computer
Q13. What visual improvements does the instructor make to the printed report?
A. Aligns and formats fields, removes alternating background colors, adds lines and headers
B. Adds pie charts for each category
C. Uses only default formatting
D. Prints only IDs and dates
Q14. Why does the instructor use Print Preview mode (acViewPreview) when opening the report from a button?
A. To let the user see and review the report before printing
B. To skip the need for report design
C. To save printer ink
D. To convert the report to PDF
Q15. If you apply a filter or sort to a form and then rename a control referenced by the filter, what is likely to happen?
A. You will receive an "Enter Parameter Value" prompt for the old control name
B. The filter will adjust itself to the new name
C. The filter is automatically deleted
D. The form will crash and close
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
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 our exploration of building a follow-up database in Microsoft Access. This is part four of the series, so if you have not viewed the first three parts, I recommend starting there to get up to speed. In this lesson, I focus on adding categories to the follow-up system, making it more flexible and easier to manage your contacts based on their needs such as sales, service, or pre-sales tasks.
To start, I address a small usability detail: the tab order on the form. Setting the correct tab order in design view helps to ensure when you are entering data, you can quickly move from one control to the next logically, including making sure fields like the date are included in the tab stops. Adjusting the positioning of some controls also helps make your data easier to see in your forms.
With that taken care of, I turn to implementing categories. A category structure allows you to group follow-ups into helpful buckets like "Sales," "Service," "Pre Sales," or whatever fits your workflow. The first step is building a separate table to store these categories. Each category gets its own ID, description, and for even better control, a SortOrder field, which gives you the ability to display the categories in any sequence you want, rather than relying on the automatic numbering.
Once the Category table is ready, I set up a one-to-many relationship with the follow-up records. This means each follow-up can be assigned to only one category. If a follow-up needs to belong to more than one category simultaneously, you would need to implement a many-to-many relationship using a junction table; for the purposes of this tutorial, one category per follow-up will suffice.
After creating the relationship, I add a foreign key for CategoryID in the follow-up (Contact) table. Keeping all key fields together at the top of the table helps maintain easy organization and clarity.
Next, I enhance the follow-up form by adding a combo box that lets users select a category for each follow-up. This combo box gets its values from the Category table, sorted by the SortOrder field to maintain the sequence you want. The combo box stores the selected CategoryID in the Contact table, allowing users to categorize their follow-ups easily. Proper labeling and positioning of this combo box make the form user-friendly.
With the new category system in place, you are now able to sort and filter your follow-ups based on category or any other criteria by using the built-in Access right-click filtering features. For more advanced searching and sorting, I mention that there are templates available which provide search boxes and clickable headers, making filtering and sorting even more intuitive, especially for those less familiar with Access.
For users who prefer working with printed lists, I walk through creating a printable report that shows all the follow-ups due by a certain date, grouped by category. The report design process involves building a query that pulls together all the necessary information by joining the Contact, Category, and Customer tables. Special attention is paid to avoid duplicating fields and making sure all relevant information, like customer names and phone numbers, is included.
Within the report, I use grouping to make it easy to view all follow-ups of the same category together. I spend some time on formatting, such as aligning fields, using the format painter for consistent styling, and adjusting label placement for clarity. I also show how to remove unwanted alternating row colors and fine-tune headers and footers for a cleaner look.
To make accessing the report easier for users, I add a button to the main menu that opens the follow-up report in preview mode first, allowing users to check everything before printing. Opening reports in print preview is a best practice to prevent unintentional paper waste.
Finally, I address an issue that can arise when renaming controls, such as combo boxes. If you have ever seen Access asking for a parameter value unexpectedly, it is often because a control was renamed but the filter or sort expressions still reference the old name. I explain how to check and clear out any saved filter or sort properties that refer to non-existent controls, restoring the form's proper function.
Throughout the lesson, I remind viewers that all of these techniques are fundamental in Access and can be adapted for many different types of databases, such as church membership, donations, or accounting systems. I also encourage feedback and requests for other topics or multi-part series that would help you build the databases you need.
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
Fixing tab order on an Access form
Ensuring date field has Tab Stop enabled
Creating a category table for follow-up types
Adding a SortOrder field for custom category sorting
Establishing a one-to-many relationship for categories
Inserting a CategoryID foreign key in the contacts table
Adding a category combo box to the follow-up form
Configuring the combo box to use the SortOrder field
Assigning categories to existing follow-up records
Sorting and filtering records by category in datasheet view
Creating a query to combine contacts, categories, and customers
Building an outer join to display contacts without categories
Filtering query results for upcoming follow-ups
Adding customer and category information to the query
Saving a query for use in a follow-up report
Designing a printable follow-up report
Adding and formatting fields in the report layout
Grouping records by category in the report
Customizing report headers, footers, and formatting
Removing alternating background color in reports
Adding a report button to the main menu
Writing VBA to open reports in print preview mode
Copying the category combo box to the contact form footer
Controlling the visibility of the category combo box with code
Resolving the Enter Parameter Value error after renaming controls
Clearing obsolete filters and order by properties on forms
|
