Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Styling data with conditional formatting
- Converting reports and templates to PDFs
- Using Application Parts to add new functions to databases
- Leveraging Office themes
Skill Level Appropriate for all
In Access 2010, commonly used tables and forms are available as Application Parts. Application Parts are internal templates that you can use to quickly create a table and its supporting forms, so you don't need to build these objects from scratch. Our Customer Orders Database has been in use for a few months and we've been asked to make some modifications. Specifically, users need to store two new types of information. The first is comments related to a customer and the second is issues that are related to fulfilling a specific order.
We're going to use Application Parts to create these Comments and Issues tables. We're looking at the Customers database right now and we could simply add a Comments field to the Customers table, but over time we would end up with a lot of text in that field. And we wouldn't be able to know when a comment was actually entered or edited. By creating a separate table for Comments, each comment will be entered individually and we'll be able to capture some data information for each comment as well. So let's take a look at putting together this Comments table using Application parts.
I'm going to click Create on the Ribbon and then Application Parts. In the Quick Start section, choose Comments. We'll be prompted to close the opened objects, the open table and the Create Relationship dialog box opens. There are three possibilities here. First is that there is no relationship between this new Comments table and anything else and the second is one type of one to many and the third is the other type of one to many relationship. I started with the Customers table open and that's why it's been chosen in the first dropdown. I could select any table in my database here.
But the first relationship that's already selected is the relationship we want. For every customer, it's possible that there would be many Comments. Let's click Next. Now we're prompted to choose a Lookup column. So when someone is entering a new comment and they want to relate it to a particular customer, how do they select that customer? We can choose any Field here from our Customers table. If we thought every single person who used this database would know the ID of every single customer, we could choose CustomerID.
But many of the folks who will be filling this out will be customer facing and they know the customers are by company name. So we're going to go ahead and use CustCompany. To make it easier to find that company in the list, we're going to sort the list of companies in ascending order. And then finally, we need to provide a name for that column and we'll just put Company. The fourth choice asks us, "Is it possible that we would want to store multiple "values for this lookup?" The multiple values we're being asked about here is multiple companies for one comment.
And the answer would be No, our comment is specific to a company. That's what our users have asked for. So I'm going to click Create and the new Comments table is automatically created. Let's open it and see what it looks like. First, we have Company names here and we can widen these fields, of course, but we can choose a Company name, choose the Date for our Comment and enter text for our Comment. Again, being able now to sort by Company, to sort by the Date Comments were entered and lots of good information that we can keep here. This is going to make our users happy.
Now let's take a look at our Issues tracking related to Orders. We're going to close this table and use Application Parts again. We have a built-in Issues tracking list. We're asked to form a relationship. We were on the Comments table when I opened this dialog box. So it's assuming we want to have Issues about our Comments, but actually we want to have Issues about our Orders. One order could have many issues. Let's click Next and it says "Okay, as you're looking at Orders, what Field would "you like to use to identify who we're talking about?" When we put together the Comments list, many of the folks who are entering information were customer facing and so it makes sense to use Customer name.
Here I think it actually makes more sense to use OrderID. We're talking about people who work in fulfillment. They're not actually seeing the customers, they're seeing customer Orders. And so, if we give them the OrderID, they should be able to enter information in a way that would be meaningful for them. Again, we're going to Sort this list in ascending order. There is probably nothing more annoying than going through a list of numbers that are sorted in a random fashion and we're going to say that this is the OrderID that you're looking for here. We don't have any need to store multiple values and let's go ahead and click Create and rather than getting one table, we're actually getting three objects with this Issues Quick Start.
Access created a table and two forms. Let's take a look at the forms first. This New Issue form is actually used to enter a brand new issue, where someone would enter the text for the Issue, choose a Priority, notice that there are four here, choose a Category, we'll talk more about these in a moment, and a Project. The IssueDetail form has more information because this is the form that we'll use to follow up later to find out when it was resolved and what the Due Date was and perhaps we have some other items that have been attached to this.
Both of these forms include two dropdowns, a Category dropdown and a Project dropdown that are clearly placeholders, Project 1, 2, 3. We're going to modify these two dropdowns to have our list of reasons that an item might have an Issue and our list of Project types. We won't do then in the form, we'll do that in the table. So let's close our form, open our table. Right-click on the tab and switch it into Design View. Let's go take a look at the Category List first.
Now there are two places that this is entered. Here is the Default Value, but let's go take a look at the items in the Lookup and you'll see that this is a simple Row Source, noted here in text, Category, Category and Category. So we're simply going to edit this list. Each item is in quotes. The items in the list are separated by semicolons and the first reason that we might have an issue with an order is that one of the Items that they asked for was actually Backordered. The second is that Item is no longer available. We probably have an endless list of reasons, but these are the two we care the most about tracking right now. They're the most common.
Unless your List is exhaustive, there should always be a choice of Other to allow people to enter all the things they need to enter. Now let's take a look at our Project List, on the Lookup, same kinds of choices. Item 1, we have Arrangements, we have Plants, we do Landscaping Projects and then we need a fourth type here. And our fourth type is simply Other.
Let's save our table. Close our table and go back and look at our Detail form. You'll note that we have our items here on the list of Categories and our list of Projects. There is only one problem, we didn't change the default. So we're going to go back into the table now and we're going to change the default. And we're going to change them differently, so you can see the difference between the two ways you might think about a default setting here. So we're going to close our form, open our table.
Right-click, choose Design View and let's take a look at our categories. We're going to choose our Item Backordered Text here from our List, so it's exactly the same, and Paste it as our Default Value for our Category dropdown list. For our Project List, we're going to supply no Default Value at all. We're going to Save and close our table and open the Detail list and see the results of those two choices we made. First, where we chose Item Backordered as our default, we have Item Backordered in the list.
With Project, there is nothing filled in by default and the user will actually have to choose 1, 2, 3 or 4. So now we have fully customized our table and both of our forms with our own list of categories and our own project types. Using Quick Start Tables, it only took us a few minutes to add a lot of new functionality that drastically improves our Customer Orders Database, the kind of functionality that our users were requesting. Whether you're designing a database from scratch or modifying an existing database, you can always save time by using Access 2010's Application Parts.