IntroductionWelcome| 00:04 | Hello! I'm Adam Wilbert and I'd like to welcome
you to Access 2010 Forms and Reports in Depth.
| | 00:09 | In this course, we're going to
explore the inner workings of two highly
| | 00:12 | configurable objects inside an Access database.
| | 00:15 | With forms, I'll show you how to
create easy-to-use data entry points that
| | 00:19 | interface with your data tables.
| | 00:20 | We'll work with controls and layout
and design views as well as explore
| | 00:24 | Access's extensive property sheets
where we can fine-tune their behavior,
| | 00:28 | appearance, and interactivity.
| | 00:30 | Reports use many of the same tools and
techniques as forms and they're great for
| | 00:34 | preparing data for the printed page.
| | 00:36 | With reports we'll look at grouping
data into categories while leveraging
| | 00:40 | header and footer sections to organize your
report structure and make them easy to read.
| | 00:44 | We'll build reports from scratch that
are tied to query record sources for
| | 00:47 | increased flexibility and we'll hook
them all back into a form-based navigation
| | 00:51 | system to help end-users move around
your database and accomplish tasks without
| | 00:55 | having to note anything
about its inner structure.
| | 00:57 | So, thank you for joining me.
| | 00:59 | Now let's dig into Access
2010 Forms and Reports in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the Exercise
Files for this course, you can put them on
| | 00:03 | your Desktop, as I've done
here, or anywhere else you want.
| | 00:07 | I'll double-click on this Exercise
folder and you'll see that I've organized the
| | 00:10 | exercises into folders for Chapter.
| | 00:13 | Within each folder are the files for each movie.
| | 00:16 | For some movies, I've included a
completed version for you to review.
| | 00:20 | Now, if you don't have access to the
Exercise Files you can follow along
| | 00:23 | with your own databases.
| | Collapse this transcript |
|
|
1. Introduction to FormsIntroducing forms| 00:00 | One of the things that I really like
about Access is that it's not just a
| | 00:03 | place to store data.
| | 00:04 | Yes, we talk a lot about data and
records and relationships and queries, where
| | 00:08 | Access truly shines is in your ability
to create a fully customized application
| | 00:13 | that is uniquely
tailored to your specific needs.
| | 00:15 | Where this really becomes apparent
is when we start digging into forms.
| | 00:19 | Forms are all about the user interface.
| | 00:20 | They provide the mechanisms that
translate what it is that you want to get done
| | 00:24 | to the inner workings of the database.
| | 00:26 | In fact, in a well-designed Access
database there may never be a need to
| | 00:29 | actually even see a data table
or a query or a relationships map.
| | 00:33 | Those are all background processes that
organize input and output, and once set
| | 00:37 | up properly should just
work with little maintenance.
| | 00:40 | Forms handle all the grunt work of
capturing user intent and moving it
| | 00:43 | through the system.
| | 00:44 | They help guide users through
all of the available options.
| | 00:47 | Now, I've gone ahead and open up the
Exercise File that we'll finish this course with.
| | 00:51 | This is the 1-1
TwoTreesCompleted Access database.
| | 00:55 | Now, you might have noticed at the
beginning the splash screen has started up,
| | 00:58 | that helps tell our end-users that
what's about to happen, that is loading the
| | 01:01 | Two Trees Olive Oil Company database.
| | 01:03 | Once that's on the screen for a few seconds,
it disappears and the Main Menu appears.
| | 01:06 | Now, I have a bunch of available options.
| | 01:08 | I can choose for instance to view the
employee directory. I'll click this.
| | 01:12 | I can look through my employees, when I'm done
reviewing this I can return to the Main Menu.
| | 01:17 | I can also do things like enter a
product review or perhaps I want to add a new
| | 01:20 | customer, I'll click that.
| | 01:22 | My Add a New Customer form is open, I
can enter in the information and save the
| | 01:25 | customer or I can cancel
that entry. I'll press Cancel.
| | 01:28 | I can even do things like Email Orders.
| | 01:31 | I'm going to e-mail this report to somebody.
| | 01:33 | I'll click on the button, Access
package set up as a PDF file, it even opens
| | 01:38 | up Outlook and addresses the e-mail and
attaches it right here, ready for me to send.
| | 01:42 | I'll go ahead and close Outlook.
| | 01:44 | So, as an end-user that has never
even seen this database before, we can
| | 01:48 | accomplish some very specific tasks
without even digging into any say Navigation
| | 01:52 | Pane here on the left or any of the
buttons that might appear on the Ribbon.
| | 01:55 | If you think about Word as a tool for
creating documents then Access is a tool
| | 01:59 | for creating more tools.
| | 02:00 | Now, I know that's a little
abstract, so let me explain.
| | 02:03 | You can use Access and have a fully
operational database with nothing more than
| | 02:07 | a few tables and queries, and in fact
that's where a lot of other database
| | 02:10 | programs end, but when you layer forms
and reports on top of that data you start
| | 02:14 | creating something more.
| | 02:16 | It becomes a custom application or access
the program doesn't really even matter anymore.
| | 02:20 | You've created a custom tool that
operates within Access but is tailored to
| | 02:24 | your specific needs because in the
end unlike with Word, the database file
| | 02:29 | really isn't the end goal.
| | 02:30 | The end goal is to have a place to
store data to retrieve records easily and to
| | 02:34 | manage the day-to-day
operations of your organization.
| | 02:37 | Your job as the database
designer is to create the tool.
| | Collapse this transcript |
| Designing for the end user| 00:00 | Throughout this title I'll often refer
to the database as end-user and I wanted
| | 00:04 | to take a moment to define who I think
this person is and why they're important
| | 00:08 | to keep at the front of your mind
while designing your forms and reports.
| | 00:11 | The end-user isn't anyone specific.
| | 00:13 | They're more of a theoretical individual
that is completely new to your organization.
| | 00:17 | They maybe a new hire or an intern or
maybe, even your grandmother, somebody
| | 00:21 | that knows very little about your
day-to-day tasks and even less about how to
| | 00:24 | use Microsoft Access.
| | 00:26 | As you develop your database
application, occasionally put yourself in their
| | 00:30 | position and look at your
workflows as if you're brand-new to it.
| | 00:33 | If you can set up your navigation and
data entry and reporting systems tailored
| | 00:37 | to the end-user that uninitiated first
day on the job employee then you'll be
| | 00:41 | well on your way towards
creating a successful application.
| | Collapse this transcript |
| Exploring the database| 00:00 | When starting any new database project
it's really important to be familiar with
| | 00:03 | the data tables that we will be working with.
| | 00:05 | Throughout this course we are going to
be taking a look at data from the Two
| | 00:08 | Trees Olive Oil Company.
| | 00:10 | Let's take a look at some of the
data that we will be working with.
| | 00:12 | On the left-hand side of my Navigation
Pane I can see I have a bunch of tables,
| | 00:15 | I have a table called DirectCustomers,
Employees, FieldReps, Orders, and
| | 00:19 | Products, I also have a couple of
additional tables that are marked as lookup
| | 00:23 | tables here with this tag for
GradeID, OilID, Sizes, and States.
| | 00:28 | Let's take a look at the Orders table.
| | 00:29 | I will double-click on it to open it
and we will review the data inside.
| | 00:32 | The Orders table has fields for OrderID,
OrderDate, the customer that placed
| | 00:36 | the order and this is their ID number,
the product that was ordered, and again,
| | 00:40 | the ID reference, and the ID number for the
salesperson that assisted with that order.
| | 00:44 | Now if we go to the Database
Tools menu here and take a look at the
| | 00:47 | relationships, by pressing the
Relationships button here, we can see how all of
| | 00:51 | the data tables within our
database relate to each other.
| | 00:54 | So here is that Orders table, we can
see the fields inside of the Orders table
| | 00:57 | and we can see that if we take a look
at the CustomerID, the CustomerID is
| | 01:00 | linked to the DirectCustomers
table based off of that ID number.
| | 01:04 | So from the Orders table I can get
the ID number, I can follow that to the
| | 01:07 | DirectCustomers table or I can get
additional information such as their
| | 01:10 | first and last name, their address, the
state that they live in, and their email address.
| | 01:15 | Similarly, I can follow the ProductID
to the left to the Products table and I
| | 01:20 | can find out the name of the product, the
size of the product, the cost of the product.
| | 01:24 | Once I have that information I can
find other information about that product.
| | 01:27 | For instance, I can translate the size
in ounces to the text name of its size,
| | 01:31 | for instance small or medium.
| | 01:32 | I can also find what oil it was made of
and what the retail grade of that oil is.
| | 01:36 | Now all of these tables are related to
each other in this database which makes
| | 01:40 | it very efficient and I can
use the data in multiple ways.
| | 01:43 | So this is the data that we will be
working with throughout this course as we
| | 01:46 | create forms and reports to organize it.
| | 01:48 | Let's get started.
| | Collapse this transcript |
| Creating a form with the Form Wizard| 00:00 | You can have a basic form up and running
and as little as three clicks of a mouse.
| | 00:04 | For instance in the Two Trees database
here I can go to the Navigation Pane,
| | 00:07 | click on the Orders table, on the
Create tab of the Ribbon I can click on
| | 00:11 | Form in the Forms group.
| | 00:13 | Access will create a form here
based off of that Orders table.
| | 00:15 | Right now I am in Layout View
which is one of the two editing modes.
| | 00:19 | In the View menu here I am going to switch
to Form View so I can work with the data.
| | 00:23 | The data in this form is coming
straight out of the Orders table.
| | 00:25 | Right now I'm looking at
record number one of 2200.
| | 00:27 | I can see that it's OrderID 10000 that
was placed on the 10/01/2005 and then it
| | 00:33 | was for customer at 2461.
| | 00:34 | Let's go ahead and see
this data in the Orders table.
| | 00:37 | If I double-click on the Orders table,
I can see in the EDS that record number
| | 00:41 | one is order 10000 on the 10th of
January for this particular customer.
| | 00:45 | Now the data in this form is a
live link to the data in the table.
| | 00:48 | For instance if I click on the
OrderDate field here and get the Calendar
| | 00:51 | Picker and I can change that date, click on
the calendar and change it to maybe the sixth.
| | 00:55 | I will click the Pencil icon here to
finalize that change and then I can go to
| | 00:59 | look at the table again to see
the updated value in the table.
| | 01:02 | Now this change goes both ways, I can
make a change in the table and we will see
| | 01:05 | that update in the form.
| | 01:06 | I will click on OrderDate again, click on
the calendar and change it back to the 10th.
| | 01:11 | Once again I'll click on the Pencil
icon or I could press Enter and move to a
| | 01:15 | different record, we will go back to the form
and we will see that the data is changed here.
| | 01:18 | I'd like to think of these squares
as little windows into the table cells
| | 01:22 | that they represent.
| | 01:23 | So the data here is the exact
same data that you're seeing here.
| | 01:26 | Okay let's go ahead and close the Orders table.
| | 01:29 | This form while it is technically
correct it's showing me data straight from
| | 01:32 | that data table, it leaves a lot to be desired.
| | 01:34 | For instance it's showing me this
customer ID, but I don't know who this
| | 01:37 | customer is, it's also showing me
this ProductID, but I am not really sure
| | 01:41 | exactly which product this means.
| | 01:43 | I would like to have a better text
description of this customer's name and the ProductID.
| | 01:47 | Because our Access database is built
around the idea of related tables you can
| | 01:51 | get that information by incorporating
a few more data sources into our form.
| | 01:55 | Let's use the Form Wizard that will
walk us through the process and get a more
| | 01:57 | useful form than the one we have now.
| | 01:59 | I will go ahead and close this form
out and I am not going to save it, I
| | 02:02 | will say No to save.
| | 02:03 | Now we can to up to the Create tab on
the Ribbon and click on Form Wizard, that
| | 02:08 | will start up the wizard where we
could choose the tables that we want to
| | 02:11 | incorporate fields from.
| | 02:12 | For instance let's go ahead and build
a form similar to the one we just had
| | 02:15 | except it has those other pieces that
we wanted, the name and the ProductID, we
| | 02:19 | will go to the Orders table and I will
choose OrderID, OrderDate and then we
| | 02:23 | will switch to a different table here.
| | 02:24 | We will pull in information
from our direct customers table.
| | 02:26 | I will click on that and we will add
FirstName and LastName, and finally I will
| | 02:31 | choose my Products table, I'll go back
up to the dropdown, choose tbl_Products
| | 02:34 | here and we'll get the ProductName and
I'll add that from the available fields
| | 02:38 | to the selected fields section.
| | 02:39 | We will go ahead and say Next.
| | 02:41 | The Next screen asks us how we want to
view our data, if I choose by tbl_Orders
| | 02:46 | it's going to show me one page
of the form for every record.
| | 02:48 | If I choose by tbl_Products you will
show me a product name in all of the orders
| | 02:52 | that are associated that included
that product, likewise if I click on
| | 02:56 | DirectCustomers they will show me a
customer name and then all of the orders
| | 02:59 | that that customer has placed.
| | 03:00 | We will look at these
options here in just a moment.
| | 03:02 | So for now just click on
tbl_orders and we will click Next.
| | 03:05 | We will go ahead and choose the default
format for the layout, say Next here and
| | 03:10 | now we can give our form a title if you'd like.
| | 03:12 | I am just going to accept the default
title here and I will choose to open the
| | 03:15 | form to view or enter
information, we will say Finish.
| | 03:18 | So already we've got a form that's better
than the one that we created just a moment ago.
| | 03:22 | I have got the same OrderID and
OrderDate, but now I know who placed the order
| | 03:26 | and what exactly they ordered.
| | 03:27 | Let's take a look at a couple of
other options that we had in the wizard.
| | 03:30 | I am going to go ahead and close this
form here, go back to the Create tab and
| | 03:34 | choose Form Wizard again.
| | 03:36 | Let's build the same form and we
will choose a different option later on.
| | 03:39 | So I will go for the Orders table, I
want OrderID and OrderDate, go back up
| | 03:43 | here to the DirectCustomers table
where I got first name and last name, once
| | 03:48 | again I will go to the top and this time I
will choose tbl_Products to get the product name.
| | 03:52 | We will say Next.
| | 03:54 | And this in this screen we are
going to choose by tbl_Products.
| | 03:57 | Now Access gives us two different
options, I could either have the Form with
| | 04:00 | subforms or Linked forms.
| | 04:02 | Let's go ahead and choose Form with
subforms now and we will look at Linked
| | 04:05 | forms in just a moment.
| | 04:06 | Go ahead and say Next, we will
accept the Default layout, Next.
| | 04:09 | We will accept the default
names, go ahead and say Finish.
| | 04:13 | Here is a form with the subform attached to it.
| | 04:15 | Now I could use a little bit of help
in the layout, but what it's showing us
| | 04:18 | right now is that I have
got 90 different products.
| | 04:20 | For those 90 products these are all of
the OrderIDs and OrderDates and customers
| | 04:24 | that have placed orders for that product.
| | 04:26 | I could scroll through my products
here, for instance, I will go to product
| | 04:30 | number 8, the Extra Virgin Oil, 32
ounce size and I guess all of the customers
| | 04:35 | that have ordered that product.
| | 04:36 | And I've had a total of 21 different orders.
| | 04:38 | Let's go ahead and close this and
we will look at the other option.
| | 04:41 | Once again to the Create tab Form
Wizard, we will build the same table.
| | 04:45 | So I will take OrderID and OrderDate,
we will go to the Customers table, we
| | 04:49 | will add FirstName and LastName and we will go
to the Products table and choose ProductName.
| | 04:56 | Okay, once we get those
five fields we will say Next.
| | 04:58 | This time we are going to choose by
tbl_Products again, but we will choose the
| | 05:02 | Linked forms option.
| | 05:03 | This will show us the same data in two
separate forms that we got on the last
| | 05:07 | one with a subform, go ahead and say Next.
| | 05:09 | This time I am going to change the names,
we are going to go ahead and use these
| | 05:12 | two files in next exercise.
| | 05:14 | So for the first form we are
going to call it Product Selection.
| | 05:18 | In the second form we are
going to call Orders By Product.
| | 05:21 | Go ahead and say Finish here and
Access will update those forms.
| | 05:25 | Now we've got a little bit of a problem
here the default selections create this
| | 05:29 | button that appears behind this label.
| | 05:31 | So if I try and click on the button,
I can't, the label is in front of it.
| | 05:34 | In order to fix this I actually need to
jump into Layout View for just a moment,
| | 05:37 | we are going to look at
Layout View in the next movie.
| | 05:40 | But for now just go ahead to the View
menu, choose Layout View, we will click on
| | 05:44 | the title here and I am going to
click-and-drag to side it over.
| | 05:46 | Then I'll click on the button to
click-and-drag and side it over and it will
| | 05:50 | move the label back.
| | 05:51 | Okay, now that they are separated we
will go back into Form View and I will test
| | 05:55 | out the functionality.
| | 05:57 | Just like the last form I can cycle
through my products here, so I'll go to
| | 06:01 | record number 6 here, the Extra Virgin
Oil, and the 8 ounce size, and I will
| | 06:05 | press the Orders By Product button.
| | 06:07 | When I press that I get
a second form that opens.
| | 06:10 | You might have noticed in the
Navigation Pane here, when it opens up it's
| | 06:13 | showing me all of the records for each
customer that have placed orders for that product.
| | 06:16 | You can see I have a total of 27
records or 27 orders for that specific oil.
| | 06:21 | So using the Wizard we were able to
create three slightly different user
| | 06:25 | experiences and using data from
multiple-related tables we were able to get
| | 06:29 | details on order activity at
the Two Trees Olive Oil Company.
| | 06:32 | Now as is typical with the objects
created with a wizard we have a good starting
| | 06:36 | point but we need to clean this
up a bit before it's presentable.
| | 06:39 | We will take a look at the Layout View
in the next movie to finish this form.
| | Collapse this transcript |
| Refining the form in Layout view| 00:00 | The Form Wizard gave us a good
head-start on setting up a couple of forms that
| | 00:03 | will allow us to select a product and
get a listing of all the orders that
| | 00:07 | included that product.
| | 00:08 | Now we need to polish up the layout a
little using the tools available to us
| | 00:11 | in the Layout View.
| | 00:12 | In the last movie we created
this Product Selection form.
| | 00:15 | I'll go ahead and double-click on it
and you will see that I could scroll
| | 00:17 | through the records to select different
products in our company and I can choose
| | 00:20 | the Order By Product button to see a
listing of all of the orders that included
| | 00:24 | that particular product.
| | 00:25 | Now we can clean up the design of
these two forms by using the Layout View.
| | 00:29 | First let's take a look at some of
the things we might want to change.
| | 00:31 | In this form when we go to Product
Selections first of all I want to change
| | 00:34 | the background color to better align with
the Two Trees Olive Oil Company's color scheme.
| | 00:38 | Second, I want to get rid of
the box around this label here.
| | 00:41 | I also want to change
the styling of this button.
| | 00:44 | Now in the Order By Product's form,
click on that, again I am going to change
| | 00:47 | the background color here, I want to
change these labels here, the OrderID,
| | 00:50 | OrderDate, FirstName and LastName,
right now these values are coming straight
| | 00:54 | out of the data table, those
are the fields from the table.
| | 00:57 | I want to make them a little more
human-readable by adding spaces between them.
| | 01:00 | Next I want to get rid of the boxes
around the data, these boxes don't really
| | 01:03 | add anything to the legibility of my
data, so I can get rid of them to clean
| | 01:07 | up the visual clutter.
| | 01:08 | And then I want to reduce the height of
each record, so they are not using up so
| | 01:11 | much space and I get fit more vertically.
| | 01:13 | Okay, let's go ahead and go into
Layout View to make those changes.
| | 01:16 | First our Product Selection form, I'll
click on it here and in the View menu I
| | 01:20 | will switch to Layout View.
| | 01:21 | I can click on the background color
here and switch the Format tab where I can
| | 01:25 | find my paint bucket, I'll use a drop-down
menu and we are going to go ahead
| | 01:29 | choose this Color Swatch here,
the Olive Green, Accent 3.
| | 01:33 | Next I want to change the styling of
the button, I'll click on the button, and
| | 01:36 | under the Quick Styles menu I am going
to choose this light green option here.
| | 01:40 | Next I want to get rid of the line
that surrounds this bounding box.
| | 01:43 | I will click on the object here, and in
the Shape Outline I am going to change
| | 01:46 | it to Transparent so it's invisible.
| | 01:48 | I will click off anywhere in this white
space to see what the results look like
| | 01:53 | and that's looking pretty good.
| | 01:54 | So I am going to switch over to
the other one, Order By Product.
| | 01:57 | Once again I am going to select Layout View,
I go into View menu here and go into Layout.
| | 02:01 | Next I am going to change the color
of the background by clicking on the
| | 02:04 | background blue, into the Format tab,
I'll click on the Paint Bucket icon, which
| | 02:07 | is already loaded with the green color.
| | 02:10 | Next I want to change the labels here.
| | 02:11 | But if you remember in the last movie
we had to adjust the label because it was
| | 02:14 | over the button, well that
applies to this form as well.
| | 02:17 | First I will click on this label and
you will see that the box is actually
| | 02:19 | covering these other three labels.
| | 02:21 | I am going to click on the
bottom here and drag it up.
| | 02:24 | Now I can click on these forms down here,
I will double-click and I will add a
| | 02:27 | space in between, so it's little more
legible, double-click on OrderDate and add
| | 02:31 | a space, double-click on FirstName and
add a space and double-click on LastName
| | 02:35 | and add a space, and I
will click out here to select.
| | 02:37 | Next I want to change the height of my records.
| | 02:40 | I will go ahead and click on this
FirstName one here and I'll Shift+Click to
| | 02:42 | select LastName at the same time.
| | 02:44 | Now I need to drag these up to match the
height of the OrderDate and OrderID fields.
| | 02:49 | And the handle here gets a little
tricky if you're too low you'll end up
| | 02:51 | dragging the top of the one below.
| | 02:53 | So you kind of need to be a more than halfway
up between those two lines, right about here.
| | 02:57 | I'll click and I can drag up.
| | 02:59 | That will collapse my form vertically
so I can get more data on the screen.
| | 03:03 | Next I want to get rid of these boxes,
I'll Shift+Click to select the other two
| | 03:06 | fields and then I will go to
Shape Outline and say Transparent.
| | 03:11 | Select off of it, now let's switch back
to Form View by going to the Design tab
| | 03:15 | and clicking the View to Form View.
| | 03:16 | I could find the same icon by going to the
Home tab and clicking the icon there as well.
| | 03:20 | So I will switch back into Form
View and that's looking pretty good.
| | 03:23 | So I am going to go ahead and close this
down and I'll save my changes, yes, and
| | 03:28 | this one looks good as well, I will
close it as well and save the changes.
| | 03:31 | So now let's take a look at
how the workflow is working.
| | 03:34 | I will double-click on Product
Selection, I can scroll to my records, I can
| | 03:39 | click Orders By Product and I
can see a better look at my data.
| | 03:43 | So the Layout View gives you some
easy-to-use tools for organizing the flow of
| | 03:47 | your forms and making sure
that everything is aligned.
| | 03:49 | For even more flexibility we will need
to dive into the full design environment
| | 03:53 | and we will do that in the next chapter.
| | Collapse this transcript |
|
|
2. Form-Design Basics: Creating a DirectoryOrganizing the form elements| 00:00 | Combining the Form Wizard and layout
views are easy ways to get started creating
| | 00:05 | your database's forms, but the real
power and flexibility lies in utilizing the
| | 00:09 | full design environment for
creating forms from scratch.
| | 00:12 | Once you understand what's going on
under the hood creating your own forms that
| | 00:15 | need your exact specifications
and needs will become second nature.
| | 00:19 | Let's start by creating a Rolodex
style employee directory for Two Trees.
| | 00:23 | Now I've got a finished example here
called rfm_Employees-Complete that we can
| | 00:28 | take a look at to review the kind
of direction that we are looking.
| | 00:30 | I will double-click on that to open it
and you will see I have a scrolling form
| | 00:34 | here that's showing me
information for my employees table.
| | 00:37 | I can scroll through here and see
photos of all my employees, easily find their
| | 00:41 | name, their phone number and email
address, as well as their physical address,
| | 00:44 | what department they are
in, and their hire date.
| | 00:46 | I also had a little bit of fun with
some of the fonts and I put their employee
| | 00:49 | number here and I put the name of
the state that they live-in over here.
| | 00:52 | I can see this list scrolls and I
have got a header up here at the top
| | 00:55 | that doesn't scroll.
| | 00:56 | So this is the kind of thing
that we are going to be creating.
| | 00:59 | Now our finished version might look a
little bit different because some of the
| | 01:02 | design decisions that we make are
going to be a little subjective.
| | 01:05 | So things might not be in exact same
spot but that's okay, it's really flexible
| | 01:08 | we can always change it up.
| | 01:09 | So let's go ahead and close this down
and we will start making our own form.
| | 01:13 | First we will go up to the Create tab
of the Ribbon and in the Form section we
| | 01:17 | will click on Form Design.
| | 01:18 | That will create a blank form editing
session, where we can start working.
| | 01:22 | The first thing that you want to do with
starting a new form is connected to a data source.
| | 01:26 | So I will go over here to add existing
fields and I will click on that to open
| | 01:30 | the window and I am going
to click on Show all tables.
| | 01:32 | This will show me a listing of all
the tables that are in my database.
| | 01:35 | I can use the Plus symbol to
expand the field, for instance I'll click
| | 01:39 | the Plus over here next to tbl_Employees
to see all of the fields within that data table.
| | 01:43 | Let's go ahead and add a field to our form.
| | 01:45 | I am going to click-and-drag
FirstName and drop it in the Form area.
| | 01:49 | Now when you click-and-drag a field into
the form area we actually get two elements.
| | 01:53 | The first one over here on the left is
a label, basically this is just the name
| | 01:57 | of the header from the table, the second
one over here is a textbox, and this is
| | 02:01 | where the data will appear as
we scroll through our records.
| | 02:04 | So this is what's going to get populated
with the actual first name of the employee.
| | 02:07 | Now when we are moving elements
around on the form, the behavior is
| | 02:10 | slightly different than what you
might have experienced in Publisher or
| | 02:12 | PowerPoint or Word.
| | 02:13 | We've got these handles around the
edges, the one on the top, the three on
| | 02:17 | the right, the one on the bottom and
these two over here on the left side
| | 02:20 | will all resize my object.
| | 02:22 | For instance, I can click on this one
in the middle to make it wider or I can
| | 02:25 | click on the one down here to make it taller.
| | 02:28 | If I click anywhere on the orange bar
I will get this crossed arrow, I can
| | 02:32 | click-and-drag to move my object around;
| | 02:34 | but when I do, the associated label comes
right with it, it maintains its same relationship.
| | 02:38 | If I click on the gray box up here
in the top-left corner I get the same
| | 02:41 | double-headed cross arrow, using
that handle I can drag the two elements
| | 02:45 | independently of each other.
| | 02:47 | Now that they are in a different
relationship and I tried moving it using the
| | 02:50 | orange over here they will
maintain that new relationship.
| | 02:53 | So go ahead, I am going to move this
around, I am actually going to put it
| | 02:56 | underneath FirstName here, and now
if I were to drag it around FirstName
| | 03:00 | will come right with it.
| | 03:01 | So that's moving objects around, it
takes a little bit of getting used to
| | 03:03 | because it is different from what you
might have experienced before, but that's
| | 03:06 | how all of the objects
in Forms and Reports work.
| | 03:09 | Okay, let's add a couple of
more fields to our form here.
| | 03:11 | I have already got first name, let's
go ahead and add LastName and I will
| | 03:15 | click-and-drag these over, Phone number,
this time I will just double-click and
| | 03:18 | that will add it into the form.
| | 03:20 | Address, City, State, Zip, Email, HireDate.
| | 03:26 | I don't want everybody to see their
hourly rate of pay, so I will skip that one,
| | 03:29 | but I will do Department and Photo.
| | 03:32 | Now that all of the fields are in my
form I can go ahead and rearrange them into
| | 03:35 | the positions that I want.
| | 03:36 | Now all of these fields came with an
associated label with it, for instance,
| | 03:40 | I've got Photo here and then the Label photo.
| | 03:42 | Now this might be a little bit redundant,
I don't need a label to tell me that
| | 03:45 | this is a picture of the employee.
| | 03:46 | So I can get rid of some of these.
| | 03:48 | I am going to click on the
Photo label and delete it.
| | 03:49 | The same thing applies to the
Address, I don't need a label that says
| | 03:53 | City, State, and Zip;
| | 03:54 | all I want is the one that says Address.
| | 03:55 | So I will get rid of City,
State, and Zip labels;
| | 03:59 | same thing applies to the first and last
name, I think that's going to be pretty
| | 04:02 | clear so I will get rid of the
FirstName and this LastName label as well.
| | 04:05 | Okay, now I am going to
start moving elements around.
| | 04:08 | The first thing I want to do is
get myself some space to work.
| | 04:10 | So I am going to drag a box around all of
these and just drag them out of the way.
| | 04:14 | I will click off to deselect and now I can
grab each element and put it where I want.
| | 04:20 | First I am going to drag the photo
and put it in the top-left corner.
| | 04:23 | And I will resize it a little bit to
maybe about the one-inch mark and about an
| | 04:27 | inch-and-a-quarter down.
| | 04:29 | Next I am going to move
the name to the very top.
| | 04:31 | So I will start with the FirstName box
which is this one here, move that up to
| | 04:34 | there, and then I will move LastName up as well.
| | 04:37 | I also want to give myself a little
more room for the LastName, so I will
| | 04:40 | make that box wider.
| | 04:41 | Now below FirstName and LastName I want
to put their phone number, I will take
| | 04:45 | Phone and drag that up.
| | 04:47 | Now I am going to line everything up
against the two-inch mark here, below Phone
| | 04:50 | I am going to put Email.
| | 04:51 | So I will click-and-drag that one up as well.
| | 04:54 | Below that I will put their
Physical Address, take Address, move it up.
| | 04:58 | We will put City, State and Zip all on one line.
| | 05:01 | So we will take City there,
State here, and Zip next to it.
| | 05:06 | Finally we will put HireDate
and Department down in the bottom.
| | 05:10 | HireDate and Department, we
will put over here to the right.
| | 05:15 | Now the grid in the background is kind
of a snapping grid so when you move all
| | 05:18 | this around they tend to snap to
these corner points, that helps with the
| | 05:21 | alignment and everything and
keep everything spaced properly.
| | 05:24 | Next let's move these labels a little.
| | 05:26 | Department, I need to move that closer,
so I will use the handle here and we
| | 05:30 | will drag that closer to department.
| | 05:31 | HireDate, I am going to move that
over as well, and we can drag to here.
| | 05:35 | And we can use some of the tools up
here on the Arrange tab to help us with
| | 05:38 | the alignment as well.
| | 05:39 | For instance if I select all of
these labels at once by dragging a box
| | 05:43 | around them, nope, but I don't want
to select Photo, so I will deselect,
| | 05:46 | just those labels here.
| | 05:47 | I can go over to the Align menu in the
Sizing and Ordering section, click that
| | 05:52 | Down box and say I want to align
everything to the rightmost object which is
| | 05:55 | that HireDate box, and
then I will move accordingly.
| | 05:59 | Finally I am going to
make my Address field bigger.
| | 06:01 | I know I am going to need more room
for that so I will just drag that wider.
| | 06:04 | And while I am here, Email address
that should probably be wider as well.
| | 06:08 | Up on the Arrange tab of the Ribbon, there
are some other tools that could help us out.
| | 06:11 | If I wanted to ensure that everything
was lined up we've already seen the Align
| | 06:14 | tab which will have us align
everything to a specific object.
| | 06:17 | I could also use the sizing and spacing
which will help me resize everything to
| | 06:21 | a common size or space
everything so they are equally-spaced.
| | 06:24 | Let's switch into the Form View
to see how our form is looking.
| | 06:26 | I will switch to the Home tab and press Form.
| | 06:30 | Now I can see the data is being
populated in and I can scroll to my records
| | 06:33 | here, we can see the photos, the
names and all the data is being populated.
| | 06:37 | So let's go ahead and save this form,
I am going to save it as Employee
| | 06:41 | Directory and you can either press Ctrl+S
on your keyboard or press this disk
| | 06:45 | icon up here, we will name this
Employee Directory, and press OK.
| | 06:51 | That will save it down on the
Navigation Pane and we are going to continue
| | 06:54 | working with this form
throughout the rest of this chapter.
| | 06:56 | Now we can continue to finesse these
positions as we develop our form, and truth
| | 07:00 | be told, form design will require a
lot of back-and-forth if the final layout
| | 07:04 | gets further refined.
| | 07:05 | Now that we have our Form Field
elements in roughly the position that we want
| | 07:08 | them, we can take a look at some of
formatting options that are available.
| | 07:11 | We will pick it up there in the next movie.
| | Collapse this transcript |
| Formatting | 00:00 | In the last movie, we looked at how we
can add references to our data tables
| | 00:04 | with the field list and how the
arrange tab in the Form design will help you
| | 00:07 | organize elements on the page.
| | 00:09 | Now let's turn our attention to the Format tab.
| | 00:11 | The Format tab has many of common
formatting options that you might recognize
| | 00:15 | from other programs, such as Word or Excel.
| | 00:17 | Right now, I have got the Employee
Directory form open that we're working with earlier.
| | 00:20 | I am going to change its View to Design
View by going to the View menu and Design.
| | 00:25 | On the ribbon up here, I am
going to change the Format tab.
| | 00:28 | We will take a look at this.
| | 00:29 | One the left side, I have got a
Selection section, in the drop-down menu where I
| | 00:32 | can select different object on my form.
| | 00:34 | Now I can select from any object here,
if I select Address for an example, here
| | 00:37 | I will choose Address Box.
| | 00:39 | If I want to choose one of the labels,
I can choose say Label2 here, I am not
| | 00:44 | sure exactly which one label 2 is though,
apparently it's the phone number one.
| | 00:47 | So I could choose objects here from this menu.
| | 00:49 | I could also say Select All
and it will select everything.
| | 00:52 | I will go ahead and click down
anywhere in the space down here to deselect.
| | 00:56 | The next section changes Fonts.
| | 00:58 | So I am going to go ahead and click on
first name here where I could change the
| | 01:01 | fonts here if I wanted to, I
could change the size which I will.
| | 01:04 | I am going to go ahead and
change this to a 14 point.
| | 01:06 | I can change whether it's Bold, Italicized
or Underlined and I am going to choose Bold.
| | 01:11 | I can change the Font color, right now
its looks kind of a dark gray and it is,
| | 01:15 | you can see it here.
| | 01:15 | Let's go ahead and change it to black.
| | 01:17 | I could also change the font background.
| | 01:19 | And now this is going to change the
background color of the entire bounding box.
| | 01:22 | So for instance, if I choose this red
color here, Red Accent 2, I will see it
| | 01:26 | fills up the entire space underneath the orange.
| | 01:28 | I will go ahead and
change that back to Automatic.
| | 01:31 | I can choose whether, I want to take
my text Left aligned, Centered or Right
| | 01:35 | aligned and this tool up here is a paint
brush, it's call the Format Painter and
| | 01:38 | we will take a look at that in just a moment.
| | 01:40 | The next section is called Number
and it applies to numerical data.
| | 01:43 | So for instance my HireDate, it is a
date field, it is a number, if I select
| | 01:47 | that, it will give some options
here to deal with those numbers.
| | 01:50 | For dates, I can either choose to
display them in their standard format or I can
| | 01:53 | display it in a different format.
| | 01:55 | For instance, I am going to choose
Medium Date and we will see the result of
| | 01:57 | that change in a little bit.
| | 01:59 | The next section here Background,
you can apply Background Image.
| | 02:02 | Now it doesn't matter what object
you have selected if you choose this
| | 02:04 | Background Image, it's going to apply the
Background Image to the entire form, back here.
| | 02:08 | So it doesn't matter if I have
HireDate selected or nothing selected,
| | 02:11 | choosing this Background Image option
would apply a Background Image to the
| | 02:14 | entire form regardless.
| | 02:16 | And over here I have got Control
Formatting which would change to a Quick Style
| | 02:19 | or Shape for things like
buttons or other shapes.
| | 02:21 | I have got Conditional Formatting which
we will look at later in this course and
| | 02:25 | I could change Shape Fill, Shape
Outline, or Shape Effects, for things like
| | 02:28 | Rectangles, and Lines and Buttons.
| | 02:31 | Okay so let's go through and
change a couple of the objects here.
| | 02:33 | First of all I am going to change the boxes,
it appear around all of my text objects.
| | 02:37 | I can go to the Select All menu to just
grab everything it wants and I will go
| | 02:41 | to Shape Outline and change it to Transparent.
| | 02:43 | So we won't see those boxes
around all of our data fields.
| | 02:46 | Next I want to apply the same formatting
to Last Name that I have now on FirstName.
| | 02:50 | I can select the FirstName box, click
the Format Painter and click on LastName.
| | 02:55 | Notice that when I click on LastName,
it deselects the Format Painter up here.
| | 02:58 | If I want to change all these to the
same size, I could do that in different way.
| | 03:02 | I might choose the phone number one for
example, change it's Font size, maybe down to 10.
| | 03:07 | I could double-click on the Format
Painter to lock it on and now I can paint
| | 03:10 | multiple objects at once.
| | 03:11 | So I click on Email, Address, City,
HireDate, State, Zip, and Department and I
| | 03:17 | click on Format Painter to turn that off.
| | 03:18 | The other thing I can do is
Shift+Click to select multiple objects.
| | 03:21 | So let's say I want to choose these labels here.
| | 03:23 | I will click on Phone number, I can
Shift+Click, on the other labels here on my
| | 03:27 | form and I can change their
formatting all at the same time.
| | 03:30 | Let's say I wanted to make them a green color.
| | 03:31 | I will click on the Font Text here, and
I will make them this dark green, Olive
| | 03:36 | Green, Accent 3, Darker.
| | 03:37 | So let's go and see the results of our
changes, I will go to my Design Tab and
| | 03:42 | click on the Form View button here
and I will click on here, so that's
| | 03:45 | de-highlighting the name here.
| | 03:47 | So I do see one little error that I want
to change here, I can see that the word
| | 03:50 | guy here, the y is being chopped off the
bottom, I need to adjust the size of my
| | 03:53 | textbox a little bit.
| | 03:54 | Let's go back into Design
View and fix that real quick.
| | 03:57 | Click the View menu>Design View.
| | 03:59 | I will click FirstName and LastName at
the same time and I am going to press the
| | 04:03 | Shift+Up button and that can make it shorter.
| | 04:05 | If I press Shift+Down that will make it taller.
| | 04:07 | If I press Shift+Right that will make
them wider, if I press Shift+Left that
| | 04:11 | will make it narrower.
| | 04:12 | So I am going to press Shift+Down a
couple of times to make the boxes taller
| | 04:15 | and then I am going to let go the
Shift key and I will press the Up arrow to
| | 04:18 | move the objects up.
| | 04:19 | That's probably going to work out better,
we will click on the Form View button
| | 04:22 | here and that's looking better for the names.
| | 04:25 | Okay let's go ahead and save our changes,
press the Save icon here and our form
| | 04:29 | is set for the next movie.
| | 04:30 | Now the Design tab puts many common
controls right up front in the ribbon,
| | 04:34 | changing fonts and colors and line
styles here could be really quick especially
| | 04:38 | if you make good use of the Format Painter tool.
| | 04:40 | But for some serious control over every
aspect of our forms we are going to need
| | 04:44 | to dig a little bit deeper
and explore the Property Sheet.
| | Collapse this transcript |
| Modifying the form through its properties| 00:00 | The Format tab on the Ribbon gives you
a quick place to change some of the most
| | 00:03 | common settings for your form elements,
such as color, font, and size. But those
| | 00:08 | options are only the tip of the
iceberg when it comes to the vast number of
| | 00:11 | properties that each element possesses.
| | 00:13 | To see the full list, we will
have to dive into the Property Sheet.
| | 00:15 | I have got my Employee
Directory form open form the last movie.
| | 00:18 | We are going to go ahead and change
our view back to Design view here.
| | 00:21 | We will go ahead and get rid of the
Field List window by clicking the x here, and
| | 00:25 | I am going to turn on the Property Sheet.
| | 00:27 | The Property Sheet has lots of
different ways you can get to it.
| | 00:29 | First you can find it in the Design tab
of the Ribbon, under the Property Sheet
| | 00:32 | button here, and I can press the shortcut
key Alt+Enter or another shortcut key. F4
| | 00:38 | and that one is actually my favorite one.
| | 00:39 | The other way you can do it is
just double-click on an object.
| | 00:41 | For instance, if I double-click on
my LastName here, it will open up the
| | 00:44 | Property Sheet for that object.
| | 00:46 | Now every object on your form, including
the background, if I click back here, and
| | 00:50 | the form itself, if I click on
the box up here, have properties.
| | 00:55 | Let's look at some of the
things that we might want to change.
| | 00:57 | Let's go back into Form view for a moment.
| | 00:59 | Now in my Employee directory, first
of all, I want to make this a scrolling
| | 01:02 | form, so that each record
appears right on top of the other.
| | 01:06 | Next, I want to get rid of some of the clutter.
| | 01:07 | This thing over here on the left side
is called s record selector, this big
| | 01:10 | gray bar with the arrow on top of it.
You can click on it and it turns black, but
| | 01:14 | it doesn't look like it does anything else.
| | 01:16 | Let me show you a record selector at a
table, just so you can see what this is.
| | 01:18 | If I open up the Employees table here,
the record selector is this gray box
| | 01:23 | here. It selects the entire row.
| | 01:25 | So I can go through and select different rows.
| | 01:26 | That's what a record selector is.
| | 01:28 | Let me close the table.
| | 01:30 | So in the form right now, the
record is this entire thing.
| | 01:33 | So the record selector extends all
the way from the top to the bottom.
| | 01:36 | But essentially what I have done by
selecting this is select the record, just
| | 01:40 | like if I had done that on the table.
| | 01:41 | The other thing I want to get rid of,
since this will be a scrolling list, are
| | 01:44 | the Record Navigation buttons down
here at the bottom. It will be a scrolling
| | 01:47 | list, so I don't need to have this.
| | 01:49 | Finally, I want this to
be a review-only document.
| | 01:52 | I don't want people to be able to go
in here, for instance, and start changing
| | 01:55 | data, like renaming people or
changing their phone number.
| | 01:58 | I want people just to be able to look
at this and not edit the data that's in
| | 02:01 | the underlying data table.
| | 02:03 | So let's make those
changes in the Property Sheet.
| | 02:05 | I am going to change the View
menu and go back to Design view.
| | 02:07 | I am going to change the Property
Sheets for the form. Again the shortcut for
| | 02:11 | that is this button right here between
the two rulers and click that so I get
| | 02:15 | this black box here.
| | 02:16 | I can also change to the property of
this specific object by using this drop-
| | 02:19 | down menu in the Property Sheet.
| | 02:21 | Here I can find the listing of every object,
including every label inside of my document.
| | 02:25 | I am going to choose Form.
| | 02:27 | Now we're at the Properties for the form itself.
| | 02:29 | The Property Sheet is made
up of five different tabs.
| | 02:32 | The Format tab here has options to
deal with formatting, including a Caption,
| | 02:37 | which would appear on the tab up here
at the top, any kind of Default Views,
| | 02:40 | pictures, borders, colors,
scrollbars, and things like that.
| | 02:44 | Let's go through and change some of these.
| | 02:46 | First of all, I am going
to change the default view.
| | 02:48 | I will change it from Single Form, and
using the dropdown menu here I can change
| | 02:52 | it to Continuous form. And I am actually
going to make this little wider so we
| | 02:55 | can see the whole thing. There we go.
| | 02:56 | So the dropdown said Continuous Forms here.
| | 03:00 | We can change what View modes are
allowed. These will control what shows up in
| | 03:04 | the Views button here.
| | 03:05 | So for instance, I have no need to see
PivotChart or PivotTable or Layout or
| | 03:10 | Datasheet view; I can turn all
those off here in the Property Sheet.
| | 03:14 | So Form view is the standard for
interacting with it. I want to make sure I keep
| | 03:17 | that. But for allow Data Sheet view,
I will change the dropdown to No.
| | 03:21 | Now instead of using the mouse, you
can use your arrow keys to move around.
| | 03:24 | I could use the down key to move down
and the up key to move up or if you prefer
| | 03:28 | the right key and the
left key to move down and up.
| | 03:31 | If you prefer left-handed controls,
Tab will move you down and Shift+Tab
| | 03:35 | will move you back up.
| | 03:36 | While you are in the left side
Ctrl+Tab will cycle through the tabs.
| | 03:40 | Now we could go through and
change some things again.
| | 03:41 | I will press Shift+Tab to move up to
allow PivotTable view and instead of
| | 03:45 | using the dropdown menu, since I
know my only two options are yes or no, I
| | 03:48 | can press N to go to No.
| | 03:50 | I will press Tab to go down a view and
again PivotChart View, that one is no as well.
| | 03:54 | Next one down with tab, Layout view,
nope, don't want that one either.
| | 03:59 | The next one I want to
change is the Record Selectors.
| | 04:01 | That's about halfway down. So I am
going to press Tab a few times until I
| | 04:04 | get to Record Selectors, and it's right
there. I am going to turn those to No as well,
| | 04:08 | so I will press N. And right below
that there is Navigation Buttons.
| | 04:11 | Those are the ones that appear at the bottom.
I am going to change that to No as well.
| | 04:15 | Two more down is Dividing Lines.
| | 04:17 | Since this will be a scrolling from,
with each record appearing right on top of
| | 04:20 | itself, I can turn on dividing lines
to provide a visual cue where one record
| | 04:23 | ends and the next one starts.
| | 04:25 | I am going to press Y to change that
to Yes and press Tab to finalize that.
| | 04:28 | Let's take a look at some of
the options in the other tabs.
| | 04:31 | I will switch to the Data tab.
| | 04:32 | We can use the mouse here to click.
| | 04:34 | The one that I want to take a
look at first is this Recordset type.
| | 04:37 | Right now it's set to Dynaset.
| | 04:39 | This is providing the dynamic link
between the form and the data table behind it.
| | 04:42 | If I change this here to Snapshot, that will
prevent people from being able to edit the data.
| | 04:48 | This will just show a snapshot of the
data table as it existed when the person
| | 04:52 | first opened that form.
| | 04:53 | Let's take a look at the next step.
| | 04:54 | I will click on the Event.
| | 04:56 | The Event tab has all the
different ways that you can control running
| | 05:00 | macros from your form,
| | 05:01 | so for instance, when a user clicks on
something, or down here, if they double-click.
| | 05:06 | I can trigger a macro when somebody
clicks the mouse down and another if they
| | 05:10 | have clicked the mouse up,
using these two properties here.
| | 05:12 | There's lots of different ways that you can
add interactivity to your forms and reports.
| | 05:16 | Let's look at the Other tab.
| | 05:17 | The Other tab has miscellaneous
options. For instance this Pop Up one here, if
| | 05:21 | I change that to Yes, that will make it a
floating pop-up window instead of the Tab window.
| | 05:25 | If I change the Modal property to Yes,
that will force the user to have to
| | 05:29 | interact and close the form before
Access will let you do anything else.
| | 05:33 | And finally, the All tab here has a long
master list of every other property in
| | 05:37 | the other four tabs.
| | 05:39 | So if I can't remember where it is in
these other four tabs, you can always come
| | 05:42 | to the All list and find it here.
| | 05:43 | Now let's take a look at the changes that
we have made here in the Property Sheet.
| | 05:46 | I am going to up to the View
menu and change to Form view.
| | 05:50 | Now you can see it's a
scrolling list of all my employees.
| | 05:53 | I can see a slight problem here.
| | 05:55 | I can see that I have got a lot of
white space in between each record, so I can
| | 05:58 | go through in Design view and tighten
that up a little bit. Go back into Design
| | 06:02 | view. Now scroll down to the bottom here,
until I get to the bottom of my form,
| | 06:05 | which is this bar right here, and I
will click and I will drag that up little
| | 06:08 | closer to my records.
| | 06:11 | Now each one will be a narrow view.
Back into Form view again and there it is.
| | 06:16 | It's looking pretty good. If I scroll
through my employees, we get a nice,
| | 06:19 | continuous rolodex-style list going.
| | 06:22 | So the property sheet is an extensive
resource of everything you might want to
| | 06:25 | control in your database, and you will
be happy to know that both forms and
| | 06:29 | reports share this common interface.
| | 06:31 | While some of the most common settings
can be changed through the Ribbon tabs,
| | 06:34 | the Property Sheet is generally my
go-to Resource because it gives access to
| | 06:38 | all of the available settings, not just
the most common ones, and often I found
| | 06:42 | the Property Sheet to be a
great discovery resource too.
| | 06:45 | It allows you to find settings and
options that you didn't even know you wanted
| | 06:48 | to change until you saw it in the list.
| | 06:49 | Now I am not going to be able to go
through all the properties in this course,
| | 06:52 | but I definitely encourage you to
scroll through that list and explore it further.
| | Collapse this transcript |
| Adding a header and some polish| 00:00 | We are going to wrap up our rolodex-
style employee directory here and add a
| | 00:03 | final bit of polish using the
Property Sheet to modify some additional
| | 00:06 | objects within our form.
| | 00:08 | We will start by adding a header section
that will appear at the top of our form
| | 00:11 | so that our end users will
immediately know what they're looking at.
| | 00:14 | As we go through this, keep an eye on
the Property Sheet and you will see the
| | 00:17 | values change as we move
things around and make selections.
| | 00:20 | I have got my employee directory
open here, and one of the changes we made in
| | 00:24 | the last movie was to
remove some of the View modes.
| | 00:26 | So now if I want to change the Design
view, all I need to do is press this
| | 00:29 | Design button right up here.
| | 00:30 | The View mode that we are not going to
be using have disappeared from this list.
| | 00:34 | So it makes it really easy to toggle back
and forth between Design view and Form view.
| | 00:38 | So I will just click the button here.
That will put me back into Design view. I
| | 00:41 | can open up the Property Sheet, if you
remember those shortcuts: Alt+Enter, F4
| | 00:46 | double-clicking, or using the
Property Sheet button here.
| | 00:49 | So however you want to do it, go
ahead and open up that Property Sheet.
| | 00:52 | Now the first thing I want to do is add that
header section at the top where my titles will appear.
| | 00:55 | I am going to right click on the
Detail bar right here, and I am going
| | 00:58 | choose Form Header/Footer.
| | 00:59 | That will put a header section at the
top and a footer section down here at
| | 01:03 | the bottom. And we are not going to be
using the footer, so I will go ahead and
| | 01:06 | scroll this up to the bottom and get rid of it.
| | 01:09 | The header section, I am going to go and
expand it a little bit so I have some room to work.
| | 01:12 | I will click here and drag it down.
| | 01:14 | The next thing I want to do is add
a logo to this top-left corner here.
| | 01:17 | There is lots of different ways that
you can add images into your forms. You
| | 01:20 | could either use this Insert Image
button here. We can use the Logo button here.
| | 01:24 | The Logo button will add a formatted
image and I will drop it in the top
| | 01:28 | left corner right here.
| | 01:29 | I like to use the Insert
Image button better, because one,
| | 01:31 | it will give me a gallery of images
that I have already used here and two,
| | 01:35 | I find that I have more control over an
image when it's not locked into this Logo type.
| | 01:39 | So I am going to click
Insert Image and then browse.
| | 01:42 | We will browse into our Desktop,
Exercise Files, the Chapter 2 folder, and we
| | 01:47 | will find TwoTreesLogo.
| | 01:48 | Go ahead and say OK.
| | 01:50 | Access gives me this little box here,
my cursor has changed across here with a
| | 01:53 | little image icon, and I will just drag
out a box to place my logo. And there it is.
| | 01:58 | Now let's add a couple of labels here.
| | 02:00 | I am going to add some titles,
| | 02:02 | so I will click on this Title button right
there. That will add a title to my form.
| | 02:05 | I am going to ahead and accept this
name of Employee Directory; it's pulling
| | 02:08 | that from the name of the form right here.
| | 02:10 | But I want to format it a little bit.
| | 02:12 | Now I could go up to the Format tab here
and change the formatting. I could also
| | 02:16 | change it down here in the Property Sheet.
| | 02:18 | Let's go ahead and change
it in the Property Sheet.
| | 02:20 | First of all, I am going to change the
font. Instead of Cambria, I am going to
| | 02:23 | change it to Calibri.
| | 02:24 | And I will leave it at an 18-point size.
| | 02:27 | I will leave the Text Alignment to
left here and actually this is kind of
| | 02:30 | interesting. Let me show you.
| | 02:31 | The Text Alignment options up on the Format
tab are left aligned, center, or right aligned.
| | 02:36 | Down here on the Property Sheet, it's
currently set to left. If I use the drop down
| | 02:39 | menu, I have Left, Center, Right, but I
also have this Distribute option, which
| | 02:43 | will distribute the letters
across the entire bounding box.
| | 02:46 | So that's an option that you only have
available in the Property Sheet and not
| | 02:49 | up here in the Ribbon.
I am going to switch it back to Left.
| | 02:53 | Now I can go ahead and
change the color of this font.
| | 02:55 | I could change it up here on the
Ribbon or I can change it down here on
| | 02:58 | the Property Sheet.
| | 02:59 | I am looking for the Fore Color,
which right now is set to Text 2.
| | 03:02 | I will click on the Build button, and
that will give me the color picker and I am
| | 03:06 | going to choose this dark gray right here.
| | 03:07 | It's Black, Text 1, Lighter by 15%.
| | 03:11 | Now I am going to move into position.
Because I use the Title tab instead of just
| | 03:14 | placing text on my own, it's kind
of locked in here in the top corner.
| | 03:17 | I can move it around using the handle
that's way over here on the left,
| | 03:20 | so it looks like it's
little bit away from the label.
| | 03:23 | I will click and drag that over.
| | 03:26 | Now when I change this background
color back here, I want to make it a green
| | 03:29 | color that matches our logo.
| | 03:30 | I will click on this background, right
over here there is nothing selected, and
| | 03:33 | into the Format here, I will
change Back color to a specific color.
| | 03:37 | I will use the Build button and instead
of choosing one of these color chips, I
| | 03:41 | am going to say More Colors and
I am going to type in the value.
| | 03:44 | Now I know that this logo color is 185
for Red, 198 for Green, and 170 for Blue.
| | 03:51 | I will type those values in and say OK.
| | 03:54 | Now my background color matches the logo.
| | 03:57 | The last thing that I want to change
are these labels down here on the detail
| | 04:00 | section of my report.
| | 04:01 | Earlier in this movie, I changed them
to a dark green color, and the more I look
| | 04:05 | at it, I think it distracts
from the actual data here,
| | 04:08 | so I might go ahead and change
those using the Property Sheet.
| | 04:10 | I will click on Phone. Now I
will Shift+Click on all the others.
| | 04:14 | Now in the Property Sheet, I
can change their properties.
| | 04:17 | First I am going to change the Size down.
I am going to change those to 9. And in
| | 04:21 | the color, right down here where it says
Foreground color, I will use the Build button
| | 04:25 | and I am going to chose a gray color,
| | 04:27 | maybe this white background darker by 25%.
| | 04:30 | Now let's take a look and see how this
form is working. I will go back to the
| | 04:33 | Design tab, switch to Form view, and there we go.
| | 04:36 | You know now that I am looking at those,
those look a little too light. Let's go
| | 04:39 | back and change it one more time.
Go back into Design view. They are still
| | 04:42 | highlighted, so I can click on the Build
button here, and we will choose a darker
| | 04:46 | color. How about this one here, the 50%?
| | 04:51 | Go to Form view, and that's looking better.
| | 04:53 | I can see it now, but it is not
distracting from the actual data.
| | 04:55 | Let's go ahead and scroll through our form,
and you can see that I can scroll through
| | 04:59 | by using the mouse here or you can
scroll using the bar, and my header here at
| | 05:03 | the top isn't moving.
| | 05:05 | I also have these lines between each
record, and all the fonts and graphics are
| | 05:08 | looking aligned up properly.
| | 05:10 | So there's our finished employee directory.
| | 05:11 | The Property Sheet is really the
master list of all the options that you have
| | 05:15 | within your database.
| | 05:16 | This applies not only to forms, but
we'll see it again when we work with reports
| | 05:20 | later on in the course.
| | 05:21 | It may look a little intimidating at
first, but I would definitely recommend
| | 05:24 | that you spend some time reading through
the list of available settings, and I am
| | 05:28 | sure that you will come across a few
of this to stimulate your brain and make
| | 05:30 | you think of new functionality that
you can incorporate into your database.
| | Collapse this transcript |
|
|
3. Form and Report ControlsIntroducing form controls| 00:01 | In this chapter we're going to be
taking a look at each of the control objects
| | 00:04 | that are available to us when
working with Forms and Reports.
| | 00:07 | We've seen a couple of them in the last
few movies, but I think it's important
| | 00:10 | to explore each one individually and
see what some of the options we have are
| | 00:13 | when working with them.
| | 00:14 | In this movie we're going to take a
look at the Selection arrow and the Label
| | 00:17 | control, so let's start by going up to
the Create tab here and starting a new
| | 00:21 | form in Design View with the Form Design button.
| | 00:23 | That will start a new form in Design
View and for the purposes of this chapter,
| | 00:27 | I actually want to change this default
background a little bit, when you first
| | 00:30 | start a form in Design View,
Access places this dot
| | 00:33 | and grid pattern in the background.
| | 00:34 | This helps you align everything on your
form, but I think that the purposes of
| | 00:37 | these movies the dots are going to be a
little bit distracting and make it hard
| | 00:40 | to see what I'm doing.
| | 00:41 | So I'm going to go ahead
and change this a little bit.
| | 00:43 | On the Property Sheet for my form I'm
going to scroll at the bottom and these
| | 00:46 | two properties here Grid X, Grid Y,
and they're currently both set to 24.
| | 00:50 | I'm going to change these to smaller
value like six, which will reduce the
| | 00:54 | density of the dots in the background,
and I think that will make it easier to
| | 00:56 | see on these movies.
| | 00:57 | I will go ahead and save this form by
pressing Ctrl+S and I'm just going to
| | 01:00 | call this Controls.
| | 01:01 | And we'll use this form
throughout the rest of this chapter.
| | 01:04 | Okay, so let's take a
look at a couple of controls.
| | 01:06 | Up here in the ribbon, on the Design Tab, I
have got this whole section called Controls.
| | 01:10 | This Controls box essentially a
toolbox of all the different objects that you
| | 01:14 | can add into your form.
| | 01:15 | The first one here is the Selection
arrow, and the Selection arrow isn't really
| | 01:19 | a control because it's not an object
that's going to go on your form, but it's
| | 01:22 | what you're going to use to
interact with all the different objects.
| | 01:24 | The other one I want to look at in
this movie is this one here, the Label.
| | 01:27 | The label we have seen in prior movies,
and this is a static bit of text, it
| | 01:31 | doesn't change as you scroll
through records on your form.
| | 01:33 | Now this is a slightly confusing
terminology because in other programs such
| | 01:37 | as Word or PowerPoint when you want to put text
on the page you use something called a Text Box.
| | 01:41 | In Access the text box is this object
right here, and in Access the text box is
| | 01:45 | specifically tied to data in the data table.
| | 01:47 | So when you want to place just a piece
of static text, you're going to use a
| | 01:50 | label, when you want to display
data that's when you use a text box.
| | 01:54 | So let's go ahead and take a look at the Label.
| | 01:55 | I'm going to click on the control here and
I will click down on my form to add a label.
| | 01:59 | You notice that once I do that a couple
of things happened the first thing is I
| | 02:02 | get those box down here with a
flashing cursor in it, ready for me to type.
| | 02:05 | The other thing that happens is its
deselected label and it's automatically
| | 02:08 | switched back to the Selection arrow.
| | 02:09 | Let's go ahead and just type in
the word text and then press Enter.
| | 02:12 | Now that object is saved and
I'm back in the Selection arrow.
| | 02:15 | If you want to add multiple labels
at once or in fact multiples of any of
| | 02:18 | these controls, you could simply right-click
on the button and say Drop Multiple Controls.
| | 02:22 | I'll click on that and now I can add
multiple pieces of text at one time, so
| | 02:26 | I'll click once and you notice it didn't
automatically select the Selection tool
| | 02:29 | here, It stayed on the on the label.
| | 02:31 | So I'll type in text again and press Enter;
| | 02:33 | now I can click third time,
text again and press Enter.
| | 02:36 | When I'm done I'll switch
back to the Selection arrow here.
| | 02:39 | Now in prior versions of Access, you
used to be able to double-click on the
| | 02:42 | button to add multiple options and
that no longer functions in Access 2010.
| | 02:45 | So just use a right-click menu and
choose Drop Multiple Controls from there.
| | 02:48 | So now that I have a couple of labels on
my form I can do a few things with them.
| | 02:52 | I choose the Selection arrow to make
sure not selecting anything, and now I can
| | 02:56 | click each individual piece.
| | 02:57 | I can Shift+Click to select
multiple objects by click off of them, I
| | 03:00 | will deselect them.
| | 03:01 | I can use the control handles with
or around the edges to resize them.
| | 03:05 | So I can drag up on the middle or right
on the right side and if I want to move
| | 03:08 | them around I can use either the handle
in the upper left-hand corner or click
| | 03:11 | anywhere on the bar between
panels to drag them around.
| | 03:14 | I can use the arrow keys on my keyboard,
so I can move things down or right or
| | 03:17 | up or left using the arrows.
| | 03:19 | And if I want to resize them I can use
the keyboard as well by pressing Shift
| | 03:22 | and using the right arrow to enlarge it,
the left arrow to shrink it, down arrow
| | 03:25 | to make it taller, and the
up arrow to make it shorter.
| | 03:28 | And if I want to get rid of an object
all I need to do is press the Delete
| | 03:31 | key once it's selected.
| | 03:32 | So I'm going to go ahead and select
these other two, I am going to press the
| | 03:34 | Delete key that will set this up for the
next movie when we look at Lines and Rectangles.
| | 03:37 | The Label Control is one of the most
common controls that you'll be working with
| | 03:40 | when you create Forms and Reports.
| | 03:42 | You'll use them anytime you want a
static bit of text, a title, or a piece of
| | 03:46 | instruction to stay on your form.
| | Collapse this transcript |
| Using lines and rectangles| 00:00 | The next two controls that I
want to explore are the Line and
| | 00:03 | Rectangle controls.
| | 00:04 | They're useful for marking of space and
grouping elements together in your forms and reports.
| | 00:08 | Generally, they are pretty passive
and not really all that exiting, but
| | 00:11 | proper use of them can go a long way to making
your forms and reports legible and unambiguous.
| | 00:16 | Let's go ahead and open up this
Controls form that I've got here, we've
| | 00:19 | created in the last movie.
| | 00:20 | Now right-click and say Design View
to jump straight into Design View.
| | 00:22 | Now the line control is
here in the Design ribbon.
| | 00:25 | I can click on it and drag out a line.
| | 00:27 | Now lines have a kind of a weird
property to them when they're completely
| | 00:30 | horizontal like this or completely
vertical like this they're invisible to you.
| | 00:35 | The only way you can see what you're
doing is to look at the ruler on the left
| | 00:38 | side, while you're dragging out a
vertical line or the ruler across the top when
| | 00:41 | you're dragging out a horizontal line.
| | 00:42 | Once I let go of the mouse though the
line will appear and it will be selected.
| | 00:46 | Now lines have a couple of properties
associated with them, you can see them in
| | 00:49 | the Property Sheet here.
| | 00:51 | Under the Format tab, I change the
Shape outline, so I can make them thicker or
| | 00:55 | thinner, I can change that line type to
dashed or dotted, I can change
| | 00:58 | the color right up here.
| | 00:59 | All of these properties are also
available in the Property Sheet.
| | 01:02 | So for instance I can change its
style from Solid to Dotted or Dashed.
| | 01:05 | I could change its Border Width from
Hairline to 6 point, and I can change its
| | 01:09 | color here using the Color
Picker or selecting from a list.
| | 01:12 | Let's take a look at the Rectangle Tool.
| | 01:14 | We'll go up to the Design Tab, the
rectangle is on the second row, and depending
| | 01:18 | on the size of your screen all of the
controls might appear on a single row or
| | 01:21 | you might have a scroll through the list.
| | 01:22 | You can also use this down arrow here
that says more, and I click that all of
| | 01:26 | the different controls that
we can work with will appear.
| | 01:28 | The Rectangle control is this one right
here and it looks a lot like this other
| | 01:31 | one here which is the Button Control,
so I want to make sure that we're using
| | 01:34 | the Rectangle and not a Button.
| | 01:36 | The Button has the access in it,
the Rectangle is just empty.
| | 01:38 | So we'll choose a Rectangle
Control, we'll drag one out as well.
| | 01:41 | Now the Rectangle control also has a
property into formatting of Shape Fill, I
| | 01:45 | can use a Shape Fill here to choose a color.
| | 01:47 | I can use the Fill Bucket here in the
Font section to choose a color or I can
| | 01:51 | change its color here
the Back color right there.
| | 01:53 | I can change its Border Style, Width, and
Border Color here just like I can with the line.
| | 01:58 | Now lines and rectangles also have
another property which makes them really useful.
| | 02:01 | I go to Arrange, you can take a
look at this Anchoring button.
| | 02:04 | Let me go ahead and click on the line
and I'll click on Anchoring, and we can
| | 02:07 | see that it's anchoring
right now is in the top-left.
| | 02:10 | With the default anchoring what happens
is Access draws the line exactly as you
| | 02:13 | place it, in reference to the
top-left corner of your form.
| | 02:16 | Let's change it to Stretch
Across Top and see what happens.
| | 02:19 | I'll click Stretch Across Top for the
line, we'll go to the Home tab and press
| | 02:22 | Forms and switch into Form view.
| | 02:24 | Now we see that the line is
much longer than what I drew it.
| | 02:27 | With Stretch Across Top what Access
does is references both the left-hand side
| | 02:31 | and the right-hand side, so it
maintains a space from the left to the line and
| | 02:34 | the right to the line, and save
the actual length of the line.
| | 02:37 | So Lines and Rectangles play an
important role in organizing other elements in
| | 02:41 | your forms and reports.
| | 02:42 | They can be used to mark of space or to
group like elements together such as a
| | 02:46 | box around a grouping of buttons.
| | Collapse this transcript |
| Organizing screen space with tabs| 00:00 | A Tab control can be used to add extra
space to your form or group similar or
| | 00:04 | related objects together.
| | 00:06 | A Tab control is a great way to
organize your form, but it's not going to work
| | 00:09 | very well on a printed report, since
you'll only be able to see the first tab
| | 00:12 | when it's printed and clicking on
paper really doesn't work that well.
| | 00:15 | Let's go ahead and open up our
Controls form by right-clicking on it and
| | 00:18 | selecting Design view.
| | 00:19 | The Tab control is this button right here.
| | 00:22 | We'll click on it once
and we'll drag out a tab.
| | 00:23 | I'm going to go ahead and draw a box
that fills up most of the bottom of my form.
| | 00:28 | Access adds in two tabs here.
| | 00:29 | If I wanted to add additional tabs, I
can right-click and say Insert Page; if I
| | 00:33 | wanted to delete a tab, I can right-
click and say Delete Page; and if I want to
| | 00:37 | rearrange the tabs, I'd
right-click and say Page Order.
| | 00:40 | Access will bring up a Page Order
dialog box, in which case I can move up or
| | 00:44 | down the tabs however I see fit, okay.
| | 00:47 | So what can we do with a tab?
| | 00:48 | Well tab essentially doubles or
triples or quadruples the screen real estate
| | 00:52 | we have on each form.
| | 00:53 | For instance, if I click on Page4, it
highlights the content area of that tab.
| | 00:57 | Anything that I put in here
won't appear when I click on Page5.
| | 01:01 | We can have discrete and
individual content for every tab.
| | 01:03 | I'll click on the Page4 tab, and I'll
go ahead and just add a label in here.
| | 01:07 | I'll select the Label control from the
toolbox and I'll move down into the tab.
| | 01:10 | Once I get over the tab area it turns black.
| | 01:13 | This is Access's way of telling me that
the next thing I'm about to do is going
| | 01:16 | to go inside of this tab,
not inside of the form.
| | 01:18 | I'll click to add a label, and
I'll just say, "This is page 4."
| | 01:21 | Let's go ahead and click on the other tab.
This time I'll just draw a line inside of it.
| | 01:25 | I'll click on the Line control
and I'll drag out a line.
| | 01:27 | Okay, let's go ahead and take
a look at this in Form view.
| | 01:29 | I'll click on the Form View button here,
and now I can see the tabs. I can switch
| | 01:33 | back and forth between the first tab
here and the second tab here, and I can see
| | 01:36 | they've got different content in each one.
| | 01:38 | Okay, let's go back into Design view.
Now we can change the titles of these
| | 01:41 | tabs. Page4 and Page5 really is
not going to help me very much,
| | 01:45 | so I'll click on the first one here,
and in the Property Sheet, I'm going to
| | 01:48 | change the caption for the tab.
| | 01:50 | The caption for this one
is just going to be Text.
| | 01:51 | Let's go ahead and change
the caption for second one.
| | 01:54 | This one I'm going to call Line.
| | 01:55 | Now you can put any kind of
content that you want inside of here.
| | 01:58 | Later on we're going to
take a look at subforms.
| | 02:00 | You can put an entire form inside of this tab.
| | 02:02 | You can put all kinds of content: buttons,
selectors, hyperlinks, text boxes, and charts.
| | 02:07 | Anything that you can put on your
form you can put inside of a tab.
| | 02:09 | Now there is another control in
Access 2010 called the Navigation control.
| | 02:13 | This is similar to a tab, but it
only works with the forms inside of it.
| | 02:17 | Let me take a look at one of these.
| | 02:18 | I am going to go ahead and
select these tabs and delete them.
| | 02:20 | We'll use the Navigation control here,
the one with the pink bar at the top.
| | 02:25 | I'll click and I'll drag
out a Navigation control.
| | 02:27 | Now the interface is a little bit
unusual with the Navigation control.
| | 02:31 | When I first put it in, it's selected,
but nothing is showing up here in
| | 02:33 | the Property Sheet.
| | 02:34 | I'll click on the Add New here,
| | 02:35 | I'll double-click on it,
and I will change its name.
| | 02:37 | For instance, I'll say
Employee Directory and press Enter.
| | 02:40 | When I press Enter, Access adds a little
tab right here and moves over to the next one.
| | 02:44 | I can add a second tab by double-
clicking here and adding another name.
| | 02:48 | I'm just going to call this
Orders by Product and press Enter.
| | 02:51 | Now I have the opportunity
to add a third tab and so on.
| | 02:53 | Once I go back to the first one that I
created, the Property Sheet is live now.
| | 02:56 | I can go ahead and
change what's connected to it.
| | 02:58 | So for instance I can go to the Data
tab. In the Navigation Target Name it
| | 03:02 | automatic shows as Employee Directory
because that's what I named the tab.
| | 03:05 | If I wanted to change that, I can use
this dropdown menu and choose different
| | 03:08 | forms that are already
present in my database here.
| | 03:11 | The Orders by Product tab, again, it
automatically selected it, because I
| | 03:14 | changed the name to that up here in the tab.
| | 03:16 | But you can make the name whatever
you'd like and then change the target here
| | 03:20 | to whatever form you want.
| | 03:21 | So let's take a look at this form in Form view.
| | 03:23 | We'll click on the Form view
button, and I can scroll down to see my
| | 03:26 | employees directory.
| | 03:27 | You change this tab here and
look at the Orders by Product one.
| | 03:30 | Now the Navigation controls,
they're little bit hard to deal with.
| | 03:33 | I honestly think the regular Tab
control works much better than the Navigation
| | 03:37 | tab control for this sort of object.
| | 03:39 | The reason being is that the regular Tab
control, you can put a form in it using
| | 03:42 | a subform, like we'll see later on.
| | 03:44 | So there is nothing really special
about the Navigation control in these tabs.
| | 03:47 | One other thing about the Navigation
control: if you go to the Create tab or the
| | 03:50 | Ribbon, under the Form section here,
you get this whole navigation group.
| | 03:54 | If I click on here, you can see a whole
bunch of different forms that are preset
| | 03:58 | up with the Navigation control and
the tabs in different arrangements. For
| | 04:01 | instance, I can select Vertical Tabs
Left and that will create a new blank form,
| | 04:05 | ready for me to type in the
values that I want in here.
| | 04:07 | So for instance, I can do the same thing.
| | 04:09 | I can double-click on the tab on the left and
type in Employee Directory and press Enter.
| | 04:14 | Then I can add the second tab here,
and this time I'll type Product Selection
| | 04:18 | and press Enter again.
| | 04:19 | Now if I view this form in Form view,
you can see how the tabs are working a
| | 04:23 | little bit better. Employee here, Product there.
| | 04:25 | All it's doing is pulling in the
content from these other forms and placing it
| | 04:29 | inside of this navigation form.
| | 04:31 | So that's a couple of ways that you
can add tabs to add dimension and
| | 04:34 | organization to your forms
when screen space is at a premium.
| | 04:37 | Later on when we take a look at
subforms, we'll see how we can make a regular
| | 04:40 | Tab object function almost exactly
like this navigation form, and we'll have
| | 04:44 | more control over it with that method.
| | Collapse this transcript |
| Adding buttons| 00:01 | Buttons are one of the key
interactive elements that you'll use on forms.
| | 00:04 | They instantly tell the end user that
something will happen when I click there.
| | 00:08 | Buttons will be linked to either Macros or
Visual Basic code to control their behavior.
| | 00:12 | Like the other controls, you add
them to reports as well for navigation
| | 00:16 | within your database.
| | 00:17 | But the nice thing about buttons is that
they don't print when you print your report.
| | 00:20 | Let's take a look at adding
some buttons to my Controls form.
| | 00:22 | I'll right-click on that form in the
Navigation pane and choose Design View.
| | 00:27 | The Button control is this one right here,
the rectangle at the Xs inside of it.
| | 00:30 | I'll go ahead and click that once
and click to add a button to my form.
| | 00:33 | When I do that the Command Button Wizard starts,
and it asks what do I want my button to do?
| | 00:38 | This first section has Categories;
| | 00:40 | the second section has
Actions within each category.
| | 00:43 | So for instance, I've got Record
Navigation, and I can do Find Next or Find
| | 00:46 | Record or Go To Next Record or Last Record.
| | 00:49 | Under Record Operations I can Add New
Records or Delete Records, Print or Save.
| | 00:53 | Under Form Operations I can apply a
filter, close or open a form, I can send a
| | 00:58 | form to a printer, or refresh the data.
| | 01:00 | Under Report Operations I can choose
to Mail a Report or Print a Report.
| | 01:04 | The Application category only has one
action and that's to Quit Access or quit
| | 01:08 | the entire program, and finally the
Miscellaneous category has things like Print
| | 01:12 | a Table or run another Macro.
| | 01:13 | Let's go ahead and choose one of these options.
| | 01:15 | I'm going to go to Form Operations
and I'm going to choose Open Form.
| | 01:19 | We'll go ahead and say Next, Access then
asks me well, which form do you want to open?
| | 01:22 | I want to open up the Employee Directory;
| | 01:24 | we'll go ahead and say Next.
| | 01:25 | Access asks us if we want to open the
form to a specific data or open the form
| | 01:29 | and show all the records.
| | 01:31 | I want to display the entire
thing, go ahead and say Next.
| | 01:33 | And finally, what do I want all my button?
| | 01:35 | Do I want Text or do I want a Picture,
and if I want a picture, I can browse and
| | 01:38 | find an icon or I can accept the default here.
| | 01:41 | We'll go ahead and choose Picture,
and we'll accept this default one here.
| | 01:44 | Let's go ahead and say Next, and
we'll accept the default name for this
| | 01:46 | button and say, Finish.
| | 01:47 | Now I've got a button on my form.
| | 01:49 | I'll go ahead and deselect it,
so I can see what it looks like.
| | 01:51 | Now buttons have lots of
properties that we can change.
| | 01:53 | For instance, I selected that I
wanted an image on my button, but what if I
| | 01:56 | wanted an image and text?
| | 01:58 | Well, I can do that over here
in the Format tab for the button.
| | 02:01 | Right now it says Picture Caption Arrangement:
| | 02:03 | No Picture Caption.
| | 02:04 | Let's go ahead and change that to Right;
| | 02:06 | that will add a caption to
the right side of my image.
| | 02:09 | Let's make the button a little bit
bigger, I'll drag on the right side and I'll
| | 02:11 | make it a little shorter.
| | 02:14 | Now I can change the text here with the Caption.
| | 02:16 | So instead of just the icon, I can
have, say, Open Employee Directory, and
| | 02:21 | again, I'll have to make it a little
bigger or I can go to the Format tab and
| | 02:24 | change the font, I'll make it a
little smaller, to an 8 point.
| | 02:26 | I can also change the font down here;
| | 02:29 | Font Size is now set to 8.
| | 02:30 | Let's see some other things that
we can change with our buttons.
| | 02:32 | I can go up to the Quick Styles button here in
the Format tab and choose a style that I like.
| | 02:36 | May be I want to make it look
like this green light button.
| | 02:38 | Now it's got a shadow and it's green colored.
| | 02:40 | I can click on the button and
take a look at Shape Effects.
| | 02:43 | We can add Shadows or Glow, or
Soft Edges, or even make it Beveled.
| | 02:47 | Now I want to caution you against
using some of these especial effects.
| | 02:50 | A lot of times we can just add
unnecessary clutter to your forms, they don't
| | 02:53 | actually add anything to the user interface.
| | 02:55 | And I'm going to leave all these alone
and let's go back and take a look at some
| | 02:58 | more properties here.
| | 02:59 | Some of the other things that I
can do is change the color when the
| | 03:01 | user interacts with it.
| | 03:02 | So for instance, I've got Hover Fore
Color right now or Pressed Fore Color,
| | 03:06 | the Hover state is what happens when
the mouse moves over the button, the
| | 03:09 | Pressed state is what happens when the
user clicks down on the button, we can
| | 03:12 | change both of these.
| | 03:13 | Hover Fore will change the color of
the text, so instead of that black color,
| | 03:16 | maybe we'll make the checks change to dark gray.
| | 03:19 | The Pressed Fore Color will change the
color of the text when it's pressed, so
| | 03:22 | instead of black, we'll
make it red when it's pressed.
| | 03:25 | The Hover Color and Pressed Color
will be at the background of the button.
| | 03:28 | So we'll set the Hover Color may be to
a darker green, here, and the Pressed
| | 03:32 | Color, maybe we'll make that red.
| | 03:33 | I'll hit the Build button
and I'll choose a nice red.
| | 03:36 | Let's go ahead and see
what my button looks like now.
| | 03:37 | I'll go to the Home tab and switch to
Form View and now if I hover over the
| | 03:40 | button, you see the color
has changed to a gray color.
| | 03:43 | As I hover over it, it changes color
and as I click down on it, it changes too.
| | 03:47 | So I can see it's got a light
pink background with a red text.
| | 03:49 | If I click on the button;
| | 03:50 | it's going to open up my Employee Directory,
so it's doing exactly what I told it to do.
| | 03:53 | Let's go ahead and close the Employee
Directory and we'll switch back into
| | 03:56 | Design View for our Controls form.
| | 03:58 | Now I can control what the button is
doing when I click on it from the Event
| | 04:01 | tab in the Property Sheet, when we
went to that wizard it was creating this
| | 04:04 | OnClick event which means when I click on it
what happens, that runs this in Embedded Macro.
| | 04:08 | I can see what the embedded macro is by
clicking the Build button to the right.
| | 04:11 | Or if I have other macros that are
already saved in my Navigation pane here, I
| | 04:15 | can use the drop-down menu to select from those.
| | 04:17 | I'll click Build to edit the Embedded Macro.
| | 04:19 | When I went to the Wizard I told that
I wanted to open the form, so I created
| | 04:22 | this Openform event.
| | 04:23 | I also told it what form I wanted
to open, so it attached that here.
| | 04:26 | But it didn't ask me some other options,
for instance, Window Mode or Data Mode
| | 04:30 | or if I wanted to apply some filtering.
| | 04:31 | So these are additional options that
I can choose to add into this Openform
| | 04:34 | Button, for instance, Window Mode,
they don't change that to dialog, in which
| | 04:38 | case the Employee Directory
would open up as a pop-up menu.
| | 04:42 | Let's go ahead and close this
and see what that looks like.
| | 04:44 | I'll return to Form View, and now I'll
click on the Button, and the Employee
| | 04:47 | Directory opens as a pop-up menu instead.
| | 04:49 | So buttons provide the main interactive
interface through the Access database.
| | 04:53 | They leverage Macros or Visual Basic
code to perform the actions, and they're
| | 04:56 | instantly recognizable for your end users.
| | 04:58 | So when somebody sees a button on a
form, they know that they can click on it
| | 05:01 | and that will affect the database.
| | Collapse this transcript |
| Linking to external content| 00:01 | The hyperlink and web browser
controls allow your forms to reach out beyond
| | 00:04 | the local database and into your
computer right onto the Web, you can use the
| | 00:08 | Hyperlink control to open a website or
help embed online content right there
| | 00:12 | inside of your database, let's take a
look at how we can use Hyperlinks and
| | 00:15 | web browser controls.
| | 00:16 | In this Controls Forms I'll
right-click and go to Design View.
| | 00:18 | The Hyperlink control is this one
right here with the global and the chain.
| | 00:21 | I'll click on it and a Hyperlink wizard opens.
| | 00:24 | I can choose to link to items within
my computer, for instance, from this
| | 00:27 | current folder I can scroll through
and make a link to this TwoTreesCatalog
| | 00:31 | file, which is a Word document.
| | 00:33 | I can say OK, and Access places a link
to that document right inside of my form.
| | 00:37 | If I go to Form view and click on that
link, Access is going to warn me about a
| | 00:40 | potential security notice, because it
doesn't know exactly what file this is,
| | 00:44 | but let me go ahead and say Yes, and
that we'll open up Word with the document
| | 00:47 | inside, so I can make any changes
and save it right back to my computer.
| | 00:49 | Let's go ahead and close Word that will
take us back to Access and I'll switch
| | 00:53 | back to Design View, let's
add another hyperlink control.
| | 00:56 | I'll click on that and the wizards starts again.
| | 00:58 | I can choose to bring in web pages or
recent files that I might have used.
| | 01:01 | I can make a link to
other objects in the database.
| | 01:04 | So for instance, I can link to a Form and
it'll work the exact same way as is link to a file.
| | 01:09 | I could also link to E-Mail addresses,
so it can provide an easy link to e-mail
| | 01:12 | the database developer, or I can go
down here to Hyperlink Builder and connect
| | 01:15 | directly to a website.
| | 01:16 | So for instance, if I wanted to
link to the Two Trees Olive Oil company
| | 01:19 | website, I can just type URL
right there in the address bar,
| | 01:22 | http://twotreesoliveoil.com and press Enter.
| | 01:27 | Access adds that link to my form as well;
| | 01:30 | I'll switch to Form and try it out.
| | 01:31 | It opens up my web browser and there is
the Two Trees Olive Oil website, right
| | 01:35 | there easily access within my form.
| | 01:37 | Okay, let's close this down;
| | 01:38 | let's take a look at the
web browser control as well.
| | 01:40 | I'll switch my view back to Design View.
| | 01:42 | The web browser control is the
one right next to the Hyperlink;
| | 01:45 | it's this one here at the
box and the globe inside.
| | 01:47 | The web browser control is new in
Access 2010 and I really think this is cool;
| | 01:51 | I'm going to first highlight
these two and get rid of them.
| | 01:53 | Now I will add a web browser control
right inside my form and I will drag out of
| | 01:57 | box when I like over the button the
Insert Hyperlink wizard starts, it asks for
| | 02:01 | an Address, let's say I want to
add some content from Wikipedia.
| | 02:04 | First, let me go find it on the web,
I'll go out to my web browser and the
| | 02:07 | Wikipedia article I want to link to is here,
it's this Wikipedia article on Olive Oil.
| | 02:11 | Let me just highlight this address and
I'll press Ctrl+C on my keyboard to copy
| | 02:15 | it to the pasteboard, I'll
come back down to Access.
| | 02:17 | Now in the Address bar I'll press Ctrl+V
to paste it in and I'll press Enter,
| | 02:21 | that adds a web browser control
link to the Wikipedia article.
| | 02:25 | In the Format tab, I can
change its size a little bit.
| | 02:27 | Most web pages work pretty well with a
website size of 960 pixels, so let me go
| | 02:32 | ahead and just type that in.
| | 02:32 | 960px to specify pixels and I will
press Enter and Access will resize that
| | 02:38 | object, let's go ahead and change to
our View menu and I'll view our form.
| | 02:41 | And after just a moment, the Wikipedia
article loads right there inside of my
| | 02:45 | form, I can scroll through
and read the entire article.
| | 02:48 | So it makes it really easy to bring an
external content for reference, let's
| | 02:52 | take another look at this web browser
control and something that I think is kind of cool.
| | 02:55 | I can switch my view into Design View
here and I'm going to delete the one that
| | 02:58 | I put with Wikipedia, we'll add one more
here and I'll just click to add that in there.
| | 03:02 | Now let's say I wanted to add video
content from YouTube, let me go back up to
| | 03:05 | my web browser here, and if I switch to
this page here, I've currently got the
| | 03:09 | lynda.com YouTube channel up.
| | 03:11 | Let's say I want to embed the content
of this video right inside of my form.
| | 03:15 | On YouTube, I'll click this Share link
here and then the Embed code, this'll
| | 03:19 | give the code that I need.
| | 03:20 | The piece that I want is his web
source right here, the http portion.
| | 03:25 | Now fortunately, I can't just select
that portion, so I'm going to Ctrl+C to
| | 03:29 | copy the whole block of text.
| | 03:30 | I'll go back into Access, and I'll paste
the whole thing here in the address bar
| | 03:33 | and I'll delete everything after the URL,
which ends right there with the first
| | 03:37 | quotation mark that I can see, I'll
get rid of that and then I'll scroll this
| | 03:41 | back to the beginning, and I'll get rid
of everything before the http, including
| | 03:44 | that first parenthesis.
| | 03:45 | So that leaves me just the URL for the
video, I'll press Enter and that will
| | 03:49 | apply down to the box.
| | 03:50 | Now let's switch into the Form view and
I can see the content of the video right
| | 03:54 | inside of my form, I can
even play it.
| | 03:59 | So bringing in content from the web can
be a great way to add dynamic content to
| | 04:03 | your database without having
to store anything inside of it.
| | 04:05 | Training or marketing documents,
customer testimonial videos, specs on products
| | 04:09 | and all kinds of information can be
made available right within your database,
| | 04:13 | so that's easy to get to when you need it.
| | Collapse this transcript |
| Entering and selecting data| 00:00 | When it comes to displaying data in your
forms there are three objects which are
| | 00:04 | used most often, they are the Text
Box, the Combo Box and a List Box.
| | 00:09 | For this exercise we're going to create
a new form based off this Reviews Table.
| | 00:12 | Let me go ahead and open up the Reviews
Table and you can see that this a place
| | 00:16 | where you can collect information
about product reviews from our customer.
| | 00:18 | I've got a review identification
number, product ID that's being reviewed,
| | 00:22 | the rating given, any comments and whether we
can use this review publicly on our website.
| | 00:27 | We're going to build a form that'll
add records to this table, let's go ahead
| | 00:31 | and close this table, let's go to the
Create tab and we're going to Create a new
| | 00:35 | Form and Design view.
| | 00:36 | Now the first thing I want to do when
creating a new form is connect it to the
| | 00:40 | table where the data is going to go.
| | 00:41 | In the Property sheet for the Form,
I'll go to the Data tab and I can set this
| | 00:45 | Record Source here by using the drop
down menu to the table I want to connect it
| | 00:49 | to, in this case Table Reviews.
| | 00:51 | Now I can start adding the fields
that'll interact with the table, there is a
| | 00:55 | couple of way I can do that if I go to
Add Existing Fields here, it'll show me
| | 00:58 | all the fields within that table.
| | 01:00 | Go ahead and grab Comments and drag-and-drop
it over, this adds a large text box to my form;
| | 01:05 | I am going to move this over a little bit.
| | 01:06 | Now we can do this in other way too,
we can add a text box manually and
| | 01:10 | connect it to our table.
| | 01:11 | The text box control is up here
on the ribbon it's got the ab icon.
| | 01:15 | If I click that and add one to
my form, it's currently unbound;
| | 01:18 | it's not connected to the table.
| | 01:20 | I can bind it by going back to the
Property Sheet and the Control Source for
| | 01:23 | this text box now, I can use the drop-
down menu, and it'll ask me what field in
| | 01:27 | the table to connect to.
| | 01:28 | For this one I'm going to connect to
the ProductID, let's go ahead and view
| | 01:31 | this form in Form view.
| | 01:33 | Now I can scroll through the records
that are already in my Reviews Table, so
| | 01:36 | for instance, if I am on record number
1 which is for this particular oil and
| | 01:40 | this is the comment.
| | 01:41 | I can scroll through them;
| | 01:42 | there is review 2, review 3, review 4,
and if I go to review 5, it's blank;
| | 01:47 | this will be a new review,
| | 01:49 | So if I wanted to add a new review to
this Reviews Table, I will just type in
| | 01:52 | the values here, but the problem with the
text box is it doesn't give you much guidance.
| | 01:56 | So for instance, I'm not really sure
which ProductID, I know the name of it, but
| | 02:00 | I don't know what its ID numbers is.
| | 02:02 | So the text isn't giving me any help.
| | 02:04 | We can make this a little bit better
by using a List Box or a Combo Box to
| | 02:07 | provide the values that I
can just choose from the list.
| | 02:09 | Let's switch back to Design View.
| | 02:11 | I'll go to the View menu and choose Design View,
let's go ahead and get rid of this text box.
| | 02:16 | I'll select both it and the label and
I'll press Delete, and a List Box and the
| | 02:19 | Combo Box function almost identically.
| | 02:22 | This right here is the Combo Box.
| | 02:24 | The List Box, if I scroll down, is right here.
| | 02:27 | The main difference between a Combo
Box and a List Box is that a Combo Box
| | 02:30 | displays one line and you can
expand the box to show the list.
| | 02:33 | The List Box is always expanded and
has a scrolling bar on the right side to
| | 02:37 | move up and down, let's go
ahead and add a Combo Box.
| | 02:39 | Let's scroll back up here and choose the
Combo Box icon, and I'll add it into my form.
| | 02:43 | When I add a Combo Box the wizard
starts and it asks me a couple of questions.
| | 02:48 | The first screen asks where the value is
going to come from that will populate the list.
| | 02:52 | We can choose to get them from
another table or query, we can type in a
| | 02:56 | list manually, or we can use a Combo
Box actually jump to a record based
| | 02:59 | off something I select.
| | 03:01 | I am going to have the list
populated from a table or query.
| | 03:03 | Let's go ahead and say Next, the
next screen is going to ask me, well,
| | 03:06 | which table or query?
| | 03:07 | Now let's pause here for a second.
| | 03:09 | What I want to do is enter in
product reviews into my Reviews Table.
| | 03:12 | You might think, okay, so we need to
connect this to my reviews table, right?
| | 03:15 | But if I do that it's going to generate
a list of products that are already in
| | 03:19 | the Reviews Table in this case I only
have four reviews, so I only should be
| | 03:22 | able to choose from those four products.
| | 03:24 | In this case I actually want a full
list of all of our products to choose
| | 03:27 | from, so I'm actually going to pull
this from the Table Products, let's go
| | 03:30 | ahead and say Next.
| | 03:31 | Now which field from the
products table do I want to use?
| | 03:33 | I don't want to use the ID number, that
was the problem we had in the first place.
| | 03:37 | I want to use the Product Name, let's
go ahead and select that to move that to
| | 03:40 | the selected field section, and I'll press Next.
| | 03:43 | We can sort this ascending or descending,
I'm just going to accept the default
| | 03:46 | and say Next and here is the list
that's going to show up in the Combo Box,
| | 03:49 | let's go ahead and say Next.
| | 03:51 | The last window says, do we want to
remember the values for later use or do I
| | 03:54 | want to store this value in a field?
| | 03:56 | We want to store the value
back into the Reviews Table here.
| | 03:58 | So we're going to store the values in
the field and the field that I want to
| | 04:02 | store it in is ProductID, go ahead
and say Next, and I'll set the default
| | 04:05 | name and say Finish.
| | 04:06 | Okay, let's make this a little bit wider
and I'm going to move it over to a line
| | 04:10 | with my Comments box.
| | 04:11 | Now let's go ahead and take
a look at in the Form view.
| | 04:14 | Now I've got a drop down list where I
can select the oil from, so again, I'll go
| | 04:17 | to record number 5, which is the new
record, and now I can select from the list
| | 04:21 | the oil that I want to review.
| | 04:22 | Let's say the Light Oil and the 8oz
size, I can type in my comment, this is
| | 04:26 | a good size product.
| | 04:28 | I'll press Enter and I'll move to a
different record just to finalize those
| | 04:31 | changes or I can press this Pencil icon
over here to commit the change to the table.
| | 04:36 | Now if I review my Table Reviews,
I'll double click on it to open it.
| | 04:39 | I'll see that new review right down here,
I'll review the Light Oil and the 8oz
| | 04:42 | size and this is the ProductID for
that and this is the comment that I left.
| | 04:46 | We're going to take a look at how
we can use the check box and a rating
| | 04:49 | system in the next movie.
| | 04:50 | So that's how you would use a Combo Box.
| | 04:52 | To populate a list of values that user
can select from, it provides additional
| | 04:56 | context over just a standard text box
that allows them to type in whenever
| | 05:00 | they'd like, without any sort
of instruction and what to do.
| | 05:03 | The Combo Box and List Box are much
friendlier controls for your end users.
| | Collapse this transcript |
| Controlling input with option groups| 00:01 | Yes/No Data Type fields have
several different ways that the user can
| | 00:04 | interact with them.
| | 00:04 | The most common is with a Checkbox
an Option Button or a Toggle Button.
| | 00:09 | We can also group several of these
elements together in what is called an option
| | 00:12 | group to deal with more complex data entry.
| | 00:14 | We are going to continue working with a
simple form to capture product reviews
| | 00:17 | that we created in the last movie.
| | 00:19 | I will double-click to open it and we
will see that we can select an oil name
| | 00:22 | from this drop-down menu and entering
comments here that we want to add to our Review.
| | 00:26 | Let me go ahead and open up this Reviews table.
| | 00:28 | I will double-click on it to open and
we will see that the Reviews table also
| | 00:31 | has two fields, one for Rating, here, which
is the numerical value and one for Public.
| | 00:35 | A Checkbox or Yes/No data type that says
whether or not we can use the review on our website.
| | 00:39 | I am going to go ahead and close this
table down and we will change our format
| | 00:42 | to Design View, here.
| | 00:43 | I am going to go up to the controls up
at the top and I am going to use the More
| | 00:46 | button to open that Controls Panel.
| | 00:48 | Now the Checkbox Control is this one right here.
| | 00:50 | This one that looks like an eyeball is
our Option button, and over here on the
| | 00:53 | left, is our Toggle button.
| | 00:54 | It's the one with the
rectangle and two different states.
| | 00:56 | We are going to add each of these to
our form, and then connect them to the
| | 00:59 | Yes/No Public box on the Reviews form.
| | 01:02 | We will click on the checkbox and add one in.
| | 01:04 | I will scroll down and add this Option
button here, and I will add one to my
| | 01:08 | form and I will scroll down and add a Toggle
button, and I will put that in my form as well.
| | 01:13 | When you first add these in, they are unbound.
| | 01:16 | They're not connected to
this Reviews table in any way.
| | 01:18 | I do need to identify which field this control.
| | 01:20 | I can do that in the Data
tab of the Property Sheet.
| | 01:23 | If it's not open, you can toggle the
Proper Sheet with this button here or
| | 01:25 | press Alt+Enter or F4.
| | 01:27 | The Control Source field, is this the
drop-down menu where I can select the
| | 01:31 | field that I want to connect it to.
| | 01:32 | In this case, I'm going to connect it to Public.
| | 01:35 | And I'm going to connect with radio
button and the checkbox to Public as well.
| | 01:38 | Make sure you click on the
radio button, not the label;
| | 01:41 | we will go to Control Source and choose Public.
| | 01:45 | Now I will click on the
checkbox, Control Source and Public.
| | 01:48 | So now all of three of these elements
are connected to the exact same field.
| | 01:52 | Let's go and take a look at our form.
| | 01:53 | I will change to Form View, and I can
see that this first review of 5, the
| | 01:57 | checkbox is turned on.
| | 01:58 | All of these are active.
| | 02:00 | As I scroll through my records,
record number 2, it's turned off.
| | 02:03 | If I were to toggle one of them,
they'll change state at the same time.
| | 02:06 | That's because they are connected to
all, the exact same field in this table.
| | 02:09 | Okay, let's go back in the Design View.
| | 02:12 | I am going to go ahead and get rid of
two of these, and the checkbox is the most
| | 02:16 | common option that people are familiar
with, so I am actually going to get rid
| | 02:19 | of these other two interface elements.
| | 02:20 | We are just going to stick with the checkbox.
| | 02:21 | I'm going to change the label for this
checkbox to, May we use your comments on the website.
| | 02:26 | I will double-click to edit it and I
will move this up below the comments box.
| | 02:32 | Now I want to add a control, so
that they can enter any product rating.
| | 02:36 | I could just have type in a value, but
some people will type in five stars or
| | 02:40 | three or some other random numbers, so I
won't be able to control this a little bit more.
| | 02:44 | What I can do is use
what's called an Option Group.
| | 02:46 | Up here in the Control section, the
Option Group is this one here with the white
| | 02:50 | rectangle on the XYZ across the top.
| | 02:51 | I will click that on, and I will
just add an Option Group down here.
| | 02:55 | When I do that, the wizard starts.
| | 02:56 | The first thing that it wants
to know is what are the labels?
| | 02:59 | These are going to be what the end user sees.
| | 03:01 | So I want to make sure that they are clear.
| | 03:02 | I'm going to put in the five
selections that I want them to choose from.
| | 03:05 | The first one is going to be Excellent
followed by Good then Average, then Below
| | 03:09 | Average and then Poor.
| | 03:11 | Once I get those five labels typed
in, I will go ahead and say Next.
| | 03:14 | The next screen asks if I
want to make a default choice.
| | 03:16 | Now I don't want to skew my results at
all, so I am going to say no, I don't
| | 03:19 | want to default choice.
| | 03:20 | Go ahead and say Next.
| | 03:21 | And then it wants to know what values
do I want to associate with the label?
| | 03:24 | So again, the users are going
to see what it says on the label.
| | 03:27 | The value is what's going
to be saved in my data table.
| | 03:29 | I am actually going to want to reverse
these numbers, Excellent is going to be
| | 03:32 | a 5 rating, Good will be a 4 rating,
Average is 3, Below Average is 2 and Poor is 1.
| | 03:38 | Go ahead and say Next.
| | 03:39 | The last screen is going to ask, do I
want to save the value for later use or
| | 03:42 | store the value in a field.
| | 03:43 | So we are connecting this to our
Reviews table, so I want to store this in a
| | 03:46 | field and I'm going to
connect it to my Ratings field.
| | 03:49 | Go ahead and say Next.
| | 03:50 | We have a choice of how we want this to display;
| | 03:52 | we can either choose an Option
button, a Checkbox or a Toggle button.
| | 03:56 | Now in standard User Interface
practices, checkboxes typically mean that
| | 03:59 | the user can select multiple options or as
an Option button, they can only check one.
| | 04:03 | So in this instance, I would
probably avoid the checkbox.
| | 04:06 | I would either choose the
Option button, or the Toggle button.
| | 04:08 | In this case, I'm going to
choose the Option button.
| | 04:10 | We can also add a style to
the line around the edge here.
| | 04:12 | You can either leave it Etched, Flat,
Raised, Sunken or Shadowed, let's go
| | 04:18 | ahead and say Next.
| | 04:19 | Finally, it's going to ask
what do I want to name this.
| | 04:21 | This is the title that's going to appear here.
| | 04:23 | Instead of Frame10, I'm going
to choose Rating and say Finish.
| | 04:26 | That adds the Option button group to my form.
| | 04:28 | Let's go ahead and
rearrange the elements a little bit.
| | 04:30 | I can highlight everything up here.
| | 04:32 | And select them this way and I'll
Shift+Click just like those labels.
| | 04:38 | I'm going to move that up to the top,
then I will move all of the rating stuff
| | 04:41 | over to the right side.
| | 04:42 | Finally, I am going to make my form a little
bit shorter, so I will scroll at the bottom.
| | 04:45 | I will drag this back up to the top
and I am going to change the labels here,
| | 04:50 | instead of Combo3, this is going to be Product.
| | 04:53 | Now I can move these over to align
them up a little bit, I will use these
| | 04:56 | handles in the top left, drag them
over and we will drop them there.
| | 05:00 | Finally, I'm going to right align the text,
so that all the labels are aligned together.
| | 05:04 | We will go in the Format
tab, and say right aligned.
| | 05:06 | Okay, let's check out our form, we
will go back to Home tab, switch to Form
| | 05:09 | View, and there is our finished form.
| | 05:11 | I can see that the first
review is for this particular oil.
| | 05:13 | This is the comment, it's public
and they gave it a rating of Good.
| | 05:16 | I can scroll through the
different reviews or I can add my own.
| | 05:19 | We will go to the next blank one, choose a
product to review, about the Mission Oil.
| | 05:24 | They can use it on the website, we will give it
a good rating and we can type in any comments.
| | 05:28 | We will click this pencil icon to
finish the review and we can double-check the
| | 05:32 | table to make sure that it got entered.
| | 05:34 | And it looks like all the values came in.
| | 05:35 | So the Option Group gave us a good way
to translate what we want to store in
| | 05:39 | the database, this numerical rating,
to a different sort of tag that's
| | 05:42 | presentable to our end users.
| | 05:43 | So we were able to use a Checkbox
and Option button or Toggle button, to
| | 05:48 | interface with the Yes/No Public field here.
| | 05:50 | And we used a Control group to
translate the numerical value that we wanted to
| | 05:54 | store on our database, to something
that's a little more user-friendly to our
| | 05:56 | end-users, the text tag,
of what their rating is.
| | Collapse this transcript |
| Attaching documents| 00:01 | There is plenty of debate on whether
you should actually attach files such as
| | 00:04 | images or Word documents to
records within your database.
| | 00:08 | Some people claim that it leads
to database bloat and slowdowns.
| | 00:11 | The Hyperlink option is definitely a
good way to include links to additional
| | 00:14 | resources without actually embedding
everything within the Access Database file itself.
| | 00:19 | But when you do have tables such as
our Employees table here if I right click
| | 00:22 | on that and go into Design View that
have the Attachment and OLE objects field,
| | 00:27 | let's scroll down here, I have got fields here,
one of Attachment and one of the OLE Object.
| | 00:32 | If you do use these two data types you
will need to know how to actually make
| | 00:35 | use of them in your forms.
| | 00:36 | Now in this table we are using
Attachment data type to store photos of our
| | 00:40 | employees and I've also got this other
field here called OLE that's using the
| | 00:44 | OLE object attachment.
| | 00:45 | Now the OLE object is kind of an older
and somewhat more obsolete data type and
| | 00:50 | it's basically just kept
around for backwards compatibility.
| | 00:53 | Now if you are not already using it,
the OLE object data type is kind of an
| | 00:57 | older and somewhat obsolete format.
| | 01:00 | If you are going to attach files to
your database, you should probably be using
| | 01:03 | the attachment data type.
| | 01:04 | But, since we have them both here,
let's take a look at how we can work
| | 01:07 | with them in our forms.
| | 01:08 | I will go ahead and close this table.
| | 01:09 | I am going to create a new form in Design
View by going to Create tab and Form Design.
| | 01:13 | Now I am going to attach those forms
to my Employees table by going to the
| | 01:17 | Data tab, the Record Source property,
I will use the drop-down menu here and
| | 01:20 | select tbl_Employees.
| | 01:22 | Then I will go to my Add Existing Fields panel,
I will click on the button here to open that.
| | 01:26 | This shows me all of the fields
that are in my Employees table.
| | 01:28 | I am going to go and double click on
the Photo field here and that will add it
| | 01:32 | to my form, and I will also double click on the
OLE field here to add that to my form as well.
| | 01:37 | Now these two objects are bound to my
table here, I can also add these fields
| | 01:40 | manually up here in the Control section,
let me go ahead and open this up here.
| | 01:44 | The Attachment field is this one
right here, it's the paperclip.
| | 01:47 | If I click on that and I click
down here in my form, it'll add an
| | 01:49 | unbound attachment.
| | 01:50 | At this point I can go into the Property
sheet and attach it to a field if I wanted to.
| | 01:54 | I could also go up here to the More
button and I actually have two different
| | 01:56 | ways that I can embed in OLE object.
| | 01:58 | I have got this one here
with a cactus and the XYZ;
| | 02:01 | this will add a bound
reference to something in my tables.
| | 02:04 | So for instance this OLE
field in my Employees table.
| | 02:06 | I can also have an unbound OLE object,
which will be this icon right here.
| | 02:10 | Let me go ahead and add one
of those to my form as well.
| | 02:12 | I will click on it and then
I'll click down here to edit.
| | 02:14 | When I do that, since it's unbound to a
data table, it wants to store the object
| | 02:18 | right here inside of the form.
| | 02:19 | Access displays a little pop-up window
that asks me, how do want to create a file.
| | 02:23 | I can either create a new file right
here inside of the form, or I can select
| | 02:27 | a file by selecting the Create From file,
and browse to something on my hard drive.
| | 02:30 | For instance, I will go to Browse
and I will go to my Exercise Folder on
| | 02:33 | the Desktop, Chapter 3.
| | 02:34 | I will scroll to the bottom here and
I'll add this to TwoTreesSalesPresentation,
| | 02:39 | which is a PowerPoint file.
| | 02:40 | I will go ahead and press OK and then OK again.
| | 02:43 | That will add that into my form, and you
will see an image of that pop right up.
| | 02:47 | Let's go ahead and take a look at
our form and we will see what these
| | 02:49 | objects look like there.
| | 02:50 | I will switch into Form view.
| | 02:52 | Now these objects are a little bit
haphazard on the screen here, but this one
| | 02:56 | right here, this is our photo
attachment that's pulling the photo directly from
| | 02:59 | our employees table.
| | 03:00 | If I double-click on it, I can bring
up the Attachments window or I can play
| | 03:03 | with the attachments that are attached to it.
| | 03:05 | Now the attachments field allows you
to attach multiple files, so right now I
| | 03:08 | just have this one image, but I can add
multiple documents here, I can say Add,
| | 03:13 | back into my Exercise Files, Chapter3
folder and I will scroll down and may be I
| | 03:17 | want to attach a catalog to this record.
| | 03:18 | I will click on the TwoTreesCatalog file,
which is a Word document, and say Open.
| | 03:23 | That adds it to the
Attachments panel, and I can say OK.
| | 03:25 | Now that I have attached this file, I
will press this Pencil button right up
| | 03:29 | here to finalize that into the table
and I will double check my tbl_Employees
| | 03:33 | here, I will double-click on it, and if
I scroll to the right, we will see that
| | 03:37 | I have now two attachments
with the specific employee.
| | 03:40 | The Word document is tied
directly to this employee;
| | 03:43 | let me go ahead and close this table.
| | 03:44 | We will go back to our form here.
| | 03:46 | Now I can also attach OLE objects,
it's kind of a similar operation,
| | 03:49 | double-click on the box Access tells
me that the OLE object is empty, and I
| | 03:53 | can't edit it until I add one, and
it tells me how to add one as well.
| | 03:56 | It says I need to right-click on the
field and then click Insert object.
| | 04:00 | We will go ahead and say OK.
| | 04:01 | This time I will right-click, go down
to Insert Object and Access brings up a
| | 04:05 | dialog box that we have seen already.
| | 04:06 | I can create a new object or
I can select one from a file.
| | 04:09 | Now creating a new object is kind of a
strange procedure here, for instance, I
| | 04:12 | will select this bitmap image
and I will go ahead and say OK.
| | 04:15 | The OLE object opens it inside of its
own window, I can do whatever I would like
| | 04:19 | in here, for instance, I
will just scribble something.
| | 04:21 | And I will go ahead and close it.
| | 04:22 | Access saves that into my OLE field.
| | 04:24 | If I wanted to get rid of that now, I
could right-click on it and say Delete.
| | 04:28 | If I right-click and say Insert object,
I can add different objects, I have a
| | 04:32 | whole list here, some of
these work better than others.
| | 04:34 | If I scroll down here for instance to
Microsoft Excel worksheet, I could say OK.
| | 04:38 | Access opens up a Microsoft Excel
editing session, the problem with it is, it's
| | 04:42 | right here, it's very tiny it's the
size of the box that I drew out on my form,
| | 04:46 | I can't really resize it, it looks
like I can, but it doesn't really resize.
| | 04:50 | Also it says I am still here inside of
Access, but now I've got windows here
| | 04:53 | that are from Excel, So it's kind
of a strange editing environment.
| | 04:57 | If I wanted to get out of here, I
will just click off this box anywhere.
| | 04:59 | I will click over here on the side and
that will take me back to my form, and
| | 05:03 | you can see I have got these really
tiny, little teeny Excel file kind of
| | 05:06 | embedded right in here.
| | 05:07 | Now I don't know of any way that
I could actually make use of that.
| | 05:10 | The attachment field is a much better
option, in fact, if you click on it once
| | 05:13 | you will get a little menu up here
that allows you to scroll through the
| | 05:16 | different attachments.
| | 05:17 | So for instance, for my first record
I have a photo, but I also attach this
| | 05:19 | Word document, so if I press the arrow over to
the right, I can get access to the Word document.
| | 05:23 | If I double click on it now, it brings
up this attachments window again, and I
| | 05:26 | can say TwoTreesCatalogDoc open, and
it will open up that file right in Word.
| | 05:31 | I can make whatever changes I want, for
instance, if I put Olive Oil and change
| | 05:36 | the font size, save my changes
here, and then close the window.
| | 05:43 | Now when I come back to Access, I say
OK, and Access recognizes that I made
| | 05:46 | changes to that file.
| | 05:48 | It asks me if I want to update the
version that's inside the database.
| | 05:50 | I go and said Yes and that will update
those changes into this Word document
| | 05:54 | that's stored inside of the database file.
| | 05:56 | The PowerPoint presentation that we
added to the OLE field works similarly, but
| | 06:00 | by default its ability to edit
it directly here is disabled.
| | 06:04 | If I go into the View menu and go into
Design View, I will take a look at the
| | 06:07 | Properties for this down here.
| | 06:08 | I will go to the Property sheet and
right down here, where it says Enabled, No,
| | 06:12 | that means that I can't
double click on it to edit it.
| | 06:14 | If I change its property to Yes, and
then switch back to Form view, now I can
| | 06:18 | double-click on it to open it up in
PowerPoint, I will press Esc to get out of that.
| | 06:22 | So the OLE data type really does kind
of duplicate functionality that we have
| | 06:25 | with the attachment data type, and
because OLE objects are kind of at this point
| | 06:30 | a legacy feature that's really only
kept around for backwards compatibility
| | 06:33 | reasons, I really wouldn't
recommend using it if you don't have to.
| | 06:36 | If you must save files into your
database, then use the newer attach file type
| | 06:40 | and will be placed on a form, images
display with the rest of your data, just
| | 06:43 | like this photo here, and you can
scroll through to get additional documents
| | 06:46 | that are attached in a single field.
| | Collapse this transcript |
| Attaching images| 00:01 | The selective use of images throughout
your database can accomplish several tasks.
| | 00:05 | They can help your documents conform to
company branding standards or reinforce
| | 00:08 | organizational pride.
| | 00:10 | They can also make your database
more attractive and give it a bit of
| | 00:12 | personality, may be even make a fun
or inspirational for your end-users.
| | 00:16 | Now there are four different ways that we can
add unbound images to your forms and reports.
| | 00:20 | And when I say unbound, I mean not
connected to a data source like our employees
| | 00:23 | photos were in the last movie.
| | 00:24 | I am going to go ahead and go to this
Controls Form, and I will right-click on
| | 00:27 | it to open it in Design View.
| | 00:30 | On the Controls part of the ribbon,
we will scroll down and we can find
| | 00:33 | the Image control here.
| | 00:34 | It's one of the mountain and the sun icon.
| | 00:36 | We can also insert images using
this button here on the right.
| | 00:39 | When we use this button, Access
creates a little gallery of images that we
| | 00:42 | have used previously.
| | 00:44 | So we would save the file here,
so I could easily get to it again.
| | 00:47 | Another way we can add an image is using the
Logo button which is this button right here.
| | 00:51 | When you use the Logo button, Access
automatically formats it and sits it in the
| | 00:55 | header as a logo, and makes a small
little icon and it puts it in the header of
| | 00:58 | your form or report.
| | 00:59 | The fourth way we can add an image, is
if you go to the Format tab, we have this
| | 01:02 | Background Image here.
| | 01:03 | When you use the Background Image
button, Access places an image into the
| | 01:06 | background of the form.
| | 01:08 | Let's go ahead and go back to the
Design tab and we will add an image using
| | 01:12 | the Image control here.
| | 01:13 | I will click on it once, and I get my
icon here that changes to a little cursor
| | 01:16 | with the image icon, and I will
drag out a box where I want my image.
| | 01:19 | I can always change to the box later.
| | 01:20 | Let's go ahead and go to our Desktop
in our Exercise Files, Chapter 3 and we
| | 01:24 | will choose this OlivesOnBranch.
| | 01:25 | Go ahead and say OK.
| | 01:28 | Access places the image inside of
the bounding box that I selected.
| | 01:31 | Let me go to the Format
tab of the Property Sheet.
| | 01:33 | And if your Property Sheet is not
opened, you can press F4, Alt+Enter
| | 01:36 | double-click on the Image control or
press the button on the Property Sheet.
| | 01:39 | Go to the Format tab for the Image.
| | 01:40 | Now there are a couple of properties
that I want to take a look at here.
| | 01:43 | The first one is this Size
Mode one and it's set to Zoom.
| | 01:45 | With the Size Mode set to Zoom,
Access makes the pictures as big as it can,
| | 01:49 | without distorting it
inside of the bounding box.
| | 01:51 | For instance, if I change the bounding
box to make it narrow, Access fits the
| | 01:55 | image inside of that new bounding box.
| | 01:56 | I will go ahead and make it bigger again.
| | 01:58 | If I change the Size Mode here from
Zoom to one of the other options, for
| | 02:02 | instance, Clip, Access puts the image
in at full size or a 100% resolution and
| | 02:07 | uses the bounding box as
sort of a clipping border.
| | 02:09 | I can change the alignment, right now
we are looking at the center of the image
| | 02:12 | here, Picture Alignment Center.
| | 02:14 | I can change where this image starts in
my bounding box, for instance, I can say
| | 02:17 | Top Left, and we will the top left of
the image, or Bottom Right and we will see
| | 02:21 | the bottom right image.
| | 02:22 | So I can move the image inside of
the bounding box with the Clip Mode.
| | 02:25 | I will go ahead and set this back to Center.
| | 02:26 | Here the Size Mode I want
to look at here is Stretch.
| | 02:30 | The Stretch Size Mode distorts the
image inside of the bounding box.
| | 02:33 | For instance, if I make it really narrow,
you can see that it's squashes the image down.
| | 02:36 | If I make it tall and skinny,
it squishes at the other way.
| | 02:39 | I will make it back out here
I will change it back to Zoom.
| | 02:43 | In the Property Sheet we can also see
that this image is Embedded right now.
| | 02:46 | We can see Picture Type, Embedded.
| | 02:47 | That means that when we added this
image, Access actually put a copy of it
| | 02:51 | inside of our database.
| | 02:52 | We can add links to images
instead of embedding everything.
| | 02:54 | And we can do that by going to the
Image Control here, clicking on it, drawing
| | 02:58 | a box that we wanted, when the Insert Picture
dialog box opens, go ahead and just say Cancel.
| | 03:03 | Access leaves the Image control on
the form, but it's not attached anything
| | 03:06 | here, if I look in the Property Sheet.
| | 03:08 | I can change the Picture Type now to
Linked, and the Picture Property here where
| | 03:12 | it says none, I will click the
Build button here to add the picture.
| | 03:16 | The Insert Picture dialog box opens
again, and I will choose the same one,
| | 03:19 | OlivesOnBranch and say OK.
| | 03:21 | Now I have got a second instance of this image.
| | 03:23 | You can see that the picture
source is linked to my hard drive.
| | 03:26 | Now the problem with this is if I move
my database or move the image, then you
| | 03:30 | can break the link to this image and
it would disappear from my database.
| | 03:33 | The embedded image here would follow
the database around but it adds file
| | 03:37 | size to the database.
| | 03:38 | So as a decision, you have to make on
how best to proceed when you are using
| | 03:41 | images in your database, whether you
want to attach them to the file or link to
| | 03:44 | them on your computer.
| | 03:45 | Let me go ahead and get rid of both of
these and we will take a look at another image.
| | 03:48 | I will click on the first one and
press delete and I will click on the second
| | 03:50 | one and press delete.
| | 03:51 | Let's add one more image here;
| | 03:53 | I am going to scroll down.
| | 03:53 | I'll click on Image Control and I
will just click once to add an image.
| | 03:57 | This time I am going to choose this tile one,
I will press OK, and it adds that into my form.
| | 04:02 | Now I can turn on Picture Tiling right
here in the Property Sheet, currently
| | 04:05 | it's to no and I can change that to Yes.
| | 04:07 | I will also change to this
Size Mode here from Zoom to Clip.
| | 04:11 | Now when I resize the bounding box,
Access tiles multiple copies of the image
| | 04:15 | across to fill up the space.
| | 04:17 | I can go ahead and position this image
here, for instance, in the top corner
| | 04:20 | here and stretch it across my form, to add
kind of an interesting border affect to the top.
| | 04:24 | Let's take a look at it in the Form View.
| | 04:25 | I will click on Form View
and I will see my border here.
| | 04:27 | Now let me show you how we compare
the image control with the attachment
| | 04:31 | feature, so we can stretch this
dynamically dependent on the size of the
| | 04:34 | computer that the end user is using.
| | 04:36 | If I go back to the View menu and
switch in to Design View, with my image
| | 04:39 | selected, I will go to the Arrange
tab, and the Anchoring here, I will
| | 04:43 | say Stretch Across Top.
| | 04:44 | This will dynamically stretch it to
whatever size monitor my end-users are using.
| | 04:47 | Okay we will go back to the Home tab,
switch to Form View and now it fills up
| | 04:51 | the entire space all the way across.
| | 04:53 | So these are just a few ways that you
can use graphics inside of your forms and
| | 04:57 | report designs, adding graphics when
done tastefully and especially when it aids
| | 05:01 | legibility, it can take a drab and
grey collection of buttons and tables and
| | 05:05 | turn it into an engaging
tool for your end-users.
| | Collapse this transcript |
| Understanding the subform control| 00:00 | Subform controls allow you to create
linked table like displays of information
| | 00:05 | that are related to a main topic.
| | 00:06 | We saw an example of a subform
briefly at the beginning of this course when
| | 00:10 | we used the form wizard to display a
list of all of the orders that included
| | 00:14 | a specific product.
| | 00:15 | Let's now duplicate that form
manually using the subform control.
| | 00:18 | I'm going to go to the Create tab or
the ribbon, and we will create a New Form
| | 00:21 | in Design View here.
| | 00:23 | The first thing that we want to do is link this
to the table that we want to be the main topic.
| | 00:27 | In this case, we want to have a form
that displays products at the top and then
| | 00:31 | down below, will list a table with all
of the orders that included that product.
| | 00:34 | I will go to my Add Existing Fields
here, I will Show All Tables and for my
| | 00:38 | Products table I will expand it here,
I will double-click on ProductID and
| | 00:42 | double-click on ProductName.
| | 00:44 | That'll add both of those fields to my form.
| | 00:46 | I will go ahead and make this wider,
because I know that my ProductNames are
| | 00:49 | fairly long, and I am going to
highlight both of these and I am going to push
| | 00:52 | them up to the top with the arrow keys, okay.
| | 00:53 | Let's go ahead and close the Field
List here and I'm going to scroll down to
| | 00:57 | the bottom of my form and make it a little
shorter, so that it all fits on the screen.
| | 01:01 | So Subform tool takes up a lot of real
estate, which makes it a great and ideal
| | 01:05 | opportunity to make use of a Tab Control.
| | 01:07 | The Tab Control if you remember is right
here, I will click on it in the Control
| | 01:10 | section of the Design tab and I
will drag out a Tab Control, like that.
| | 01:15 | Access places two tabs here.
| | 01:17 | In the first tab I'm going to place my
Subform, so I will click here to select
| | 01:20 | the Tab and then I will find my Subform,
I will use the down arrow or the more
| | 01:24 | button, and the Subform
button is this one right there.
| | 01:26 | I will click on Subform and come
down into my tab, well, it turns black.
| | 01:30 | The next thing I am going to do is go
inside of this tab, so I will drag out the
| | 01:33 | area that I want my Subform.
| | 01:34 | There, and the wizard starts.
| | 01:36 | And the first page of the wizard is going
to ask us where is our data coming from?
| | 01:38 | Do you want to use an existing Table and
Query or Do you want to use an existing form?
| | 01:42 | Let's go ahead and use an existing Table
and Query, we will go ahead and say Next.
| | 01:45 | We are going to build a table that
displays order information for the product
| | 01:49 | that we selected on the main part of the form.
| | 01:51 | I will switch my table here, to the
orders table and from there I will choose
| | 01:55 | the OrderID and the ProductID.
| | 01:57 | We also want to find out
who ordered this product?
| | 01:59 | So I will go back up here and I
will choose my Direct Customers table.
| | 02:02 | And I will choose First Name and Last Name.
| | 02:04 | Let's go ahead and say Next.
| | 02:05 | Access then asks how does that data in
the subform relate to the data in the
| | 02:09 | main part of the form up here?
| | 02:10 | We can either Choose from the
list or Define my own relationship.
| | 02:13 | Now because we've already have a
relationship that's been established in this
| | 02:16 | database, Access has already
correctly identified how they relate.
| | 02:20 | So I will just take that
and go ahead and say Next.
| | 02:22 | We can go ahead and name our Subform,
it's going to appear down here at the
| | 02:25 | Navigation Pane, or we
can just accept the default.
| | 02:27 | I will just leave as it is and say Finish.
| | 02:29 | Access adds in the subform into our tab,
now we can add another form into the
| | 02:32 | second tab here, for instance, I will
switch to this one that says Page4 and we
| | 02:36 | will go up and add another subform down here.
| | 02:37 | I will click the down arrow, the Subform
tool, and I will just click, to put in there.
| | 02:42 | This time instead of building a new
table, we will use an existing form.
| | 02:45 | I am going to choose the Reviews form.
| | 02:47 | This is the one that has product reviews.
| | 02:48 | I will go ahead and say Next, Access
again asks me, well how does this relate
| | 02:52 | to what I have selected up in the main section,
and it's correctly identified that relationship.
| | 02:56 | So I will go ahead and say Next again, and we
will just accept the default and say Finish.
| | 03:00 | Access places that in my tab as well.
| | 03:01 | Now let's go ahead and
rearrange these a little bit.
| | 03:03 | First of all I'm going to rename the tabs
up here, so I don't need labels inside here.
| | 03:07 | So I am going to go ahead and delete
the label and then I will select the form,
| | 03:10 | and I will resize it, so it
fits inside the tab better.
| | 03:13 | Next I will go to the other tab;
| | 03:14 | I will do the same thing.
| | 03:16 | I will delete the label and I
will move this into position.
| | 03:20 | Finally, I want to rename these tabs.
| | 03:21 | And I can do that in the Property Sheet.
| | 03:23 | I will select the tab, and the
Caption property here, on this first one I'm
| | 03:27 | going to put Orders.
| | 03:28 | I will press Enter and I
will go to the next tab.
| | 03:31 | And here the Caption will be
Reviews and I will press Enter again.
| | 03:34 | Okay, let's go back into our form
and see how those all are working.
| | 03:37 | We will switch it to Form View.
| | 03:38 | Right now I am on my first product of 90.
| | 03:40 | I can scroll to the different products
that we have, as I do so I get an updated
| | 03:45 | table here that shows me all of the
Orders that has been placed for that
| | 03:48 | specific product, and if I go to the
Reviews tab, I can see reviews that might
| | 03:52 | have been made on this product.
| | 03:53 | So now I have an easy way to review
lots of information about a specific
| | 03:57 | product, all in one simple form.
| | 03:59 | The subform object brings in yet
another way to view your content by displaying
| | 04:02 | information that is related to a single record.
| | 04:05 | Pairing the subform with the tab
control also allowed us to keep related
| | 04:08 | information together in one space
efficient location, so that it is easy to
| | 04:11 | get to.
| | Collapse this transcript |
| Adding charts| 00:00 | The Chart control can give insight into
your businesses metrics by presenting a
| | 00:04 | dynamic graphical view of your data
that changes as your database grows.
| | 00:08 | Unlike exporting data to Excel and
graphing there, creating your graphs
| | 00:12 | directly within Access maintains the
live and always updated link to your data
| | 00:17 | tables and queries.
| | 00:18 | Further, pairing a chart with a query
gives you all the flexibility of adding
| | 00:22 | query criteria to instantly
refine the chart's dataset.
| | 00:26 | In this movie we are going to create a
chart object and in the next movie we are
| | 00:29 | going to bring this chapter full circle
and incorporate some other Form controls
| | 00:33 | that will take user input to
alter the chart's parameters.
| | 00:36 | First let's create a query that collects
the data that we want to use in our chart.
| | 00:39 | I will go to the Create tab and
we will use the Query Design view.
| | 00:43 | This will be a pretty simple query we
are going to take a charge that takes
| | 00:46 | a look at orders over time, we will use
the Orders Table and say Add and then close.
| | 00:51 | From the Orders Table I want
information on the OrderID and OrderDate.
| | 00:55 | Let's go head and save this query now, I'll
close it and in the Save window and I say Yes.
| | 01:00 | I am going to name this the qry_
OrderDates, I will press OK and it saved my
| | 01:04 | query to my database.
| | 01:05 | Now let's make a chart off of that data,
I will go to the Create tab again and
| | 01:09 | this time we will do a new form in Design view.
| | 01:10 | I am going to go ahead and close the
Property Sheet and I am going to expand my
| | 01:14 | Form area wider a little bit, to about
the 10 inch mark and I am going to scroll
| | 01:18 | down and make it a little shorter.
| | 01:20 | Okay, so now the chart object is
this control right here, it's shown with
| | 01:25 | a three bars on it.
| | 01:26 | I will click on it and then I will
drag out a window where I want my chart,
| | 01:29 | the chart Wizard starts up and it
asks me which table or query do I want to
| | 01:32 | use as the data source.
| | 01:34 | I can choose from the tables that are
my database, or I can switch my View to
| | 01:37 | Queries here to select from the queries.
| | 01:39 | There is the query we just built,
so I will go ahead and say Next.
| | 01:42 | And then which fields do I want to
add from the query into my chart.
| | 01:45 | We will take both of them, so I will use
the double arrow to move everything.
| | 01:48 | Go ahead and say Next.
| | 01:49 | This screen is asking us what type of
chart we want to use, there are lots of
| | 01:52 | different options here and a lot of these
will work better for some data types than others.
| | 01:56 | I am going to choose this basic line chart here.
| | 01:58 | Go ahead and say Next and now we get a
layout how our fields lay on the chart.
| | 02:03 | Access tries to help us out a lot, but
usually it gets this part wrong here, so
| | 02:06 | I like to just take these fields and
drag them off of the drop zones and just
| | 02:10 | start with a fresh slate.
| | 02:11 | So I'll drag both of these back off,
that resets the chart area, and now I can
| | 02:14 | set it up the way I wanted instead
of the way Access thinks I want it.
| | 02:17 | I am going to take my OrderDates and
put them on the x-axis on the bottom.
| | 02:20 | I will just click OrderDate and
drag it to down here and drop it down.
| | 02:23 | When I do that Access automatically
groups them by month if I wanted a different
| | 02:27 | grouping level, I can double-
click on it and choose it from here.
| | 02:30 | So I could choose to Group by Year
or Quarter or even Hour if I want.
| | 02:33 | I am going to accept the
default Month and say OK.
| | 02:36 | The OrderID is going to go into the
Data section here on the top left.
| | 02:39 | When I drop it there, Access is going
to aggregate based off of the account.
| | 02:42 | Now if had numerical values, I
could do sum or average as well.
| | 02:46 | But since this isn't a numerical value,
it's just going to count them up, and if
| | 02:49 | I try and double-click on it to
change it, it's going to tell me that.
| | 02:51 | Go ahead and say OK,
let's go ahead and say Next.
| | 02:55 | We can now give our chart a title, and I
am going to name it Orders by Month, we
| | 02:59 | could either choose to display a legend
or not to display a legend, and I will
| | 03:02 | just leave it on, and we will say Finish.
| | 03:04 | That will put us back into our form,
it's had the chart object inserted here,
| | 03:08 | but this image that pops up actually
has nothing to do with the data we just
| | 03:11 | selected, it's just a placeholder
image at this point, so don't get confused
| | 03:14 | with these east and west and north.
| | 03:15 | Let's go into Form view and take a
look at the chart as it renders, we will
| | 03:18 | switch our view to Form view.
| | 03:20 | And occasionally in Access, the chart
doesn't pop up, if it doesn't show up
| | 03:23 | automatically you might have to
press the Refresh button up here.
| | 03:26 | So there's a basic chart object and at
this point it's pretty ugly, needs a lot
| | 03:29 | of work in order to get it to work properly.
| | 03:32 | If charting is something that is very
important to your organization, then I
| | 03:35 | would recommend looking into some of
the third-party charting add-ins that are
| | 03:38 | available for Access.
| | 03:39 | For now though, let's see what some of the
capabilities are with the built-in charts.
| | 03:43 | So let's go ahead and edit this chart
contents, I will double-click on it to
| | 03:46 | open it up in the chart editing session.
| | 03:48 | I am going to go ahead and close the datasheet,
because I don't need to see what the data is.
| | 03:52 | Now working with the charts is a little
bit wonky, it's an older object within
| | 03:55 | Access, and it's a feature that really
hasn't been updated in several versions
| | 03:59 | of the program, so it is going to be a
little bit tricky to start getting these
| | 04:02 | into the right size.
| | 04:03 | For instance, the fonts that I have here
are really large, compared to the chart
| | 04:06 | area up above, which makes the data illegible.
| | 04:09 | But if I double-click on the text here,
for instance, if I double-click on word
| | 04:12 | September and go to the Font section, I
will see that Access is trying to tell
| | 04:15 | me that this is an 8 point font.
| | 04:16 | The problem is it looks like its way
too large to actually be in 8 point font.
| | 04:20 | Let me go ahead and say OK.
| | 04:20 | The issue with the chart here is that
we are actually really zoomed into this
| | 04:24 | chart, and in order to zoom out, we
actually need to make the chart area bigger,
| | 04:28 | so that Access will try and
refit it back on to the screen.
| | 04:31 | Let me show you what I mean, if I drag
the width here, over here to the right,
| | 04:34 | Access resizes the chart and it updates
the size a little bit, let me drag it a
| | 04:38 | little wider again, and
it makes it smaller again.
| | 04:40 | Now this is going to take a little work
and you might have to redo your charts
| | 04:44 | a couple times until you get the hang
of this, like I said, it's kind of a
| | 04:47 | goofy user interface.
| | 04:48 | Maybe I will make this a little wider now.
| | 04:51 | And once I get the chart area looking a
little bit like I wanted, for instance,
| | 04:54 | now I could actually see the data, I
could double click on the text again and
| | 04:58 | now Access is telling me
that the font is a lot bigger.
| | 05:00 | At this point I can change it back to a
smaller size, maybe 8 now, and say OK and it updates.
| | 05:05 | Now that's looking a little too
small, so I will change it straight 10.
| | 05:09 | That's a better size, but now they
are angled, double click on it again and
| | 05:12 | this time we will go to Alignment and
it's telling me that Access is trying to
| | 05:15 | make it horizontal, let's force it to
angled and then double click on it again,
| | 05:19 | try horizontal again. There we go.
| | 05:21 | We finally got into a position that
looks good, let's go ahead and double click
| | 05:24 | here to change in size on the y-axis;
| | 05:26 | I will go to the Font and
change that to 10 as well, say OK.
| | 05:30 | So I am starting to get a chart that
looks a lot better, I am going to take
| | 05:33 | this legend here, and I will click on
it once, and I am going to drag it up,
| | 05:36 | get it out of the way.
| | 05:37 | And then if I click on the chart
area anywhere between two bars in this
| | 05:40 | white area, I will select the
background of the chart, and I can make it
| | 05:43 | bigger to fill up the space.
| | 05:44 | Finally let me change the color here,
these black bars in the background I think
| | 05:47 | are really distracting from the actual data.
| | 05:49 | I am going to double-click on the bar to
change its color, here the color is set
| | 05:53 | Automatic, and I am going to
choose this light Gray-25%.
| | 05:55 | I will say OK and there it goes.
| | 05:58 | It looks like it has lots more receded
into the background and it really makes
| | 06:01 | me focus on the data more.
| | 06:02 | Now before we start editing this chart,
now there is one more thing I want
| | 06:05 | to add to this chart.
| | 06:05 | If I go up to Chart menu I can choose
that I want to add a Trendline here, when
| | 06:09 | I click on that, the Add Trendline
window opens and it asks me what type of line
| | 06:12 | I would like to create.
| | 06:13 | Some regression types or trendlines
will fit some datasets better than others.
| | 06:17 | For now I am just going to choose
this linear regression type and say OK.
| | 06:21 | And Access adds the Trendline into my
chart and I can see that unfortunately we
| | 06:25 | have got a downward trend in our orders.
| | 06:27 | Let's go ahead and click off of our
chart anywhere in this white space out on
| | 06:29 | the outside, and then I will
return this back to our form.
| | 06:32 | Let's go ahead and take a look at
how this form is looking in Form View.
| | 06:34 | I will change my view here to Form
View and Access displays that chart, looks
| | 06:38 | like we have a little bit of graphic
issue here, we need to resize it a little bit.
| | 06:41 | So, one more time, into Design View and
I'll click on the chart area and I will
| | 06:46 | make it a little bit bigger,
that should fit better.
| | 06:48 | Okay, go back to Form
View and there is our chart.
| | 06:51 | The chart object allows us to better
visualize patterns that might be hidden in
| | 06:55 | our data and adding in trend analysis
and forecasting abilities can help you see
| | 06:59 | a bigger picture of where
you've been and where you're going.
| | 07:01 | Now in the next movie we are going
to take this chart one step further.
| | 07:04 | and incorporate some of the other
controls to help us make this chart even
| | 07:07 | more flexible.
| | Collapse this transcript |
| Linking controls| 00:00 | Now that we have seen all of the
controls that we have at our disposal when
| | 00:03 | creating forms and reports.
| | 00:05 | Let's bring this full circle and piece
a couple of them together to control the
| | 00:08 | chart that we created in last movie.
| | 00:10 | So in the last movie we ended up
with this chart here, I am going to
| | 00:12 | double-click on it to open it, and we
see that's a graph of Orders by Month.
| | 00:16 | And right now it can see all of the
data for my entire database, all the way
| | 00:19 | from January 05 through July 2010.
| | 00:22 | That data is coming from this query here;
| | 00:23 | I'll double-click on the query to run it.
| | 00:25 | This is the data that is
getting fed into the chart.
| | 00:27 | If I switch this query in to Design
View using the button up here, Design View,
| | 00:31 | I can see how the query was created.
| | 00:33 | Now what we can do in order to modify,
with the data that gets put into the
| | 00:36 | chart, is I can add a criteria here,
for instance, I can select a date range.
| | 00:40 | For instance, in this criteria under
OrderDate, if I type in >1/1/2009, Enter.
| | 00:47 | Now rerun the query, it
gives me a different dataset.
| | 00:50 | Now I only have 598 records instead of 2200.
| | 00:53 | If I go back to my chart and refresh it,
actually I need to save the query, so I
| | 00:58 | will go back to query, press Ctrl+S to
save it, then go to the chart and refresh
| | 01:02 | it, I will see a different chart.
| | 01:04 | It's only reporting the data now
based off of that new criteria.
| | 01:07 | But what we can do is create a form
that will update this criteria in the query
| | 01:10 | here, if I go back to Design View.
| | 01:12 | It will update this criteria
dynamically based off user selections that they
| | 01:17 | can type in themselves.
| | 01:18 | So let's go ahead and create that
form to capture the user intent.
| | 01:20 | I will go to the Create tab and we
will create a new Form in Design View.
| | 01:24 | We first need a place to capture data,
we could use a text box here and have
| | 01:28 | the end-user type in the values,
but that really doesn't give them much
| | 01:31 | direction, for instance, the end-user
is not going to know what data is
| | 01:35 | available, they are not going to know
what the beginning date is, what the end
| | 01:38 | date is for our database.
| | 01:39 | So Combo Box is a much better choice.
| | 01:41 | The Combo Box will allow them to
choose a date from a list of possible values
| | 01:45 | that we definitely know up here in the database.
| | 01:47 | The Combo Box is this one right here, I will
click on it once and I will add one into my form.
| | 01:52 | The Combo box Wizard starts up and I
am going to tell you upfront that we are
| | 01:55 | actually not going to use the
results of this wizard, but I want to go
| | 01:58 | through the wizard a little bit, so I can't
show you why this isn't going to work for us.
| | 02:01 | We will go ahead accept the first
selection here to get the values from another
| | 02:05 | table or query, we will say Next.
| | 02:07 | The dates in our database are
coming from the Orders table.
| | 02:09 | So I will select that here and say
Next, and here's the Date field, the
| | 02:13 | OrderDate, and I will add that over
to my selected fields, go ahead and say
| | 02:17 | Next, we will leave the default
sorting, go ahead and say Next.
| | 02:21 | Now we can see why this
isn't going to work for us.
| | 02:23 | In our Orders table we have multiple
orders on the same day, for instance I
| | 02:26 | have two orders on the 10th of January 2005,
and three orders on the 12th of January 2005.
| | 02:32 | If I leave it this way, Access is
going to display a list with all of these
| | 02:35 | duplicate values in it.
| | 02:36 | I just want each day to show up one time.
| | 02:38 | So let's go ahead and say Cancel to back
out of the wizard and not use any of this.
| | 02:42 | And now we will set the
data source for ourselves.
| | 02:44 | In the Property Sheet -- and I can
open that with the F4 key or Alt+Enter or
| | 02:47 | this button here, I'll
take a look at the data tab.
| | 02:50 | So the row source here is currently
blank, the row source is what we will
| | 02:54 | populate in the list in our Combo Box.
| | 02:56 | I can build a query manually using the
Build button here and I will click on
| | 03:00 | that and the Query session opens.
| | 03:02 | Now we could choose to pull data from our
Orders Table and I will Add and then Close.
| | 03:07 | And I will add our OrderDates here.
| | 03:09 | Now we will turn on our Totals row
to group to like values together.
| | 03:12 | So when I click on Totals up here in the
ribbon, that adds this Group By section
| | 03:15 | here, so all the dates that are
same will collapse into each other.
| | 03:18 | I will say Run, and now I get only each
date represented one time in my table.
| | 03:23 | So let's go ahead and close this, we
will save our changes and then I will take
| | 03:27 | a look at my form to make sure that I
am getting the results that I expect.
| | 03:29 | I will make it a little wider first and
then we will switch our view to Form View.
| | 03:33 | Now I have got a Combo Box here and
when I select the down arrow, I get all the
| | 03:36 | dates that are present in the orders table.
| | 03:38 | Okay, let's go back to Design View.
| | 03:39 | Now what I would like to do is create
a selection box where the end user is
| | 03:44 | going to type in a start date and an end
date for the graph that they would like to see.
| | 03:48 | So I am going to need two of these Combo Boxes.
| | 03:50 | What I could do is highlight both of
these the label and the combo box, I will
| | 03:54 | press Ctrl+C on my keyboard and
then Ctrl+V to paste, to copy.
| | 03:57 | Next let's go ahead and name these, I
am going to double click on the first
| | 04:00 | label here and type in
Start Date and then press Enter.
| | 04:04 | And I am going to double-click on the
second one and type in End Date, and press Enter.
| | 04:09 | I can move these over a little bit
I'll use the handle in the upper left hand
| | 04:13 | corner to drag this one over and I can
align both of them together to make sure
| | 04:17 | they're aligned by highlighting both of them
and going to the Arrange tab, Align to Right.
| | 04:23 | Now I want to right align the text to
make sure that's flush against this edge.
| | 04:26 | We will go to the Format tab
and press the right align button.
| | 04:29 | Next I need to name both of these
boxes here where the users are going to
| | 04:32 | make their selection.
| | 04:33 | Once they're named I can reference
those names over here in our query.
| | 04:37 | Let me click on the first box here
and I am going to go to the Other tab.
| | 04:40 | The name right now is Combo0, and I am
going to change that to startdate, all
| | 04:44 | one word, and press Enter.
| | 04:45 | Then I am going to go to second box here,
and I am going to changes its name to
| | 04:49 | enddate all one word and then press Enter.
| | 04:52 | Now my form is almost complete, I do
need to add a button to open up the chart.
| | 04:56 | I will switch to the Design tab;
| | 04:57 | I will click on my Button button,
and I will add a button down here.
| | 05:01 | The wizard will start, I am going to go
Form operations, open a form and say Next.
| | 05:06 | The form that I want to open;
| | 05:07 | that's the chart form, go ahead and say Next.
| | 05:09 | Where we want to display on the button?
| | 05:11 | I can either have the picture or a text,
instead of Open Form, it's a little bit
| | 05:15 | confusing for my end users, I think
View Graph is a better option, and we will
| | 05:18 | go ahead and say Next.
| | 05:19 | We well accept the default name and say
Finish and now I have got my button here
| | 05:23 | and I will just go ahead
and move it in a position.
| | 05:25 | Okay, let's switch in the Form View, I
can test my functionality, okay, I could
| | 05:29 | see all the dates there,
and I can see dates there.
| | 05:32 | Go ahead and save our form here, so I
will press Ctrl+S to save it, and I am
| | 05:36 | going to name this
GraphDateRange and then press OK.
| | 05:38 | Now we just need to hook to our
query into the selections that the
| | 05:41 | end-users will make here.
| | 05:42 | I will go to my query and in the
criteria I have currently got this >1/1/2009
| | 05:47 | typed in, I am going to highlight all
of that and press the Delete key and
| | 05:50 | then I will right-click to open up my
Expression builder, I will go to the Build option.
| | 05:54 | Now I just need to write in
the expression that I want.
| | 05:56 | And for my Date range, I can use the
between and syntax, so I will write
| | 05:59 | between, press the Spacebar, then I
can find the reference that's in my form.
| | 06:04 | I will drill into the Database folder here, by
using the plus button, I will go to Forms.
| | 06:08 | Right now I have got the form open,
so I can find it in Loaded Forms, the
| | 06:11 | GraphDateRange, and I will
double-click on startdate.
| | 06:14 | Next, I will type the word and,
another space, and I will double-click on
| | 06:17 | enddate, that will add the references
to the enddate field and the startdate
| | 06:21 | field that are on my GraphDateRange form.
| | 06:24 | Go ahead and say OK.
| | 06:25 | That adds the criteria down here into my query.
| | 06:28 | Now I can go ahead and save my query,
Ctrl+S to save it, and I will close it out.
| | 06:32 | Now I can go ahead and choose dates in
my form here on the GraphDateRange form.
| | 06:36 | For instance, I can choose, I will
scroll off through this list, may be March
| | 06:40 | 1st, 2007 and I will go to the enddate,
and I will scroll down, and may be I
| | 06:44 | will choose a date in 2008,
how about February 1st 2008?
| | 06:48 | Now when I press View Graph, it
switches the chart object, because it was
| | 06:52 | already open, I actually need to
refresh it, so I'll press Refresh to get a
| | 06:55 | new look at the data.
| | 06:56 | If the chart was closed, for instance,
I will close it now, and press View
| | 06:59 | Graph, it would show me
the new data automatically.
| | 07:01 | So for my end-users the chart will
closed, they choose their date range, let
| | 07:05 | me change it up a little, let me change to
December 1st 2009 and go ahead and say View Graph.
| | 07:10 | There we go;
| | 07:10 | I have got a new date range here.
| | 07:12 | So using a few simple objects, I was
able to create a couple of selection Combo
| | 07:17 | Boxes to choose options from, and
once I press the button I instantly get a
| | 07:21 | chart that meets my specifications.
| | 07:23 | I can change my selection as often as I
want and get an endless number of charts
| | 07:27 | that highlight exactly
what I want to take a look at.
| | 07:29 | Once you get comfortable with each of
the control object available to you with
| | 07:33 | in Access, it's going to be up to you
to be creative and figure out effective
| | 07:36 | ways to hook each of these pieces
together to create your database application.
| | Collapse this transcript |
|
|
4. Getting around the DatabaseCreating the main menu| 00:00 | The forms that we have looked at so
far have all been linked to a data source
| | 00:03 | and pulled records from our databases tables.
| | 00:05 | But forms don't always
have to interact with data;
| | 00:08 | they can be standalone elements that
provide interactivity for your end users.
| | 00:11 | In other words, a form can simply be a
blank area where you can group buttons
| | 00:15 | and capture user intent to
help them navigate the database.
| | 00:19 | When used appropriately, these types of
forms can completely remove any need for
| | 00:22 | your users to dig around in
the navigation pane over here.
| | 00:25 | You can control exactly what your
users have access to and often more
| | 00:28 | importantly what they don't have access to.
| | 00:30 | In this movie we are going to create a
main menu that will help our end users to
| | 00:33 | navigate the Two Trees database, we
will start with a fresh, clean, blank form
| | 00:38 | in Design View and add some
buttons that trigger macros.
| | 00:40 | Let's go ahead and go up to Create tab,
and we will create a new Form in Design View.
| | 00:44 | In the Property Sheet, if it's not
open, you can press this button here.
| | 00:47 | I will go to the Alt tab, we are
just going to go down the list here and
| | 00:50 | change some properties.
| | 00:52 | The Caption I am going to change
to Main Menu, with space in it.
| | 00:54 | I am going to tab down to Pop Up, I
am going to change that to yes, by
| | 00:57 | pressing the Y key.
| | 00:59 | I am going to go down here a few more.
| | 01:00 | Allow Datasheet View, I am going to
turn that to No, Pivot Table View to No,
| | 01:04 | Chart View to No and Layout View to No.
| | 01:07 | And again, I am just tabbing through these,
and I am pressing the N key to select No.
| | 01:11 | I want to add a picture into the
background, so I might go to the Picture
| | 01:14 | property and I am going to click
Build button here on the right.
| | 01:16 | We will go to my Exercise Folder here,
we are in the Chapter 4 section and I am
| | 01:21 | going to choose Ojai500x333, this is a
picture of the Ojai Valley in California
| | 01:26 | where our olives are grown.
| | 01:27 | I am going to go ahead and say OK.
| | 01:28 | And that will add that image
into the background of my form.
| | 01:31 | Let's go down some more in the Property
Sheet, I will go down to the Width Property.
| | 01:35 | I want to make my form exactly the
same width as my image, so I am going to
| | 01:38 | choose 500 pixel, we will
type in 500 px and press Enter.
| | 01:42 | The height of the form is a property of
the detail sections, so we will change
| | 01:45 | that in a moment, Auto Center, I am
going to turn that to Yes, so it appears in
| | 01:48 | the middle of my stream when it starts,
I am going to scroll down some more till
| | 01:52 | I get the record selectors.
| | 01:53 | I am going to change No, Navigation
buttons, I am going to change that to No.
| | 01:57 | And I look like I just missed one, I am
going to go back to Border style here,
| | 02:00 | instead of Sizable, I am going to
change that to Thin and that will make a
| | 02:03 | really thin border around the menu system.
| | 02:05 | I am going to go back down the scrollbars;
| | 02:07 | I will change it from Both to Neither,
so we don't get any scrollbars appearing.
| | 02:11 | Okay, so let's go ahead and change the
Detail section, so we can get the height.
| | 02:14 | I will click anywhere in the form
back here, the Heights property is there
| | 02:17 | for the detail section, and I what this to be
333 pixels or the same height as my picture.
| | 02:22 | I will press Enter to apply that.
| | 02:23 | And this point let's go ahead and save
our form here, I am going to press Ctrl+S
| | 02:26 | and we are going to type in Main Menu.
| | 02:28 | Now it took the name right here from the
name that I had saved as the Caption property.
| | 02:33 | So that's a fine name for my form,
I will just go ahead and say OK.
| | 02:37 | Next I am going to add a
couple of titles in here;
| | 02:38 | we'll use our Label control for that.
| | 02:40 | So I will go up and grab one label
control, click down here and I am going to
| | 02:43 | type Two Trees Olive Oil
Company and press Enter.
| | 02:45 | I will add a second label below it here,
and I will type in Main Menu, and press Enter.
| | 02:51 | Let's go ahead and select both of them,
I will press Shift and select the other
| | 02:54 | one, and we will change their formatting.
| | 02:56 | I will go to the Format
tab, I will make them bold.
| | 02:58 | I will change their color from that gray to
a dark gray, maybe this one here, 15% black.
| | 03:03 | I will make them right aligned, and I
will change their font, to let's say 60.
| | 03:08 | Now that I have changed the font, the
text is a little bit bigger than the
| | 03:11 | bounding box, so I can enlarge the
bounding boxes together at once by clicking
| | 03:15 | and dragging this left corner here,
so I'll click and drag that open.
| | 03:18 | Now I can arrange them together, I'll
go to the Arrange tab, Align to Right.
| | 03:24 | And I'll deselect them all, I will click
on the Main menu and I am going to make
| | 03:27 | that little bigger by going to
Format and I will change it's font to 20.
| | 03:31 | Now I will move them into position, use
the up arrow key to move that one, I'll
| | 03:35 | Shift+Click on this one and then
I will move them where I want to.
| | 03:37 | Let's move it up and over to the right.
| | 03:39 | Okay, those are in position now.
| | 03:41 | Let's go ahead and add a couple of buttons
to open up forms that are in our database.
| | 03:45 | We will go to Design tab, use our
button control with the Button button.
| | 03:48 | We will add a button down into our detail
section and the command button Wizard starts.
| | 03:53 | We are going to do Form Operations,
open a form, Next, the form I want to open
| | 03:58 | with this button is the Employee
Directory, so go ahead and say Next.
| | 04:01 | We are going to show all the
records, so go ahead and say Next.
| | 04:04 | And the text for my button, I want to
be more specific than just open a form,
| | 04:08 | because that doesn't have the end-user
which form is about open.
| | 04:11 | So I am going to say View Employee Directory.
| | 04:13 | Go ahead and say Next and we
will take default name and Finish.
| | 04:17 | So there's one button, let's add a
couple of more, we will grab another button,
| | 04:20 | click below, we go to Form Operations,
open a form, Next this time I want to
| | 04:25 | open up the Review form, so
click Reviews and say Next.
| | 04:28 | Will show all the records again, Next,
and I am going to choose text for this
| | 04:31 | one again, and once again, instead of
open form, I am going to say Enter Product
| | 04:35 | Review, go ahead and say Next and Finish.
| | 04:38 | Let's add a third button down in
the bottom, one more button here;
| | 04:41 | we will click on the bottom, right?
| | 04:42 | This time I am going to choose the
Application category and the only action here
| | 04:46 | is Quit Application and that will close Access.
| | 04:48 | Go ahead and say Next.
| | 04:49 | The text I want to say, instead of Quit App, I
am going to say Exit Database. And say Finish.
| | 04:54 | Now we can color these a little bit by
going to the Format tab, instead of this
| | 04:57 | blue button for the exit, I am going to
change that with the quick styles to a
| | 05:01 | red, to really call attention
to it, it's like a stop button.
| | 05:05 | The other two, I will highlight both
of them at the same time by pressing the
| | 05:07 | Shift key, and I will change their style
to a green color, maybe this green one here.
| | 05:12 | Now we can arrange them to
get them in the position.
| | 05:13 | I will click the Arrange tab, the Size
and Space button and then To Widest will
| | 05:19 | make them the same width.
| | 05:20 | Then I will align them together by
going to the Align tab and saying Left.
| | 05:23 | Then I can move them together, move
them up a little and align both of them and
| | 05:28 | I know they are aligned and
they are all the same width.
| | 05:30 | So I will just move these into
position with the arrow keys.
| | 05:33 | Okay, let's go ahead and
test out our functionality now.
| | 05:34 | I will go to the Home tab and click the form.
| | 05:37 | Now I have got a free-floating Main
Menu system here with couple of buttons on
| | 05:41 | it, let's test the buttons and see if they work.
| | 05:43 | Now the Exit database one, that's in
the close Access, so stay away from that
| | 05:46 | for just a moment, but we will try
to View Employee Directory button.
| | 05:49 | When I click on that the Employee
Directory opens, but this Main Menu is
| | 05:53 | still in front of it.
| | 05:53 | So we need to actually engineer
these button a little bit differently.
| | 05:57 | So that they provide the
functionality that I'm expecting.
| | 05:59 | Let's go ahead and close the Employee
Directory and we will return to Design
| | 06:02 | View for this Main Menu.
| | 06:03 | But if I go to the Home tab now, you'll
notice that Design View is grayed out here.
| | 06:07 | Now that this is a free-floating window,
I have to actually right-click in the
| | 06:10 | window and say Design View from here.
| | 06:12 | Now if I click on the button and take a
look at its Properties under the Event
| | 06:15 | tab, the On Click event, this has
this embedded macro, and this is what the
| | 06:18 | Wizard created for us
when we created the button.
| | 06:21 | We can click the Build button over here on
the right to edit the macro that was created.
| | 06:24 | I will click on Build and we can see
that the macro right now just has one
| | 06:27 | step, it's opening a form, we will see
that the macro just has one step right
| | 06:30 | now, it's opening a form here and it's
this Employee Directory form, it's the
| | 06:34 | one that's opening.
| | 06:35 | I want to actually add another step here.
| | 06:37 | Let's go ahead and add a new action
right here, we are going to use the
| | 06:40 | Close Window action.
| | 06:41 | Now the Close Window action will
close the active window if I leave the
| | 06:45 | Arguments blank, or I can
specify a window that I want to close.
| | 06:49 | But if I leave it blank now, what's
going to happen is, Access is going to open
| | 06:52 | the Employee Directory and then close
the active window, which since we just
| | 06:56 | opened the Employee Directory, is
going to be the Employee Directory.
| | 06:59 | So I need to make sure that the Close
Window property is first, so I will move
| | 07:02 | it up in the list using this up arrow here.
| | 07:04 | So now when I click on the button, it
will close the active object which is
| | 07:07 | going to be the Main Menu window, and
then it will open the Employee Directory.
| | 07:10 | Go head and close this macro
and Yes I will save that change.
| | 07:14 | We will do the same thing to the other
button, I will click on it, click the
| | 07:17 | Build button over here on the right.
| | 07:18 | I will use the CloseWindow action
and I will move it up on the list.
| | 07:23 | Go ahead and close and save your
changes and now let's test it out, go back
| | 07:27 | to Form view, on my window, my free-floating
Main Menu here, I will click
| | 07:31 | View Employee Directory.
| | 07:32 | The first time I do that, since it's
going to close this Main Menu, it's going
| | 07:35 | to ask if I want to save my changes.
| | 07:36 | So Yes, we are going to make sure we save that.
| | 07:38 | And there is my Employee Directory.
| | 07:40 | Now the last thing I need in order to
make this a full circle, I need to provide
| | 07:43 | a way to get back from my
Employee Directory to the Main Menu.
| | 07:46 | So once again we will go to Design
View for the Employee Directory, we will
| | 07:49 | add a button to the header up here, this is
going to be Form Operations, open a form, Next.
| | 07:55 | This time we are going to
open the Main menu form, Next.
| | 07:57 | The text for that is going to be
Return to Main Menu, Next and Finish.
| | 08:03 | And again, we will need to
change that macro a little.
| | 08:05 | So the On Click event in the
Properties will go to the Build button here, we
| | 08:09 | will add Close window and we will
move that up to the top, close the window
| | 08:15 | here, and we'll save that macro.
| | 08:17 | I am going to change the
style of the button here;
| | 08:19 | I will go to Quick styles and
choose the same green, just so we are
| | 08:22 | consistent throughout this.
| | 08:24 | Go to the home tab, view our form
and I could test out that button.
| | 08:27 | It's going to ask me if I want to
save the Employee Directory changes, so
| | 08:30 | Yes, and there we go.
| | 08:31 | Now I can view the Employee Directory,
when I am done looking for the record I
| | 08:34 | am looking for, return to Main Menu,
and then back to where I started.
| | 08:37 | And as our database grows, as we add
additional functionality through other
| | 08:40 | forms and reports over here, we can
return to this Main Menu form and add
| | 08:44 | additional navigation buttons that
will get our users to where they need to
| | 08:47 | go and back.
| | Collapse this transcript |
| Adding a splash screen with startup options| 00:00 | A Splash Screen is a small window
that displays when a program is loading.
| | 00:04 | It stays on the screen for a few
seconds and then it disappears.
| | 00:07 | You've probably seen the Microsoft Access
Splash Screen every time you start the program.
| | 00:11 | Adding a Splash Screen to your
database and setting some startup options is a
| | 00:14 | great way to create an engaging
startup sequence to your database.
| | 00:17 | Now, whenever I can reuse content that
I've already created, I'm going to go
| | 00:21 | ahead and do that, because
it's going to save me time.
| | 00:22 | For instance, we've created those Main
Menu in the last movie, if I double-click
| | 00:26 | on it, I can open it.
| | 00:27 | I want to create a Splash Screen
now that reuses some of the similar
| | 00:30 | content from the Main Menu.
| | 00:31 | Let me close the Main Menu and
I'll right-click on it and say Copy.
| | 00:35 | Then I'll come down here in my
navigation pane, I'll right-click and say Paste.
| | 00:38 | Access is going to ask me for a
new name and I'm going to call this
| | 00:41 | Splash Screen and say OK.
| | 00:42 | Now, I've got a duplicate of my Main Menu
that I can use to create my Splash Screen.
| | 00:47 | I'll double click on it to open it and
then I'll right click on the form here
| | 00:50 | and say Design View.
| | 00:51 | Now, for my Splash Screen
I don't need any buttons.
| | 00:54 | So, I'm going to highlight all three
of these and then I will press Delete.
| | 00:56 | I'm also going to change the
image that's in the background.
| | 00:58 | So, I'm going to select my
form here between the two rulers.
| | 01:01 | On the Format tab I'm going to go to
this Picture here and I am going to
| | 01:04 | click the Build button.
| | 01:05 | I'm going to go back to my Desktop in
the Exercise Folder 4, and I'm going to
| | 01:09 | choose OliveSplash500x125. Say OK.
| | 01:13 | It adds the new image into the background.
| | 01:15 | And when I change the picture
alignment here, instead of Centre, I'm going to
| | 01:18 | change it to Top Left.
| | 01:19 | Then I'm going to go to adjust
the height in my Detail section.
| | 01:22 | So, click in the background back here, and the
Height, I'm going to change to about 2 inches.
| | 01:26 | Next, I'm going to use the two labels that I
have for my Main Menu for my Splash Screen.
| | 01:30 | So, I'm going to move those down.
| | 01:31 | We'll highlight both of them and I'll
use the arrow keys to move them down.
| | 01:35 | Next, I want to change the label of
Main Menu here to Access Database.
| | 01:40 | Some other things that you can add to a
Splash Screen, you might consider adding
| | 01:43 | a hyperlink to e-mail the database
designer if there are any problems or other
| | 01:47 | copyright information or versioning information.
| | 01:49 | So, there's our basic Splash Screen.
| | 01:51 | Let's go ahead and save it.
| | 01:52 | I'll press Ctrl+S and close it out.
| | 01:54 | Now, let's double click on it to open
it and there's one thing else that I
| | 01:57 | want to change here.
| | 01:58 | Right now it's got a small around it.
| | 02:00 | We can actually get rid of the
border entirely in the properties.
| | 02:03 | So, I'll right click on it, go into
Design View, in the Form properties here
| | 02:07 | I'll scroll down to Border and
I'll change it from Thin to None.
| | 02:11 | Once again, we'll go to Form View to
view it and that's looking much better.
| | 02:15 | And right-click on it and close the
object, and that's going to save the
| | 02:18 | design and we'll say Yes.
| | 02:20 | Now a Splash Screen ideally should
open up when the project opens and then
| | 02:23 | close automatically.
| | 02:25 | We can choose the Close
Automatically from the properties as well.
| | 02:28 | One more time we'll go to our Splash
Screen, I'll right-click on it in Design View.
| | 02:32 | On the Event tab for the properties, if I
scroll down, I'll find the On Timer event.
| | 02:37 | The On Timer event will
automatically fire after a specified interval.
| | 02:41 | Let's go ahead and build our On
Timer event using the Build button.
| | 02:44 | When I click the Build button Access
asks me which building method I want to use.
| | 02:48 | I can either use the Macro Builder, an
Expression Builder or if I know Visual
| | 02:51 | Basic code, I could use a Code Builder.
| | 02:53 | I'm going to use a Macro Builder and say OK.
| | 02:55 | Now, the first thing I want to
do is close the Splash Screen.
| | 02:58 | I'm going to say CloseWindow.
| | 03:01 | I can leave the Object Type blank
because it'll close the active object, which
| | 03:04 | by design is going to be the Splash Screen.
| | 03:06 | The next thing I want to do is open
the Main Menu form. I'll say OpenForm.
| | 03:10 | I can specify what form I want to open
using the drop-down menu, choose Main Menu.
| | 03:16 | I can also specify what view I want to
view it in and what window mode, but I
| | 03:19 | can leave these defaults.
| | 03:20 | We'll go ahead and close our
Macro and we'll save our changes.
| | 03:24 | Now, we need to specify the Timer Interval
that will wait before applying this macro.
| | 03:28 | The Timer Interval is entered in milliseconds.
| | 03:30 | So, if I want to do 3
seconds, I would type in 3000.
| | 03:33 | Go ahead and press Enter to finalize that value.
| | 03:36 | We'll close our form, we'll save the changes
and we'll double-click on it to see if it works.
| | 03:40 | It pops-up for 3 seconds,
disappears and the Main Menu appears.
| | 03:44 | So, the last thing we need to do is
create a Splash Screen and have it open,
| | 03:48 | right when Access opens.
| | 03:49 | We'll close the Main Menu, we'll go
to File, Options, we'll switch to the
| | 03:54 | Current Database options and right
here where it says Display Form and says
| | 03:58 | none, I'm going to choose my Splash Screen.
| | 04:00 | This is what will display as
soon as the database opens.
| | 04:03 | Now, I can change a couple of
other properties while I'm here.
| | 04:05 | For instance, I can change
the Display Navigation Pane.
| | 04:08 | I can turn that off and if I scroll down,
Allow Full Menus, let's turn that off as well.
| | 04:14 | Let's go ahead and say OK.
| | 04:14 | So, it's going to say I must close
and reopen the current database for the
| | 04:18 | specified option to take effect.
| | 04:19 | Let's go ahead and say OK.
| | 04:20 | And we'll Access and we will reopen it
again to see what our changes look like.
| | 04:24 | We'll close Access.
| | 04:26 | I don't need to keep anything
on the Clipboard, so that's fine.
| | 04:28 | And I'll reopen my file.
| | 04:30 | I'll double-click on 4-2
SplashScreen, there's my Splash Screen.
| | 04:33 | 3 seconds later the Main Menu appears.
| | 04:35 | You notice that I don't have my
navigation pane anymore, and that it don't have
| | 04:38 | most of the tabs on the ribbon, but I
can still go to View Employee Directory
| | 04:42 | and back, and I can Enter a
Product Review or exit the database.
| | 04:45 | Now, I don't want this to look like
this is securing the database in anyway.
| | 04:49 | The navigation pane is still
available using the shortcut key F11.
| | 04:53 | If I deselect my Main Menu by clicking
on the background and then pressing F11,
| | 04:57 | the navigation pane appears.
| | 04:58 | Also, if I press the Exit Database
button here to go back to Windows and then
| | 05:03 | hold down the Shift key while I double-click
on my file, Access will actually
| | 05:06 | bypass all of the startup options.
| | 05:08 | So, the navigation pane is here, the
tabs are here, and it doesn't automatically
| | 05:11 | run my Splash Screen.
| | 05:12 | So, hiding the navigation pane and
minimizing the ribbon doesn't actually
| | 05:16 | secure your database in anyway, it
just keeps things out of sight and
| | 05:19 | potentially out of mind.
| | 05:20 | So, a well thought out user interface
is a really nice thing to have in your
| | 05:23 | database and something your
end users will appreciate.
| | 05:26 | These little bits of polish, the
details that you put into your database can
| | 05:29 | take something that might otherwise
just be a tool and make it something that
| | 05:33 | people really actually enjoy using.
| | Collapse this transcript |
| Creating a customer form| 00:00 | So far we've taken a look at
creating navigation forms to move around our
| | 00:03 | database, we've created a reference
form in our employee directory and we've
| | 00:07 | created a form that looks up orders
that include a particular product.
| | 00:10 | Let's now create a form that can be used
to import records into our data tables.
| | 00:14 | We'll create a form that can be used to
capture information about new customers.
| | 00:17 | I'm going to close our Main Menu window
here and I'm going to click once on my
| | 00:21 | DirectCustomers table and go to the
Create tab and by clicking on Form we'll
| | 00:24 | build a quick form that I can then edit.
| | 00:26 | So, there's my form here, it's showing
me data at the top and if I scroll down I
| | 00:30 | can see that Access
automatically put in a subform.
| | 00:32 | Let's go into Design View.
| | 00:33 | I'll change my View to Design
View and we'll clean this up.
| | 00:36 | First, I'm going to scroll down and
I'm going to get rid of that subreport.
| | 00:39 | I'll click on it and press Delete.
| | 00:41 | I'm going to scroll back up.
| | 00:42 | Next, I want to get rid of these headers.
| | 00:43 | So, I'll click on the Detail
section and then right-click and turn off
| | 00:47 | Form Header/Footer here.
| | 00:48 | Since, there's content here,
I've got an icon and this label.
| | 00:51 | Access is going to confirm
that I want to delete those.
| | 00:53 | So Yes, get rid of that.
| | 00:55 | Next, I want to realign some of these objects.
| | 00:58 | They're pretty big for the data that's inside.
| | 01:00 | Now, by default Access groups everything
together so it's easier to edit in Layout View.
| | 01:04 | I think it's even still easier
to edit things in Design View.
| | 01:07 | In order to do that though, I
need to ungroup these objects.
| | 01:09 | So, I'm going to press
Ctrl+A to select everything.
| | 01:12 | I'll go the Arrange tab
and I'll say Remove Layout.
| | 01:16 | Now, these elements are all individual.
| | 01:18 | I can deselect them by
clicking off in the space above.
| | 01:21 | I'm going to resize this Customer
box here, make it a little shorter.
| | 01:25 | Now, I'll select everything again, and I
can go, Size/Space>To Shortest, to make
| | 01:29 | them all the same height.
| | 01:30 | Then I can go to Size/Space and say
Decrease Vertical to make them all evenly
| | 01:35 | spaced and decrease the size a little.
| | 01:37 | Now, I'll do that a couple of
times to bring them all together.
| | 01:39 | Size/Space>Decrease Vertical.
| | 01:41 | We'll do it a few more times
to nudge them all together.
| | 01:44 | Next, I'm going to resize my form.
| | 01:47 | So, I'm going to deselect all of these
objects and then I'm just going to select
| | 01:50 | the ones on the right.
| | 01:51 | I'll scroll over, I'll grab the right edge
and I'll bring it in to maybe about 3 inches.
| | 01:55 | Then I'll grab the right edge of my form
and drag it into about 3.5. I'll scroll over.
| | 02:01 | It's looking good.
| | 02:02 | I need to bring the bottom up now.
| | 02:03 | So, I'll scroll down.
| | 02:05 | So, I'll scroll down and drag the
bottom up, maybe about 4 inches.
| | 02:08 | That leaves me enough room at the
bottom to add some command buttons.
| | 02:11 | Now, let's take a look at some
of the properties for our form.
| | 02:13 | I'll select out form
selector here between the rulers.
| | 02:16 | I'll go to the Other tab here and
I'm going to make this a pop-up window.
| | 02:19 | So, instead of No, I'm going to choose Yes.
| | 02:21 | I also want to make sure that the user
interacts with this form or closes it
| | 02:25 | before they can do
anything else in the database.
| | 02:27 | So, I'm going to choose Modal to Yes as well.
| | 02:30 | In the Format tab I can
find the Caption property.
| | 02:33 | The caption is what's going to
appear at the top of the window.
| | 02:35 | Here, I'm going to say Add a new customer.
| | 02:37 | I'll press Enter and then finally in the
Data section in the Data Entry property
| | 02:42 | here, it's currently set to No,
I'm going to change that to Yes.
| | 02:45 | What that will do, is it'll force
this form to only enter in records into
| | 02:48 | the customers table.
| | 02:49 | It's not going to allow me to view
records that are in the customers table, it's
| | 02:52 | just for data entry.
| | 02:53 | Let's go ahead and press Enter to
finalize and we'll view our form here.
| | 02:57 | I'll switch to Home and view into Form View.
| | 03:00 | So, there's my form.
| | 03:01 | I might want to get rid of these record
selectors and this bar over here on the right as well.
| | 03:05 | So, let's go ahead and go back into Design View.
| | 03:07 | I'll right click, Design View.
| | 03:09 | I can find those into the Format tab.
| | 03:10 | We'll scroll down till we get to Record
Selectors, change that No and Navigation
| | 03:15 | Buttons, No as well.
| | 03:17 | Now, we need to add a couple of buttons,
so the user can interact with our form.
| | 03:19 | We'll go to the Button wizard by clicking the
Button control and pressing the button there.
| | 03:23 | Now, let's take a moment here
and think about the workflow here.
| | 03:26 | When you enter in records into Access,
Access is automatically saving those
| | 03:30 | records straight into the data table.
| | 03:32 | So, if the end-user comes to this form
and they type in some values and then
| | 03:35 | they close the form, what's actually
happening is that Access is taking the data
| | 03:39 | and saving it to the table
before the form is closing.
| | 03:42 | So in essence, closing the form is the
same as saving the data and closing the form.
| | 03:47 | If I want to have the ability to
cancel out, so for instance, you might have
| | 03:51 | typed something in and then decide
you don't want to do that, before I can
| | 03:54 | cancel, what I need to do is make
sure that these fields are cleared out,
| | 03:56 | that they're empty;
| | 03:57 | they don't contain any
data before closing the form.
| | 04:00 | If they contain anything at all, that's
going to get saved into the data table.
| | 04:03 | Now, I want to make sure that I have an
easy workflow for my end-users and they
| | 04:06 | don't really have to worry about that.
| | 04:07 | So, I'm going to create two buttons here.
| | 04:09 | One is going to be Save Customer and
the other one's going to be Cancel Entry,
| | 04:13 | but I need to think about these
macros a little bit in order to make the
| | 04:16 | functionality work as intended.
| | 04:17 | So, this first one is going to be the
Save Customer one and I can go to Form
| | 04:21 | Operations and Close Form, and the act
of closing the form will automatically
| | 04:26 | save those records into the table.
| | 04:27 | Let's go ahead and say Next.
| | 04:29 | The Text button, instead of Close
Form, we're going to say Save Customer,
| | 04:34 | and we'll say Finish.
| | 04:35 | So, there's my first button.
| | 04:36 | The second one is going to be
a little bit more complicated.
| | 04:38 | We'll add this button control;
| | 04:40 | we'll add that down below.
| | 04:41 | This one we need to clear out the records first.
| | 04:44 | So, we're going to go to Record
Operations and then Delete Record, that will
| | 04:48 | clear out everything within this form.
| | 04:49 | Let's go ahead and say Next.
| | 04:51 | The Text for this button is going to be
CancelEntry, we will press Next and Finish.
| | 04:57 | Now, when I click this button CancelEntry,
it's just going to clear out the form.
| | 05:01 | I'm going to actually edit this macro,
so that it clears out the form and
| | 05:04 | then closes it as well.
| | 05:05 | So, again, for the button, we'll go to
the Event tab, the On Click event is this
| | 05:09 | Embedded Macro that we
just built with the wizard.
| | 05:11 | I'll click build to edit it, and you
can see all of the error controls that
| | 05:14 | Access added into this macro.
| | 05:16 | All I need to do is scroll to the bottom,
we'll add a new action and we'll say CloseWindow.
| | 05:21 | We can leave the Object
Type and Object Name blank.
| | 05:23 | It'll close the active window, which
is going to be that Add Customer form.
| | 05:26 | I'll close my macro and save that change.
| | 05:28 | Let's go ahead and format our buttons,
I'll Shift+Click to select both of them.
| | 05:32 | Go to Format, we'll choose our green style,
so we're consistent throughout the database.
| | 05:37 | We'll go to the Arrange tab;
| | 05:39 | we'll Size/Space them To Widest, and
then we'll Align them to the Right.
| | 05:44 | Now, they're aligned inside the same, I
can use my arrow keys to nudge them into
| | 05:48 | position, and I'll just put
them over here on the right.
| | 05:50 | So, they align up with the
edge of my text boxes. All right!
| | 05:52 | Let's go to the Home tab and view our form here.
| | 05:55 | So, there's my Add a New
Customer form, it's a pop-up form.
| | 05:58 | I can type in some values here.
| | 06:00 | I can Save the Customer or I can Cancel Entry.
| | 06:02 | Right now I'm going to go
ahead and cancel this entry.
| | 06:04 | It's going to ask me if I want to save
this and I'll go ahead and say Yes, I'll
| | 06:07 | save that form and it'll ask me for a name.
| | 06:09 | I'm going to create this as
Add a new customer, that's fine.
| | 06:11 | Go ahead and say OK.
| | 06:12 | Now the final step of our workflow is
to add this into our Main Menu structure.
| | 06:16 | I'll double click to open up our Main
Menu and then I'll right click on it to
| | 06:19 | go into Design View.
| | 06:20 | We'll add a button here that says
Form Operations, Open the Form, Next.
| | 06:26 | We want to open the Add a new
customer form, Next and Finish.
| | 06:30 | Now, we've got a button that'll edit that.
| | 06:32 | It's got an icon on it, I can change that.
| | 06:34 | If I go to the Format tab where it says no
Picture Caption, I can change that to Right.
| | 06:40 | We'll add text to the right side.
| | 06:41 | The caption here will be Add a customer.
| | 06:43 | I'll make my button a little wider, we'll
format its color, and we'll move it into position.
| | 06:50 | Let's go ahead and close our menu.
| | 06:53 | We'll save our changes and
we'll look at our workflow now.
| | 06:57 | I'll scroll down, double click on Splash Screen.
| | 06:59 | We'll see the Splash Screen works.
| | 07:00 | It opens up the Main Menu.
| | 07:02 | Now, I can click Add a new customer.
| | 07:03 | I get my new customer window, I can
type in the values and Save, or I can
| | 07:06 | cancel the process.
| | 07:07 | So, data entry tasks don't really have
to be a chore, with a little thought into
| | 07:11 | the layout of a data entry form, your
end-users will be able to quickly and
| | 07:15 | accurately capture the information
that you need to store in the database.
| | Collapse this transcript |
|
|
5. Report LayoutGrouping and sorting data| 00:00 | Access's way of preparing material and data for
the printed page is through the report object.
| | 00:05 | Reports take data from tables and
queries, applies formatting and organization
| | 00:09 | rules, and bundles all of it together
into documents that are ready to be sent
| | 00:13 | to a printer or a PDF file, for example.
| | 00:16 | In this movie, we're going to take a
look at how Access organizes data into
| | 00:19 | grouping and sorting levels.
| | 00:20 | If you've ever used the totals row
within a query, this may seem somewhat
| | 00:24 | familiar, as the concept is really similar.
| | 00:25 | Let's get away from the Olive Oil
Company for just a moment and we're going to
| | 00:29 | take a look at a database
that's all about playing cards.
| | 00:31 | I'm going to double-click on
this table here called Draw.
| | 00:33 | We can see that this table has 5000
total records at the bottom here, and we
| | 00:37 | have a listing of randomized cards.
| | 00:39 | Basically, I took a random card from a deck
of cards and I made a record of each pull.
| | 00:43 | So, for the first pull I had 6 of Hearts,
on the second pull I had 4 of Clubs.
| | 00:47 | Let's go ahead and close the table and
we'll take a look at a summarized look
| | 00:51 | using this query here, DrawResults.
| | 00:52 | I'll double click on the query and we
see that this query is counting up the
| | 00:56 | number of times each card
appeared out of the total 5,000 draws.
| | 01:00 | My Queen of Diamonds for instance, had
121 showings, the Four of Clubs appeared
| | 01:04 | 118 times, and if I scroll to the
bottom here, I can see that the Six of Hearts
| | 01:09 | showed up the fewest number of
times in my random drawings at 76.
| | 01:12 | Now, this is the data that we're
going to use to build a report off of.
| | 01:15 | Let's go ahead and close the query,
but I'll keep it selected over here in
| | 01:19 | the navigation pane.
| | 01:20 | I'll go to the Create tab, in the Report
section we'll click on the Report button.
| | 01:24 | Access will create a very simple report
for us based off of the data in that query.
| | 01:28 | Now, the default layout with all the
records alphabetized doesn't really allow
| | 01:33 | for a detailed understanding of our data.
| | 01:35 | For instance, looking over this report
could you tell me which card appeared
| | 01:38 | most often or how do the King of
Clubs rank out of all of the Club cards?
| | 01:42 | This report is not
organized very we'll at the moment;
| | 01:44 | it can't even help us to
answer these very basic questions.
| | 01:47 | We can fix that using the Group & Sort
options to better understand the layout.
| | 01:51 | We can turn on Grouping & Sorting and the
Grouping & Total section of the Design ribbon.
| | 01:55 | Press this button here and we'll see
a new window up here at the bottom.
| | 01:57 | Inside of this window we have two options.
| | 02:00 | We can either Add a group or Add a sort.
| | 02:02 | Let's start by adding a sort.
| | 02:03 | I'll click on Add a sort and then
Access asks me which value I want to sort on.
| | 02:07 | Let's go ahead and sort on the CountOfID
that's the number of times it appeared.
| | 02:10 | I can also choose to sort it from
smallest to largest or using the drop down, I
| | 02:15 | can change it to largest to smallest.
| | 02:17 | Now, I can see which card
appeared the most times.
| | 02:19 | We can also use the Grouping &
Sorting to group like values together.
| | 02:22 | Let's go ahead and delete this sort right
now, using the X on the far edge of the bar.
| | 02:27 | This time I'll Add a group, and let's
say we want to group all of the cards
| | 02:30 | together by color, so that all the black
cards are together, and then all of the
| | 02:33 | red cards appear together.
| | 02:34 | I can choose SuitColor.
| | 02:37 | Now, I can see in my report, I've got
black here at the top and then all my
| | 02:40 | black cards, the Spades and Clubs,
and if I scroll down, I'll find the Red
| | 02:44 | cards, and then that's where
the Hearts and Diamonds are.
| | 02:47 | Now, within this grouping, we
can add an additional grouping.
| | 02:49 | Right now we've got a group for Red color.
| | 02:51 | Let's add another group below that by Suit.
| | 02:53 | We'll add a group down here at
the bottom, we'll say SuitName.
| | 02:57 | Now, we'll start at the top of the report.
| | 02:58 | I've got Black Clubs, there are all my
Club cards, and I'll scroll down, then my
| | 03:03 | Spade cards, here they are.
| | 03:05 | Scrolling down further, I get to the
Red section with Diamond startingm and
| | 03:10 | scrolling down further, there're my Heart cards.
| | 03:13 | Now, within that we can add a
sorting, so we can sort them ascending or
| | 03:16 | descending by the value that they appeared.
| | 03:18 | We'll Add a sort now, we'll say
CountOfID at this point and we'll change it
| | 03:22 | again to from largest to smallest.
| | 03:24 | So, now they're ranked in order, but
grouped by suit name and suit color.
| | 03:27 | Now, let me take a look at
this report in Print Preview mode.
| | 03:30 | We'll switch our view here at the top,
we'll click the down arrow and we'll
| | 03:33 | choose to go to Print Preview mode.
| | 03:36 | In Print Preview I can scroll through
the report and see what it looks like.
| | 03:39 | Now, when I get to the bottom of the
page here, I notice that my Red section
| | 03:43 | actually starts here, but the vast
majority of that data is on page number 2.
| | 03:46 | I'll select this button to change
our page and I'll scrub and this is the
| | 03:50 | rest of the Red data.
| | 03:51 | I can use our Grouping & Sorting
levels to help control where the page
| | 03:54 | breaks fall for my report.
| | 03:55 | If I close Print Preview, it will take
me back to Layout View and here on Group
| | 04:00 | on SuitColor, I can click the More
button right here, and one of the More
| | 04:04 | Options that I have is do not
keep group together on one page.
| | 04:07 | I can change that to keep
whole group together on one page.
| | 04:11 | Now, if I go back to Print Preview and
take a look at it, I can scroll down,
| | 04:15 | here's my Black Club cards,
scrolling down, here's Spades.
| | 04:18 | I get to the bottom of that and I
don't have the Red cards anymore.
| | 04:22 | I actually have to go to page 2 and up at
the top to get to the Red Diamonds and Hearts.
| | 04:27 | So, understanding grouping and
sorting behavior within your reports is key
| | 04:31 | to creating documents that deliver
the information needed in the most
| | 04:34 | organized manner possible.
| | Collapse this transcript |
| Understanding report structure| 00:00 | The structure that Access uses to
assemble the reports on the page is kind of
| | 00:03 | like building a layer cake or maybe
even designing blueprints for a skyscraper.
| | 00:07 | There are multiple sections that can
design one time each, but then Access
| | 00:11 | uses that blueprint over and over again as
it needs them for each section of the report.
| | 00:15 | Let's take a look at how this works.
| | 00:17 | Now, we're back in the Two Trees
database here and I've got a query set up here
| | 00:20 | called OrdersByRegion.
| | 00:21 | Let me double-click on
that so we can see the data.
| | 00:23 | The OrdersByRegion query is giving me
a total count of all the orders that
| | 00:27 | appeared in each region for each year.
| | 00:29 | So, I can scroll through the list,
I've got 2005 and 2006 and 07 and so on.
| | 00:34 | For each Region and Division I've
got a count of the number of products
| | 00:36 | that came from there.
| | 00:37 | Okay, let's build a report off of this data.
| | 00:40 | I'll close the query.
| | 00:41 | In my Create tab I'll click on Report,
and Access generates a basic report.
| | 00:46 | Let's switch into Print Preview
mode to take a look at the full sheet.
| | 00:49 | We'll go to View>Print Preview.
| | 00:52 | Now, I can scroll through this report
and I can see that there are multiple
| | 00:55 | sections that get repeated.
| | 00:56 | At the very top of the page I've
got what's called the report header.
| | 00:59 | The report header appears once at
the very top of the very first page.
| | 01:03 | After the report header,
I've got the page header.
| | 01:05 | This page header includes Year,
RegionName, DivisionName and
| | 01:08 | CountOfOrderID fields.
| | 01:10 | These are the header names that
appear at the top of the columns of data.
| | 01:13 | The page header appears at
the top of every single page.
| | 01:16 | After the page header, we have Details.
| | 01:18 | Each detail section is one
record from our database.
| | 01:21 | So, 2005>Midwest>East North Central and
49 represents one record from our query,
| | 01:27 | and this is one instance of the Detail section.
| | 01:29 | The Detail section repeats over and over
again for every record that we're reporting.
| | 01:32 | Let me scroll down to the bottom.
| | 01:34 | When we get to the bottom of the page,
we have what's called the page footer.
| | 01:38 | The page footer appears at the bottom
of every page and this page footer is
| | 01:41 | showing me what page
number of the report I'm on.
| | 01:43 | If I click the Next button here to go
the Next Page and then scroll back up to
| | 01:47 | the top, we'll see another instance of
the page header, because we're on a new
| | 01:50 | page, more details and scrolling down
at the bottom here, when I run out of
| | 01:55 | details or when I run out of records to
report, I have what's called the report footer.
| | 01:59 | In this particular report footer, it's
cut off a little bit, but it's giving me
| | 02:02 | a calculation to show me how
many rows I'm having in my report.
| | 02:04 | And on the very bottom of the page, I've
got another instance of the page footer.
| | 02:09 | So again, it's saying I'm on Page number 2.
| | 02:10 | Let's go ahead and take a look at what
this report looks like in Design View.
| | 02:13 | I'll Close Print Preview;
| | 02:15 | it's going to take me back to Layout view.
| | 02:17 | So, I'll go to the View menu
here and switch to Design View.
| | 02:21 | In Design View the report looks much
different than what you might have expected.
| | 02:24 | Each section is repeated only one
time here, I only have to design it once.
| | 02:28 | So, I've got my report Header Section
here with the icon and the title, I've got
| | 02:32 | my Page Header section here with the
titles of the columns of data, I've got the
| | 02:36 | Detail section, this is where the data
appears, I've got the Page Footer, which
| | 02:40 | has the calculation for the page number
and I've got the Report Footer with the
| | 02:43 | calculation of the number
of records I'm reporting.
| | 02:46 | When designing your reports it's
important to keep in mind that you want to
| | 02:49 | keep each section as narrow as possible because
it's going to get repeated over and over again.
| | 02:53 | For instance, if I take my Detail
section here and make it a little bit taller
| | 02:56 | by clicking right above this
Page Footer bar and dragging down.
| | 02:59 | So, I'm just going to add a little bit
of white space here, but every single
| | 03:02 | time I have a row of data it's
going to repeat that white space.
| | 03:05 | So, if I look at that small change
in Print Preview mode here, you'll see
| | 03:09 | that it makes a much bigger difference in
my report, because it repeats so many times.
| | 03:13 | Let's go back to Design
View and reverse that change.
| | 03:15 | I'll grab this bar and drag it back up.
| | 03:18 | So, in the last movie we
talked about grouping levels.
| | 03:21 | When it comes to header and footer
sections, each grouping level can have its
| | 03:24 | own headers and footers.
| | 03:25 | We can change the grouping level here or
we can change it in Layout View like we
| | 03:29 | did in the last movie.
| | 03:30 | The advantage of doing it in Layout
View is that it moves items around for us
| | 03:33 | automatically as we make any changes.
| | 03:35 | So, let's take advantage of that.
| | 03:36 | We'll switch our View here to
Layout View and then we'll Add a group.
| | 03:40 | We're going to group based off of the region.
| | 03:43 | I'll click on RegionName
and then we'll sort by Year.
| | 03:45 | We'll Add a sort, and we'll sort by Year.
| | 03:49 | Now, let's take a look at this
report in Design View again, to see the
| | 03:51 | changes that were made.
| | 03:52 | We'll go to View>Design View, then
we'll see I have a new section here
| | 03:56 | called RegionNameHeader and the
RegionName box has been moved up from the
| | 04:00 | Details to the RegionName.
| | 04:01 | Now we'll only see a RegionName
every time we get to a new region.
| | 04:04 | When I change my View to Print
Preview, you'll see that in action.
| | 04:08 | So, I've got my Midwest region.
| | 04:10 | I don't see the word Midwest
repeated over and over again.
| | 04:13 | I can scroll down and I've got my Midwest data.
| | 04:15 | When I run out of Midwest data, I've
got my Northeast data, and scrolling
| | 04:18 | further, my South data.
| | 04:19 | I can scroll back up.
| | 04:21 | We'll Close Print Preview
and go back to Design View.
| | 04:24 | Now, I can make one small change to this
report that will help clarify the data.
| | 04:27 | When I created this RegionName header,
the region name appears every single
| | 04:30 | time, but the Page Headers up here are
where the titles of the columns of data appear.
| | 04:35 | Those are still
appearing only once on each page.
| | 04:38 | I can move these down, so every time
I have a new region name, I repeat the
| | 04:41 | title of the column, that'll help keep your
bearings as you scroll through the report.
| | 04:44 | I can go ahead and click on this Year
box here and in drag it down to the bottom
| | 04:48 | section below, to the RegionName header.
| | 04:50 | I'll click on DivisionName, and I'll
click and drag that down as well, and
| | 04:54 | finally, the CountOfOrderID,
and I'll drag that down.
| | 04:58 | One more time in Print Preview view,
I'll change my View here, and now I've got
| | 05:02 | the Midwest, I've got the Year.
| | 05:03 | DivisionName and CountOfOrderID columns.
| | 05:06 | As I scroll down, when I get to
Northeast, I've got those column headers
| | 05:09 | repeated again every time I have a new region.
| | 05:11 | So, I don't lose track of what the column of
data means, as I scroll through the report.
| | 05:15 | Now, there's one more thing that I want
to change on this report and this really
| | 05:18 | drives me nuts about the way
Access designs its automated reports.
| | 05:21 | Every time you create one of these
automatic reports, Access puts boxes around
| | 05:24 | everything and it really doesn't add
anything to the legibility of this report.
| | 05:28 | So, let me clean that up really quick.
| | 05:29 | I am going to Close Print Preview view,
that will take us back to Design View.
| | 05:33 | I can go to the Format tab, click the Select
All button or press Ctrl+A on your keyboard.
| | 05:38 | Let me go over to Shape
Outline and change it to Transparent.
| | 05:42 | We'll take a look at the change in Print
Preview, Print Preview here, and you'll
| | 05:47 | notice that this report is
certainly much easier to read.
| | 05:49 | So, you'll see the same header and
footer options available to you when you
| | 05:53 | design forms, but they're really only
applicable to forms that are set up with a
| | 05:57 | continuous view property turned
on, like our Employee Directory.
| | 06:00 | So, headers and footers are a
limited value in forms, but they would work
| | 06:03 | the same way there.
| | 06:04 | In reports however, headers and footers
coupled with grouping and sorting levels
| | 06:08 | create the primary structure within
which your data will be organized.
| | Collapse this transcript |
| Building reports from wizards| 00:00 | Reports don't know anything about data.
| | 00:02 | All that report knows is formatting,
this font and this color and this
| | 00:06 | position and that's it.
| | 00:07 | For all of their data needs,
reports rely on a data source.
| | 00:11 | Data comes either directly from a Table
or a little more roundabout from a Query.
| | 00:15 | The nice thing about using a Query as
a data source is that you can bring in
| | 00:18 | fields from multiple related tables,
calculated fields and added expressions and
| | 00:23 | give it all to the report
in one nice and tidy package.
| | 00:25 | When using a Query, you have the
option of using a pre-build query that you
| | 00:29 | could find in the Navigation Pane over
here or create what's called an Embedded
| | 00:33 | Query directly inside of the reports.
| | 00:35 | Let's take a look at how our reports
get data by having the way they walk us
| | 00:38 | through the first two steps.
| | 00:39 | We can find the Report Wizard on the
Create tab right here in the Report section.
| | 00:44 | I will click Report Wizard.
| | 00:46 | Let's work towards getting a report
that looks at our customers orders.
| | 00:49 | Now the Report Wizard looks and
feels just like the Query Wizard if you
| | 00:52 | have ever use that feature and
that's because essentially, that's exactly
| | 00:55 | what we were doing here.
| | 00:56 | We are just building a query with a
few added steps to design the report.
| | 01:00 | We are going to start our report
with information from the Orders table.
| | 01:03 | I will select it from the list, and
then I will add Order ID, Order Date,
| | 01:08 | Customer ID and Product ID to the table.
| | 01:11 | We will go back up here and select a
related table, the Direct Customers table
| | 01:15 | at the top, and from here I will
get FirstName, LastName and State.
| | 01:21 | Finally, I need to get the price of the
products so I can get their total spent,
| | 01:24 | I can get that from the Products
table and that's where I will find Price.
| | 01:28 | We will add that to my report as well.
| | 01:31 | Go ahead and say Next, Access asks
as how do we want to view our data?
| | 01:34 | This window looks identical to
when we saw in the Form Wizard.
| | 01:37 | We could choose by tbl_Products to
group everything by Price or by Customer to
| | 01:42 | group everything by name.
| | 01:43 | I'm going to leave it as by
Table Orders, go ahead and say Next.
| | 01:48 | Now within that, how do
I want to group our data?
| | 01:50 | I want to group it by State and
then add over, go ahead and say Next.
| | 01:55 | In the sorting section I want to sort by
Price and I want to sort it Descending,
| | 01:59 | so the highest value appears to the top.
| | 02:01 | Go ahead and say Next.
| | 02:02 | It looks at the default Layout and
Orientation for the paper and say Next.
| | 02:07 | And we will go ahead and give this a title.
| | 02:09 | We will call it OrdersByState.
| | 02:10 | We will choose to Preview
the report and say Finish.
| | 02:15 | So here is our basic report.
| | 02:17 | We can see we have
everything grouped by the state.
| | 02:19 | I have got the summarize total of the
spend in Price here, descending I have got
| | 02:23 | the OrderDate, the CustID, the Products
that they ordered and the First and Last
| | 02:28 | Name of the person who ordered it.
| | 02:29 | Let's go ahead and take a
look at this in Design View.
| | 02:31 | I will Close Print Preview and
I return them into Design View.
| | 02:35 | Now let's take a look at where that query
went that we just built with the wizard.
| | 02:38 | If I go to the Property Sheet here and
if it is not selected, we will select
| | 02:42 | entire report by clicking this
black button between the rulers.
| | 02:44 | We will look at the Data tab and the
Record Source we can see the Select statement.
| | 02:48 | Now if you have taken my Queries in
Depth course here at lynda.com, this might
| | 02:52 | look really familiar to you.
| | 02:53 | This is telling me that this is
using a query in the background.
| | 02:56 | I can click the Build button to see that query.
| | 02:58 | That will take me into a Query design
session where I could take a look at what
| | 03:01 | the Wizard created for us as
we click through the menus.
| | 03:04 | We can run this query just like any
other query to see the data table behind it.
| | 03:07 | I will click on Run.
| | 03:09 | And I can see that data table.
| | 03:10 | So this is a data that's
getting formatted by the report.
| | 03:13 | Let's go ahead and back in
the Design View for the query.
| | 03:16 | The name of the query right now
is Orders By State : Query Builder.
| | 03:20 | This is telling me that this is
a query inside of this report.
| | 03:23 | It's not going to exist in
my Navigation Pane over here.
| | 03:26 | Now we can edit this query, for
instance, you might want to make changes to a
| | 03:29 | query and you might think, well if I
want to summarize by Total price, I can
| | 03:33 | click on the Totals button here and I
scroll over to price, and under Total I
| | 03:37 | could say, Sum, if I Run the
query, the query works just fine.
| | 03:42 | The problem is, is that Access has
created links to the field names in the
| | 03:46 | query, to the text boxes
that are in the report already.
| | 03:48 | And when I group by SumOfPrice, it
changed to the column name from Price to
| | 03:52 | SumOfPrice, and thereby breaking the link
to the fields that are already on the report.
| | 03:57 | Now you can fix this,
there is no problem with that.
| | 03:59 | But it does take a lot of clicking.
| | 04:01 | But now that we know what powers the
Record Source for our reports, let me go
| | 04:04 | ahead and back out to this query and
I am not going to save any changes.
| | 04:08 | We have got the Select statement here is
where the date is coming from our Record Source.
| | 04:11 | I have got the Select statement here,
it's providing the data for the report.
| | 04:15 | I could also use the drop-down menu here,
to select any of the Queries or Tables
| | 04:19 | so I could manually choose a Record Source.
| | 04:21 | We can smooth out our workflow by
first creating a query that gathers exactly
| | 04:25 | the information we want including any
aggregation or calculations, and then
| | 04:29 | build a report on that afterwards.
| | 04:30 | That's going to be topic of the next movie.
| | 04:32 | Now one last thing, the Record Source
property in the Reports functions exactly
| | 04:36 | the same way when bringing data into a form.
| | 04:38 | You can go back through some of the
forms that we have created throughout this
| | 04:41 | course, explore the record source
property and find out if we're using a table
| | 04:45 | reference, a query reference or an
embedded query to generate those records.
| | 04:49 | Of course with forms, you also have the
option of having an unbound form, that
| | 04:53 | isn't connected to any data source, such as
our Splash Screen and our Main Menu forms.
| | 04:56 | Whereas reports, we will kind
of worthless if left unbound.
| | Collapse this transcript |
| Building reports from queries| 00:00 | Building a report off an existing
query is probably the easiest way to get
| | 00:04 | a great looking report that displays some
fairly complex information from your database.
| | 00:08 | By leveraging the power of queries,
and feeding the results into the report's
| | 00:11 | record source, you'll create a good
foundation from which report design becomes
| | 00:15 | just a matter of dragging-and-dropping
fields into the appropriate locations.
| | 00:18 | I've said this before about Access,
but sometimes all of the wizards and
| | 00:22 | automated object-creation features of
the program can really get in the way and
| | 00:25 | just make things more
confusing than they really are.
| | 00:27 | Once you understand what's going on
behind-the-scenes, it's often the case that
| | 00:30 | the hand-built from the
ground up really is the way to go.
| | 00:33 | In this exercise, I've gone ahead and
pre-built a query that totals up our
| | 00:36 | customer's lifetime spent amount
with the Two Trees Olive Oil Company.
| | 00:39 | Let's go ahead and open up
TotalSalesCustomers query.
| | 00:42 | I'll double-click on it to open it and
we'll see that we're getting our first
| | 00:46 | and last name of the customer, the
total amount of money that they spent with
| | 00:48 | the company, and the state that they are from.
| | 00:50 | Let's take a look at this query in Design View.
| | 00:52 | I'll close the Property Sheet
over here to give myself some room.
| | 00:55 | So I've got my four tables
that I'm building this off of;
| | 00:58 | Customers table, the Orders table, the
Products table, and the States table.
| | 01:02 | From those four tables, I'm pulling the first
and last name, the price, and the state name.
| | 01:07 | And on Price, I've got the Totals
turned on and I'm adding up the values with
| | 01:11 | the Sum function here.
| | 01:12 | So this is the data that we're
going to be using in our report.
| | 01:14 | I'll go ahead and close the query.
| | 01:16 | We'll start creating a new report from scratch.
| | 01:19 | I'll go to the Create Tab and
inside of Report & Design view.
| | 01:21 | Now, just like with forms, the first thing
I want to do is connect it to a data source.
| | 01:26 | I'll go ahead and go to Property Sheet,
we'll look at the properties of our
| | 01:30 | report, and we've got this
Record Source property here.
| | 01:32 | I can use the drop-down menu and
choose the query TotalSalesCustomers.
| | 01:37 | At this point, we can go ahead and
start setting up the sections of our report.
| | 01:40 | We'll go to the Design Tab, the
Group & Sort section, turn that on, and
| | 01:45 | then we'll add a group.
| | 01:45 | I'll click on Group;
| | 01:46 | I'm going to group this by state.
| | 01:48 | Go ahead and click on the StateName.
| | 01:49 | That will add a StateName Header
to our report up here at the top.
| | 01:53 | I'll also add a sort.
| | 01:54 | I'll add a sort and I'll sort the values
within the states by the Lifetime Sales
| | 01:58 | or the total spent amount.
| | 02:00 | Let's go ahead and turn off Group &
Sort to get rid of that window and I'm
| | 02:03 | going to scroll down.
| | 02:04 | Now, we've got that Detail section to make it
a little smaller, here and I'll drag this up.
| | 02:10 | Now I can see everything on one screen.
| | 02:12 | At this point, we can just start
dragging-and-dropping fields and dropping
| | 02:15 | them into our reports.
| | 02:16 | I am going to go to Add Existing Fields.
| | 02:18 | Since I've connected this to a data source,
these are the four fields from that query.
| | 02:22 | So I'll take FirstName, LastName,
and Lifetime Sales, and drop them into
| | 02:26 | the Detail section.
| | 02:27 | I can just double-click on each one.
| | 02:29 | I'll take StateName and I'm going to
drag-and-drop it into the StateName Header, here.
| | 02:37 | Now, let's go ahead and
rearrange these a little bit.
| | 02:38 | I'll type FirstName, and I'll drag
it and I'll put it up here at the top,
| | 02:41 | take LastName, put it at the right,
and I'll drag Lifetime Sales and I'll put
| | 02:46 | it to the right of that.
| | 02:47 | Now the labels that came with them,
right now they're overlapping a little bit.
| | 02:51 | I can have those repeating every time I
have a new state, I will have the labels
| | 02:54 | with the columns of data below.
| | 02:56 | But unfortunately, you can't move
these labels just directly across from the
| | 02:59 | Detail section to the StateName Header.
| | 03:01 | You try and drag them using the top-left
anchor, it'll just hit that wall, and
| | 03:04 | it won't move across.
| | 03:06 | So what we can do is copy and paste
them up in the State Header section.
| | 03:09 | I'll select each of the labels;
| | 03:11 | the FirstName label, and I'll
Shift+Click on LastName Sales and this
| | 03:14 | FirstName label here.
| | 03:15 | I'll press Ctrl+X to cut.
| | 03:18 | I'll select my StateName Header
section and press Ctrl+V to paste those in.
| | 03:22 | Now, we'll just line them up.
| | 03:23 | I'll move Lifetime Sales right about
there, the LastName field, I'll move
| | 03:28 | it right about there, and the FirstName one,
I'm going to move that right about here.
| | 03:34 | I'll move my StateName Header up
and I'll align these underneath.
| | 03:38 | Now, we can go through and fine-tune
the alignment as we continue to
| | 03:42 | further refine our report.
| | 03:43 | Let's go ahead and just take
a look at the functionality.
| | 03:44 | But, before I do that, I'm going to
take this Detail section, I'm going to make
| | 03:48 | it really narrow, and we'll take a
look at this in Print Preview Mode here.
| | 03:52 | Now, I can see I've got pretty
respectable looking report already.
| | 03:55 | I mean, the fields are in the right
spot, essentially I can move them around
| | 03:58 | and refine the layout.
| | 03:59 | But, it's giving me the
results that I was looking for.
| | 04:01 | I've got the StateName here, Alabama, and
I've got my customers sorted underneath.
| | 04:05 | So I've got Rylee Whitfield,
and the Lifetime Sales.
| | 04:08 | It looks like I've got this reverse
though from the way I really wanted it.
| | 04:10 | I wanted the highest value at the top,
and the lowest value at the bottom.
| | 04:14 | So I can go and change that in
the Grouping & Sorting options.
| | 04:17 | Close Print Preview, I'll turn on in my
Design Tab, Group & Sort, we'll take my
| | 04:22 | Sort by Lifetime Sales, and I'll change it here.
| | 04:25 | Now, let's add a couple of
other items to my report.
| | 04:27 | We can add a title into the
Header section here using a label.
| | 04:30 | I'll click on Label, click
there once to type in the title.
| | 04:34 | Title for this is going to be
Lifetime Sales by State. I'll press Enter.
| | 04:38 | Now, I can format my title.
| | 04:39 | We'll change the font to 22.
| | 04:42 | I'll change the color from gray to
this darker gray here to be 15% dark.
| | 04:47 | I'll make this section a little bit
bigger, and I'll make my textbox bigger.
| | 04:52 | We can also add lines to
help break up this space.
| | 04:54 | I'll go to my Design Tab,
and grab my line object, here.
| | 04:59 | I'll add a line to the StateName Header.
| | 05:01 | So every time I have a new state, I'll
have a line that's marking out that space.
| | 05:04 | I'll click-and-drag out a line.
| | 05:05 | We'll go ahead and rearrange these a little bit.
| | 05:09 | I don't necessarily need a label for
the StateName, it will be obvious that,
| | 05:11 | that's the StateName.
| | 05:12 | So I'm going to delete the StateName
label and I'll move the StateName field over.
| | 05:17 | I'll go ahead, and format that, I will
make it bold, and then I'll make it a
| | 05:21 | little bit bigger here.
| | 05:22 | I'll take these values
here and I will line them up.
| | 05:24 | I'll click on each of these
LastName fields, all six at once.
| | 05:29 | I'll go to the Arrange Tab>Size/Space>To
Widest, to make them all the same size,
| | 05:36 | then I'll line them up
all against their left edge.
| | 05:38 | So I'll select these two, Align>Left,
these two, Align>Left, and these two,
| | 05:46 | Align>Left, and maybe I'll move
the LastName over a little bit.
| | 05:50 | Okay, let's take a look at our design.
| | 05:53 | Go to the Design Tab, and Print
Preview, and there's our design looking up.
| | 05:57 | I am going to go ahead
and get rid of those boxes.
| | 06:00 | We'll close the Print Preview.
| | 06:01 | I'm going to press Ctrl+A to select everything.
| | 06:04 | We'll go to Format>Shape Outline>
Transparent to get rid of the boxes around everything.
| | 06:09 | Let's take a look at the Final Layout here;
| | 06:13 | Print Preview, and that's
what our report is looking like.
| | 06:15 | So with a very little effort, we've
already got a pretty respectable looking report.
| | 06:20 | By starting from scratch, we didn't
have to tweak any of the pre-made pieces
| | 06:23 | that the wizards tend to put-in, and
we're able to make it to our specifications
| | 06:26 | right from the start.
| | 06:27 | In the next movie, we're going to
continue building on this form by looking at
| | 06:30 | the conditional formatting
rules that we can establish.
| | Collapse this transcript |
| Formatting conditionally | 00:00 | When skimming a long report, it could
be pretty easy to skip right over some
| | 00:03 | very significant numbers.
| | 00:05 | By utilizing the Conditional Formatting
rules within your reports, you can make
| | 00:08 | sure that the values that were
in the second look get noticed.
| | 00:11 | We will continue building on a Lifetime
Sales report that we made in the last movie.
| | 00:15 | I'll double-click on it to
open it to review where we are at.
| | 00:18 | Now let's go ahead and switch into Design View.
| | 00:20 | I will change my view here,
switch to Design View.
| | 00:23 | We want to call out some data from our report.
| | 00:26 | Let's say that we wanted to
highlight all of the values of Lifetime Sales
| | 00:29 | that were less than $20.
| | 00:29 | I will click on Lifetime Sales here,
go up to the Format Tab, and click on
| | 00:34 | Conditional Formatting.
| | 00:35 | This will start up the Conditional
Formatting Rules Manager where we can apply
| | 00:39 | rules for formatting our data.
| | 00:40 | I am going to click on New Rule, and
we can tell Access that I want to format
| | 00:44 | all the cells where the field value
is less than, and I will type-in 20.
| | 00:48 | For all the data that is less than 20 in the
Lifetime Sales field, I want to color that red.
| | 00:54 | I will click on this red
button here to change the text.
| | 00:57 | We will go ahead and say OK,
apply those changes and OK.
| | 01:01 | Let's go ahead and take a look at
our report here in Print Preview.
| | 01:04 | I will switch to Design View>Print Preview.
| | 01:07 | Now, if I scroll down, I will see that
the values that are less than $20 have
| | 01:10 | been highlighted red.
| | 01:11 | Now, what if I wanted to change the
entire row to red, not just the number, so
| | 01:15 | for instance the name as well?
| | 01:16 | Well we can do that with
Conditional Formatting too.
| | 01:18 | I will close Print Preview.
| | 01:19 | I am going to click on the FirstName
box and then Shift+Click on LastName.
| | 01:23 | I will return to the Format Tab and
click on Conditional Formatting again.
| | 01:26 | I will do New Rule.
| | 01:28 | Now, this time if I were to type in
the Field Value is less than 20, Access
| | 01:32 | will be actually trying to compare the
name, the FirstName to the value of 20
| | 01:36 | or the LastName to the value of 20 and
obviously that comparison doesn't make any sense.
| | 01:40 | So what we need to do is actually
change this from Field Value Is to
| | 01:44 | Expression, and then we'll put it a
calculation that compares the Lifetime
| | 01:48 | Sales value to the value of 20.
| | 01:50 | We can make a reference to Lifetime
Sales by wrapping it in square-brackets.
| | 01:53 | So I will write [Lifetime Sales]<20.
| | 01:54 | I will go ahead and format this the
same way with the red color and I will say
| | 02:03 | OK, apply the change, and say OK.
| | 02:06 | So now the Conditional Formatting of
these two boxes is referencing the value
| | 02:11 | that's in this box here.
| | 02:13 | Take a look at it in Design View,
Print Preview and now we will see that
| | 02:16 | the entire row is red.
| | 02:17 | Now, the expression that you can use
with Conditional Formatting can get
| | 02:20 | fairly sophisticated.
| | 02:22 | Let's take a look at another example.
| | 02:23 | It's a little more complicated.
| | 02:24 | Let's say on my report, I wanted to
highlight all of my top 100 customers
| | 02:28 | regardless of what state they are in;
| | 02:30 | some states might have none of my top 100
and some states might have four or five people.
| | 02:33 | How can I get a conditional
formatting that does that?
| | 02:36 | Now, there's probably lots of
different ways that we can go about this.
| | 02:38 | If you know Visual Basic, I am sure there
are another few ways you could do it there too.
| | 02:41 | Let me show you one way
that we can go about this.
| | 02:43 | First, I need to identify
who my top 100 customers are?
| | 02:46 | I have got a query over here
called top 100 customers, and I will
| | 02:50 | double-click on that to run, and we
will see that I have 100 records here, I
| | 02:53 | have got the First and LastName and
the Lifetime Sales and the State that
| | 02:56 | these people live in.
| | 02:57 | In fact, this is an exact duplicate of
the query that we built our entire report
| | 03:01 | off of this
TotalSalesCustomers except for one minor change.
| | 03:05 | Let's switch into Design View.
| | 03:07 | This query is exactly the same except
I've changed this return value to 100.
| | 03:11 | So it's only returning the
first 100 records in that report.
| | 03:14 | Let's go ahead and close the query.
| | 03:16 | So what I need to do in my
Conditional Formatting in the report is make
| | 03:19 | reference to the fields in
the top 100 customers query.
| | 03:22 | If the name is on the top 100,
then they are going to be here.
| | 03:25 | The way I am going to do that
is using a function called DMin.
| | 03:29 | The DMin function looks at a column
of data and it finds the minimum value.
| | 03:32 | One more time if I open up this query,
and I find the minimum value in this
| | 03:36 | Lifetime Sales field, it's going
to be here at the bottom; $135.08.
| | 03:42 | With DMin, I can use this value
and use that as a comparison in my
| | 03:45 | Lifetime Sales report.
| | 03:47 | If I find any values that are at
that value or above, then by definition,
| | 03:51 | they're my top 100 customers.
| | 03:53 | So how we would build that query?
| | 03:54 | Let's go ahead and close this Print Preview.
| | 03:56 | I'm going to go back to the
Format Tab>Conditional Formatting.
| | 04:00 | I am going to say New Rule, and
let me point out real quick here.
| | 04:04 | If I were to actually select all three
of these at the same time, and do a New
| | 04:07 | Rule at the same time, since they have
slightly different conditions at this
| | 04:10 | moment, they would
actually wipe out the first one.
| | 04:12 | So because these first two have the
same condition, I could add another one,
| | 04:16 | and it would be just fine, and then I
will just copy that to this third one
| | 04:18 | over here, but again if I were to
highlight all three at once, it would
| | 04:21 | actually delete the first condition and it puts
in the same condition for the other three boxes.
| | 04:25 | So that's why I have been
doing this in two steps.
| | 04:27 | So we'll do a New Rule here.
| | 04:30 | This time I am going to do Expression.
| | 04:31 | I am going to make reference to that
same Lifetime Sales field here in the
| | 04:35 | report, then I am going to
wrap it in square-brackets.
| | 04:38 | Now I want to say this value if it's
greater than or equal to the DMin, I am
| | 04:42 | going to open up a parenthesis for
the function, and now I need to make a
| | 04:46 | reference here this top 100 customers field.
| | 04:48 | Now DMin, like all of the other domain
aggregate functions like DAverage or DSum
| | 04:53 | has a slightly different
syntax than you might be used to.
| | 04:56 | In order to make reference to those
values, we wrap them in quotation marks.
| | 04:59 | So write a quotation mark, and then a
square-bracket, the name of the field that
| | 05:03 | I want which is Lifetime Sales in my
top 100 customers field, the closing
| | 05:07 | quotation mark, a comma, an open
quotation mark, another square-brackets, and
| | 05:12 | then the name of the query
I will find that field in;
| | 05:14 | qry_Top100Customers.
| | 05:18 | I will finish it with a closing
square-bracket, a closing quotation mark and a
| | 05:23 | closing parenthesis.
| | 05:24 | Now, I can format this how I want.
| | 05:26 | For these values, I am going to
highlight them with this light purple, this
| | 05:29 | Purple 3 color, and just so I have this
on my clipboard, I am going to copy this
| | 05:33 | so I can paste it on that other value.
| | 05:35 | So copy, I will press Ctrl+C, so it's stored.
| | 05:38 | Go ahead and say OK, and Apply.
| | 05:41 | Now I will say OK again.
| | 05:42 | Then we will apply that to our
Lifetime Sales condition as well;
| | 05:46 | Conditional Formatting>New Rule>
Expression Is, and now I will paste that in. Okay.
| | 05:51 | Let's go ahead and see if those works.
| | 05:53 | I am going to say OK, OK, except I need
the change of the coloring, so I will go
| | 05:58 | back to Conditional, I will
double-click on it, and I will change that
| | 06:02 | background color to Purple 3. All right!
| | 06:03 | That looks good, say OK here.
| | 06:06 | We will go to the Home Tab, and
we will view the Print Preview.
| | 06:09 | It's going to take it a minute to
process, but now I have got the purple
| | 06:12 | highlighting for my Top100Customer here,
and I have got the red highlighting for
| | 06:16 | everybody that's below $200.
| | 06:17 | It looks like Alaska has a
whole bunch of our top 100 club.
| | 06:22 | Now I can clean this up a little bit more.
| | 06:23 | Right now, the purple coloring is just
where the textbox is, and if I want to
| | 06:26 | make this look like it's highlighted
all the way across the row, I just need to
| | 06:29 | make the textboxes wider.
| | 06:30 | We will close Print Preview, back
in Design View and I will just make
| | 06:34 | these textboxes wider.
| | 06:38 | If they overlap a little bit, that
tends to help without any white lines
| | 06:41 | appearing sometimes between them.
| | 06:42 | So I will change my view to Print
Preview, and now I have got a purple bar that
| | 06:46 | extends all the way across
for my Top100 club customers.
| | 06:50 | So using Expression Builder and a
bit of logical problem solving, the
| | 06:53 | Conditional Formatting rules can
highlight just about any records that you'd
| | 06:56 | like to call special attention to.
| | Collapse this transcript |
| Calculating fields| 00:00 | We've seen how report stay connected to
a data source through the record source
| | 00:03 | property, which is linked to either a
table, an embedded query or in the case of
| | 00:07 | our lifetime sales report a linked query.
| | 00:09 | But we can actually calculate
additional values right here inside of our Report
| | 00:13 | using unbound text boxes, let's go
ahead and right click on our Lifetime Sales
| | 00:17 | report and open it
straight into the Design View.
| | 00:19 | What I would like to do is add some
calculated fields that add up the number of
| | 00:24 | customers inside of each state and then
adds up a total value that each state is
| | 00:28 | brought to the company, we can do that
with two text boxes and we could do that
| | 00:31 | in a site footer section.
| | 00:32 | We'll go up to our Group & Sort button
here on the ribbon, I'll turn that on and
| | 00:36 | for StateName where it say More I'll
open that up and over here on the right,
| | 00:41 | right now it says without a footer
section I click the down arrow and change
| | 00:44 | that to with a footer section.
| | 00:46 | That'll add a new section here called
StateName Footer that will appear after every state.
| | 00:51 | Now I can put my calculated field
inside of this box, I'll go up here to Text
| | 00:55 | Box and I'll add two text boxes into
the StateName Footer section, I'll click
| | 00:59 | once and add one in and I'll click
second time and add a second one over here
| | 01:03 | under Lifetime Sales.
| | 01:04 | I am going to go ahead and change the
labels associated with them now, I'll
| | 01:08 | double click on this first one and this
first one I'm going to type in Numbers
| | 01:11 | of Customers followed by colon
| | 01:14 | and in the second one I'll double click
and type Total Sales followed by a colon.
| | 01:19 | Let's go ahead and arrange these into
position, I'll click on all four and I
| | 01:25 | will go up to the Arrange tab>Align>to Top.
| | 01:30 | Now I will just space these out a
little, I'll deselect them and I'm going to
| | 01:33 | move this label over using the handle here and
I'll move Total Sales closer to this box there.
| | 01:42 | Now I'll right align these two labels
so that they appear against the text box
| | 01:45 | go to Format>Alignment>Right.
| | 01:49 | And finally I want to align these
underneath the values above, so I'll move this
| | 01:53 | over here and this one
looks about right there, okay.
| | 01:58 | The next thing I want to do is change the
calculation that'll appear here in the box.
| | 02:02 | For each text box we can go to the Data tab
of the Property Sheet and type in the value.
| | 02:07 | We can either type the formula right
here if we know it or we can use the
| | 02:10 | build button here to open up the Expression
Builder that'll help us construct the expression.
| | 02:15 | So this first box is going to add up
the total number of customers, we can do
| | 02:18 | that with the counting function.
| | 02:19 | If you want to find it here we can
find it in the Functions folder, Built-In
| | 02:23 | Functions, scroll down to the SQL
Aggregate group>Count, I'll double-click to
| | 02:28 | add that to my Expression up here.
| | 02:30 | The next thing you want to know
is, well what are we counting?
| | 02:32 | I'll click in the Expression, close
that up, I'll go into my report here, I'll
| | 02:37 | do under the Access database Reports>
Loaded Reports the Lifetime Sales report
| | 02:43 | and I'll just add up the number
FirstName, I'll double click on that to add it
| | 02:46 | up here, we'll go ahead and say OK.
| | 02:48 | So that adds that calculation here
into the box of the Control Source.
| | 02:52 | Let's go ahead and do another one for
this one the Total Sales that'll add up
| | 02:55 | the Lifetime Sales across the state,
we'll click here and this time we'll just
| | 02:58 | type in the value there is a pretty
simple formula it's going to be a sum, so
| | 03:01 | we will say =Sum open
parenthesis, open square bracket
| | 03:06 | Lifetime Sales with space closing
square bracket and closing parenthesis,
| | 03:12 | go ahead and press Enter to finalize
that and we'll take a look at our report.
| | 03:16 | Go Home change our View to Print Preview.
| | 03:20 | After a moment the report runs and I
can see I've got these two calculated
| | 03:23 | fields now, I've got the Number of
Customers in each state and the Total Sales
| | 03:26 | for each state before it starts the next state.
| | 03:28 | Let's go ahead and clean this up a
little bit I want to make these things align
| | 03:31 | and get rid of this text boxes, so
I am going to go back to Close Print
| | 03:34 | Preview, I'm going to highlight both of these
go to my Format>Shape Outline and Transparent.
| | 03:41 | Now I'm going to make sure that these
are all align properly look like the
| | 03:44 | number is here we are formatted a little
bit to the right, we'll just format the
| | 03:47 | Lifetime Sales and summary
calculation to the right.
| | 03:51 | And finally I want to create the size
of the footer a little bit we'll change
| | 03:56 | it's background color using the paint
bucket here and I'll change it to a little
| | 03:59 | green color, it's Olive Green, Accent 3.
| | 04:03 | And maybe I'll add a line across the
top to denote that this is the footer
| | 04:06 | section we have moved on from data,
I'll click all four of these boxes by
| | 04:10 | Shift+Clicking moving down a little bit,
we'll go to the Design tab and I'll
| | 04:14 | add a line object here.
| | 04:15 | Now I'll add a line right across there
and I move it up so it's tight against
| | 04:21 | the section, go to Home change the view
to Print Preview and there is our boxes.
| | 04:27 | So calculating controls can be
pretty straightforward and the Expression
| | 04:30 | Builder can help you construct the
formula for any of the functions available
| | 04:33 | within Access.
| | Collapse this transcript |
| Adding the finishing touches| 00:00 | No report should be complete without
those finishing touches that make for a
| | 00:03 | polished presentation thing such as page
numbers, date and time stamps and logos
| | 00:07 | help present a professional looking document.
| | 00:10 | In this movie we're going to finish
up our Lifetime Sales report by adding
| | 00:13 | these elements, we're going to go to
our Lifetime Sales report and right click
| | 00:16 | and go into Design View.
| | 00:18 | We can add a few things here with the Page
Number or a Logo, Title or Date and Time.
| | 00:23 | Now the Logo and Title objects
automatically coming formatted and I prefer to
| | 00:27 | have the finer control that just a
standard label or for our logo standard image
| | 00:32 | provides, so I'm going to actually not
use these two I'm just going to use an
| | 00:35 | image, we have already got
the label in for our title.
| | 00:37 | So let's go ahead and add a logo, I'll
click on the Image box I'll drag out a box here.
| | 00:42 | Access will open up the Insert Picture
window and I'll go to my Exercise folder,
| | 00:47 | Chapter 5 and I'm going to
choose the TwoTrees logo, press OK.
| | 00:51 | That'll put it into my report, I am
going to go ahead and move it over to the
| | 00:54 | right side, I'm also going to take my
title here and I'm going to align it up
| | 00:59 | with the left side of my
text here the StateName.
| | 01:02 | Now let's change to the
background color of the page header.
| | 01:04 | I'll click on the background here and
that'll take me to the PageHeaderSection
| | 01:07 | of the Property Sheet.
| | 01:09 | The background color right now is this
Background 1, I'll click on the Build
| | 01:13 | button and then I'm going to go the More
Colors, so I can type in the value that
| | 01:16 | matches my logo I'll click on More
Colors>Custom and I'll type in the value of
| | 01:21 | the green, its 185 for Red, 198 for
Green and 170 for Blue, go ahead and say OK
| | 01:28 | and that will color the background.
| | 01:30 | Now I can see that I've got a background
color actually applied to this image as well.
| | 01:32 | So when we click on that we have got
background color it's currently set to this
| | 01:36 | white here and I can actually just
make a Transparent by clicking Back Style
| | 01:39 | above and changing it to Transparent.
| | 01:41 | You can also go to Formatting and make
sure that doesn't have any outline we go
| | 01:44 | to Shape Outline and Transparent.
| | 01:47 | So that's add a logo to the top right, I
have got my title in top left, let's go
| | 01:52 | back to the Design tab.
| | 01:53 | You can also add page numbers to my
report by clicking on the Page Numbers button.
| | 01:56 | And we go ahead and click that and I
get a little wizard that pops up that ask
| | 01:59 | me how I want to format these.
| | 02:01 | So I can use the single page number or
I can say page number of however many
| | 02:05 | pages are in the report.
| | 02:06 | I'll just choose the single number.
| | 02:07 | You can also choose where to put it,
in the top, in the page header or in the
| | 02:11 | bottom in the page footer.
| | 02:12 | I'll choose Bottom, go ahead and say
OK and it adds calculated unbound text
| | 02:17 | field just like we calculated up here
that calculates the page number there.
| | 02:20 | Let's go ahead and add a date and time
stamp, I'll click Date and Time right here.
| | 02:25 | Access again ask me how I want to
format it I could use a full date or I could
| | 02:29 | use one of this abbreviations, I'll
just choose the full date and I can choose
| | 02:33 | whether I want to include
time or not and what format.
| | 02:35 | I am going to turn the Time off and
just include the Date, go ahead and say OK.
| | 02:39 | Now Access doesn't ask us what we're
going to put it, it just automatically puts
| | 02:42 | it in the Report Header section and if
I close the Property sheet you'll see
| | 02:45 | it's right over here.
| | 02:46 | Now I can actually copy it and put it
wherever I like, so I'm going to select it
| | 02:50 | press Ctrl+X that'll cut it and I am
going to come back down here into my Report
| | 02:55 | Footer and I'll place it down at
the bottom on my report instead.
| | 02:57 | And I'm going to go ahead and move it
all the way over to the right like so.
| | 03:02 | I'll scroll back up to the top we'll
get rid of this report header that was
| | 03:05 | added just by shrinking it up. Oh!
| | 03:06 | There is a box actually in there I need
to delete that out, now I can shrink it.
| | 03:12 | See what happens when Access add things for you.
| | 03:14 | Few last-minute details
that I want to attend to.
| | 03:16 | The report actually got wider when I
put in this date field, so I'm actually
| | 03:19 | just remove this logo over so it stays
in the corner, I'm also going to change
| | 03:23 | the background color of these two boxes.
| | 03:25 | Instead of being white on top of green
I'm going to make them transparent, so
| | 03:27 | you can see the green through it, I'll
select both of them by Shift+Clicking on both.
| | 03:32 | Format tab>Shape Fill>Transparent,
you could also check it here in the Font
| | 03:37 | field, I also want to move this line
here it's above my StateName I'm going to
| | 03:41 | move it up one square, just pressing
Up on the keyboard and that'll kind of
| | 03:44 | tighten that up against the state header name.
| | 03:46 | And everything else I think is looking
okay, let's go ahead and scroll through.
| | 03:50 | Well I think I want to change the color
of the state, it look like it's going to
| | 03:53 | print blue, I actually don't want that
and I can make it match this page number
| | 03:56 | in coloring by using the Format Painter here.
| | 03:58 | So I'll select the Page Number, go to
that Format Painter and then apply that to
| | 04:03 | the date as well, and now I will select
the date and I'll right align it again.
| | 04:06 | Now I think that's looking okay,
let's go ahead and go to check it out in
| | 04:10 | Print Preview mode and here is my
report, its looking okay, got some color
| | 04:16 | going on down here in the bottom
I've got my columns tilted up, got the
| | 04:20 | calculations showing.
| | 04:21 | Now I've got my conditional
formatting that have picking out my top 100
| | 04:24 | customers throughout the whole company.
| | 04:27 | So now that the report is finished we
can go ahead and send it to the printer or
| | 04:30 | save it as a PDF file email it
out or just save it in our database.
| | 04:34 | Every time we open it it's going to
go back to the data fetch the current
| | 04:38 | snapshot of our business and it's
going to update each customer spend,
| | 04:41 | reevaluating the conditional formatting
and the top 100 customers list, and it's
| | 04:45 | going to give us an up-to-date
information in a presentation ready format.
| | Collapse this transcript |
| Populating pre-printed documents| 00:00 | We've been hearing of the so-called
paperless office for quite some time now,
| | 00:04 | but that promise is yet to become a reality.
| | 00:06 | There's just no getting away from
having to fill in paper documents sometimes.
| | 00:10 | Luckily with a little planning and
help from Access we can streamline some of
| | 00:13 | the process when it comes time to
filling in preprinted paper documents and
| | 00:16 | forms with information from our database.
| | 00:18 | For this exercise we'll look at
populating information about our employees into
| | 00:22 | a preprinted government
employee form such as this form i-9.
| | 00:26 | The first thing we need to do is get
an image of our preprinted document.
| | 00:29 | We can get this image in a number of ways;
| | 00:31 | you can scan it in using a
scanner or export it from a PDF file.
| | 00:35 | However you do it for the best printed
quality you should capture your image at
| | 00:39 | 300 pixels per inch.
| | 00:40 | It's also important that it's not scaled.
| | 00:43 | Next, we need to prep this image a bit
to overcome what I consider to be a bit
| | 00:46 | of a quirk with Access reports.
| | 00:48 | In Access you can't set page margins
to zero, so if we put an image of our
| | 00:52 | document that's exactly the same size
as our printer paper Access will try and
| | 00:56 | wrap that image on the multiple pieces of paper.
| | 00:59 | To overcome this we need to crop down our
image, a quarter of an inch on all sides.
| | 01:04 | So our final image will be 8"x10.5"
at 300pixels per inch this works out to
| | 01:10 | 2400pixels x 3150pixels.
| | 01:14 | Then when we import the image back
into Access it can accommodate those
| | 01:18 | quarter inch margins.
| | 01:19 | Now if you need additional resources
for image preparation, you can find them
| | 01:23 | here at Lynda.com, in the course of
Scanning Techniques for Business and Home,
| | 01:27 | or Acrobat X Tips and Tricks.
| | 01:29 | Now once we have a preprinted document
in it, it's time to get started in Access.
| | 01:33 | Now back in Access we can start
building our i-9 report .We go up to the Create
| | 01:37 | tab, we will do a new report in Design View.
| | 01:40 | First let's go and get
rid of these page headers.
| | 01:43 | I'll right-click on this Detail bar
and turn off the Page Header/Footer.
| | 01:47 | Next we'll double check our margins.
| | 01:49 | I'll go to Page Setup and make
sure our margins here are set to
| | 01:52 | quarter-inch, which they are.
| | 01:53 | Then we'll go ahead and set up this
Detail section to accommodate the image.
| | 01:58 | Under the Property Sheet for the
Reports I'll go to the Format tab, let me go
| | 02:02 | ahead and set the width
of my document to 8 inches.
| | 02:06 | Up here in the Picture I'm going to
click where it says None, and I'll click the
| | 02:09 | Build button to select the image.
| | 02:11 | We'll go out to my Desktop>Exercise
folder, Chapter 5 and then I've got my i-9
| | 02:18 | Cropped image that's already been prepared.
| | 02:20 | I'll say OK, I'll change my Picture
Size Mode from Clip to Zoom, and then I'll
| | 02:27 | set the Detail section height.
| | 02:29 | I'll click on the Details here and
then I'll change the height here to 10.5.
| | 02:35 | Now that background is set up, we can
go ahead and start adding our fields.
| | 02:38 | We'll go to the Design tab and we'll
turn on our Add Existing Fields window.
| | 02:42 | We'll click on Show all tables, the Employees
table, and then we'll start adding the fields.
| | 02:46 | So I want FirstName, now let's double-
click to add them, LastName, Address,
| | 02:51 | City, State, Zip, and we're going
to need the middle name as well.
| | 02:57 | But we'll have to convert to it to
initial and we'll do that in just a moment.
| | 03:00 | So I will just have the MiddleName.
| | 03:02 | Okay, now Access moved all these with
labels associated and I don't need the
| | 03:06 | labels because we're putting this on a
form that already has labels, so we can
| | 03:09 | go ahead and get rid of all of these.
| | 03:10 | So I highlight all the labels here in
the left side, we'll get rid of those,
| | 03:14 | I'll press the Delete key, and for
the purpose of the exercise I'm going to
| | 03:17 | go ahead and color my font right now,
I'm going to make a big, bright, bold,
| | 03:20 | red, so you can see it.
| | 03:21 | We go to the Format tab;
| | 03:23 | I'll change it to Red, Bold, okay.
| | 03:27 | Now let's start moving them into position.
| | 03:28 | On this report it goes
LastName and then FirstName.
| | 03:31 | We'll take the LastName and I'll drag it there.
| | 03:34 | First name is going to go over here.
| | 03:36 | MiddleName will go over here, Address is
below, Address with the City, State, and Zip.
| | 03:45 | Now we can work on the alignments a
little bit, I'm going to move the Address up
| | 03:50 | just a little bit here and make it wider.
| | 03:54 | The City, I'm going to make wider as
well, the LastName and the FirstName I'll
| | 03:59 | make wider just to make sure I
don't have any long names to get off.
| | 04:01 | And then actually I'm going to move them up a
little so they don't overlap the line below.
| | 04:06 | Next, I'm going to highlight all of
these and make sure they have a transparent
| | 04:09 | background and no border.
| | 04:10 | So in the Format tab, we'll do
Shape Fill>Transparent, Shape
| | 04:15 | Outline>Transparent.
| | 04:17 | Okay, the last thing I need to do
is format this MiddleName, right now
| | 04:21 | it's showing the whole MiddleName whereas
the form is just asking for the Middle Initial.
| | 04:25 | Let's switch to the Design
tab and open the Property Sheet.
| | 04:27 | I'll click on the MiddleName field here
and we'll look at the All tab, now I can
| | 04:32 | and change with the MiddleName textbox
displays with a formula, but first I need
| | 04:36 | to change the name here.
| | 04:37 | If I change the formula first then the
name will create a circular reference,
| | 04:41 | because it will think I'm referencing
itself instead of referencing the field
| | 04:44 | from the original table.
| | 04:45 | So I'm going to change the name of this
textbox here to Middle Initial, then I
| | 04:52 | can change the Control Source.
| | 04:53 | So instead of just MiddleName I
actually want to use a function.
| | 04:56 | I'm going to right-click in this box and
say Zoom, so I can have some room to type.
| | 05:00 | I am going to change the font
here, so it's little easier to see.
| | 05:04 | The function that I can use to extract
just the first letter for the MiddleName
| | 05:08 | is called the left function.
| | 05:09 | So what I'm going to do here is write =
Left, open a parentheses, type in a
| | 05:14 | square bracket and then move to the
end. Close the square bracket, comma
| | 05:19 | 1, and then close parentheses.
| | 05:22 | So now what happens is the Left
function is going to extract the one of the
| | 05:26 | first left character from the MiddleName field.
| | 05:29 | We'll go ahead and say OK, and I'm
going to make sure that's Right Aligned so
| | 05:32 | it's again at the right-side here.
| | 05:33 | I'll go to Format and make
that textbox right aligned.
| | 05:37 | Alright, let's go ahead and check it out.
| | 05:39 | Go to Design view>View>Print
Preview, and take a look at our form.
| | 05:44 | I can see that the text that is lining
up in the boxes pretty well, I might want
| | 05:48 | to adjust the spacing a little bit, but
more or less it's looking pretty good.
| | 05:52 | Now the display under the screen
looks a little bit chunky and that's just
| | 05:55 | because of the way it's
rendering this zoomed out view.
| | 05:58 | If I go and view this I would say
like 200%, we'll see the text looks a lot
| | 06:02 | sharper and it'll print a lot
nicer than it looks from the screen.
| | 06:05 | We also have two options when
it comes times to print this.
| | 06:07 | I can either print this document as is,
and I will print the report in the
| | 06:11 | background and the data at the same
time or if I actually do want to print on a
| | 06:15 | preprinted piece of paper, I could
go back and delete the image from the
| | 06:18 | background of this report and print
just the data, and it should line up just
| | 06:22 | right on the paper, so there we go.
| | 06:24 | It's how we can setup our Access
database to help us print on the preprinted
| | 06:27 | reports and documents.
| | 06:29 | With a little ingenuity and some
time spent in the Design View we can get
| | 06:32 | everything lined up just right.
| | Collapse this transcript |
|
|
6. Printing, Tweaking, and AutomatingPrinting reports| 00:00 | We've seen the Print Preview screen many
times throughout this course, but we've
| | 00:03 | never really stopped to
take a look at the ribbon.
| | 00:05 | Let's go ahead and open up our Lifetime
Sales report in Print Preview by right
| | 00:09 | clicking on it and saying Print Preview.
| | 00:10 | Now most of these buttons are going
to be pretty obvious in what they do.
| | 00:14 | We can change the size of the paper
or set its margins, I can change from
| | 00:18 | Portrait to Landscape paper.
| | 00:20 | These changes however would
alter the layout on our report.
| | 00:23 | For instance, if I switch to Landscape,
Access will redraw the report on the new
| | 00:26 | page but it doesn't actually take
advantage of the space over here on the right.
| | 00:29 | If I wanted to make this a landscape report;
| | 00:31 | I'd want to go back in the Design View
and reorient my page so that I can take
| | 00:35 | advantage of the extra space.
| | 00:36 | Let's go ahead and switch it back to Portrait.
| | 00:38 | I also have an option here for Columns,
but again you would have to set up your
| | 00:42 | report specifically for columns in
order to best take advantage of this.
| | 00:46 | Let's go ahead and take a look
at one that I've already set up.
| | 00:48 | I'm going to right-click on Lifetime
Sales Columns and choose Print Preview.
| | 00:53 | This report, I have set it to be only 4
inches wide, now I can go to the Columns
| | 00:57 | setting here, select Columns, and
choose the Number of Columns to 2.
| | 01:00 | I am going to accept this layout here
where it goes down and then across rather
| | 01:05 | than across then down.
| | 01:06 | So I will leave down then across, and say OK.
| | 01:09 | Access will redraw the report and now
you can see that it starts with Alabama
| | 01:13 | which ends here, then goes to Alaska
data which goes down to the bottom, starts
| | 01:18 | again at the top and then ends
down here before starting Arizona.
| | 01:21 | Now the multicolumn report might be
confusing to some users because they might
| | 01:25 | think that the data for Alabama for
instance is the data over here in the left
| | 01:29 | and this data over here on the right.
| | 01:31 | So depending on your data I'll leave it
to you to decide whether this is a good
| | 01:34 | format for you or not.
| | 01:35 | Let's go ahead and close this report,
we will go back to the other one.
| | 01:39 | This Zoom section here allows us
to view multiple pages at a time.
| | 01:42 | I can choose this view a
Two-Page spread or More Pages than that.
| | 01:47 | I can zoom out to different zoom levels.
| | 01:48 | And then over I have a Data section,
the Data section allows me to export my
| | 01:53 | report to different file formats.
| | 01:54 | For instance an Excel file or PDF
file, or I can attach it as an E-mail
| | 01:58 | attachment and I have some more
options here in the More section.
| | 02:02 | Some of these file formats will
strip out all of the formatting and
| | 02:05 | coloring that we have.
| | 02:06 | PDF file will keep out of it but it
won't be editable, and finally on the far
| | 02:09 | left we've got the Print button.
| | 02:11 | We can click that, choose
a printer, and send it off.
| | 02:14 | Technically you can also print forms
too, but you're not going to find Print
| | 02:17 | Preview in the View menu for a form,
for instance if I close this Print dialog
| | 02:21 | box, I'll go to one of my forms here,
I'll go to the Employee Directory if I
| | 02:25 | double-click on it to open.
| | 02:27 | I don't have a Print option up here,
but if I go File and then Print I can
| | 02:32 | choose Print Preview from here.
| | 02:34 | In some forms like this Employee
Directory would actually work pretty well as a
| | 02:37 | printed document, but that shouldn't
really be the primary use for forms.
| | 02:41 | Reports are made to be printed and exported.
| | 02:43 | We can use them as a screen-only
presentation but that's really what forms are for.
| | 02:47 | Reports like some of the features on
screen that you would expect, such as being
| | 02:50 | able to filter or page through records.
| | 02:52 | On Screen and Report View reports
become exceedingly long and endless scrolling
| | 02:57 | list of records which
really isn't that user friendly.
| | 02:59 | So if you stick to the rule that forms
are for the screen and reports are for
| | 03:03 | paper it'd be a much happier database designer.
| | Collapse this transcript |
| Tweaking the design| 00:00 | At the end of Chapter 03, we took a
look at connecting a user input form to a
| | 00:04 | query which then passed
data onto a Chart Object.
| | 00:07 | We can do the same thing with a Report.
| | 00:09 | I've got a workflow setup
here called SalesByDivision;
| | 00:11 | I am going to start with a
SalesByDivision form here.
| | 00:14 | I'll double-click on it to open it and
I'll see that this form is asking me for
| | 00:18 | a Geographical Division and a Year
and I want to compare it to another
| | 00:21 | Geographical Division and Year.
| | 00:23 | Let's go ahead and fill in some boxes.
| | 00:24 | I'll use the drop-down menus and
choose I am interested in the New England
| | 00:28 | Division for 2009 and now I want to compare
that data to the Pacific Region for 2009 as well.
| | 00:36 | Now I've got two buttons here as well.
| | 00:38 | I can either Run the Query
or I can Preview a Report.
| | 00:40 | Let's go ahead and press
the Preview Report button.
| | 00:43 | I'll press Preview Report and the
report generates, showing me New England for
| | 00:48 | 2009 and here is the states and data
and then if I scroll down, at the Pacific
| | 00:53 | States 2009 with their states and data.
| | 00:55 | I also have some calculated
fields to the very bottom.
| | 00:58 | Both this form and the report have
had their designs refined quite a bit.
| | 01:02 | So in this I want to dissect these
elements to see how they were designed and
| | 01:06 | some of the decisions that were
made to get them to this point.
| | 01:09 | We'll start where the user starts with the form.
| | 01:10 | I'll go ahead and close the report here and
we'll take a look at this form in Design View.
| | 01:15 | I'll switch to Design View here and we
can see a couple of things on this form.
| | 01:19 | First, there really
aren't all that many elements.
| | 01:21 | In the top left corner I have this logo
and the bottom of the logo is a kind of
| | 01:24 | a brown color and the top of
this logo has this green color.
| | 01:28 | I've put it in the top left corner
of my form and in the top of the form
| | 01:31 | I've put a rectangle.
| | 01:33 | The rectangle is all of
this area here in the orange.
| | 01:35 | The rectangle color is set
to the same green as my logo.
| | 01:38 | The background of the details section
of my report is set to the same color
| | 01:42 | as this light brown.
| | 01:43 | Down in the bottom right
corner I have a tree image.
| | 01:45 | Again we're using the same light brown
color for the background of that tree picture.
| | 01:49 | Let's also take a look at this tree, if
I click on it and go to the Arrange tab
| | 01:53 | and take a look at it's anchoring here,
we'll see that it's set to anchor to the
| | 01:56 | bottom right corner.
| | 01:57 | That means that no matter what size of
screen the end-user is looking at, that
| | 02:01 | tree image will always appear in the
bottom right corner of their screen.
| | 02:04 | At the top, I have a label.
| | 02:06 | It's just been bolded text and set to white.
| | 02:08 | And then I have my elements down here.
| | 02:10 | I've got a couple of command
buttons and I have some combo boxes.
| | 02:14 | Let's take a look at these combo boxes
and I am going to go to the Design tab
| | 02:17 | and turn on my Property Sheet.
| | 02:18 | Let's click on the first combo box here.
| | 02:21 | On the All tab, I'll see that this
combo box has a name cbo_division.
| | 02:26 | The box below it has a name
as well, cbo_CompDivision.
| | 02:30 | My Year boxes, they have names as well,
cbo_Year and cbo_CompYear, so we can
| | 02:36 | make reference to whatever the
user selects here in our query later.
| | 02:39 | So let's see where the data is
coming from for these drop-down menus.
| | 02:42 | I'll click on the first one
again and go to the Data tab.
| | 02:46 | The Row Source is this SELECT query right here.
| | 02:48 | Let's take a look at the query behind it.
| | 02:50 | I'll press the Build button and we
see that there is a pretty simple query,
| | 02:53 | it's pulling in information from my
States table and I've got my DivisionName
| | 02:57 | and the DivisionID.
| | 02:58 | I can also see that I am sorting on
the DivisionID ascending, but I am not
| | 03:02 | showing it in the list, so
why do I have this part here?
| | 03:04 | Well let me run the query and you could see.
| | 03:06 | So when I run the query, I'll notice
that my DivisionNames are listed in
| | 03:10 | geographical order, not alphabetical order.
| | 03:13 | And this is important for my end-users.
| | 03:14 | This might be the order that they're more
comfortable seeing this data presented in.
| | 03:17 | Let's go ahead and go back to Design View.
| | 03:20 | And I'll close the query here, we'll
take a look at the Year box, it has a Row
| | 03:25 | Source as well, it's also a
query, these are embedded queries.
| | 03:28 | I can click the Build button to see its
details and this is simply pulling out
| | 03:32 | the Order Date from the Orders table
and applying the Year function to it.
| | 03:36 | So when I run it, it's just getting a list of
Years that are available within my database.
| | 03:41 | I'll close this out and
take a look at the query now.
| | 03:44 | So I've got the names of these
boxes that the end-user selects.
| | 03:48 | Close out the form, and I am
not going to save any changes.
| | 03:51 | When I click on those buttons, the query runs.
| | 03:54 | The query that runs is this one
right here, the qry_SalesByDivision, I'll
| | 03:58 | right-click on that and
take a look in Design View.
| | 04:01 | I'll close the Property Sheet
here so I could see the whole thing.
| | 04:04 | And this query is made up
of four different tables;
| | 04:06 | Products, Orders, Direct Customers, and States.
| | 04:09 | And then down here in the Criteria, I
right-click on this and say Zoom, we'll
| | 04:13 | see the text of that Criteria.
| | 04:15 | The Criteria for the Year field for
instance is reading to pull the year
| | 04:19 | from the combo box year on the
SalesByDivision form or the CompYear from the
| | 04:25 | combo box on the form.
| | 04:26 | Go ahead and say OK to that,
we'll take a look at the other one;
| | 04:30 | I'll click here, right-click and say Zoom.
| | 04:34 | The Criteria for the DivisionName is
also coming from the combo boxes on my
| | 04:38 | form, the cbo_division or the cbo_CompDivision.
| | 04:42 | So if it matches either of the selections
from the form, it will show up in the query.
| | 04:46 | Go ahead and say OK and I'll close
the query, without saving any changes.
| | 04:52 | The next thing that
happens is that the report opens.
| | 04:54 | This is the report down here,
rpt_SalesByDivision-complete.
| | 04:59 | I'll right-click on it
and switch to Design View.
| | 05:01 | First we'll go to the Property Sheet and take
a look at the Record Source for this report.
| | 05:06 | I can see that it's a linked query, it's
linked to that query we're just looking
| | 05:09 | at, the qry_SalesByDivision-complete
this is where the data is coming from.
| | 05:13 | It's split into a couple of sections;
| | 05:15 | I've got the Report Header here with the title.
| | 05:17 | The Division Header Name that will show
the division every time, the Year Header
| | 05:20 | below that, a Detail section, the
DivisionName Footer and scrolling down I've
| | 05:26 | got a Page Footer and a Report Footer.
| | 05:28 | Now each of these boxes have some
interesting properties, for instance this
| | 05:32 | SumOfPrice right here is the
data that's coming from the query.
| | 05:35 | Down below that in the DivisionName
Footer, I've got another box here that
| | 05:39 | says Division Sales;
| | 05:40 | this is calculating the total sales.
| | 05:42 | And it says equals Sum of
SumOfPrice (=Sum([SumOfPrice])).
| | 05:45 | So it's adding up all of the
fields here in the SumOfPrice column.
| | 05:49 | But down below that in the Report
Footer here, I've got another box and when I
| | 05:53 | click on that I see that it has the
exact same formula =Sum([SumOfPrice]), this
| | 05:58 | one is getting the total
value of the entire report.
| | 06:01 | Because it's in the Report Footer section,
it's aggregating over the entire report.
| | 06:05 | Because this one is in the
DivisionName Footer section, it's aggregating over
| | 06:08 | the division name section.
| | 06:09 | So let's go ahead and close this report and
we'll run through that workflow one more time.
| | 06:13 | I'll double-click on the form to open it;
| | 06:16 | I'll choose an area of interest.
| | 06:18 | How about this time East South Central
for the year 2009 and we want to compare
| | 06:23 | it to the same section for 2008?
| | 06:25 | We'll Preview the Report and that will
open up in Print Preview mode with the
| | 06:30 | data that I'm interested in.
| | 06:32 | So the only thing different is between
this and some of the forms and reports
| | 06:35 | that we've created in this course is
the amount of time spent fine-tuning the
| | 06:39 | position of the elements, adjusting the
colors and fonts and I'm going to admit
| | 06:42 | it, sometimes obsessing over every single pixel.
| | 06:45 | Step one in creating your forms and
reports should always be to get the
| | 06:48 | functionality in place.
| | 06:50 | If the form or report is exceptionally
ugly, once it's functioning properly, all
| | 06:54 | the rest is just moving the pieces around.
| | 06:56 | And that part can take a while as you
change your View Mode back and forth
| | 06:58 | between Design View, Layout View and
Form or Report View, sometimes moving
| | 07:03 | elements one step at a time.
| | 07:04 | It can be tedious sometimes, but
the end result will look great and
| | 07:08 | function great.
| | Collapse this transcript |
| Automating the workflow with macros| 00:00 | Before we wrap up this course, I
thought it will be helpful to outline a
| | 00:03 | couple of the macros that you can build that
will help you connect your forms to your reports.
| | 00:07 | As we have seen in several places
throughout Access, sometimes the wizards only
| | 00:11 | present you with the most common options.
| | 00:13 | To get access to all the available options,
you will need to dig into the designers.
| | 00:17 | The same applies to the button wizard.
| | 00:19 | The actions available in the button
wizard only give you a few basic options, to
| | 00:23 | move beyond that, we need to
construct macros ourselves.
| | 00:25 | So let's add some of these reports
that we have created in the last two
| | 00:28 | chapters, to the Main Menu that
we created earlier in the course.
| | 00:31 | I will right click on the Main
Menu and switch into Design View.
| | 00:33 | We will go up to that Button command
and we will add a button to our form.
| | 00:37 | I will click here to add it and
I'm going to go to Report Operations.
| | 00:41 | The first one I want to do is Mail Report.
| | 00:43 | I will go ahead and say Next.
| | 00:45 | The report I want to mail is that Orders
By State, I will go ahead and click Next.
| | 00:49 | And for this button I wanted some Text on here.
| | 00:51 | I'm going to put Email Orders Report, go
ahead and say Next, it will accept that
| | 00:58 | name and say Finish.
| | 00:59 | So there's one button.
| | 01:00 | Let's go ahead and add one more.
| | 01:02 | I will click on the Button control and
add that down to my form, the wizard will
| | 01:06 | start and I am going to go to Report
Operations again, and this time I am going
| | 01:10 | to choose Send Report to File.
| | 01:11 | Go ahead and say Next, the report I
want to send is my Lifetime Sales report.
| | 01:15 | I will click on Lifetime Sales and say Next.
| | 01:19 | The text for this instead of just Report
to File, we will say Export Lifetime Sales.
| | 01:23 | Go ahead and say Next and Finish.
| | 01:27 | So now I have got two buttons here,
well let's take a look at what the
| | 01:30 | wizard made for us.
| | 01:31 | I will click on this first
one the Email Orders one.
| | 01:34 | We will go to the Event tab here.
| | 01:36 | The On Click event is this Embedded Macro
and I will click the Build button to edit it.
| | 01:40 | I will see that the wizard created
this macro for us that that includes this
| | 01:43 | EmailDatabaseObject action.
| | 01:44 | It filled in a couple of these options
for us, for instance, I have the option
| | 01:49 | to include a report in
the Orders By State report.
| | 01:52 | But I also have some additional choices.
| | 01:54 | I can specify an Output Format.
| | 01:56 | And if I click on this, then the drop-down
menu here I can choose a specific
| | 01:59 | file format so that Access doesn't ask
me every time I press the button, what
| | 02:03 | type of file I would like.
| | 02:04 | I am going to choose PDF.
| | 02:06 | I can specify a recipient so I
could type in the To box here.
| | 02:09 | And I am just going to
send this to Test@test.com.
| | 02:13 | I can specify carbon copy or blank
carbon copy recipients, I can type in a
| | 02:17 | subject here, Here is
the Orders by State report.
| | 02:22 | And I can add Message Text in to
the body, The report is attached.
| | 02:26 | This last one here, this Edit
Message one is currently set to Yes.
| | 02:31 | If I leave it set to yes and press that
button, Access will take this report, it
| | 02:35 | will convert it to a PDF file.
| | 02:37 | It will open up Outlook on My Computer,
it will create a new e-mail, address
| | 02:41 | it to this person, put in the Subject line
and the Message Text, and attach that PDF file.
| | 02:45 | And then it will sit there and
wait for me to send the email.
| | 02:49 | If I change Edit Message to No here,
when I click on that Send Email button on
| | 02:55 | my Main Menu, all of that will
package up, get attached to the email, get
| | 02:58 | addressed and it will be send off
with no further interaction on my part.
| | 03:02 | So I won't even see that email
go out, it will just be gone.
| | 03:04 | Let's go ahead and change that back to Yes.
| | 03:06 | So I don't accidentally send any emails.
| | 03:07 | We will go ahead and close
this and we will save the macro.
| | 03:10 | So now that macro is providing more
functionality than what the wizard gave for us.
| | 03:15 | Let's take a look at the other
one, this Export Lifetime Sales.
| | 03:19 | The On Click event is this Embedded Macro again.
| | 03:21 | We will look at the builder for that.
| | 03:23 | This macro includes the
ExportWithFormatting action, and again I have got a
| | 03:27 | couple of options filled in.
| | 03:28 | If I click here, I can
select some additional options.
| | 03:31 | Once again, I can specify a file format.
| | 03:34 | Maybe I want to export this in a Excel file.
| | 03:37 | I can also specify an Output Location,
so if I want to specify an archived
| | 03:41 | folder on a network or some place on
my local computer where I always want to
| | 03:44 | save it, I could type that in here, so
Access won't even asks me for it when
| | 03:47 | I click the button.
| | 03:48 | It will just put it right in that spot.
| | 03:50 | I am going to leave it
blank for now, and click close.
| | 03:52 | I will save the changes, so again,
now this button is doing something more
| | 03:56 | useful than what the wizard left us off with.
| | 03:58 | Let's go ahead and format these a little.
| | 04:00 | I will click here and here, we will
go to Format, I will change their Quick
| | 04:04 | Style to maybe this black and we
will go ahead and Arrange them.
| | 04:08 | I will Align them to the Left side, I
will take their spacing and Decrease
| | 04:12 | Vertical and maybe a couple of times.
| | 04:15 | That's actually going to take too long so I
am going to press the button move it up here.
| | 04:21 | Now I will select both of them and
maybe I will drag them down here.
| | 04:24 | So now I have got a couple of additional
buttons that I have added to my Main Menu.
| | 04:28 | I can go take a look at it.
| | 04:30 | I will press to switch to Form View and
now if I press this Email Orders Report
| | 04:34 | button, we will see what happens.
| | 04:36 | The order gets packaged up and in just
a moment, we shall see Outlook pop-up,
| | 04:40 | there it is, my email is formatted, the file
is attached and it's ready for me to press Send.
| | 04:44 | So while using the Button Wizard, was
able to get us started, digging into the
| | 04:48 | full macro editor, was able to
provide further functionality.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:01 | I want to thank you for joining me here
in Access, and I would like to leave you
| | 00:03 | with these parting thoughts.
| | 00:05 | Form and Report design in Access
is one of those areas that really is
| | 00:08 | interdisciplinary, and you can pick up
a helpful idea on how to structure your
| | 00:12 | content in the most unexpected of places.
| | 00:14 | Start paying close attention to the
experience you have completing forms on the
| | 00:17 | Internet or interacting with
the screen on an ATM machine.
| | 00:20 | If you find an unusually well laid out
financial report, take a few minutes to
| | 00:23 | pick it apart and figure out
what really works about it.
| | 00:26 | Inspiration on how to create a better
interactive experience for end users,
| | 00:29 | literally is all around you.
| | 00:31 | I would like to recommend a couple of
additional lynda.com courses that can help
| | 00:34 | you build on the core concepts of user
interface and would apply to what we've
| | 00:38 | seen here in Access.
| | 00:39 | The first is Web Form Design Best Practices.
| | 00:42 | That course covers the importance of
understanding user behavior and form organization.
| | 00:46 | The second course is Typography
for Web Designers, which explores the
| | 00:50 | fundamentals of organizing text for
increased clarity in organization.
| | 00:53 | Until next time, thanks
again and have a fantastic day!
| | Collapse this transcript |
|
|
