Start learning with our library of video tutorials taught by experts. Get started

Access 2010: Real-World Projects
Illustration by Neil Webb

Saving and reusing objects as application parts


From:

Access 2010: Real-World Projects

with Gini Courter

Video: Saving and reusing objects as application parts

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.

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
Please wait...
Access 2010: Real-World Projects
28m 34s Appropriate for all Jan 22, 2010

Viewers: in countries Watching now:

In Access 2010: Real-World Projects, author Gini Courter uses real-world examples to explore Access's database creation and management features. Gini shows how to create professionally formatted forms and reports and make ugly databases a thing of the past using Office themes. Creating a database in Access is even easier with built-in navigation templates and reusable Application Parts. Gini also reviews how to save reports as PDFs for easy distribution and how to highlight important data. Exercise files accompany the course.

Topics include:
  • Styling data with conditional formatting
  • Converting reports and templates to PDFs
  • Using Application Parts to add new functions to databases
  • Leveraging Office themes
Subject:
Business
Software:
Access
Author:
Gini Courter

Saving and reusing objects as application parts

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.

There are currently no FAQs about Access 2010: Real-World Projects.

Share a link to this course
Please wait... Please wait...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Access 2010: Real-World Projects.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Welcome to the redesigned course page.

We’ve moved some things around, and now you can



Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked