IntroductionWelcome| 00:04 | Hello! I am Adam Wilbert, and I'd like to welcome
you to Access 2007: Forms And Reports In Depth.
| | 00:10 | In this course, we are going to
explore the inner workings of two highly
| | 00:13 | configurable objects
inside of an Access database.
| | 00:16 | With Forms, I will show you how to
create easy-to-use data entry points that
| | 00:20 | interface with your data tables.
| | 00:21 | We will work with controls
and layout and design views,
| | 00:24 | as well as explore Access's extensive
Property Sheets, where we can fine-tune
| | 00:28 | their behavior, appearance and interactivity.
| | 00:31 | Reports use many of the same tools and
techniques as Forms and are great for
| | 00:35 | preparing data for the printed page.
| | 00:37 | With reports, we'll look at grouping
data into categories, while leveraging
| | 00:41 | header and footer sections to organize your
report structure and make them easy to read.
| | 00:45 | We will build reports from scratch
that are tied to query record sources for
| | 00:49 | increased flexibility, and we'll hook
them all back into a form-based navigation
| | 00:53 | system to help end users move around
the database and accomplish tasks without
| | 00:57 | having to know anything
about its inner structure.
| | 00:59 | Thank you for joining me.
| | 01:00 | Now let's dig into Access 2007:
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 have done
here, or anywhere else that you want.
| | 00:07 | Here are the files that we will be using.
| | 00:09 | I have organized them into
folders for each chapter.
| | 00:11 | And inside the folder, it's
a database for each movie.
| | 00:14 | Now for some movies, I have saved a
completed version of the database for your review.
| | 00:18 | If you don't have access to the
Exercise Files, you can follow along with
| | 00:21 | your own database.
| | 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:05 | Yeah, we talk a lot about data and
records and relationships and queries, but
| | 00:09 | where Access truly shines is in its
ability to create a fully customized
| | 00:12 | application that is uniquely
tailored to your specific needs.
| | 00:16 | Where this really becomes apparent
is when we start to digging into Forms.
| | 00:20 | Forms are all about user interface.
| | 00:22 | They provide the mechanisms that
translate what you want to get done to the
| | 00:26 | inner workings of the database.
| | 00:27 | In fact, in a well-designed Access
database, there may never be a need to
| | 00:32 | actually even see a data table,
or a query, or a relationships map.
| | 00:36 | Those are all background processes
that organize input and output, and once
| | 00:40 | they're set up properly should
just work with little maintenance.
| | 00:44 | Forms handle all of the grunt work
of capturing user intent and moving
| | 00:47 | it through the system.
| | 00:49 | They help guide users
through the available options.
| | 00:51 | I'm going to go ahead and open up
the completed file from this course.
| | 00:54 | This 1-1 TwoTreesCompleted.
| | 00:58 | I will double-click on it to open it in Access
| | 01:01 | and we will see a couple of things happen.
| | 01:02 | First, we have a splash screen that
tells the end-user what's going on, that
we're loading the Two Trees
Olive Oil Company database.
| | 01:09 | The next thing I see is a Main Menu
screen, and here it presents me with a lot
| | 01:12 | of options that I can do within the database.
| | 01:14 | For instance, I can View the Employee Directory.
| | 01:18 | If I click on this button here, it takes me
to the Employee Directory for the company.
| | 01:21 | I can scroll through the list to see
all of our employees, or I can return to
| | 01:26 | the Main Menu using this button here.
| | 01:29 | Going down the list I
can enter a Product Review.
| | 01:31 | If I click this button, I get a
window showing me reviews for the products.
| | 01:36 | I can return to the Main Menu and
Add a new customer to my database by
| | 01:40 | clicking this button.
| | 01:42 | The Add a new customer form opens, allows
me to type in the values and Save that
| | 01:46 | Customer, or decide I don't
want to do this and Cancel out.
| | 01:49 | I will go ahead and say Cancel Entry.
| | 01:51 | I can also Export Customers to an Excel
file or email a report to an employee.
| | 01:56 | I also have options to Exit this Database, here.
| | 02:00 | So knowing nothing about this database
going into it, we can accomplish some
| | 02:04 | very specific tasks without even
digging into the navigation system.
| | 02:07 | If you think about Word as a tool for
creating documents, then Access is really
| | 02:11 | a tool for creating more specific tools.
| | 02:13 | I know that's kind of an
abstract idea, so let me explain.
| | 02:17 | You can use Access and have a fully
operational database with nothing more than
| | 02:21 | a few tables and queries.
| | 02:22 | And in fact, that's where a lot
of other database programs end.
| | 02:26 | But when you layer Forms and
Reports on top of that data, you start
| | 02:29 | creating something more.
| | 02:30 | It becomes a custom application where Access,
the program, doesn't really matter anymore.
| | 02:35 | You have created a custom tool that
operates within Access, but it's tailored to
| | 02:38 | your specific needs, because in the end,
unlike with Word, the database file
| | 02:43 | really isn't the end goal.
| | 02:45 | The end goal is to have a place to store
data, to retrieve records easily, and to
| | 02:49 | manage the day-to-day
operations of the organization.
| | 02:52 | Your job as a database
designer is to create the tool.
| | Collapse this transcript |
| Designing for the end user| 00:00 | Throughout this title, I will
often refer to the database's end-user.
| | 00:04 | And I wanted you to take a moment to
define who I think this person is and why
| | 00:08 | they are important to keep at the front of
your mind while designing your Forms and Reports.
| | 00:12 | The end-user isn't anyone specific.
| | 00:14 | They are more of a theoretical individual
that is completely new to your organization.
| | 00:19 | They may be a new hire or an intern or
maybe even your grandmother. Somebody
| | 00:23 | that knows very little about your day-
to-day tasks and even less about how to
| | 00:27 | use Microsoft Access.
| | 00:29 | As you develop your database
application, occasionally put yourself in their
| | 00:32 | position and look at your
workflow as if you are brand-new to it.
| | 00:36 | If you can set up your navigation and
data entry and reporting systems tailored
| | 00:40 | to the end-user, that uninitiated, first-
day-on-the-job employee, then you will
| | 00:45 | be well on your way towards
creating a successful application.
| | Collapse this transcript |
| Exploring the database| 00:00 | One of the main ideas behind
creating Forms and Reports is to allow your
| | 00:04 | end-users to be able to work with the
database without knowing a whole lot
| | 00:07 | about its structure.
| | 00:08 | You as a database designer, on the other
hand, need to have some familiarity with
| | 00:12 | the data tables within the database
in order to be able to work with them.
| | 00:15 | Let's take a look at the Two Trees
Olive Oil company database that we will be
| | 00:18 | working with throughout this course.
| | 00:21 | In my navigation pane on the left, I've got
all of the data tables we'll be working with.
| | 00:24 | I have got a table for Orders, a table for
Customers, Employees, Products, and so on.
| | 00:31 | Let's go ahead and double-click on the
Orders table and take a look at what's inside.
| | 00:35 | And within the Orders table, I have got
fields for the Order ID, the Order Date,
| | 00:40 | Customer ID, Product ID, and Salesperson ID.
| | 00:44 | Now all these ID numbers reference
other fields and other data tables.
| | 00:47 | So for instance, Customer ID here is
linked to Direct Customers table and if I
| | 00:53 | open up that, I can find the customer
that was associated with the order by
| | 00:58 | referring to their Customer ID number here.
| | 01:01 | And all the data tables within our
database are related to each other.
| | 01:03 | I can see how they are related by going
up to Database Tools and Relationships.
| | 01:09 | Here I can see that the Orders table and
the customer ID within is linked to the
| | 01:14 | Direct Customers table and the Customer ID.
| | 01:17 | So by using these two tables together, I
can find the order that was placed and
| | 01:21 | the name of the person who placed that order.
| | 01:23 | The same thing applies to the Employees table;
| | 01:25 | I can find the salesperson
who assisted with the order.
| | 01:28 | Both of these tables, the Employees
table and the Direct Customers table, are
| | 01:33 | linked to a lookup table of States,
where we match an abbreviated state to the
| | 01:37 | full state name, and we can find out
additional information about what division
| | 01:41 | and region that state is in.
| | 01:42 | Now take a look at our Product ID
number in the Orders table; I can use that as
| | 01:47 | a reference to look up the
products in our Product Table.
| | 01:51 | So from the Product ID, I
can find the product name.
| | 01:54 | And once I know the product name, I
could find out what size it is, for instance,
| | 01:59 | or what type of oil it used.
| | 02:01 | So these are the data tables we'll be
working with as we create our Forms and
| | 02:04 | Reports throughout the rest of this course.
| | Collapse this transcript |
| Creating a form with the wizard| 00:00 | You can have a basic form up and running
with as little as three clicks of the mouse.
| | 00:05 | For instance, I can go to this Orders
table, click on it once, go to the Create
| | 00:10 | tab and in the Form section, click on Form.
| | 00:15 | Access will open up a new form based off of the
Orders table and will place it in Layout view.
| | 00:20 | Let's change our View to Form
View so we can work with that data.
| | 00:24 | From the Views button on the left, I
will go ahead and click this button, and it
| | 00:27 | will switch us into Form View.
| | 00:30 | Now this is a live link to our
data within the Orders table.
| | 00:33 | I can see that I have 2,200 records, and
this first record here is order number 10,000.
| | 00:38 | If I open up my Orders table here--
double-click on it to open--we will see the
| | 00:43 | record number one is indeed order 10,000.
| | 00:45 | Now these are live linked to our data
table. I can go ahead and make changes here
| | 00:50 | within the form. For instance, if I
click on this OrderDate 1/10/2005, and using
| | 00:55 | the calendar picker on the right, I can
change this date. Let's say it's the 6th.
| | 00:58 | I will click on the pencil icon here to
commit that change to the table, and now
| | 01:05 | in my Orders table, I can see
that that change has been made.
| | 01:08 | This works the other way as well.
| | 01:09 | I can click on the OrderDate, click on the
Calendar icon, and change it back to the 10th.
| | 01:14 | I will select the pencil icon again
to commit that change and return to the
| | 01:20 | form, where I can see that the
date is back to where it was.
| | 01:24 | I like to think of these little
squares as windows into the data table cells.
| | 01:27 | So this is a little window here into the
OrderID of the Orders table, and this one
| | 01:32 | is the window into the
OrderDate for the Orders table.
| | 01:35 | Now the basic form that Access created
for us leaves a little bit to be desired.
| | 01:40 | For instance, it's only built on the
fields that are within the Order table.
| | 01:44 | I got a CustomerID here, but I
don't know who this customer is.
| | 01:48 | The same way, I have got the ProductID, but
I don't know what product this refers to.
| | 01:53 | I can create a better form using
the FormWizard that will give us that
| | 01:56 | information from the related data tables.
| | 01:58 | Let me go ahead and close both of these,
and I don't need to save any changes,
| | 02:02 | so close, and I will say No to save
changes, and I will close my data table.
| | 02:08 | Now I will use the wizard, and we can
find that by using the Create tab, More
| | 02:12 | Forms, and then Form Wizard.
| | 02:14 | And we will create a form based
off of multiple related tables.
| | 02:18 | I am going to choose my Orders table
from the list here, and I'll use the
| | 02:23 | OrderID and OrderDate.
| | 02:25 | But instead of CustomerID, I am going to
pull that from the Direct Customers table.
| | 02:30 | So I'll go back up to my Tables and Queries
selection, select the Direct Customers
| | 02:34 | table, and pull in the FirstName and LastName.
| | 02:38 | I will return to the drop-down menu,
select my Products table, and from there I
| | 02:44 | can get the ProductName, and
I will add that to my form.
| | 02:47 | Now that I have got all the fields for
my data tables selected, I will go ahead
| | 02:51 | and say Next, and Access is going to
ask us how we want our form to lay out.
| | 02:57 | This first option, by table Orders, as a
Single form, is similar to the form that
| | 03:02 | Access created for us just a moment ago.
| | 03:04 | It will display each record in its own page.
| | 03:06 | Let's go ahead and say Next, and we
will accept this default column. We will
| | 03:12 | accept the default style,
this Access 2007, and say Next.
| | 03:16 | And I will go ahead and accept the
default name, and then say Finish.
| | 03:19 | Now Access gives us a form that's
similar to the first one, except that it's
| | 03:25 | giving us the name, First and Last Name,
and the Product Name of the oil that
| | 03:29 | was ordered, instead of
just the reference ID numbers.
| | 03:31 | Let's go ahead and use the Wizard
again to select a couple of different
| | 03:34 | options, and see how we can improve upon this.
| | 03:37 | Go to the Create tab > More Forms, and Form Wizard.
| | 03:41 | We will build another
form using the same options;
| | 03:46 | Select my Orders table, OrderID, and Date.
| | 03:48 | I will select my Direct Customers table,
First and Last Name, and I will select
| | 03:56 | my Products table,
ProductName. Go ahead and say Next.
| | 04:02 | This time, let's view it by the table Products.
| | 04:05 | When I click on by tbl_Products,
Access shows me a little image here what
| | 04:09 | it's going to look like, and it's showing me
that it's going to be a Form with subforms.
| | 04:13 | So we are going to have a Product Name
here at the top, and then we have a list
| | 04:17 | of all the orders that included that product.
| | 04:19 | We will look at Linked forms in just a moment.
| | 04:22 | Go ahead and say Next, we will accept
the defaults here, and again, and we will
| | 04:27 | accept the default name. Go and say Finish.
| | 04:29 | Now I have got a form that's set up,
and I can scroll through the records here
| | 04:34 | and select different oil, so I
have got 90 products within my database.
| | 04:38 | I can select an order here on the bottom,
and it will show mw all of the orders
| | 04:42 | for that particular oil.
| | 04:44 | Let's go ahead and close this out and
I will show you this last option that
| | 04:46 | we want to work with.
| | 04:47 | I will close those two tabs, and I am
actually going to delete them from my
| | 04:51 | Navigation Pane here.
| | 04:52 | So I will select the last one and
Shift+Click to select the first one,
| | 04:57 | right-click and Delete.
| | 04:59 | And yes, we will go ahead and delete this.
| | 05:00 | Let's run through the Wizard one more
time and look at that Linked forms options.
| | 05:04 | Let's click Create > More Forms> Form Wizard.
| | 05:09 | Once again, we will pull in the same
fields, so Orders table: OrderID, and
| | 05:13 | OrderDate. The Direct Customers
table: First and Last Name. And the
| | 05:20 | Products table: ProductName.
| | 05:23 | Go ahead and say Next.
| | 05:26 | This time we'll choose to view by the
table products, but instead of Form with
| | 05:29 | subforms, we will choose Linked forms.
| | 05:32 | This will create two separate forms
that show me the same information.
| | 05:35 | I will go ahead and say Next.
| | 05:37 | We will accept the defaults here, and
this time I'm going to go ahead and name
| | 05:41 | these, since I want to work
with them in the next movie.
| | 05:44 | So this First form I am going to call
ProductSelection, and the Second form I am
| | 05:48 | going to call OrdersByProduct.
| | 05:49 | We will go ahead and say Finish.
| | 05:54 | And Access opens up the First form.
| | 05:56 | Now there is a little bit of a bug
in the way Access creates this form.
| | 05:59 | It's got a button here, but I can't
click on the button, because this label
| | 06:02 | gets put on top of it.
| | 06:04 | So in order to fix that, we are going
to switch into Layout View real quick so
| | 06:07 | we can rearrange this a little bit, and
we will look at Layout View in depth in
| | 06:10 | the next movie. But for now, go ahead and just
click the View button to switch to Layout View.
| | 06:15 | And I will click and drag this label to
the right a little, and that will free
| | 06:20 | up my button, so I can click and drag
that to the right, and I then I will move
| | 06:23 | the label back to where it was.
| | 06:24 | Okay, press the form view
button to switch back to form view.
| | 06:28 | Now I have got a form here where I can
scroll through my different oils. Once
| | 06:34 | I find one that I want to investigate,
this Extra Virgin Oil 8 oz size which
| | 06:38 | is product 6 of 90,
| | 06:39 | I can click my OrdersByProduct button.
| | 06:42 | That will open up a second form that
will list all of the orders that included
| | 06:47 | that particular product.
| | 06:49 | So using the Wizard, we were able to
create three slightly different user
| | 06:52 | experiences, and using data from
multiple related tables, we were able to get
| | 06:56 | details on order activity at Two Trees.
| | 06:59 | As is typical with the objects created
with the Wizard, we have a good starting
| | 07:02 | point, but we need to clean this
up a bit before it's presentable.
| | 07:05 | Let's take a look at the Layout View
in the next movie to finish these forms.
| | 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:04 | will allow us to select a product and
get a listing of all the orders that
| | 00:08 | included that product.
| | 00:10 | Now we need to polish up the layout a
little using the tools available to us
| | 00:13 | in the Layout View.
| | 00:14 | Let's take a look at what we want to
change before we go into Layout View.
| | 00:18 | Now, the ProductSelection form is pretty basic.
| | 00:22 | I want to change some of the colors a
little bit to make it in line with our Two
| | 00:25 | Trees company colors.
| | 00:26 | I also want to remove the
box around this field here.
| | 00:30 | It doesn't really add anything to the form.
| | 00:31 | But other than that, there's not
a whole lot here to change.
| | 00:35 | The other form, OrdersByProduct,
has a lot of changes I want to make.
| | 00:38 | For instance, this blue color
is not really doing much for me.
| | 00:41 | I also want to change this up a little bit.
| | 00:44 | It's got a lot of space between each record,
| | 00:46 | so I want to condense this
down to fit more on each screen.
| | 00:49 | I also want to change the headers up here.
| | 00:51 | Right now they're coming from the form
fields in the Orders table, and I'm going
| | 00:54 | to go ahead and insert spaces in
these to make them a little more legible.
| | 00:57 | So we're going to go into the
Layout View and we'll make these changes.
| | 01:00 | Let's go to the ProductSelection form.
| | 01:03 | We'll switch to Layout View by going to
the View menu and clicking this button here.
| | 01:07 | That'll move us into Layout View.
| | 01:09 | If the Field List pops open, we
can go ahead and just close it down;
| | 01:11 | we won't use that in this movie.
| | 01:13 | Now, let's look at the tabs that
are available to us in Layout View.
| | 01:17 | I've got the Format Tab selected
right now and on the Format Tab, we have
| | 01:20 | options for changing the Font--for
instance, the Font Face and Size, whether
| | 01:25 | it's Bold, Italicized, or Underlined,
its layout--if it's Left, Center, or
| | 01:30 | Right-Justified, Color.
| | 01:32 | I can also have an option for
conditional formatting and we'll talk about
| | 01:35 | Conditional formatting later on in the course.
| | 01:36 | There's a section here called Formatting,
and the Formatting section applies to
| | 01:41 | numerical fields like dates and times.
| | 01:43 | We can specify whether they're currency,
or percent, or where the decimals fall.
| | 01:47 | Gridlines will apply to the records,
so we can put grids around each record
| | 01:51 | as it appears in the form.
| | 01:52 | This Controls section has elements for
Title, Date, and Time. We can adjust that
| | 01:57 | Line Type that appears around
things, such as this line right here.
| | 02:00 | Then we've got a section called
AutoFormat that has a couple of galleries, and we
| | 02:05 | can select some predefined styles.
| | 02:07 | The AutoFormat galleries, it's a
scrolling list here, or we can click this
| | 02:10 | button, the Downward-Pointing Arrow, to
see the full list of galleries. And we
| | 02:14 | can go through and select different ones
to see different color options for our form.
| | 02:17 | For instance, I'll click on this
Northwind one here, and it'll change it to an
| | 02:20 | orange and stripes format, or I can
select one called None here, and I like
| | 02:26 | the None one because it
removes all the formatting.
| | 02:29 | It gives me a blank slate, creates
a white background, black text. And I
| | 02:33 | like to use this as a good starting
point when I want to customize my forms.
| | 02:36 | So I'm going to select None from the
AutoFormat gallery and then we'll go ahead
| | 02:39 | and change some of the colors.
| | 02:40 | I'm going to select the top of our
form here and that'll highlight and
| | 02:45 | I'll change the color of this to
Green 4 by going to this Paint Bucket
| | 02:49 | icon and selecting Green 4.
| | 02:50 | I'll also remove the line that appears
around our oil name by selecting that
| | 02:56 | field, going to the Controls section,
and changing Line Type to this Transparent
| | 03:01 | option that appears at the top.
| | 03:03 | And incidentally, that's exactly the same
as going to Line Color and Transparent.
| | 03:07 | So either of other those
options will do the same thing.
| | 03:09 | That will remove the line around my oil
name and that will clean it up quite a bit.
| | 03:14 | So that's all I really
want to change on this form.
| | 03:16 | Let's go ahead and switch
to the OrdersByProduct form.
| | 03:20 | This one, I'll do the same thing.
| | 03:21 | I'll switch into Layout View by
clicking the View menu up here in the left.
| | 03:24 | And I'm going to start with a clean slate.
| | 03:27 | So I'll go to AutoFormat
and choose the None option.
| | 03:31 | We'll change our header color to the
same, green, so I'll click on the header,
| | 03:35 | click on the Paint Bucket which is
already filled with that green paint, or I
| | 03:40 | can go ahead and click the Down
button and select Green 4 again.
| | 03:42 | I want to change these rows here so
that they alternate color, and I can
| | 03:48 | click on one of the rows over here on
the right and we'll select that entire row
| | 03:51 | and that's actually selecting
the background for each record.
| | 03:55 | And here on the Font section, even though
this isn't really a font option, but
| | 03:59 | that's where the button is...
| | 04:00 | So in the Font group, I've got this
button here for Alternating Color, and I can
| | 04:04 | select maybe Green 2.
| | 04:06 | That will give it a light
green color every other row.
| | 04:09 | Let's go ahead and clean these up a little.
| | 04:12 | We want to shrink these so
that I can fit more on a page.
| | 04:16 | Select from one of the fields here. For
instance under this Last Name, I'll click
| | 04:19 | Oneil, and from there, I can drag
on the bottom to move this up a little.
| | 04:24 | That'll tighten up the display.
| | 04:25 | I also want to remove
the boxes around each name.
| | 04:28 | Access likes to put boxes around
everything, and it really just adds to the
| | 04:31 | visual clutter, so it doesn't really
add anything to the legibility of my form.
| | 04:35 | So I'm going to go ahead and select all
of these at once by Shift+clicking and
| | 04:38 | then click across, so FirstName,
OrderDate, and the OrderID.
| | 04:42 | And I'll remove the lines from all of these
by going to the Controls section, Line
| | 04:47 | Type, and then None.
| | 04:48 | I can also click Line Color and Transparent.
I'm going to select the Record Selector over here
on the left to deselect everything else
| | 04:55 | so I can see how we're looking so far.
| | 04:58 | It's looking pretty good.
| | 05:00 | One other thing I want to do is change some
of the spacing that I have here in my columns.
| | 05:05 | I want to take Date and give it a
little more space and maybe reduce the space
| | 05:09 | that FirstName is taking.
| | 05:11 | So I'll click on this FirstName field
and when I do that, I notice, just like
| | 05:15 | with that Product Selection form from
the last movie, that the label is actually
| | 05:19 | taking up all the space here.
| | 05:20 | It's actually covering up these other three.
| | 05:22 | So I need to make that a little smaller so
I can grab the objects that are underneath.
| | 05:25 | Now I can click on FirstName and I can
make this column a little bit narrower.
| | 05:30 | I can click on OrderDate and I'll drag
on the right to make that a little wider.
| | 05:36 | I can also center these so that
they appear centered in the column.
| | 05:38 | I'll press the Center Alignment button
and I'll do the same for the data below.
| | 05:44 | Click on the data and Center Align.
| | 05:46 | Now, since Date is a numeric field, the
Formatting options have appeared and I
| | 05:52 | can change the way that my dates are formatted.
| | 05:54 | From the drop-down menu, I can select,
for instance, Medium Date and that'll
| | 05:58 | change how the dates are displayed.
| | 05:59 | Now the last thing I want to do is
change the titles for the row headers, and
| | 06:04 | I'll double-click on those to activate them.
| | 06:06 | Then I'll just click my mouse where I
want them to have a space in here.
| | 06:09 | So I'll double-click on OrderDate, add a
space, double-click on FirstName, add a
| | 06:13 | space, and double-click on LastName,
and I'll move my cursor and add a space.
| | 06:19 | So, there's my form, and let's go
ahead and close these, and we'll save the
| | 06:23 | changes, and we'll look at
how our workflow is shaping up.
| | 06:25 | I'll close this form.
| | 06:27 | I'll save the changes, and I'll close the
ProductSelection form and save those changes.
| | 06:33 | Now when I want to review my
OrdersByProduct, I'll open up my ProductSelection
| | 06:37 | form, I can scroll to the
product that I'm interested in.
| | 06:40 | For instance, I'll stop on record 7,
this Extra Virgin Oil, 16 ounces, and
| | 06:45 | say OrdersByProduct.
| | 06:46 | Then it opens up the OrdersByProduct
form and it shows me all of the orders that
| | 06:52 | included that particular product.
| | 06:54 | The Layout View gives you some easy-to-
use tools for organizing the flow of your
| | 06:57 | forms and making sure
that everything is aligned.
| | 07:01 | For even more flexibility, we'll dive
into the full design environment, and we'll
| | 07:04 | take a look at 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
View are easy ways to get started creating
| | 00:05 | your database's forms.
| | 00:06 | But the real power and flexibility lies
in utilizing the full design environment
| | 00:11 | for creating forms from scratch.
| | 00:13 | Once you understand what's going on
under the hood, creating your own forms that
| | 00:17 | meet your exact specifications
and needs will become second nature.
| | 00:20 | Let's start by creating a Rolodex
style Employee Directory for Two Trees.
| | 00:24 | Now I have got a completed
example here in my database called
| | 00:28 | frm_Employees-Complete. I will
double click on that to open.
| | 00:32 | Now this is an example of
what we might be shooting for.
| | 00:35 | Now, because some of the design
decisions that we are going to make while
| | 00:38 | creating our form are going to be a
little bit subjective, our finished form
| | 00:42 | might be a little bit different than this one,
but this is the idea of what we are shooting for.
| | 00:46 | We are going to have a scrollable
form that will show all of our employees
| | 00:50 | within the company, we will have their
photo and their name and some details
| | 00:53 | about the employment.
| | 00:54 | And it will have this header at the
top with a title, and that part doesn't
| | 00:57 | scroll as we move through the records.
| | 01:00 | And it's got a couple of design
elements such as the shaded background on every
| | 01:03 | other record and some playful fonts
here for the employee ID number and the
| | 01:07 | state that they are in.
| | 01:09 | So let's see how we can
duplicate this using the Design view.
| | 01:12 | I will go ahead and close this format
and we will start a new one by going to
| | 01:16 | Create and then Form Design in the Forms group.
| | 01:19 | This will open up a blank form in Design
view and I can see that I have got this
| | 01:23 | grid here in the background
with these dots in the gridlines.
| | 01:26 | And this is an alignment grid; this
will help us organize our elements within
| | 01:29 | the form and make sure everything is lined up.
| | 01:31 | So these dots aren't going
to show on the final form.
| | 01:34 | Now when I start a new form in Design
view, the first thing I needed to do is
| | 01:38 | link this form to the particular
table that the data will be pulled from.
| | 01:42 | Now with that open, you can open up
the field list by going to Design ribbon
| | 01:47 | and clicking on Add Existing Fields.
| | 01:48 | That will open up this Fields List here
and from it we can see a list of all the
| | 01:53 | tables within our database, and if I
open these up by pressing the plus
| | 01:56 | button, we'll see all the fields within each table.
| | 01:59 | Let's go ahead and open up my
Employees table, tbl_Employees.
| | 02:05 | And I will find the FirstName field and I
will double-click on that to add that to my form.
| | 02:11 | Now when I do that, a couple of things
happen. First I get a FirstName field
| | 02:15 | here put into my form.
| | 02:17 | And now that I have also told my
form that the data will be becoming from
| | 02:21 | the Employees table, it changes the
field list a little bit to show me some
| | 02:25 | related tables and the fields that
are within those tables that are related
| | 02:29 | to my Employees table.
| | 02:31 | Over here on my form, I have got
these two objects that appear.
| | 02:34 | This one here on the right with the
white background is the text box and that's
| | 02:39 | where the data will show up as
I scroll through the records.
| | 02:42 | The one on the left is a label, and every object
that you add to your form will come with a label.
| | 02:48 | The label is a static piece of text that just
tells the end-user what appears to the right.
| | 02:52 | If I switch into Form view at this
point by clicking up on the View
| | 02:55 | button, this Form button,
| | 02:57 | I can see that the label still reads
FirstName, whereas the text box changed to the
| | 03:01 | data for the first record.
| | 03:02 | If I scroll through the records down
here on the bottom, I could see the first
| | 03:06 | name of each of my employees.
| | 03:07 | Let's go back into Design
view by clicking View > Design View.
| | 03:13 | Let's add the other fields to our
form and then we'll move them around and
| | 03:16 | arrange them into position.
| | 03:17 | From the Field list, I will double-
click on LastName, Phone number, Address,
| | 03:24 | City, State, Zip, Email, HireDate.
| | 03:30 | I don't want everybody's hourly pay
showing up, so I will skip that one.
| | 03:33 | We will do Department, Photo, and
then back at top, the Employee ID number.
| | 03:39 | Now I have got all of these listed on
my form, some of them are overlapping a
| | 03:43 | little bit, like the LastName appeared
right on top of first name. If
| | 03:46 | I click on it I can drag it by using
this orange handle around the edges. So I
| | 03:51 | will click and once I get the double
headed arrow, I can drag off of it.
| | 03:54 | So now they are separated.
| | 03:56 | Now when you move an
object the label comes with it.
| | 04:00 | So for instance, when I just move that LastName
box, the label for LastName moved with it.
| | 04:04 | If I drag on the object around the
edge right anywhere where I can see the
| | 04:08 | crossed arrow, that'll move both the
label and the object at the same time.
| | 04:13 | So for instance, if I grab here, click and drag, that
will move the text box and the label with it.
| | 04:19 | If I use the handle in the upper left-
hand corner, I get the same icon, but I can
| | 04:24 | move the objects independently of each other.
| | 04:25 | So for instance, I could take this last
name and drag it using the handle on the
| | 04:30 | upper left corner and put it below
the label, and it moves it like that.
| | 04:35 | Now if I drag using the orange box
around the edge it maintains that new special
| | 04:39 | relationship between the text box and the label.
| | 04:42 | The other handles around the edge will
allow you to resize, so I could drag it
| | 04:46 | to the left, drag it down to the
left, make it taller, make it wider.
| | 04:50 | So let's go ahead and arrange these onto
our form, and we are going to move the
| | 04:55 | objects around and put them into position.
| | 04:58 | Now some of the labels might be a
little bit redundant. For instance, the data
| | 05:02 | that appears in First and Last Name, I
am going to be able to understand that
| | 05:05 | that's the first and last name for my
employee; I don't need a label that says
| | 05:08 | this is the first name, this is the last name.
| | 05:11 | So I can get rid of some
of these redundant labels.
| | 05:12 | I am going to get rid of the
FirstName label by clicking on it and pressing
| | 05:16 | Delete. LastName, click on
that label and press Delete.
| | 05:20 | I also want to get rid of some of
these that are associated with the address.
| | 05:25 | I can leave one that says address, but
for City, State, and Zip, I am going to
| | 05:29 | have all of these boxes grouped together.
The end users, I think, will understand
| | 05:32 | that Address, City, State, and
Zip all are part of the address.
| | 05:35 | I don't need a label that says now this
is the City, now this is the State, now
| | 05:39 | this is the ZipCode.
| | 05:41 | So we will get rid of these three.
| | 05:42 | I can select multiple objects by Shift+
clicking, so I will press Shift and click
| | 05:47 | on the other two for City, State, and Zip,
and I'll press the Delete button to get
| | 05:51 | rid of those labels.
| | 05:52 | Photo is the same way, I don't need a
label to tell me that this is a picture.
| | 05:55 | I will click on the
Photo label and press Delete.
| | 05:59 | Let's start moving these into position.
| | 06:00 | I will drag my FirstName box and I am
going to put it up here just to the right
| | 06:06 | of the two inch marker. I will take LastName
and drag it and put it to the right of that.
| | 06:11 | I am going to move Phone number below FirstName.
| | 06:18 | And I am also going to take the Email
address and put that below Phone number.
| | 06:21 | So click on Email, drag that up.
| | 06:24 | Below that, I am going to display the
address. We will click on Address and I will
| | 06:29 | move that over. Below Address, I can
put City, State, and Zip all in one line.
| | 06:34 | So City, I will drag that and put it in position.
| | 06:39 | State, I am going to drag that to the
right of City. And Zip, I will put to
| | 06:43 | the right of State.
| | 06:44 | HireDate and Department, I will go ahead and
put it below the address, and maybe I'll
| | 06:52 | put Department over here to he right.
| | 06:53 | I can also use the Arrow keys to
move things around, so I can nudge the
| | 06:57 | objects on the form by pressing the
Down key and the Right key to move
| | 07:01 | things into position.
| | 07:02 | Let's go ahead and move these labels over.
So that I have a little bit of room,
| | 07:06 | I want to put the photo
over here on the left side.
| | 07:08 | So I want to move these labels for
Phone number, Email, Address, and HireDate
| | 07:12 | out of the way a little bit.
| | 07:13 | I will go ahead and click on HireDate
one and using the brown handle to move it
| | 07:17 | independently of the HireDate text
box, I will scroll that over a little.
| | 07:23 | And once it's in position, I can actually
use its position align the other objects.
| | 07:27 | So if I drag a box around all four of
these, I can go up to the Arrange tab
| | 07:33 | and click on this Right button and that will
align all of the objects to the rightmost object.
| | 07:37 | And so they all line up.
| | 07:39 | Now I have got room for my photo, I
will click on the Photo box, drag that up,
| | 07:45 | and I can make it a little bit bigger.
| | 07:46 | Now I have got something special in mind
for the Employee ID number. What I would like
| | 07:51 | is for that to display on top of the
photo, kind of like a little transparent
| | 07:54 | box or an ID tag that you
might see on a badge or something.
| | 07:57 | So I am going to get rid of this label,
I will click on the EmpID label on the
| | 08:02 | left, and press Delete.
| | 08:03 | And I will drag the EmpID number field
and I will drag that, and I am actually
| | 08:07 | going to put it over the Photo box.
| | 08:10 | So you can see the Photo
box here in the background.
| | 08:12 | Now because I have related tables
here, I am pulling in information from my
| | 08:15 | employees data table, I can relate
other data tables within my database.
| | 08:19 | For instance, I have got a two letter
state abbreviation stored with each employee.
| | 08:23 | And from that, in my States lookup
table I can grab the full StateName.
| | 08:28 | So I am going to take this, in the
fields available in related tables section of
| | 08:32 | my Field List, and I am going to double-click
on StateName to add that to my
| | 08:37 | form. Get rid of the label, and I will move that up.
| | 08:41 | Now I am going to have a little bit of
fun with the StateName label in the next
| | 08:44 | movie, but we can finesse these
positions as we continue to develop our form and
| | 08:49 | truth be told, form design is going to
require a lot of back-and-forth as the
| | 08:53 | final layout gets further refined.
| | 08:54 | Let's take a look at where we are at this point.
| | 08:56 | I am going to go back to the Design
tab and click the View button, and switch
| | 09:01 | back into Form view.
| | 09:03 | Incidentally, I could also to go the
Home tab and find same button there.
| | 09:06 | I'll switch back into Form view and we
will see my form there with elements in
| | 09:10 | position. Not all of my employees have
photos associated with them, so I can
| | 09:14 | scroll through until I find one, say
record number six for Nicholas Bond,
| | 09:18 | has a photo attached.
| | 09:20 | One thing before I leave here that you can
notice is that some of these boxes need
| | 09:23 | to be a little bigger. Address for
example, Email address need to be a little
| | 09:28 | bit bigger. Same with Department ID.
| | 09:31 | So we are going to make these a little bit
bigger. Switch back into Design view real
| | 09:34 | quick, by clicking the
View down button > Design View.
| | 09:39 | And we will make Address wider by
clicking on it and dragging to the right, and
| | 09:43 |
make Email wider by dragging to the right
| | 09:47 | And I will make Department wider. I drag
this to the left, I have room there, and we
| | 09:53 | will see how that looks.
| | 09:54 | Switch back to Form view, and now
everything is looking like it's fitting
| | 09:58 | in its box properly.
| | 09:59 | Now that we have our Form field
elements in roughly the positions that we want
| | 10:02 | them, we could take a look at some of the
formatting options that are available to us.
| | 10:06 | We will pick it up there in the next movie.
| | Collapse this transcript |
| Formatting the form| 00:00 | In the last movie we looked at how we
can add references to our data tables
| | 00:03 | using the Field List and how we
can arrange fields within our form.
| | 00:08 | Now let's turn our attention to formatting.
| | 00:10 | The Design tab has many of the
common formatting options that you might
| | 00:14 | recognize from other
programs such as Word or Excel.
| | 00:16 | We are going to continue
working with the Employee Directory.
| | 00:19 | I have gone ahead and saved it with
the name Employee Directory, and we will
| | 00:22 | switch into Design view.
| | 00:25 | On the Design tab of the Ribbon, we
have got all of our Formatting options.
| | 00:30 | Let's go ahead and format our objects, and
we can start with this first name field.
| | 00:34 | I am going to click on FirstName to select it
and we will take a look at our Font options.
| | 00:39 | First, I want our first and last name
to stand out from the rest of the data.
| | 00:44 | I am going to go ahead and change its
font size a little bit from 11 up to 14.
| | 00:48 | I am also going to make it bold, so I
will click on this B icon to make it bold.
| | 00:55 | Now, I can apply the formatting that
I've chosen for my FirstName field to the
| | 00:59 | LastName field by using this
paintbrush icon called the Format Painter.
| | 01:03 | I will click on the paintbrush and
then I will click on the LastName field to
| | 01:07 | apply the same formatting
from FirstName to LastName.
| | 01:09 | I want to take the labels here for
Phone number, Email, Address, HireDate and
| | 01:15 | Department and I want to actually make
that a little bit lighter gray. I don't
| | 01:19 | want those to stand out and
confuse with the data a little bit.
| | 01:22 | So we are going to reduce those in
the visual hierarchy on the screen by
| | 01:26 | turning those to a gray color instead of black.
| | 01:28 | I will click on the Department label,
and I will Shift+Click and click HireDate,
| | 01:33 | Address, Email, and Phone number to
change their properties all at once.
| | 01:39 | For them, I am going to change the Font
color using this button up here on the
| | 01:42 | top; it's got this A with
a red bar underneath it.
| | 01:44 | I will use the drop-down menu and
I'll choose this one here, Light Gray 5.
| | 01:51 | Now I want to go ahead and remove the
boxes from around all my form fields.
| | 01:56 | If I go back into Form view to take a
look at that, you will see that we have
| | 01:59 | boxes around everything and it actually
really adds a lot of clutter to our form.
| | 02:03 | So I want to get rid of those boxes.
| | 02:06 | Go back into Design view by
clicking the View button > Design view.
| | 02:09 | And we can select every object on our
form all at once by using this button here
| | 02:13 | in the Control section of the Design
ribbon called Select All, or you can use
| | 02:17 | the keyboard shortcut Ctrl+A. So
select everything on our form and we will
| | 02:22 | change the Line style to Transparent.
| | 02:26 | Again, you can also use the Line color as
transparent and that will do the same thing.
| | 02:31 | Now all my boxes are gone, or at least...
they not gone; they are just invisible.
| | 02:36 | I go ahead and play with the
fonts for Employee ID number and StateName.
| | 02:39 | For EmpID, I want that to appear on top
of the photo, so I don't want a white box
| | 02:44 | appearing behind it.
| | 02:45 | I am going to change its
background color here to Transparent.
| | 02:50 | I also want to make it big and bold
so it stands on top of every photo.
| | 02:55 | I am going to change its font to Impact
by clicking the Font down button and
| | 03:00 | scrolling through my list until I find Impact.
| | 03:03 | I am going to move it to the right side of
the field by clicking the Right Align button.
| | 03:10 | And I am going to change
its color to a dark gray.
| | 03:12 | I will go to the Font Color and
I am going to choose Dark Gray 3.
| | 03:17 | Now I am going to apply the same
formatting to the StateName field over here.
| | 03:22 | Again, I will use the Format Painter to that.
| | 03:24 | I will click on the
paintbrush and click on StateName.
| | 03:27 | Now the StateName, I am going to
have a little bit of fun with this in my
| | 03:30 | report. I am going to make this
really big and just use it as a graphical
| | 03:33 | element behind everything else.
| | 03:35 | I am going to drag its box
and make it really big here.
| | 03:39 | I am going to move it and put it
right up against the edge of my form.
| | 03:43 | In fact, I am actually going to make
my form a little bit wider and I can do
| | 03:46 | that by clicking on this edge here
and dragging out to about the 8-inch mark.
| | 03:50 | I will move StateName all
the way over to that edge and
| | 03:54 | I am going to move it up.
| | 03:55 | Now I am going to make it a little bit taller.
| | 03:59 | And I am going to change its font
size to something really big, like 48.
| | 04:03 | Now the whole StateName is not going to
fit in this box; it's only going to show the
| | 04:06 | top of the StateName, but again, we are
just having fun with this. It's going to
| | 04:10 | be a little visual element, and I
will make this little bit bigger.
| | 04:14 | And now I want it to appear behind everything else.
| | 04:16 | So let me go to the Arrange tab and put
Sent to Back and that will put it behind
| | 04:22 | all these other fields.
| | 04:23 | Let's take a look at how our form is looking.
| | 04:26 | I can go to the Home tab, switch into
Form View or again, I can go to Design tab
| | 04:32 | and switch it to Form View;
either one will do the same thing.
| | 04:34 | And we will take a look at how this is working.
| | 04:37 | Let's scroll to one that has a photo
attached. I am going to scroll to Record
| | 04:40 | Number 6, and we can see Nicholas Bond. Here
is his ID number appearing on top of the photo.
| | 04:45 | I've got the State Name appearing big
and bold in the background and taking a
| | 04:50 | look at this, I can see couple of other
things that I might want to change here.
| | 04:53 | For instance, numbers tend to
appear to the right of their form fields.
| | 04:58 | So the Zip code is appearing all
the way on the right side. Hire Date,
| | 05:02 | appearing on the right side.
| | 05:04 | Also I want to adjust my font a little
bit for this Employee ID number and maybe make
| | 05:07 | the State Name a little bit lighter
so it recedes into the background.
| | 05:11 | Go back to the Form View to make those
changes. Go to View > Design View. First,
| | 05:17 | let's change the alignment of the
Hire Date and Zip Code boxes. I will click on
| | 05:20 | HireDate, Shift+Click on Zip, and
then force those to be left aligned.
| | 05:27 | Nothing really changes on the screen here,
but it will change when we view it in Form View.
| | 05:30 | EmpID--I am going to make that little
bit bigger, change its font, how about
| | 05:36 | 24. And both of these, I think, were a
little bit dark. I am gong to make them a
| | 05:41 | little bit lighter.
| | 05:42 | So EmpID, I am going to Shift+Click
on StateName here. I am going to change
| | 05:46 | that color. How about the medium gray
one? That should stand out a little bit
| | 05:51 | better on the photo.
| | 05:52 | Let's go into Form view. We'll scroll
to record number 6, and we can see the number
| | 05:58 | appearing on top of the photo.
| | 06:00 | The Design tab puts many common
controls right up front in the ribbon.
| | 06:03 | Changing fonts and colors and line
styles here can be really quick, especially
| | 06:07 | if you make good use of the Format Painter tool.
| | 06:08 | But for some serious control over
every aspect of your form, we are going to
| | 06:13 | need to dig a little bit deeper and
explore the Property Sheet, and we will do
| | 06:17 | that in the next movie.
| | Collapse this transcript |
| Modifying the form through its properties| 00:00 | The Design 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 Font Color and Font Size.
| | 00:08 | But those options are only the tip of
the iceberg when it comes to the vast
| | 00:11 | number of properties
that each element possesses.
| | 00:14 | To see the full list, we'll have to
dive into the Property Sheet for our form.
| | 00:18 | And we can do that by switching into
Design View in the Ribbon. I'll click the
| | 00:21 | View Down button, and then Design View.
| | 00:23 | Now I can open the Property Sheet by
going to the Design tab and clicking the
| | 00:28 | Property Sheet button, way
over here on the right side.
| | 00:31 | I could also use a couple of shortcut keys.
| | 00:33 | I could press Alt+Enter, which will
toggle the Property Sheet on and off, or I
| | 00:38 | can press the F4 key.
| | 00:39 | And the F4 key will also toggle it on or off.
| | 00:43 | So however you'd like to do it, go
ahead and turn on the Property Sheet.
| | 00:45 | Now as I click around the objects on
my form--for instance, I'll click on
| | 00:49 | this FirstName field here--we'll notice
that it jumps to the properties for FirstName.
| | 00:53 | If I click on LastName, it moves
to the properties for LastName.
| | 00:57 | Every object on your form has
properties associated with it.
| | 01:02 | Even this blank area in the
background has properties.
| | 01:04 | If I click back here just on the dot
grid where I don't have any objects,
| | 01:08 | it'll switch to the properties for the
Detail section, which is basically the background.
| | 01:11 | And if I click between these two rulers
here--this little box right here--it'll
| | 01:16 | select the properties for the entire form.
| | 01:19 | Now I can choose other objects by
using this drop-down menu, and this list can
| | 01:23 | get pretty long just because
everything has properties including things like
| | 01:27 | Labels and the Photo.
| | 01:28 | So let's go ahead and take a look at
the Properties for the overall form.
| | 01:32 | You can choose it from the list here,
or you can choose it by clicking the
| | 01:35 | button between the rulers.
| | 01:37 | Now the Property Sheet is
split up into five different tabs.
| | 01:40 | Let's take a look at them in turn.
| | 01:42 | I'll click on the Format tab.
| | 01:44 | Now there are a lot of properties here
and we're not going to go through every
| | 01:46 | single property that's
available in the Property Sheet.
| | 01:48 | So I'm going to call out a few that
I think are particularly interesting.
| | 01:51 | The first one I want to take a
look at is this Caption here.
| | 01:55 | Caption changes what displays on
the tab when you view it Form view.
| | 02:00 | Right now what's displaying is
EmployeeDirectory, CamelCase with no space in the name.
| | 02:05 | That's because that's what the form
is named here in my Navigation pane.
| | 02:08 | I'm going to go ahead and change the
Caption to Employee Directory with a space in it.
| | 02:15 | Now when I view it in Form View, it'll
display Employee Directory with a space.
| | 02:19 | Let's move down the list.
| | 02:21 | The Default View right
now is set for Single Form.
| | 02:24 | If I click there, we'll notice it
becomes a drop-down menu and I can choose from
| | 02:28 | a variety of choices.
| | 02:29 | I've got Continuous Forms, Datasheet,
PivotTable, PivotChart, and Split Form.
| | 02:35 | For our Rolodex-style Employee
Directory, I'm going to choose Continuous
| | 02:40 | Forms, which will make our records appear
one on top of the other in a scrolling list.
| | 02:44 | And right now here, I want to take a
quick aside here and point out something
| | 02:48 | that's kind of interesting with Access.
| | 02:49 | If you go up to the Create tab and
look at this Forms section in the Create
| | 02:54 | Ribbon, you'll notice a bunch of
buttons here for creating different forms.
| | 02:57 | We've got Split Form, Multiple Items
which is just a continuous list, PivotChart.
| | 03:03 | If I click on More Forms, I'll
see Datasheet and PivotTable.
| | 03:09 | All that these do, they create basic
forms that have this Default View set to
| | 03:13 | their respective property.
| | 03:15 | Instead of creating a form in Split
Form view, you can create just a basic form
| | 03:20 | and change it to a Split Form on your own.
| | 03:21 | Let's go back into Design View. We'll
continue taking a look at these properties.
| | 03:26 | You can move up and down in the Property list
by pressing the Arrow keys on your keyboard.
| | 03:30 | So I can press Down to move down the
list or I can press Up to move up the list.
| | 03:34 | I can also press Right to move
down or Left to move back up.
| | 03:39 | And if you like working on the other
side of the keyboard, the Tab key will move
| | 03:42 | you down, and pressing Shift+Tab
will move you back up.
| | 03:47 | Let's go down to the Allow Form View.
| | 03:49 | These next few properties, the Allow
whatever View, will change what appears in
| | 03:53 | this View drop-down menu.
| | 03:55 | Right now I've got Form View, Datasheet,
PivotTable, PivotChart, Layout, and Design View.
| | 04:00 | We can turn some of these off if
we're not going to be using them.
| | 04:03 | Now Form View, we definitely want to use.
| | 04:04 | That's the default view for working with the data.
| | 04:06 | So we'll leave that one on Yes.
| | 04:09 | But Datasheet View, I can choose
this drop-down menu and choose No.
| | 04:12 | I'm going to press Tab to go to the next one.
| | 04:14 | Allow PivotTable View.
| | 04:16 | I'm going to change this to No as well.
| | 04:18 | Now since I know that the only two
options are Yes and No, I can actually press
| | 04:22 | N on the keyboard to jump to the No option.
| | 04:24 | Then press Tab to go to Allow
PivotChart View and again, to say No.
| | 04:29 | And Allow Layout View, I'll
change that to No as well.
| | 04:33 | So moving through the Property Sheet with
the keyboard can be a really fast way to
| | 04:36 | navigate all these properties.
| | 04:37 | Let's keep going down the list and we'll find
some other properties that we can work with.
| | 04:41 | I'm going to move down the list
using the Arrow keys until I get to the
| | 04:45 | Record Selectors property.
| | 04:46 | Right now this is set to Yes.
| | 04:48 | You'll notice the one below it,
Navigation Buttons, is also set to Yes.
| | 04:53 | Let's switch into Form View so I can
point out what these two objects are.
| | 04:55 | I'll switch my form into Form View
and I could take a look at my form.
| | 05:00 | Right now on the left side, I've
got this gray bar with an arrow on it.
| | 05:04 | This is my Record Selector.
| | 05:06 | The Record Selector in a form is a
little bit weird; let me point out what a
| | 05:09 | record selector looks like in a table.
| | 05:10 | I'm going to go ahead and
open up my Employees table.
| | 05:14 | In a table, a Record Selector is the
same area; it's the same box right here.
| | 05:17 | If I click on that, it
selects that entire record.
| | 05:19 | Let's go ahead and close the table.
| | 05:22 | In a form, it does the same thing, except the
record is almost the full height of my form.
| | 05:26 | If I click on this box, basically,
I'm selecting the same record.
| | 05:30 | That's the Record Selector.
| | 05:32 | On the bottom, I have the navigation buttons.
| | 05:34 | These are these forms right down here.
| | 05:35 | Let's go ahead and turn both of these
off on our form. We're not going to need them
| | 05:39 | for our scrolling directory.
| | 05:41 | Switch back into Design View and you'll
notice right here, my Form View and Design
| | 05:46 | View are the only two buttons that I've got
right now because I changed those options.
| | 05:48 | We'll go back over here and we'll go to the
Record Selectors and I'll change that to No.
| | 05:55 | Navigation Buttons,
we'll also change those to No.
| | 05:59 | Let's go ahead and take a look at the Data tab.
| | 06:01 | The Data tab is all about how our
form connects to our data tables.
| | 06:05 | This Record Source right here, you
can see is a SELECT statement, and if
| | 06:09 | you've taken queries in depth,
you might recognize this as SQL.
| | 06:13 | Basically when we were adding
existing fields to our form from our data
| | 06:16 | tables, Access in the background was creating
a SELECT query to pull out those information.
| | 06:21 | This Recordset right now is set to Dynaset.
| | 06:25 | That means that this is a dynamic link
between the form that we're creating and
| | 06:30 | the data table that it's based on.
| | 06:31 | I could edit the record's
contents right here on the form.
| | 06:35 | Now for my Directory List, I want
this to be a reference that's real.
| | 06:37 | I don't want people to be able to go in and
change phone numbers or names for instance.
| | 06:40 | So I'm going to set Recordset
Type from Dynaset to Snapshot.
| | 06:45 | That'll prevent any edits from
happening while people are viewing the form.
| | 06:49 | Let's take a look at the Event tab.
| | 06:51 | The Event tab has all the different
ways that can fire macros or Visual Basic
| | 06:56 | code within our database.
| | 06:58 | I can load a macro, for instance, when
the form loads using the On Load event.
| | 07:03 | I can load a macro, for instance, on
the On Click event when somebody
| | 07:07 | interacts with the form.
| | 07:09 | I've got a Double Click event right
down here, so I can load different macros
| | 07:12 | when somebody double-clicks on something.
| | 07:15 | I can also fire a macro when somebody
clicks the mouse down and a different
| | 07:19 | macro when they lift their finger up.
| | 07:21 | There are lots of different ways that you
can trigger events within your database.
| | 07:25 | And all of these can add
interactivity to your forms.
| | 07:27 | Let's take a look at the Other tab.
| | 07:30 | The Other tab has miscellaneous options.
| | 07:32 | For instance, this Pop Up one here will
create a floating window instead of a tab view.
| | 07:38 | Modal will make your form force an interaction.
| | 07:41 | In other words, the user can't open
the form and transfer to something else
| | 07:45 | without closing the form first.
| | 07:47 | And finally, the All tab has a listing of
all of the properties from the other four tabs.
| | 07:52 | They're just in one long master list.
| | 07:54 | So if you can't find something in the
first four and don't remember where it's
| | 07:57 | at, you can always go to
the All tab and find it here.
| | 08:00 | For instance, there's one property
that I want to change called Order By.
| | 08:04 | Let's see, if we can find it in the All tab.
| | 08:06 | Let's scroll down the list, and here it is, Order
By, and then the next one, Order By On Load.
| | 08:15 | The Order By property will
organize the records in a report.
| | 08:19 | So we could tell Access
how we want to sort these.
| | 08:21 | We could sort by, for
instance, Last Name or Employee ID.
| | 08:25 | I'm going to go ahead and
type EmpID and press Tab.
| | 08:30 | Order By On Load, I want to
make sure that's set to Yes.
| | 08:33 | This will order all of my records
based off of the Employee ID number.
| | 08:36 | You could also choose to order the
records based off of the LastName which will
| | 08:39 | provide an alphabetical list of your employees.
| | 08:41 | Now incidentally, I found the
Order By here in the All tab.
| | 08:47 | It normally resides in this Data tab.
| | 08:49 | So I skipped it in the Data tab but I could
still pick it up again in the All tab.
| | 08:53 | Let's now take a look at our form and
see the changes that have been made based
| | 08:57 | off of these properties.
| | 08:58 | I'm going to go ahead and
close the Property Sheet.
| | 09:01 | And before I view the form, I
need to change its size a little bit.
| | 09:04 | Since we changed it to a continuous form,
it's going to repeat over and over again.
| | 09:08 | The piece that will repeat is this
entire Detail section, including all of this
| | 09:12 | white space below all of my fields.
| | 09:14 | So I'm going to go ahead and scroll
down to the bottom, I'm going to grab the
| | 09:18 | bottom of my form, and I'm going to move
it way up to the top, so it's tight
| | 09:21 | against the bottom here.
| | 09:23 | Now the piece that will repeat is this
section right here, and it'll repeat over
| | 09:26 | and over again for each employee.
| | 09:28 | Let's go ahead and view it in Form View,
and now I've got a scrolling list. I can
| | 09:33 | scroll through and see all
of my employees within the company.
| | 09:36 | Now the Property Sheet is an extensive
resource for everything that you might
| | 09:40 | want to control in your database.
| | 09:41 | You will be happy to know that both forms
and reports share this common interface.
| | 09:46 | While some of the most common settings
could be changed through the Ribbon tabs,
| | 09:51 | the Property Sheet as generally my go-to
resource because it gives access to all
| | 09:54 | of the available settings,
not just the most common.
| | 09:57 | Often, I find the Property Sheet
to be a great discovery resource as well.
| | 10:02 | It allows you to find settings and
options that you didn't even know you wanted
| | 10:05 | to change until you saw them on the list.
| | 10:07 | I definitely encourage you to go through
the Property Sheet and explore some of
| | 10:11 | those options.
| | Collapse this transcript |
| Adding a header and some polish| 00:00 | We're going to wrap up our Rolodex-
style Employee Directory here and add a
| | 00:04 | final bit of polish using the Property Sheet to
modify some additional objects within our form.
| | 00:09 | We'll start by adding a Header section
that will appear at the top of our form
| | 00:12 | so that our end users will
immediately know what they're looking at.
| | 00:15 | As we go through this, keep an eye on
the Property Sheet and you'll see the
| | 00:19 | values change as we move
things around and make selections.
| | 00:21 | Now I've got the EmployeeDirectory
open; it's the same form that I saved
| | 00:24 | from the last movie.
| | 00:25 | I'm going to switch into Design View,
which is now really easy because I adjusted
| | 00:29 | those properties to only
show Form View and Design View.
| | 00:32 | So now it's just a toggle button instead
of having to go to this drop-down menu.
| | 00:35 | I'll toggle into Design View and I'm
going to open up the Property Sheet.
| | 00:41 | If you remember, the shortcut key is
either F4, Alt+Enter, or you can click on
| | 00:46 | the button up here on the Ribbon.
| | 00:48 | I'm going to click on the F4 key
to toggle my Property Sheet open.
| | 00:52 | Now let's go ahead and add the header section.
| | 00:55 | On this bar that says Detail,
I'm going to right-click and choose
| | 00:58 | Form Header/Footer.
| | 01:01 | That'll add a header section to the
top and a footer section to the bottom.
| | 01:04 | Now when Access displays my form,
it's going to put the header at the top.
| | 01:08 | It's going to have a Detail
section and that'll repeat for every record
| | 01:12 | in my Employees table.
| | 01:14 | Once I run out of records,
Access will display this form footer.
| | 01:18 | Now we're not going to display
the form footer in this exercise.
| | 01:20 | So let's go ahead and minimize that out.
| | 01:22 | We'll go to the very bottom and I'll
drag it up to the top, and that'll make the
| | 01:27 | form footer have zero height.
| | 01:28 | And effectively it'll get rid of it.
| | 01:29 | Now let's move up to the header section.
| | 01:32 | I'll make that a little bit
bigger so that I have room to work.
| | 01:36 | Above the Detail, I'll grab this bar
where it has this double-arrow and I'll
| | 01:39 | click and drag down.
| | 01:41 | Now I want to add a logo here at the top
and a label here or a title. I'll press
| | 01:47 | the Logo button in the
Controls group of the Design tab.
| | 01:50 | Press Logo and I'll browse to my
Exercise Files which I've got on my Desktop.
| | 01:55 | Exercise Files, Chapter 2, and
I'll choose this TwoTreesLogo file.
| | 02:00 | Go ahead and say OK and
Access drops that into my header.
| | 02:04 | I'm going to go ahead and
make that a little bit bigger.
| | 02:08 | Now I want to add a title. I'll press the
Title button here on the Controls group.
| | 02:13 | Access adds a title that's based
off of the Caption for my entire form.
| | 02:18 | If you remember, we set this in the last movie.
| | 02:20 | I can view it if I click on the
square between the two rulers.
| | 02:24 | That will choose the properties for the
form and we see that Caption is Employee
| | 02:28 | Directory with a space.
| | 02:29 | That's where this text is coming from.
| | 02:32 | Now let's change the properties
for the background, the header.
| | 02:35 | I want to make this a green color that
matches my logo and makes it look like
| | 02:38 | a complete unit.
| | 02:40 | I'll click on this area,
any blank area on my header;
| | 02:42 | it'll jump to the properties for the FormHeader.
| | 02:45 | Now right now the Background
Color is set to White and this is the
| | 02:48 | hexadecimal code for white.
| | 02:50 | I can use the drop-down menu to select
a predefined color or I can click on the
| | 02:55 | Build button to select a specific color.
| | 02:57 | And I click on Build and go down to
More Colors here and I happen to know the
| | 03:04 | color of this green here, so I'm going
to Custom and I type in those values.
| | 03:09 | So for Red, 185, for
Green 198, and for Blue, 170.
| | 03:15 | I'll go ahead and say OK, and I'll
press Enter to give me that change.
| | 03:21 | And you can see the background of the header
section changes to match the green in my logo.
| | 03:24 | Now let's take a look at
the properties of this title.
| | 03:27 | I'm going to click on that
and I'm going to change the font color.
| | 03:32 | I could change it up here in the Ribbon
or I could change it in the Property Sheet.
| | 03:35 | Let me find Foreground Color right here.
| | 03:39 | Right now it's set to Text Black.
| | 03:41 | I could change it to a different color, use
the Build button, and maybe I make it white.
| | 03:47 | I'll press Enter to commit the change.
| | 03:48 | I can also change the font,
right here in the Property Sheet.
| | 03:53 | I'll click on Font Name. We get a drop-
down menu that lists all the fonts on my
| | 03:57 | system, and maybe I'll choose a
different font. Let's say Georgia.
| | 04:02 | I can actually scroll through the list
or I can Backspace off of this and
| | 04:06 | just type in Georgia.
| | 04:07 | It will jump right on the menu
and I'll press Enter to commit.
| | 04:10 | I want to show you one other thing
here on the property sheet that I think is
| | 04:13 | kind of interesting.
| | 04:14 | I'm going to take the bounding box
for my title and I'm going to make it
| | 04:18 | a little bit bigger.
| | 04:20 | Now we've seen up here in the Ribbon
I've got options to Align Text Left, Align
| | 04:24 | Center, or Align Right, and I can
click on those and the text moves around
| | 04:29 | inside of its bounding box.
| | 04:30 | Inside of the Property Sheet, we
can find the Text Align property.
| | 04:35 | Right now it's set to Left.
| | 04:37 | If I click there and look at the
drop-down menu, I actually have a
| | 04:40 | fourth option, Distribute.
| | 04:42 | I can click on Distribute, and then
it'll distribute the letters across the
| | 04:46 | entire bounding box.
| | 04:47 | So now if I expand this,
they space out a little more.
| | 04:50 | I can collapse it and they get closer together.
| | 04:53 | So that's just an example where the
Property Sheet has more options available
| | 04:58 | than what's initially
presented just in the Ribbon.
| | 05:02 | There's our title, I've got my header
section set up, let's go ahead and take a
| | 05:05 | look at how our form is operating.
| | 05:06 | I'll switch back into Form View.
| | 05:10 | Now I've got a header at the section
at the top and if I scroll through my
| | 05:12 | records, we'll see that the header remains at
the top no matter what record I'm looking at.
| | 05:16 | Let's go ahead and change a couple of
properties here for the rest of the form
| | 05:20 | and we'll finalize everything up.
| | 05:22 | One more time we'll go back into Design View.
| | 05:24 | I'm going to click on the properties
for this Detail section, clicking anywhere
| | 05:29 | here in the background.
| | 05:32 | And what I want to do is
change the Alternating Color.
| | 05:34 | So instead of the form being completely white,
every other record will appear a different color.
| | 05:40 | For Alternate Back Color
right now it's set to No Color.
| | 05:43 | I can use the Build button
and I'm going to choose Green 2.
| | 05:50 | Nothing really changes here on the
form, but when we view it in Form View,
| | 05:52 | we'll see that color.
| | 05:53 | Let's see what that looks like.
| | 05:55 | I'll click back to Form View and now
every other row has a different color.
| | 05:59 | A couple of other things I want to change here.
| | 06:02 | Right now my Department has a white box
which is masking the text below it, so I
| | 06:06 | want to get rid of that.
| | 06:07 | I also want to move this Employee ID
number over just a little bit, so it lines
| | 06:11 | up better with the image below.
| | 06:13 | The last thing I want to do that is
add a line between each record to further
| | 06:16 | define where one record
stops and the next begins.
| | 06:19 | Let's go ahead and find those properties.
| | 06:21 | Go back into Design View. Let's move
that employee tag over, so I'll click on
| | 06:26 | Employee ID and it'll scoot that over a little.
| | 06:28 | We click on the Department field and
turn its Background Color here from
| | 06:34 | Normal to Transparent.
| | 06:36 | I can also change the rest of these
from Normal to Transparent as well.
| | 06:40 | We'll click Zip and I'm going to Shift+
Click State, City, HireDate, and we'll
| | 06:46 | just grab all of these for good measure.
| | 06:48 | Change those Background Styles to Transparent.
| | 06:50 | Finally, I want to add
that line between records.
| | 06:53 | That's a property of the form itself.
| | 06:55 | I'll click on the square box between
the rulers to select the form properties,
| | 07:00 | or again, I could have chosen
it from this drop-down menu.
| | 07:04 | The property that I'm looking for
can be found in the Format tab, and we're
| | 07:08 | looking for Dividing Line.
| | 07:10 | Let's go down here, there it is, Dividing Line.
| | 07:13 | It's currently set to No;
| | 07:14 | I'll change that to Yes.
| | 07:15 | Let's take a look at how our
form looks. And there we go!
| | 07:19 | Our completed form.
| | 07:22 | So there's our finished Employee Directory.
| | 07:24 | The Property Sheet is really the
master list of all the options that you have
| | 07:27 | within your database.
| | 07:28 | This applies not only to forms but
we'll see it again when we work with reports
| | 07:31 | later on in the course.
| | 07:32 | It may look a little intimidating at
first, but I would recommend that you spend
| | 07:35 | some time reading through the list of
available settings and I'm sure that
| | 07:38 | you'll come across a few that
stimulate your brain and make you think of new
| | 07:41 | functionality that you can
incorporate into your database.
| | Collapse this transcript |
|
|
3. Form and Report ControlsIntroducing form controls| 00:00 | 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 these in
the last few movies, but I think it's
| | 00:10 | important to explore each one
individually, and see what some of the options
| | 00:14 | we have when working with them.
| | 00:16 | In this movie, we're going to take a look
at the selection arrow and the label control.
| | 00:20 | First I'm going to go up to Create
tab and then click on Form Design.
| | 00:24 | Now I want to set up this
Detail section a little bit.
| | 00:27 | By default Access displays this dot
grid in the background, and when working
| | 00:31 | with some of the little controls I
think it's going to be a little bit
| | 00:33 | difficult to see, especially if you're
viewing this video on a smaller screen
| | 00:36 | such an iPad or an iPhone.
| | 00:38 | So I am going to adjust these dots a
little bit. Let me go up to the Property
| | 00:41 | Sheet and in the Format section, I'm
going to scroll down to Grid X and Y.
| | 00:49 | Right now these values are 24. I'm
going to set them to 6, so I press 6 and Tab
| | 00:53 | and then 6 and Tab again for the X and Y.
That will get rid of most of the dots and
| | 00:57 | I think it'll make this easier to see.
| | 00:59 | Now let's take a look at our first control.
| | 01:02 | In the Controls group of the Design
Ribbon, this middle group of buttons,
| | 01:05 | everything between these two lines,
are all of the different objects or
| | 01:09 | controls that you can add to your forms.
| | 01:10 | We're going to be taking a look at
this Label one. We will go ahead and click on
| | 01:14 | Label and click once to add
a label into our form.
| | 01:19 | Now, when I did that, you'll notice
that Access automatically deselected
| | 01:22 | label and it turned on the selection
arrow which is this white arrow that's
| | 01:25 | used to select objects.
| | 01:27 | By default, every time you add an
object from the Controls panel down into your
| | 01:31 | Detail section, Access will place one
instance of that object and then return
| | 01:35 | you back to the selection arrow.
| | 01:38 | My cursor is inside of a new text box,
it's flashing, it's waiting for me to
| | 01:41 | type in some text, so I'm just going
to type in Text and press Enter. I just
| | 01:46 | added a single label to my form.
| | 01:48 | Now, most of the other objects when you
add them such as text boxes, buttons,
| | 01:53 | some of these other images...
| | 01:54 | When you add them in, you'll get not only
the object, but you also get a label with it.
| | 01:58 | The Label button adds labels that
aren't attached to any other objects.
| | 02:02 | It's just used for static text.
| | 02:04 | This'll be good for things like
titles or descriptions or any kind of
| | 02:08 | instruction that you want to pass on to your end-
user that's not dependent on a specific record.
| | 02:13 | The label will create a static bit of
text that'll always say the same thing
| | 02:17 | every time you view this form.
| | 02:19 | To add multiple labels, you can double-click
on the Label button and that'll lock it on.
| | 02:23 | Now I can add one label and I'll just
write Text again and press Enter and it's
| | 02:28 | locked on, so it still staying as a Label button.
| | 02:30 | I can click again to add another
text button, and I'll click again to add
| | 02:35 | a fourth text label.
| | 02:37 | When I am done, I can use the
Select button up here to change back to the
| | 02:43 | white arrow and to turn off the label.
| | 02:45 | Now I've added multiple labels to my
form. At this point, I can go up to the
| | 02:50 | Arrange tab and we'll look out
how we can move these around.
| | 02:53 | If I drag a box around all of these to
select them all, I could use this To
| | 02:58 | Left button to move them all so
they align to the left objects.
| | 03:03 | I can say to the Bottom, which will
move them all toward the bottom object.
| | 03:06 | I'm going to go ahead and
press Ctrl+Z to Undo both of those.
| | 03:09 | I also have an option over here on the
left, Control Margins. And if I open up
| | 03:15 | that I've got different settings: None,
Narrow, Medium, and Wide. And that controls
| | 03:20 | how close the text is to the edge
of the box in the top left corner.
| | 03:24 | Press, if I click Wide, it'll increase
the margin so the text box has actually
| | 03:28 | moved the text outside of the expanded area.
| | 03:31 | Let me go ahead and drag these boxes
down a little bit, and since I have them all
| | 03:34 | selected at once, it'll increase
the size of all of them at the same time.
| | 03:37 | Now I have expanded my box and I could
see that the margin between the text and the
| | 03:42 | edge of the box has been expanded. And
just like we saw in the last movie, when
| | 03:48 | we were looking at the alignment--the
Left, Middle and Right alignment--there is
| | 03:54 | actually more properties that we
have available in the Property Sheet.
| | 03:58 | So if I look at this Control Margins, I
can just choose None, Narrow, Medium, or
| | 04:01 | Wide, but in the Property Sheet, I can
actually specify a value that I want.
| | 04:05 | If I scroll down to the bottom of the
properties to the Format section, I can set
| | 04:11 | the Top Margin, Bottom Margin, Left and
Right Margins to any value that I want.
| | 04:15 | So for instance, I can type
in 0.5 to make it half an inch.
| | 04:19 | Again the text moves off the screen a
little, so let me expand these out. And so
| | 04:23 | it moved half an inch from the top of the box.
| | 04:25 | This can help you really fine-tune
your alignments in setting up your forms.
| | 04:29 | And the Label control is one of
the most common controls that you will
| | 04:32 | be working with when you create
your forms and reports. They are pretty
| | 04:35 | much everywhere.
| | Collapse this transcript |
| Using lines and rectangles| 00:00 | The next controls that I want to
explore are the Line and Rectangle controls.
| | 00:04 | They're useful for marking off space and
grouping elements together in your forms and reports.
| | 00:09 | Generally, they are pretty passive and
not very exiting, but proper use of them
| | 00:13 | can go a long way to making your
forms and reports legible and unambiguous.
| | 00:17 | I'm going to go ahead and open up this
Controls form that I've saved from the
| | 00:20 | last movie and I'm actually going to
right click on it and say Design View.
| | 00:24 | This is the one that I set up in the
last movie that has the dot grid reduced in
| | 00:29 | density, so it's easier to see what I'm doing.
| | 00:31 | Up here on the Ribbon, in the Design tab,
in the Controls group, I've got Line
| | 00:36 | and the one right below it, Rectangle.
I'll go ahead and select Line and I'm
| | 00:41 | going to drag one out here in the form.
| | 00:43 | Now as I drag a line out, one of the
unique and kind of strange properties of
| | 00:48 | lines is that when they're horizontal
or vertical, which is the way you're going
| | 00:51 | to want 95% of your lines, it's
actually completely transparent.
| | 00:56 | So you have to use the ruler at the
very top to kind of get a estimate of
| | 00:59 | where your line is.
| | 01:01 | So if I go completely,
exactly horizontal, I can't see it.
| | 01:04 | If I go diagonal, I can see it.
| | 01:06 | If I go completely
vertical, I can't see it again.
| | 01:09 | So it's just one of the strange quirks
with these lines. So I am going to drag
| | 01:12 | out a line here, and if I go up to my
Ribbon here, I can change some of the
| | 01:19 | properties such as Line Thickness.
| | 01:21 | I can change it from a hairline to
maybe at 2 point line. I could change its
| | 01:26 | Dash Style or its Line Type. Probably
don't want to make a line transparent;
| | 01:29 | if you don't want it there, you could
just delete it. But I can make it a solid
| | 01:33 | line or dashed line. I've got
some dotted options as well.
| | 01:36 | We'll go ahead and select dots.
| | 01:38 | I can also change its color here, Line Color.
| | 01:41 | I could pick a color from the picker or I can
say More Colors to type in a specific value.
| | 01:45 | I'm just going to leave mine as black.
| | 01:47 | I am going to click off of it, so you
can see it, and now I've got a 2 point dotted
| | 01:53 | black line. Let's take a look at the
properties for a Rectangle. I'll click on
| | 01:57 | Rectangle, draw one out.
| | 02:01 | Now I've got the same properties I can
change here--the Line Thickness, the Line
| | 02:06 | Type, and the Line Color. I
can also adjust its Fill.
| | 02:10 | The Fill box is over here in the Font
section, even though this has nothing
| | 02:13 | to do with typing. But there is the
Fill bucket. I will click on the Down
| | 02:17 | button and I could use a color, maybe
Green 4, and I can fill the interior of
| | 02:23 | the box with a color.
| | 02:24 | Now lines and rectangles also have
another called Special Effect and if I
| | 02:31 | move up into the Controls Ribbon,
right up here to this button here, I can
| | 02:35 | choose its Special Effect.
| | 02:36 | Right now, it's set to Flat.
| | 02:37 | I can choose from a couple of different
options. I can make it a shadowed box, I
| | 02:42 | can make it appear sunken or
raised. I'm going to choose Shadowed.
| | 02:46 | Now my rectangle has a shadow around it.
| | 02:48 | I can see all of these in the Property
Sheet if I open up the Property Sheet
| | 02:53 | using this button here on the
Ribbon and then click on my Rectangle.
| | 02:56 | Notice I can see the Special
Effect right here. Now this is Shadowed. So
| | 03:00 | I've got the same options in my Property
Sheet that I have from this button on the Ribbon.
| | 03:03 | Now there is one other property
that applies to lines and rectangles in
| | 03:08 | particular. You can apply this to other
controls, but it works really well with
| | 03:11 | lines and rectangles.
| | 03:12 | If I look at the Arrange tab, I want to
take a look at this Anchoring option, so
| | 03:16 | let me go ahead and click on this
line here and click on Anchoring.
| | 03:19 | Right now, the default is to anchor it
on the top left, and what that's saying
| | 03:23 | is that Access will move this far over and
this far down, and draw a line that's this long.
| | 03:29 | If I change its options to perhaps
Stretch Across Top, what Access will do is
| | 03:34 | use the margins from the left to the
line and from the right to the line and
| | 03:38 | then it will draw the line in
between. So if my form gets wider, the
| | 03:42 | relationship between the endpoints and
the edge is what's going to be maintained,
| | 03:45 | not necessarily the width.
| | 03:46 | So if I switch into Home and change my
View, you'll see that my line looks a lot
| | 03:52 | longer than I drew it, but the space
between the left side and the right side is
| | 03:57 | what's now been fixed.
| | 03:58 | So you can make your lines and
rectangle a little bit dynamic to adjust to
| | 04:02 | a user screen size.
| | 04:05 | Lines and Rectangles play an
important role in organizing other elements on
| | 04:08 | your forms and reports.
| | Collapse this transcript |
| Organizing screen space with tabs| 00:00 | The Tab control can be used to add extra
space to your form or to group similar or
| | 00:04 | related objects together.
| | 00:06 | Adding a Tab control is a great way to
organize your form, but it's not going
| | 00:09 | to work very well on a printed report,
since you will only be able to see the
| | 00:12 | first tab when it's printed and
trying to click on a tab on a paper report
| | 00:16 | doesn't work very well.
| | 00:17 | But for forms, they work great.
| | 00:18 | I am going to take a look at this
Controls Form. I will right click and say
| | 00:23 | Design View. ThenRibbon we go ahead and add a
Tab object, clicking this Tab Control
| | 00:28 | up here in the Ribbon.
| | 00:29 | I will drag out a box in the bottom
of my detail section and that will add
| | 00:34 | two tabs to my form.
| | 00:36 | I can add additional tabs using this
Insert Page button up here on the Ribbon.
| | 00:42 | Press that once to add a
third tab and a fourth tab.
| | 00:45 | I can also add Tabs by right-clicking on
the Tab Control and choosing Insert Page.
| | 00:51 | If I want to delete a tab, just
activate it, right-click, and say Delete Page.
| | 00:59 | And if I want to rearrange pages, I
will right-click again and say Page Order.
| | 01:04 | This will bring up a window where I can
choose a tab and move it up or down in the order.
| | 01:10 | Go ahead and say OK to that.
| | 01:11 | Now each tab presents its own
area that we can fill with content.
| | 01:16 | I am going to switch to the first
tab here and add a label inside of it.
| | 01:20 | I will click on the Label
control and move down into the tab.
| | 01:24 | When I do that, the tab turns black.
That's telling me that whatever I am
| | 01:27 | about to put in will appear inside
of the tab and not just in the detail
| | 01:31 | section of the report.
| | 01:32 | So I'll move down here where it turns
black and I will click to Add a Label and I'm
| | 01:37 | just going to write, This is the first tab.
| | 01:39 | Let's go ahead and move to the second tab.
In here I can put another object.
| | 01:43 | How about a Rectangle?
| | 01:44 | I will click on the Rectangle
control and I will draw in a rectangle.
| | 01:49 | Let's go ahead and take a look
at how these tabs are operating.
| | 01:51 | We will go up to my View menu and
switch into Form view and now when I am
| | 01:56 | viewing my form, I have
different tabs with different content.
| | 02:00 | The first tab says, This is the first
tab, the second tab has a rectangle, and the
| | 02:04 | third and fourth tabs are currently blank.
| | 02:06 | Let's go back into Design
View and look at some properties.
| | 02:09 | Go into View menu and Design View. And I
want to change the name of these tabs.
| | 02:15 | Right now Page5, Page6, Page7, and
Page9 are not really doing it for me.
| | 02:19 | In the Property Sheet--if it's not open,
you can press F4 or the button up here,
| | 02:24 | or press the Alt+Enter shortcut key...
| | 02:27 | In the Property Sheet, we are going
to look for the Caption property, which
| | 02:29 | is on the Format tab.
| | 02:30 | So with the Tab selected, clicking
Caption. In here we can type in whatever we
| | 02:36 | want to appear here in the tab.
| | 02:37 | I am going to write Employees.
| | 02:41 | Perhaps this is a tab
| | 02:41 | where we are going to put a bunch of
content that deals with working with our
| | 02:44 | employees in our database.
| | 02:45 | Let's go ahead and click on the second one.
| | 02:49 | For its caption, I am going to write Reports.
| | 02:52 | Maybe this is a tab where we put a bunch of
content that deals with working with reports.
| | 02:56 | You can go through and change all
the tabs to identify what content is
| | 02:59 | appearing within the form.
| | 03:01 | Now, back into Form View, I have
different content and I can tell what it is that
| | 03:06 | I am going to do when I interact with that tab.
| | 03:10 | So adding tabs can add some dimension
and organization to your forms when screen
| | 03:14 | space is at a premium.
| | Collapse this transcript |
| Adding buttons| 00:00 | Buttons are one of the key
interactive elements that you will use on forms.
| | 00:04 | They instantly tell the end user that
something will happen when I click here.
| | 00:08 | Buttons will be linked to either Macros or
Visual Basic code to control their behavior.
| | 00:12 | Let's go ahead and try out
a couple of button controls.
| | 00:16 | In my database I am going to right-click
on the Controls Form and say Design View.
| | 00:19 | Now the Button control is this one right
here, the rectangle with the four Xs in it.
| | 00:24 | I like to call it the Button button.
| | 00:26 | So we can go ahead, press the
Button button and add one to our form.
| | 00:30 | When I add it to the from, the Command
Button Wizard starts, and this gives me a
| | 00:35 | list of options that I can choose
from for what the button will do.
| | 00:38 | We've got categories for Record
Navigation, I can go to the Next record, or
| | 00:42 | Find a Record, I can Go to the First Record or
the Last Record, Previous or Next Records.
| | 00:49 | Under Record Operations I can
Add a new record or Delete records.
| | 00:51 | I can Print, Save, or Undo.
| | 00:55 | From Form operations, I can apply a Filter.
| | 00:58 | I can Close a Form or Open a Form, Print it,
Print the Current Form, or Refresh Form Data.
| | 01:06 | Under Report Operations, I can do
things like Mailing a Report, Opening a
| | 01:10 | Report, taking a look at in print
preview view, sending it directly to the
| | 01:14 | printer, or saving it as a file.
| | 01:17 | In the Application category, I have
got one option, Quit access. It's Quit
| | 01:21 | application--it will shutdown your
database. And finally in the Miscellaneous
| | 01:25 | category, I have options for an Auto
Dialer if you are connected to a phone,
| | 01:30 | Print Table, Run a Macro, or Run a Query.
| | 01:34 | Running a Macro and Running a Query are two of
the most popular options with the Button Wizard.
| | 01:38 | Let's go ahead and choose Form Operations.
| | 01:40 | We are going to go ahead and
open a form. Go ahead and say Next.
| | 01:45 | Access now asks us which form do we
want to open, and it gives us a list of the
| | 01:49 | forms that are currently in our database.
| | 01:50 | Let's go ahead and choose our
Employee directory. I'll say Next.
| | 01:57 | Access asks us if we want to display
the Form with a specific record displayed
| | 02:02 | or the entire form in all the records.
| | 02:03 | Go ahead and just choose
All records and say Next.
| | 02:07 | And then we get to choose what
we want to display on the button.
| | 02:10 | We can either choose a Picture, like an icon
here or we can type in some text, OpenForm.
| | 02:14 | I am actually going to be a little
more specific and I am going to say
| | 02:18 | Open Employee Directory.
| | 02:20 | Go ahead and say Next, and we will
accept the default name. Press Finish.
| | 02:26 | Now I have got a button on a form that
will open the Employee Directory when clicked on.
| | 02:30 | Let's go ahead and test it out.
| | 02:31 | We'll go into the Form view and press
the button. And there it is. My Employee
| | 02:37 | Directory opens right up.
| | 02:38 | Let's go ahead and close that down, and
we will go back into Design View.
| | 02:44 | We can take a look at the Properties for
this button to see how it's functioning.
| | 02:47 | If I click on the button and then go
to the Event tab, we can see that the On
| | 02:52 | Click event is set to this Embedded Macro.
| | 02:55 | If we had other Macros that have
been saved in our database and they were
| | 02:59 | appearing here in the Navigation Pane,
you could select this dropdown menu and
| | 03:03 | choose those from the list.
| | 03:05 | You can also click the Build button to
edit those Macros that Access built for us.
| | 03:09 | So I am going to click Build and
it's going to show the Embedded Macro in
| | 03:13 | the Macro Design tool.
| | 03:15 | So this is the step that it produces. It
opens a form and here are the arguments
| | 03:19 | for that action. Which form?
Employee Directory. Which view? Form view.
| | 03:25 | We can change properties here and that
will apply to what the button does.
| | 03:27 | So if instead of opening it in Form View,
we wanted to open it up in maybe Print
| | 03:32 | Preview View, we can change that there.
| | 03:35 | If we close the Macro it will apply
those changes, and yeah, we can go ahead and
| | 03:39 | save that. And now the button
does something slightly different.
| | 03:44 | Change back to Form View and press it
and now my Employee Directory form opens
| | 03:49 | up in the Print Preview window.
| | 03:51 | Go ahead and close the Print
Preview and we'll close the Form.
| | 03:56 | So that's what a button does.
| | 03:56 | You click on them and they do things.
| | 03:59 | There is not much more to than that.
| | 04:01 | The best thing about them is that they
are instantly recognizable for your users
| | 04:04 | and they provide the main
interactive interface to your Access database.
| | 04:08 | The key to fully leveraging buttons is
understanding and being able to write
| | 04:12 | Macros or Visual Basic Code.
| | 04:14 | The better you get it writing
Macros, the more functionality will be
| | 04:17 | included in your buttons.
| | Collapse this transcript |
| Linking to external content| 00:00 | The Hyperlink control allows your form
to reach out beyond the local database
| | 00:04 | and into your computer or out on to the Web.
| | 00:07 | Let's add a Hyperlink Control to our form.
| | 00:09 | I am going to right-click the
Controls form and say Design View.
| | 00:13 | The Hyperlink Control is this one right
here with the globe and chain icon. I will
| | 00:17 | click on that and the Insert
Hyperlink dialog box opens.
| | 00:20 | Let's go ahead and fill in some of these fields.
| | 00:22 | I am going to first take a look at
this Link to Existing File or Web Page.
| | 00:27 | What we can do is link to a file
that's outside of our database.
| | 00:31 | For instance, inside of my Chapter 3
Exercise Folder, which just happens to be
| | 00:35 | the Current Folder, I can select the Two
TreesCatalog, which is a Word document.
| | 00:40 | I can also look at Browsed Pages that
are in my Internet History or Recent
| | 00:44 | files from My Computer.
| | 00:45 | I will go ahead and say OK to select the
catalog and Access adds a link to that in my form.
| | 00:52 | I can change the caption of what this
says with the Caption Property. Right now
| | 00:56 | it says TwoTreesCatalog.docx. I'm going to go
ahead and change this to Open Two Trees
| | 01:03 | Catalog, and I'll need to make it a
little bit bigger so it fits all that text.
| | 01:07 | I will drag it down to the middle here.
| | 01:11 | Okay, let's go ahead in Form View;
| | 01:13 | we will take a look at this.
| | 01:14 | If I click on the link, Access tells
me that we might have a little problem
| | 01:18 | here, doesn't know exactly what we are
linking to, but we can go ahead and say
| | 01:21 | Continue, and Access will open up
that Word Document in Microsoft Word.
| | 01:26 | So I have now reached out into my
computer and brought in additional content, so
| | 01:30 | it's not taking up any more
file size in the database.
| | 01:32 | Go ahead and close that out.
Now return to Access.
| | 01:36 | I will switch into Design View, and we
will look at a couple more hyperlinks.
| | 01:40 | I will click on the Hyperlink Control.
| | 01:44 | This time we can look at
Object in This Database.
| | 01:46 | When I select that, Access presents me
the list, or tree view, of all the
| | 01:50 | objects within my database.
| | 01:52 | I can select Tables for instance,
and that will show me all the tables in
| | 01:56 | my database or I can expand Forms, and that
will show me all the forms in my database.
| | 02:00 | Let's go ahead and select the Employee
Directory Form and say OK. That adds a
| | 02:06 | second link to my form called Employee
Directory, and again, I will change its
| | 02:10 | caption. Employee Directory is a little
bit vague. Let's go ahead and say View
| | 02:15 | Employee Directory Form.
| | 02:16 | I always like to tell my end users exactly
what's going to happen when they click on it.
| | 02:22 | So now they know they are
going to be viewing this form.
| | 02:25 | Let's add one more Hyperlink while we
are here. Do Hyperlink and E-mail Address.
| | 02:31 | Here we can specify that we
want to send an email to somebody.
| | 02:34 | So for instance, I can send an email to
test@test.com and the Subject is "Here is an email."
| | 02:42 | This Text to display will change
what the caption says so I don't need
| | 02:45 | to change it later.
| | 02:47 | Send an E-mail to test.
| | 02:50 | Go ahead and say OK, and that
adds another link to my form.
| | 02:54 | One more. We will add a hyperlink to a website.
| | 02:56 | We will click the Hyperlink
Control > Existing File or Web Page.
| | 03:01 | I can select Browsed Pages to select
from my browsing history from Internet
| | 03:05 | Explorer, Recent Files or files that I
have opened, or I can just type in an address.
| | 03:11 | For Address I am going to type
in http://twotreesoliveoil.com.
| | 03:17 | Again, I can change this Text to display up
here. Visit the Two Trees website. And say OK.
| | 03:25 | So now I've got four hyperlinks here.
| | 03:28 | Let's go ahead and test them out.
| | 03:29 | I will switch into Form view, and let's
first view the Employee Directory form.
| | 03:35 | I click on that, any employee
directory form which is in my database opens up.
| | 03:39 | This functions identically to the button
that we added in the last movie. Just a
| | 03:42 | different way of doing it.
| | 03:43 | I will go ahead and close the Employee
Directory. This time, Send an E-mail to Test.
| | 03:49 | I click on that, Outlook opens,
which is my default email program on this
| | 03:53 | computer. And it opens up a New
message already addressed, already
| | 03:56 | subjected, ready to send.
| | 03:59 | Go ahead and close that. Don't need to
save any changes. And the last one, I am
| | 04:04 | going to click on the Visit Two Trees
Website. When I click on that, Internet
| | 04:07 | Explorer opens and it goes directly
to the twotreesoliveoil.com website.
| | 04:13 | Bringing in content from the web or
from your local computer can be a great
| | 04:17 | way to add dynamic content to your database
without having to store anything within it.
| | 04:22 | Training or marketing documents,
customer testimonials, specs on products, and
| | 04:26 | all kinds of information can be made
available right within your database so
| | 04:30 | that it's easy to get to when you need it.
| | Collapse this transcript |
| Entering and selecting data| 00:00 | When it comes to displaying your data
in forms, there are three objects which
| | 00:04 | are used most often.
| | 00:05 | They are the Text box, the
Combo box, and the List box.
| | 00:10 | Because these three controls work with
data, we will have to connect our blank
| | 00:13 | form to a data source so
that they'll work properly.
| | 00:15 | We are going to build a simple form
that helps us enter in reviews into
| | 00:20 | Table Reviews.
| | 00:21 | We are going to open up that table
and take a look at what's inside.
| | 00:25 | The Table Reviews captures comments
from our customers about our products.
| | 00:29 | You get an identification number for
the review, the product that was reviewed,
| | 00:34 | the customer's rating and whether they
give us permission to use the review, like
| | 00:37 | on a website, and then the
comments that they left for us.
| | 00:40 | So let's build a form that helps
us enter data into this data table.
| | 00:43 | I will close the table.
| | 00:45 | Let's go up to the Create Tab and
we will do a new Form in Design View.
| | 00:51 | And the first thing we need to do is
connect our form to the data source or
| | 00:56 | to the Reviews Table.
| | 00:58 | We can do that in the Property sheet.
Once again, our shortcut is F4 or
| | 01:02 | Alt + Enter, or it's this
button up here in the RRibbonibbon.
| | 01:06 | Make sure we are taking a look at the Form
properties and in the Data tab, the Record Source.
| | 01:12 | It's a dropdown menu; we can
select from a list of tables.
| | 01:15 | We are going to choose the Reviews table.
| | 01:18 | Now let's go ahead and add a couple of elements.
| | 01:20 | First, I want to add a Text Box. I
can click on the Text Box control in the
| | 01:24 | Design Tab of the Ribbon.
| | 01:25 | We will click in our form somewhere to
add a Text Box and now I will link this
| | 01:31 | Text Box to a field on our table.
| | 01:33 | Under the Control Source I can use
this dropdown menu that shows me all the
| | 01:37 | fields in the table, and I
am going to choose ProductID.
| | 01:40 | Let's add another Text
Box for the Comments field.
| | 01:42 | We will create another Text Box, we
will click down here, we will change its
| | 01:48 | Control Source to Comments.
| | 01:50 | Now the Text Box for Comments needs
to be a little bit bigger because the
| | 01:54 | comments fields is a memo. We want
people to be able to type in some comments
| | 01:58 | here, a long comment, so I will drag
this box open and make it a little bit bigger,
| | 02:01 | so we have room to write.
| | 02:03 | I'm going to go ahead and view my
Form to see what it looks like.
| | 02:05 | I can scroll through the three
records that make up our Reviews Table right
| | 02:10 | now. So this is Record #1,
Record #2, and Record #3.
| | 02:15 | I can add a New record by clicking
this new blank record button here, but now
| | 02:19 | I have got a problem.
| | 02:21 | I want to type in a Product
Review for a specific product.
| | 02:24 | And in order to do that with this table
the way it's set up, I will have to type
| | 02:28 | in the product identification number and
if I don't know what that is, this form
| | 02:32 | isn't giving me any help.
| | 02:33 | So let's go back into Design View,
and we will fix this a little bit using a
| | 02:36 | Combo Box, so it can provide some
context and help us enter in our review.
| | 02:40 | I will go back into Design View and I am
going to delete this Text Box here for ProductID.
| | 02:47 | The Combo Box and the List Box controls
here on the Ribbon--this button here is
| | 02:51 | for Combo Box, the one
right below it is for List Box--
| | 02:55 | These two controls provide lists
of objects that we can select.
| | 02:58 | Now the Combo Box and the List
Box are functionally identical.
| | 03:02 | The only difference is that the Combo
Box collapses to a single row when you are
| | 03:05 | not using it, whereas the List Box,
this one, stays open all the time and
| | 03:10 | displays the values in the list.
| | 03:11 | I am going to choose the Combo Box
and we will click in our Form to add it.
| | 03:17 | The Combo Box and the List Box
both have wizards associated with it.
| | 03:21 | The first question on the wizard is how do
you want your Combo Box to get its values?
| | 03:25 | We can choose "I want the Combo Box to
look up the values in a table or query."
| | 03:30 | The second option says we can type in
the values that you want and make a list
| | 03:33 | on our own, or we can use a Combo
Box to search for a record in our form.
| | 03:38 | I want to make this a dynamic list, so
if we add new products to our database,
| | 03:42 | that list expands as well.
| | 03:43 | So I am going to say, I want the Combo Box
to look up the values in a table or query.
| | 03:47 | Go ahead and say Next.
| | 03:50 | Now it's going to ask us which table
or query is going to provide that list.
| | 03:53 | Now you might think that you want to
connect this list to the Reviews table.
| | 03:56 | But let's think about this for a moment.
| | 03:58 | If I connect the list to the Reviews
Table, all it's going to show me are the
| | 04:02 | products that have already had reviews
written for them; in this case we only have three.
| | 04:06 | What I really want to do is provide a
list of all the products in our database.
| | 04:09 | So I am going to choose
the Products table instead.
| | 04:12 | This will give us that complete
master list of everything that we carry.
| | 04:16 | I am going to say Next. Now I get to choose
which fields do I want to show up in the box.
| | 04:22 | I want the ProductName, so
it's easy to choose from the list.
| | 04:24 | We will move that over from Available
fields to Selected fields, and say Next.
| | 04:31 | How do you want to sort these?
| | 04:32 | We can make sure that it sorts
alphabetically base off the Product Name, or we
| | 04:36 | can sort them numerically
by the Product ID number.
| | 04:38 | I am going to choose
ProductName and leave it as Ascending.
| | 04:44 | Click Next, click Next again.
| | 04:46 | Now Access asks us what does it want us to
do once we've made a selection in our list.
| | 04:51 | It can either remember the value for
later use or store the value in a field.
| | 04:56 | "Remember the value for later use" will
simply allow us to make a selection and
| | 05:00 | then it will hold that selections value
in memory, so that we can reference it
| | 05:04 | later, for instance, from a query.
| | 05:07 | If we choose to store the value in a
field we'll actually change data in a
| | 05:10 | data table. We are going to create a
new review so we want to store that value
| | 05:14 | in the Reviews Table.
| | 05:15 | Go ahead and choose the ProductID as
where it's going to save that value.
| | 05:18 | We will say Next, and we will
accept the default name. Say Finish.
| | 05:24 | Now our products have kind of long names,
so I know I need to expand the slider.
| | 05:28 | Let me click and drag on the right
side to make it a longer list, and we can
| | 05:33 | go ahead and check out our functionality.
| | 05:34 | I will go to the View tab and
change it back to Form View.
| | 05:38 | Now if I go to create a new record--I will
click on New Blank record here on the bottom--
| | 05:42 | I've got a list of all
the products in our database.
| | 05:46 | I can easily scroll through the list,
find the oil that I am interested in
| | 05:51 | writing a review for--say Refined Oil
32 oz size--and now I can type in the
| | 05:57 | comments that I have
about that particular product:
| | 06:00 | This oil is a fantastic value.
| | 06:05 | At this point I can go ahead and
finalize that comment by moving forward to a new
| | 06:08 | record and I can take a look at my
Reviews Table to see that change being made.
| | 06:13 | So now I have a Review here:
This oil is a fantastic value.
| | 06:17 | Now there are a couple of additional
fields in our data table, the rating and
| | 06:21 | public fields, and we will add
controls to our form in the next chapter.
| | 06:25 | But for now, if we switch back to our
form?So instead of using a Text Box,
| | 06:29 | where we have to manually type in the
name of our oil, by using a Combo Box, we
| | 06:33 | can easily select it from a list to
ensure that every time we type in a value,
| | 06:37 | so that it's entered consistently and
that the values match the records that are
| | 06:40 | already in our database.
| | Collapse this transcript |
| Controlling input with option groups| 00:00 | We're going to continue editing our Reviews
form for entering data into our Reviews table.
| | 00:06 | Let's take a quick look at
the Reviews table one more time.
| | 00:09 | And now I need to add some elements
that help us deal with this Rating column
| | 00:12 | and the Public column.
| | 00:13 | The Public column is a Yes/No field and by
default in a table, it shows up as a checkbox.
| | 00:17 | The Rating column is a number field.
| | 00:20 | We could type in whatever number we
want but we want to restrict users to a 1
| | 00:23 | through 5 scale. But we also want
to identify what 1 through 5 means,
| | 00:28 | so we can do that on our form.
| | 00:29 | Let's go ahead and close our Reviews table.
| | 00:33 | We'll go back to our Reviews form that
we were working on in the last movie.
| | 00:35 | I'm going to switch into Design View and
we're going to take a look at a couple of
| | 00:40 | elements that deal with the Yes/
No data type like our Public field.
| | 00:45 | In the Control section of our Design
tab, we've got the Check Box, the Option
| | 00:51 | Button and sometimes it's called a radio button.
| | 00:53 | We also have a Toggle Button,
that's this icon up here.
| | 00:57 | Let's go ahead and add one of each
of these elements onto our form.
| | 01:01 | Take a Check Box and drop it onto our
form, we'll take a Radio Button and drop
| | 01:07 | it on our from, and I'll take a
Toggle Button and drop it on our form.
| | 01:13 | Now each of these presents data in a
slightly different way but they all do
| | 01:16 | basically the same thing. They either
check on or check off, turned on or
| | 01:21 | turned off, pressed or unpressed.
| | 01:25 | Let's go ahead and connect
all of these to our data source.
| | 01:27 | I am going to select the button, I'm
going to Shift+click and select the Radio
| | 01:33 | button or the Option Button, and I am
going to click on the Check Box to select
| | 01:37 | all of those three at once.
| | 01:39 | Now, in the Property Sheet on Data tab,
I could change the Control Source for
| | 01:43 | all three at the same time.
| | 01:45 | For the Control Source we're going to
connect this to the Public field, which is
| | 01:48 | that Yes/No data type.
| | 01:51 | Okay, let's check it out on our form.
| | 01:53 | Switch into Form View. And we can see that
the first record has this check mark turned on.
| | 01:59 | The check is on, the radio
button is on, the button is pressed.
| | 02:03 | If we cycle through our records, we
can find one where they're all off.
| | 02:06 | Now since they're all connected to the
same field, they'll all be in the same
| | 02:10 | state at the same time.
| | 02:11 | So, record number 3, they're all on,
record number 4 they're all turned off.
| | 02:16 | Let's go back into our Design View and
we're going choose to use the Check Box.
| | 02:23 | The Option button and the
Toggle Button, we can get rid of.
| | 02:26 | Click on each one and delete them.
| | 02:30 | Let's move our check box to the bottom.
| | 02:34 | Click and Drag it down.
| | 02:35 | I want to change the label associated
with the check box, so instead of Check6
| | 02:40 | it says, Can we use your comment on our website?
| | 02:48 | This tells the end-user
exactly what this check box is doing.
| | 02:51 | Now, it's being stored in our
Reviews table under the field Public, but on
| | 02:55 | the form that the user is
interacting with, it's more specific about what
| | 02:58 | that checkbox means.
| | 02:59 | While we're doing it, we can also
change the labels for the other fields.
| | 03:02 | This one here is the Comments field.
| | 03:03 | We can change it to "Please share your
comments" and I'm going to move it above
| | 03:10 | using the handle in the
top left corner to drag.
| | 03:13 | Let's change this one too.
| | 03:16 | Instead of Combo4, I'm going to
choose to just ask, Which product are you
| | 03:19 | reviewing? And again I'll
move that above the field.
| | 03:23 | The next thing I want to do is give our
customers the opportunity to leave a numeric review.
| | 03:30 | For that I'm going to use a control
called the Option Group. That's this box up
| | 03:34 | here in the ribbon; it's got
this box with the XYZ in it.
| | 03:37 | I'll click once, and then
add that down into my form.
| | 03:41 | The Option Group Wizard starts and it
asks us to label each of the options
| | 03:46 | that we want to present.
| | 03:47 | Now, these can be numerical fields
like 1, 2, 3, 4, 5, or again I like to be
| | 03:52 | really specific. I want this to
say exactly what I mean those to be.
| | 03:56 | So, instead of 1, 2, 3, 4, and 5,
I can say something like this.
| | 03:59 | The first one is going to say
Excellent, the next one Good, the third one
| | 04:04 | Average, the fourth one Below
Average, and the fifth one Poor.
| | 04:11 | That's going to be what the end-
user will select from the list.
| | 04:14 | We'll go ahead and say Next.
| | 04:16 | The next screen asks us if we want any
of the options to be the default choice.
| | 04:19 | Now, I don't want to skew our results
at all, so I'm going to choose No, I
| | 04:22 | don't want to default.
| | 04:24 | We'll go ahead and say Next.
| | 04:26 | Now, our Access wants to know what
values we're going to assign to these labels.
| | 04:30 | The user is just going to see the label.
| | 04:33 | The value is what's going to
get stored in our data table.
| | 04:36 | Now, I've got these in backwards
order right now with Excellent on top.
| | 04:39 | So, I want to Excellent tag
to actually be the value of 5.
| | 04:42 | So, I'm going to type 5 there and press Tab.
| | 04:44 | I'm just going to go through this list and
renumber these. So Excellent is 5, Good is 4,
| | 04:50 | Average is a rating of 3, Below
Average equals 2, and Poor is 1.
| | 04:55 | Again, this is what the end-user will see in
the Label Names. The values are what'll get stored.
| | 04:59 | Go ahead and say Next.
| | 05:02 | Access now wants to know what
you want to do with that value.
| | 05:05 | We can save it for later or
we can store it in a field.
| | 05:07 | I'm going to store it in
the Rating field for my table.
| | 05:12 | Go ahead and say Next.
| | 05:14 | This window here is asking us
how we want to display this.
| | 05:18 | We have the option of an Option
button, a Check box, a Toggle Button.
| | 05:22 | There's a couple of
different rules of thought here.
| | 05:26 | Generally, check boxes are reserved
for options where we can choose more than
| | 05:30 | one option at a time.
| | 05:31 | So, I don't think that's going
to be a good option for this.
| | 05:34 | Option Buttons and
Toggle Buttons will both work.
| | 05:37 | I'm going to choose the Toggle Button option.
| | 05:41 | This group down here asks us which style
we want to use and we could choose from
| | 05:44 | Flat, Raised, Shadowed, Sunken. Just
some different options for how we want the
| | 05:50 | style of the box around it.
| | 05:51 | Now, we could always change
that in the Property Sheet later.
| | 05:54 | So don't feel like you need to make a
decision at this point; you can change it
| | 05:57 | later if you change your mind.
| | 05:58 | I am just going to stick with the Etched.
| | 06:00 | Go ahead and say Next.
| | 06:02 | What do we want to call this box?
| | 06:04 | Right now, it says Frame11 and I'm going to
go ahead and change this, and I'm going to
| | 06:08 | make it say Rating, and say Finish.
| | 06:13 | Access adds that Option Group to our
form here and we can move it around. We'll
| | 06:17 | drag it maybe over here to the left.
And now let's go ahead and check out our
| | 06:21 | form and see how it's functioning.
| | 06:23 | I'll switch my view into Form View.
| | 06:25 | Now, we can scroll through the
existing reviews that are in our table, review
| | 06:29 | number 1, review 2, review 3.
| | 06:33 | Review 4 was the one we put in where we
selected this box and typed in the value
| | 06:36 | but we weren't able to select a
rating because that wasn't created yet.
| | 06:39 | So, let's go ahead and give this a rating.
| | 06:41 | This was a fantastic value;
it gets an Excellent rating.
| | 06:43 | Can we use the comment on
our website? Yes, you can.
| | 06:47 | Now if I go through and create a new
record, it will save that to our Reviews table.
| | 06:52 | Take a look at our Reviews table
and we see that Review has now been
| | 06:55 | populated into the table.
| | 06:56 | So, now we have an easy form where we can
capture customer reviews about all of our products.
| | 07:01 | Let's say a new record, choose a
product from the list, don't have to type in
| | 07:05 | there, choose the Rating, how about Good.
| | 07:09 | Share our comments, Another comment.
And we don't want that to be used on the
| | 07:14 | website, so I will leave that on blank.
| | 07:17 | So, by using controls like Check Boxes,
Option Groups, Combo Boxes, and List
| | 07:22 | Boxes we give our end users multiple
ways to enter data into our data tables.
| | Collapse this transcript |
| Attaching documents| 00:00 | In this video we'll explore the
controls for working with attached files.
| | 00:04 | Now, there's plenty of debate on
whether you should actually attach additional
| | 00:07 | files such as images or Word
documents to the record within your database.
| | 00:11 | Some people claim that it leads to
database bloat and unnecessary slowdowns.
| | 00:15 | I definitely recommend the hyperlink
option as a good way to include links to
| | 00:19 | additional resources without actually
embedding everything in the database.
| | 00:24 | But when you do have file attachments
and OLE object fields within your tables,
| | 00:28 | you'll need to know how to
actually make use of them.
| | 00:31 | Working with them and their forms
requires the use of the attachment control and
| | 00:34 | the bound object frame control.
| | 00:36 | I'm going to go ahead and open up my
Employees table here, double-click on it,
| | 00:40 | and scroll to the right. And the last
two fields in my Employees table are the
| | 00:45 | Attachment field that currently has the
headshot or the photograph of a few of
| | 00:49 | our employees and I have
a field for OLE objects.
| | 00:53 | Now, OLE stands for Object Linking and
Embedding and it's kind of an older way to
| | 00:57 | attach files to your database.
The Attachment field is the newer way.
| | 01:00 | Let's go ahead and close this out and we'll
create a form that looks at these two fields.
| | 01:04 | We'll go to the Create tab and
create a New form in Design View.
| | 01:09 | First, we need to this
form to our Employees table.
| | 01:13 | So, in our Property Sheet, with Form
selected, we'll go to the Data tab and
| | 01:18 | change our Record Source to tbl_Employees.
| | 01:20 | Now, let's add those controls
for working with the attachments.
| | 01:23 | I can do it in a couple of ways.
| | 01:25 | If I go up to the Controls section, the
paperclip icon is the Attachment field.
| | 01:29 | So, I can select it and drop it down here
and attach the Control Source to that object.
| | 01:36 | So, the Control Source for that is
going to be Photo from my Employees table.
| | 01:40 | I could've also gone to this Add Existing
Fields button and added it right from here.
| | 01:45 | So, if I scroll down through my table of
employees, I'll find Photo, and if I
| | 01:51 | double-click on that, that
adds the Attachment field as well.
| | 01:54 | So, it added a second instance of this photo.
| | 01:56 | We'll go ahead and delete that one.
| | 01:58 | Now, I can also add a control
that deals with our OLE object.
| | 02:01 | We're going to use this Bound Object Frame.
| | 02:05 | It's this icon here with the cactus and the XYZ.
| | 02:08 | So, click on that once and I'll
click down into my form to add one.
| | 02:11 | I'll go to my Property Sheet I'll set the
Control Source for that to the OLE field.
| | 02:17 | Now, just like we saw with the Photo,
we could've gone to Add Existing Fields
| | 02:22 | and added it right here.
| | 02:23 | It does the same thing. Adding a blank
object and then linking it, or adding it
| | 02:27 | from the Field List, does the same thing.
| | 02:29 | Let's also add one more control here.
| | 02:31 | There's this one here
called the Unbound Object Frame.
| | 02:34 | It's the same icon, but without the XYZ,
so it's got the cactus in the road.
| | 02:37 | I'll click on that and I'll
drag out a smaller box right here.
| | 02:42 | Now because this one is unbound, it's
not linked to any specific record in our
| | 02:46 | table; it's linked to the form itself.
| | 02:48 | Access asks me what kind of
document do I want to attach here.
| | 02:53 | I can either create the attachment from
an existing file or I can create a new
| | 02:57 | document directly inside of the form.
| | 02:59 | For instance, if I click Bitmap Image
and say OK, Access will open up Paint,
| | 03:05 | which is the default program on this
computer to deal with bitmaps and I can
| | 03:08 | just scribble in something here.
| | 03:09 | Go ahead and close this out and Access
saves that object right inside of my form.
| | 03:15 | So, this file doesn't exist on my
computer anywhere, except inside of this form
| | 03:19 | object inside of my database.
| | 03:22 | Same thing is going to apply to the
linked objects in this OLEBound field, but
| | 03:26 | instead of being linked to the form,
it's going to be linked to the specific
| | 03:29 | employee that we have selected when we create it.
| | 03:32 | The Photo is also linked to the employee.
| | 03:34 | Let's go ahead and take a
look at this in Form View.
| | 03:37 | We can see the photo for person number
1 and we've got an OLE object field that
| | 03:44 | we can right-click on and say Insert Object.
| | 03:47 | We can choose an object to insert and
create a new document, and there's a long
| | 03:52 | list of documents that we can work with.
| | 03:53 | Some of these work better than
others. For instance, WordPad document.
| | 03:58 | If I say OK, it will open a
New document in the WordPad.
| | 04:01 | Let me close that out.
| | 04:03 | Instead I'll right-click and say, Insert
Object, and we'll choose a PowerPoint slide.
| | 04:09 | Now, PowerPoint slides are
handled a little bit differently.
| | 04:12 | When I say OK to that, Access
actually opens up a small PowerPoint design
| | 04:18 | environment and I've got this little
teeny side down here that I can't really
| | 04:21 | resize beyond the bounds of the control.
| | 04:24 | So it's a little bit more difficult to
work with a PowerPoint slide. It doesn't
| | 04:27 | open in a full window.
| | 04:29 | But we could add a pre-existing PowerPoint
slide, and that seems to work pretty well.
| | 04:33 | We'll actually click off of it
anywhere and then I'll unselect it.
| | 04:37 | One more time I'll right click
inside this Bound Object frame, say Insert
| | 04:42 | Object, Create from File, and then we
can browse to a file we want to embed.
| | 04:47 | I'll say Browse, Chapter 3, and I'll
choose the TwoTreesCatalog. We'll say OK.
| | 04:55 | We'll say OK again, and it takes a
moment to bring that into my database.
| | 05:00 | Now, I've got my Two Trees Olive Oil
Word document saved, attached to the
| | 05:04 | record for Employee #1. If I go to
Employee #2 by selecting next record, you can
| | 05:09 | see that the same unbound object applies,
because this is attached to the form
| | 05:12 | itself, but the unbound object is blank.
| | 05:15 | So I can choose an additional
file for the second employee.
| | 05:18 | Let me go back to the first.
| | 05:21 | If I right-click on the object inside
of the Bound Object frame, I can choose
| | 05:25 | Document Object and then Open, and
that'll open up the original Word document
| | 05:30 | inside of an editing window in
Microsoft Word, where I can make changes and it'll
| | 05:35 | save it right back into my data table.
| | 05:36 | Let's close that out.
| | 05:39 | Having attached objects in your database,
such as this Word document or these images,
| | 05:43 | really does increase the file
size of your database by quite a bit.
| | 05:47 | So, I definitely recommend working with
the Hyperlink option if at all possible,
| | 05:50 | but when you do need to have file
attachments attached to records within your
| | 05:53 | database, the OLEBound object and the
Attachment field are the ways to go.
| | Collapse this transcript |
| Attaching images| 00:00 | In the last movie, we talked about
the Bound Object control and the
| | 00:03 | Attachment control for working with
files that were directly attached to
| | 00:07 | fields within a data table.
| | 00:09 | Now, I don't want to make it sound
like I'm recommending you don't work with
| | 00:12 | images at all within your database.
| | 00:14 | The tasteful application of selected
images within your forms and reports can
| | 00:18 | add some much needed personality and
make it fun for your end users to work
| | 00:21 | with your database.
| | 00:22 | I do want to take a look at the Image
control and how that can add to your forms.
| | 00:27 | I'm going to right click on our
Controls form and say Design View.
| | 00:33 | The Image control is this one here, the one
with the mountain and the little sun icon.
| | 00:36 | I'll click on Image and click
once to add an image down to my form.
| | 00:41 | That opens up this Insert Picture
window, Chapter 3, and I am going to choose
| | 00:48 | this Ojai 500x300 image.
| | 00:49 | Go ahead and say OK and Access
adds that image onto my form.
| | 00:53 | Now, there're a couple of other
ways you can add images to your forms.
| | 00:57 | For instance, we've seen
this Logo button previously.
| | 00:59 | If I press Logo and choose the same
picture, Chapter 3, Ojai500x333, say OK to
| | 01:08 | that, the logo actually adds
into the Form Header section.
| | 01:12 | So, in order to add a logo, Access added
this From Header and placed it right up
| | 01:16 | here in the top corner.
| | 01:17 | A logo is always going to
appear in the top left corner.
| | 01:20 | But in the end, all it is is an image,
so I could have just made a header myself
| | 01:24 | and added the image in the same place.
| | 01:25 | So, let me go ahead and delete that.
| | 01:27 | That's what a logo looks like.
| | 01:28 | I'm actually going to right-click
here and say Form Header/Footer to turn
| | 01:32 | the Header section off.
| | 01:33 | So, this is what an image looks like.
| | 01:35 | It's basically just placed
in the middle of my form.
| | 01:37 | We can take a look at some of the
properties that come with the image.
| | 01:40 | I'm going to open up the Property
Sheet and let's first shrink this window to
| | 01:45 | see what happens to the image
when I shrink its bounding box.
| | 01:48 | I'm going to click on the bottom and drag up.
| | 01:51 | When I do that, the image shrinks
to fit within the bounding box.
| | 01:54 | By default, if I look at its properties on
the Format tab, its Size Mode is set to Zoom.
| | 02:01 | We've got a couple of different options here.
| | 02:03 | I can choose from Zoom, Clip, or Stretch.
| | 02:07 | Clip is going to clip the image or
essentially crop it to the bounding box
| | 02:11 | that I've selected.
| | 02:12 | Stretch will distort the image, so
we'll stretch it out wide and distort it.
| | 02:16 | Let me go ahead and change this back to Zoom.
| | 02:19 | I can also change the Width and Height manually.
| | 02:22 | So, instead of dragging, I
can type in specific values.
| | 02:24 | So for instance, I can type in 2 inches
for the Width and 1 inch for the Height
| | 02:28 | and Access will fit the image within that box.
| | 02:32 | This kind of an undocumented feature;
you can actually type in, instead of just
| | 02:35 | inches by using the quotation mark,
| | 02:38 | I can type in centimeters. So I
can type 2cm and it'll convert it.
| | 02:44 | I can also work with pixels,
which is kind of interesting.
| | 02:47 | You can type in 500px and it
will make it 500 pixels wide.
| | 02:53 | For the Height, I know this image is 500x333
so I can type in 500 wide, 333
| | 03:00 | for the height, finish that with px,
and press Enter, and it return me to the
| | 03:05 | actual size of this image.
| | 03:06 | As with most of the other controls, I can
also add a border or a line style around it.
| | 03:10 | I can go up to this Line menu and
choose 4 point and it'll add a 4 point
| | 03:16 | border around my image.
| | 03:17 | Let's go ahead and delete this out and
I'll look at one other way that we can
| | 03:20 | use images in our forms.
| | 03:22 | If I select my Form here by choosing
the box between the two rulers here, it'll
| | 03:27 | select the Form in my Property Sheet
or I can select Form from the dropdown
| | 03:31 | menu here. Select Form.
| | 03:33 | We can add a picture to
the background of our form.
| | 03:35 | Right here I've got this Picture
property; right now its set to none.
| | 03:39 | If I click on the build button, Chapter
3, and choose the same Ojai500x333 and
| | 03:46 | say OK, Access adds this
into the background of my form.
| | 03:50 | Now it won't get in the way
if I try and click on it.
| | 03:52 | I'm going to select the properties for my Form
again and take a look at some other options.
| | 03:57 | I can choose to tile this.
| | 03:58 | Now, this image isn't a tile-able image,
but if I had a background pattern, for
| | 04:01 | instance, I can turn on Tiling and
it'll tile the same image across the field.
| | 04:05 | So, I'm going to choose that back to No.
| | 04:08 | Picture Alignment--I can choose from
Center, Top Left which will move it to the
| | 04:13 | top left corner, Top Right, Bottom
Left, or Bottom Right corner, if I want to
| | 04:17 | attach it to a specific corner.
| | 04:18 | We can also choose to have this either
Embedded, which it does by default, or
| | 04:23 | Linked, in which case it won't add any
file size to our database; it just has to
| | 04:27 | be able to find it in our computer.
| | 04:29 | So, if I change this from Embedded to
Linked, Access is going to ask if I want
| | 04:32 | to remove it from the form.
| | 04:34 | I do, and now I just need to go back up to
my Picture field. This is now set to Linked.
| | 04:39 | I'll go to my Picture
field and build one more time.
| | 04:43 | Chapter 3 and choose it again. And now
instead of embedding the file within my
| | 04:48 | database, it's just
referencing a link to its file location.
| | 04:52 | So, it's not adding any size to my database.
| | 04:54 | Now, you can get pretty creative
with embedding images within your forms.
| | 04:59 | For instance, this particular image
seems to fade to white as it gets to the top.
| | 05:02 | I can take advantage of that by making my
form exactly the same width of the image.
| | 05:07 | If I drag my form closed, now it looks
like the picture starts at the bottom
| | 05:11 | and goes all the way up to the top, even
though the image actually ends right about here.
| | 05:16 | So by being a little bit creative with
your application of images within your
| | 05:19 | forms, we can create some interesting effects.
| | 05:22 | We'll see this later on the
course as we apply images to our forms.
| | Collapse this transcript |
| Understanding the subform control| 00:00 | Subform controls allow you to create
linked table-like displays of information
| | 00:04 | that are related to a main topic.
| | 00:07 | Now, we saw an example of a Subform
briefly in the beginning of this course
| | 00:10 | when we used the Form Wizard to
display a list of all of the orders that
| | 00:14 | included a specific product.
| | 00:16 | Let's go ahead and duplicate that
form manually using the Subform control.
| | 00:20 | First, I'm going to go up to the Create tab
and we'll do a new form in Design View.
| | 00:25 | Subforms take up a lot of screen
real estate and that really makes them a
| | 00:28 | perfect candidate to pair with a Tab Control.
| | 00:30 | So, let's add a Tab Control to our form here.
| | 00:34 | So, I'll use this tab button here, and
I'm going to draw out a Tab Control that
| | 00:38 | takes up the majority of this form.
| | 00:40 | And I'll just Ribbondraw a box up there.
| | 00:42 | So, the first thing we need to do is connect
our form to the data source that we want to use.
| | 00:47 | We're going to be creating a form
that takes a look at a specific product
| | 00:50 | and then within the tabs shows information
about the orders that included that product.
| | 00:54 | So, first I'm going to go
up to the Property Sheet.
| | 00:56 | I'm going to change my properties, so
I'm looking at the properties of the Form.
| | 01:01 | Underneath the Data tab, I'm going to
go ahead and set the Record Source here
| | 01:06 | to my tbl_Products.
| | 01:08 | Next, I'm going to go to my Add Existing
Fields button on the Ribbon, click that
| | 01:13 | and I'm going to add
ProductID and ProductName to my form.
| | 01:17 | I'll just click on the first one, Shift+Click
on the second one, and drag and
| | 01:22 | drop them on the top of my
form in the Detail section.
| | 01:25 | Now I know my product names are pretty
long, so I'm going to go ahead and expand
| | 01:27 | this box a little bit and
that'll keep everything nice and neat.
| | 01:30 | So, now let's go ahead and add the
Subform that'll relate to the products
| | 01:34 | that we selected in the main form.
| | 01:36 | The Subform control is here on the Ribbon.
| | 01:38 | I'll go ahead and click that once
and then come down into my Tab Control.
| | 01:42 | When the Tab Control turns black, that
means whatever I'm about to do is going to
| | 01:46 | happen inside of the tab.
| | 01:47 | I'll go ahead and click and drag out
a box for my Subform and Access brings
| | 01:52 | up the Subform Wizard.
| | 01:53 | The Subform Wizard gives
me two different options.
| | 01:56 | I can either use existing tables and
queries, or I can use an existing prebuilt form.
| | 02:01 | We'll take a look at the existing prebuilt
form and we'll put one in Tab 2 in just a moment.
| | 02:06 | For now, just go ahead and choose Use
existing Tables and Queries and click Next.
| | 02:10 | We're going to be adding information that
relates to the products from our Orders table.
| | 02:14 | We'll go ahead and up here, select my
Orders table, and I'll add OrderID and
| | 02:21 | ProductID to the Selected Fields pane.
| | 02:23 | I also want to know who ordered these products.
| | 02:26 | So I can go back up to the top, pull
from my Direct Customers table and I can
| | 02:31 | grab FirstName and LastName. We'll press Next.
| | 02:36 | In the next Window, Access wants to know
how are these two tables related to the
| | 02:40 | main table, the Product table.
| | 02:43 | It's correctly identified that the
relationship is based off of the ProductID.
| | 02:46 | Go ahead and say Next.
| | 02:48 | We can go ahead and accept the
default file name here and say Finish.
| | 02:52 | Now, Access added the Subform to our
navigation control down here, and it also
| | 02:56 | placed it inside of our Tab Control here.
| | 02:59 | I'll go ahead and expand this out to
fill up my tab, and I can also delete the
| | 03:04 | label that came with it.
| | 03:05 | I'll click on the Label and
I'll press the Delete button.
| | 03:09 | Now I can move my Subform up a
little bit to fill up the tab.
| | 03:12 | So, my first tab is populated. Let's
go ahead and put a second one in Tab 2.
| | 03:17 | I'll click on Tab 2 to activate it,
I'll move back up to the Design tab of the
| | 03:21 | Ribbon, click on the Subform control here,
and draw the box again inside of Tab 2.
| | 03:29 | This time we'll choose to use an
existing form and I'll choose the Reviews form
| | 03:33 | that we built earlier in this chapter.
| | 03:34 | Let's go ahead and say Next.
| | 03:37 | Access again asks us how these are
related, and it's correctly identified that
| | 03:41 | they're related off of the ProductID.
| | 03:42 | So, I'll say Next and I can go ahead and
accept the default name again and say Finish.
| | 03:47 | Access places an instance of the form
that we build earlier inside of this tab.
| | 03:51 | Once again, I'll go ahead and delete the
label and I'll resize my form a little.
| | 03:55 | Now the only thing I need to do
is change the text that's on the tabs.
| | 04:02 | I'll click on the Page1 tab.
| | 04:05 | I'll close my Field List and
open up the Property Sheet.
| | 04:10 | On the Format tab you'll find the
Caption property, and this is where we type in
| | 04:15 | the text that we want to appear on the tab.
| | 04:17 | For this one, the first tab, I
want to Caption to read: Orders.
| | 04:21 | So, I'll type in Orders and press Enter.
| | 04:23 | Let's go ahead and switch to Tab 2.
| | 04:26 | Now, for the Caption for Tab 2, I want
to put the word Reviews and press Enter.
| | 04:34 | That changes the name of the tabs.
| | 04:37 | Let's go ahead and take a look at
our form and see how it's working.
| | 04:40 | I'll switch into Form View here, and
now I can scroll through my products--I've
| | 04:44 | got 90 products in our database.
| | 04:45 | I'll go to product number 8.
| | 04:47 | Product number 8 is our Extra
Virgin Olive Oil in the 32 oz size.
| | 04:54 | In this first tab I've got a subreport
that's showing me all of the orders that
| | 04:57 | included this product.
| | 04:58 | I've got the OrderID, the ProductID, and
then the name of the person who ordered it.
| | 05:04 | In the second tab here, I've got
another Subform that's displaying the contents
| | 05:08 | of the Reviews form that we built earlier.
| | 05:10 | So, I can see the reviews that have been
placed for this particular product, and one
| | 05:14 | person we see has rated it Good.
| | 05:16 | So the Subform object brings in yet
another way to view your content, and by
| | 05:21 | pairing Subforms with a tab control,
allows us to keep related information
| | 05:25 | together in one space efficient
location, so that it's easy to get to.
| | Collapse this transcript |
| Adding charts| 00:00 | The chart control can give insight into
your business's metrics by presenting a
| | 00:04 | dynamic, graphic 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, always updated link to your data
| | 00:16 | tables and queries.
| | 00:18 | Further, paring a chart with a query
gives you all the flexibility of adding
| | 00:21 | query criteria to instantly
refine the chart's dataset.
| | 00:25 | The Chart Object within Access is a feature
that hasn't been updated in several versions
| | 00:30 | and it can be a little
bit difficult to work with.
| | 00:32 | I will leave it to you to decide if the
insight that they provide is worth the
| | 00:35 | effort it takes to create them.
| | 00:37 | But in this movie, we are
going to create a chart object.
| | 00:40 | And in the next movie, we will bring
this chapter full circle and incorporate
| | 00:44 | some of the other form controls that will
take user input and alter the charts parameters.
| | 00:49 | Now the first thing we want to do is
identify what it is that we want to chart.
| | 00:52 | We are going to go ahead and create a chart
that looks at the number of orders over time.
| | 00:57 | We can build this chart directly off of
a table, but instead, we can build a
| | 01:01 | query that will gather the data first,
before feeding it into the chart.
| | 01:04 | So let's go up to the Create tab. We
will create a new Query in Design View.
| | 01:08 | I am going to build a
query off of my orders table.
| | 01:12 | I will double click Orders and say Close.
| | 01:15 | From the orders table, I need
the OrderID and the OrderDate.
| | 01:19 | I will double click on each to
add it to the Query Design Grid.
| | 01:24 | We can go ahead and run
this query to see the results.
| | 01:27 | And we will see that I
have 2200 records displayed.
| | 01:30 | Let's go ahead and save this query,
so we can use it in the chart.
| | 01:33 | I will name it qry_OrderDates.
| | 01:36 | I will press the Save button up
here or press Ctrl+S get the Save
| | 01:40 | dialog, qry_Orderdates.
| | 01:44 |
| | 01:45 | Go ahead and say OK and we can
go ahead and close the query.
| | 01:50 | Now let's start our chart.
| | 01:50 | We will go up to the Create tab,
and start a new Form in Design View.
| | 01:54 | I will make the form a little bit
bigger. I am going to drag the edge over
| | 01:58 | it to about the 10 inch mark and I'll scroll
down to the bottom and move it up a little bit.
| | 02:02 | The chart control is this one
here, with the three bars.
| | 02:07 | I will click on that once and drag
in a box to insert my chart control.
| | 02:11 | The Chart Wizard starts and Access wants to
know where the data is going to come from.
| | 02:15 | Again, I can choose one of my tables or
I can choose my view to Queries and use
| | 02:20 | the Query we just built.
| | 02:21 | I will select it here and say Next.
| | 02:25 | Now the query we built was specifically
built for this chart, so I will just go
| | 02:28 | ahead and take both fields by clicking
the double arrow to add it to the Fields
| | 02:32 | for Chart section.
| | 02:34 | Go ahead and say Next.
| | 02:36 | The next window asks what kind of chart we want.
| | 02:38 | Now we have lost different options.
Some of these will work better for some
| | 02:42 | data types than others.
| | 02:43 | For this chart I am going to
choose the simple line graph.
| | 02:46 | Go ahead and click the Line
Chart button here and press Next.
| | 02:50 | The next screen asks where
the data is going to fall.
| | 02:53 | And Access tries to fill these boxes in
for us. It usually gets it wrong, so let's
| | 02:56 | go ahead and drag these little fields
off of the drop zones, so we can start
| | 03:00 | with a fresh screen.
| | 03:01 | Now on my X-axis on the bottom,
I'm going to put the date.
| | 03:05 | We will drag OrderDate
and drop it on the axis.
| | 03:09 | Access automatically changes
this to OrderDate by month.
| | 03:12 | It's grouping all of our
orders together within a month.
| | 03:14 | If I double-click on it, I can change
the grouping level to, say, year or quarter.
| | 03:19 | I am going to leave it on month and say OK.
| | 03:23 | The Data section is where
our orders are going to go.
| | 03:25 | I will take OrderID, drag and
drop it on the Data section.
| | 03:29 | Access changes this to CountOfOrder.
| | 03:30 | So it's going to count up the number
of orders that follow in each month.
| | 03:35 | Let's go ahead and say Next.
| | 03:37 | Let's go ahead and give a title to our
chart. I am going to call mine Number
| | 03:40 | of Orders per Month.
| | 03:42 | I can choose to display a legend or not.
| | 03:43 | I am going to choose No legend,
and go ahead and say Finish.
| | 03:49 | Now Access populates a
chart object into our form.
| | 03:52 | But this graph actually has nothing to
do with the data that we just gave it.
| | 03:54 | It's just a placeholder image.
| | 03:56 | Let's go ahead and switch
into Form View to see the chart.
| | 03:58 | I will switch into Form View here and
Access looks like it's rendering the chart.
| | 04:03 | Occasionally this doesn't
render on screen properly.
| | 04:06 | I found that if you just press the
Refresh button here or try and change your
| | 04:09 | view using the drop-down menu
here, the chart will refresh.
| | 04:13 | I will press the Refresh button here.
| | 04:15 | And there's a basic chart. I leaves a
lot to be desired, so we have got a lot
| | 04:19 | of work ahead of us to format this properly.
| | 04:21 | If charting is something that is very
important to your organization, then I
| | 04:25 | would recommend looking into some of
the third-party charting add-ins that are
| | 04:28 | available for Access.
| | 04:29 | For now though, let's see what
some of the capabilities are with the
| | 04:32 | built-in charts.
| | Collapse this transcript |
| Linking controls| 00:00 | Now that we've seen all of the controls
that we have at our disposal when creating
| | 00:03 | forms and reports, let's bring this
full circle and piece a couple of them
| | 00:07 | together to control the chart
that we created in the last movie.
| | 00:10 | Let's take a look at that chart here.
| | 00:12 | I've got it saved as OrdersChart here as a form.
| | 00:14 | If I double click on it, it will open
the chart area, and again, you might have
| | 00:18 | to refresh the screen in
order to get the chart to draw.
| | 00:20 | I'll click the refresh
button and there's the chart.
| | 00:23 | We can see that we have data
from January 05 to July 2010.
| | 00:28 | The data for this chart is coming from
this query here called qry_OrderDates.
| | 00:32 | I'll double-click on this
query here to see the data.
| | 00:35 | I've got 2200 records that
are being pushed into the chart.
| | 00:39 | Let's go ahead and close both of these.
| | 00:43 | Now what I'd like to do is create a
form that allows my end users to type in
| | 00:47 | the date range that they are interested in,
instead of viewing the entire date range.
| | 00:50 | We can do that by going to Create tab
and Form Design. That'll give us a blank
| | 00:56 | form area where we can work.
| | 00:58 | Now, there's a couple of ways that we can
capture the information from our end users.
| | 01:04 | I could just add in a Text Box and
allow my end user to type in a date.
| | 01:08 | But that doesn't really give them any sort of
feedback as far as what dates are available.
| | 01:12 | They might not know where the
data starts and where it ends.
| | 01:16 | Instead, we can go ahead and use a Combo Box.
| | 01:18 | The Combo Box will allow them to choose
from a list of dates that are present in
| | 01:22 | the database. That way they
know what's available to choose.
| | 01:25 | I'll go ahead and click the Combo Box
button and I'll add one into my form.
| | 01:30 | Now, the Combo Box Wizard starts up,
and I'll tell you upfront that the Combo
| | 01:33 | Box Wizard isn't going to give as a
result that we need, but we're going to go
| | 01:36 | ahead and go through this wizard a
little bit, and I'll show you why this isn't
| | 01:39 | going to work for us.
| | 01:40 | We're going to choose the, "I want the combo
box to look up the values in a table or query."
| | 01:44 | Let's go ahead and say Next.
| | 01:47 | This table that we're pulling dates
from is going to be our Orders table.
| | 01:51 | So, I'll choose my Orders table and press Next.
| | 01:53 | Here is the Date field, the OrderDate.
I'll add that to my Selected Fields,
| | 01:59 | because that's what I want to
populate the list, and I'll say Next.
| | 02:02 | Access will automatically sort
them ascending, so I don't need to
| | 02:05 | choose anything here.
| | 02:06 | I'll say Next, and this is why the
data's not going to work for us here.
| | 02:10 | You can see that my
OrderDates are listed multiple times.
| | 02:12 | For instance, I had two orders on the
10th of January 2005 and three orders on
| | 02:17 | the 12th of January 2005.
| | 02:18 | I don't want these dates to
show up multiple times in my list.
| | 02:22 | We'll go ahead and cancel out of the
Wizard to cancel all of this and move back
| | 02:26 | to an Unbound Combo Box.
| | 02:28 | Let's go ahead and attach
the data source manually.
| | 02:32 | I'll open up the Property Sheet and
in the Data tab for the Combo Box,
| | 02:37 | we're going to go to the Row Source.
| | 02:38 | If I were to connect the Control
Source to this Combo Box, it would automatically
| | 02:43 | will be pulling data from a table and
would feed data back into the table if
| | 02:46 | we were to edit it.
| | 02:47 | I don't want to interact with the table at all;
| | 02:49 | I just want it to reference the table to
populate the list that'll appear here.
| | 02:52 | So instead, we're going to connect this
to the Row Source, not the Control Source.
| | 02:57 | I'll click on the Build button in the Row
Source section and that will open up a query editor.
| | 03:02 | This is an embedded query
that's embedded inside of that form.
| | 03:06 | I'll choose my Orders table here
and say Add and Close, and then I'll
| | 03:11 | double-click on OrderDate
to add that to the query.
| | 03:15 | I'll turn on my Totals row by pressing the
sigma button up here that will turn on Total:
| | 03:20 | Group By.
| | 03:21 | Now, all the similar dates will
group together into a single field.
| | 03:25 | I'll click on Run, and I can see that I
have 1350 individual dates that orders
| | 03:31 | were placed on. Some might have multiple orders.
| | 03:34 | Let's go ahead and close the query and
I'll save the changes. And now this query
| | 03:38 | is going to feed in what
will appear into this Combo Box.
| | 03:41 | I'm going to make it a little bit
wider here and I'll change the label.
| | 03:45 | I'll double-click on it and highlight it.
| | 03:46 | And I'll change this to Start Date.
| | 03:50 | Now I have a place where my end users
can choose the date that they want
| | 03:53 | the data to start at.
| | 03:54 | I'll need a second copy of this in
order for them to choose the date that they
| | 03:57 | want the data to end at.
| | 03:58 | I'm going to drag a box around both
of these and then press Ctrl+C on the
| | 04:03 | keyboard and then Ctrl+V to place a copy.
| | 04:06 | I'll change the label here to End Date.
| | 04:09 | Double click on it again and type in End.
| | 04:12 | Now, I can go ahead and
name both of these objects.
| | 04:15 | I'm going to click on the Start Date
Combo Box here and in the other tab I am
| | 04:20 | going to find the Name property.
| | 04:22 | Instead of Combo0, I'm going to change this
to StartDate with no space and press Enter.
| | 04:29 | I'm going to click on the Combo Box for our
End Date, and I'll give it the name
| | 04:34 | EndDate, with no space.
| | 04:37 | Now, let's go ahead and save our form.
| | 04:39 | I'll go up to the Save button or press
Ctrl+S on the keyboard, and I'll name
| | 04:43 | this form GrafDateRange.
| | 04:44 | Now, there's one other property that I
can add here for my End Date Combo Box.
| | 04:49 | I can put in a Validation Rule that
makes sure that the end user selects a date
| | 04:53 | that's after what they've
selected for Start Date.
| | 04:55 | If I go into the Data tab,
| | 04:58 | under Validation Rule I
can click the Build button.
| | 05:02 | This will bring up the Expression
Builder and I can type in the expression
| | 05:06 | "greater than" by pressing Shift+period.
| | 05:08 | and then drill into my Forms > All Forms.
| | 05:12 | I'll scroll down to find my
GraphDateRange form that I'm working with and I'll
| | 05:16 | find the StartDate field,
and I'll double-click on it.
| | 05:19 | So, now I've got an expression that says
it must be greater than the StartDate field.
| | 05:24 | Say OK.
| | 05:26 | In our Validation text down here, there is an
error message that'll pop-up if they pick a date
| | 05:30 | that's before this date range.
| | 05:32 | I'll type in, Please enter a date
greater than the start date, and press Enter.
| | 05:38 | Let's save our from again. Press Ctrl+S,
and take a look at it in Form view.
| | 05:42 | It's looking pretty good.
| | 05:45 | I've some Combo Boxes here
and I can select dates in.
| | 05:47 | Let's go back into Design
View and add one more element.
| | 05:50 | Let me go ahead and click on this Button object.
| | 05:53 | I'm going to create a
button that will open up my form.
| | 05:57 | I'll add that down here.
| | 05:58 | I'll go to From Operations, Open Form,
and the form I want to open is our Chart,
| | 06:04 | I'll click on the OrdersChart. Say Next.
| | 06:08 | Access then wants to know how
we want the button to look.
| | 06:10 | I can either choose a
Picture or I can type in some text.
| | 06:13 | And instead of OpenForm, I'm
going to type in View Graph.
| | 06:16 | Go ahead and say Finish and
that'll populate my button.
| | 06:20 | Switch back into Form View, and now
the only thing left to do is to loop in
| | 06:26 | my query into this.
| | 06:27 | I got two boxes here.
| | 06:29 | I've got Start Date and End Date.
| | 06:31 | Now, I can open up my query.
| | 06:34 | I'll switch into Design View and I'll
use those Start Date and End Date boxes on
| | 06:39 | my form to populate a
criterion here in my query.
| | 06:41 | I'm going to right-click in
this criteria, under OrderDate.
| | 06:45 | Right click and say Build.
| | 06:49 | This will bring up the Expression Builder again.
| | 06:51 | Now, I'm going to type in the expression
"between" and I'm going to go into my
| | 06:56 | Form > All Forms. I'll scroll down till I
find the GraphDateRange form that we're
| | 07:04 | working with and I'll say
double click on StartDate.
| | 07:06 | Then I'll type in the word "and"
then I'll double click on EndDate.
| | 07:12 | What I've just done is built an
expression that says whatever dates are showing
| | 07:15 | in the query must be between whatever
I've selected on the form in the Start
| | 07:20 | Date box and whatever I've
selected on the form in the End Date box.
| | 07:24 | We'll go ahead and say OK.
| | 07:26 | I can go ahead and close and save my query, and
I'll save my form again just for good measure.
| | 07:33 | Now, we'll select a date in the Start Date box.
| | 07:35 | For instance, I'll choose the 14th of
January 2005 and I'll try and select an
| | 07:40 | end date that's before that.
| | 07:42 | I'll choose the 10th of January 2005.
| | 07:45 | Access gives me a warning message.
| | 07:46 | Please enter a date greater than the start date.
| | 07:48 | Okay, I'll go back in, change
my End Date to something greater.
| | 07:52 | Let's choose 17th of January
2006, and I'll say View Graph.
| | 08:00 | Again, I might have to refresh my graph,
and now I've got a data range that runs
| | 08:04 | from those two dates specified.
| | 08:05 | I can close the chart here.
| | 08:07 | I can select different dates here.
| | 08:09 | For instance, I'll choose an End
Date that's further in the future, View
| | 08:16 | Graph, again refresh it, and now I've got a
different graph with a different data range.
| | 08:21 | So, how cool is that?
| | 08:22 | Using a few simple objects, I was able
to create a couple of selection Combo
| | 08:27 | Boxes to choose options from.
| | 08:28 | I press a button and instantly get
a chart that meets my specifications.
| | 08:32 | I can change my selections up as often
as I want and get an endless number of
| | 08:37 | charts that highlight
exactly what I want to look at.
| | 08:40 | Once you get comfortable working with
each of the control objects available to
| | 08:43 | you within Access, it's going to be
up to you to be creative and figure out
| | 08:47 | effective ways to hook each piece
together to create your database application.
| | Collapse this transcript |
|
|
4. Getting around the DatabaseCreating the main menu| 00:00 | The forms that we've looked at so far
in this course have all been linked to a data
| | 00:04 | source and pulled records
from our database tables.
| | 00:06 | But forms don't always
have to interact with data;
| | 00:09 | they can be stand-alone elements that
provide interactivity for our end users.
| | 00:13 | In other words, a form can simply be a
blank area where we can group buttons and
| | 00:17 | capture user intent to help
them navigate the database.
| | 00:21 | When used appropriately, Navigation
forms can completely remove any need for
| | 00:25 | your users to dig around in the Navigation pane.
| | 00:28 | You can control exactly what your
users have access to and, often more
| | 00:31 | importantly, what they don't have access to.
| | 00:34 | In this movie we're going to create a
main menu that'll help our end users
| | 00:37 | navigate the Two Trees database.
| | 00:39 | We'll start with a fresh, clean, blank
form in Design View and add in some buttons
| | 00:44 | that trigger macros.
| | 00:45 | I'm going to go to the Create tab
and create a new form in Design View.
| | 00:48 | Now, let's go ahead and save
this form right off the bat.
| | 00:52 | I'll press Ctrl+S or you can use the
icon in the top left corner, the little
| | 00:55 | icon of a floppy disc.
| | 00:57 | Press Ctrl+S and I'm
going to call this Main Menu.
| | 01:00 | Now first we need to set up the
properties of our Main Menu form.
| | 01:05 | I want this to be a free-floating window
that appears in the center of the screen.
| | 01:09 | And since I have to spend my day
sitting in front of a computer working on
| | 01:12 | this database all day, I want to make
sure that I give myself a little bit
| | 01:14 | of scenery to look at.
| | 01:16 | In the Property Sheet for my form, I'll
go to the All tab and I'll just scroll
| | 01:22 | through this list and I'll
change things as I see them.
| | 01:25 | First, I want to change this Caption property.
| | 01:27 | I'm going to change the Caption that
will appear at the top of the menu to Main
| | 01:31 | Menu with a space in it.
| | 01:32 | Let's go down the list a little bit.
| | 01:35 | I'll change the Pop Up property to Yes.
| | 01:39 | Continuing down the form I'm just going
to press the Tab key to move down here
| | 01:42 | until I get to the Picture property.
| | 01:44 | I want to add a background picture to my form.
| | 01:46 | I'll choose the build button to choose
a file from my computer and I'll browse
| | 01:50 | to my Desktop into my Exercise folder
into the Chapter 4 folder and I'm going
| | 01:54 | to choose Ojai500x333.
| | 01:57 | This is an image of where olives come from,
the beautiful Ojai Valley in California.
| | 02:01 | Go ahead and say OK.
| | 02:04 | And that puts it into the background
on the Detail section of my form.
| | 02:07 | Let's go down a little bit.
| | 02:08 | I'm going to press Tab to navigate
through my Property Sheet some more.
| | 02:11 | When I get to the Auto Center
property I'm going to change that to Yes.
| | 02:15 | My form is going to automatically center
into the middle of the screen when it opens.
| | 02:19 | Let's go down some more.
| | 02:20 | I'm going to change Border Style and
instead of being Sizable I want to say Thin.
| | 02:26 | This will make the border
really thin and not intrusive.
| | 02:29 | Continuing down I'm going
to go to Record Selectors.
| | 02:31 | Record Selectors are that gray bar
that appears on the left side of my form.
| | 02:34 | Since this form isn't even connected to a
data source, I don't need Record Selectors.
| | 02:38 | I'm going to change this property to No.
| | 02:41 | Navigation Buttons--again, those
appear at the very bottom of the form.
| | 02:43 | They'll let you page through
forward and backward through your records.
| | 02:47 | Again, there're no records here, I can
easily say No to Navigation Buttons and
| | 02:50 | get them off the screen.
| | 02:51 | Let's skip down a little bit more
until we get to the Scroll Bars property.
| | 02:58 | Right now it's set to Both to show scroll
bars in the vertical and horizontal axis.
| | 03:02 | I'm going to change that to Neither.
| | 03:04 | Let's go ahead and resize my form a little bit.
| | 03:07 | The image that I chose in the
background is 500 pixels wide.
| | 03:10 | I want to make my Main Menu exactly the same
size so that the image fully fills up the form.
| | 03:15 | I can do that with this Width property here.
| | 03:18 | Right now, it's set to 6.1694 inches.
| | 03:21 | I can highlight that value and
type in 500px. That stands for pixels.
| | 03:26 | When I press Enter, Access converts that
back into inches and it resizes my form
| | 03:30 | so now it's exactly 500 pixels
wide to match the background image.
| | 03:34 | Now the Height property is a
property of the Detail section.
| | 03:37 | I want to make the Height
match the image there as well.
| | 03:39 | I am going to click on this background
back here to select the Detail section.
| | 03:44 | The Detail section Height property
here is where I'll set the height.
| | 03:48 | I'm going to change that to 333
pixels, again to match the image.
| | 03:53 | Now, my image is exactly
the same size as my form.
| | 03:56 | Let's add a couple of elements to
my form. Let's add a title here.
| | 03:58 | I'll click on Label, click here in the
form, and this is going to be my Main Menu.
| | 04:05 | I'll type Two Trees Olive
Oil Company and press Enter.
| | 04:12 | I'll add one more label below it and
I'm going to type in here Main Menu.
| | 04:17 | Okay, I'll highlight both of these labels.
| | 04:21 | I'm going to go ahead and align their
text, so this is the right side of the
| | 04:25 | bounding box and I'll switch the
Arrange tab and align them right so they
| | 04:30 | line up with each other.
| | 04:31 | Switch back into the Design tab and I
can change their Font and their Font Size.
| | 04:35 | I'll go ahead and change the Font here.
| | 04:38 | I'll scroll up the list
till I find Arial Black.
| | 04:40 | I'll go ahead and change the Size,
maybe 12 points, and then I'll resize these
| | 04:45 | boxes a little bit, so
that it fits all of that text.
| | 04:47 | I can also change the color of the font.
| | 04:52 | Maybe I'll select my Font Color here
and change it to? white probably
| | 04:56 | won't show up very well, maybe?how about red?
| | 05:02 | You can change whatever you like;
| | 05:04 | these are really subjective
changes that I'm making here.
| | 05:06 | I'll move these up using my arrow
keys to move it into the top corner, and
| | 05:12 | there's the beginning of my Main Menu.
| | 05:13 | Now let's add a couple of
navigation buttons so I can navigate to other
| | 05:16 | elements within my database.
| | 05:18 | I'll go ahead and click on my button
control, this Button button right here in
| | 05:21 | the Design tab, and I'll click
once in my Detail section of my form.
| | 05:25 | That will start the Command Button Wizard, and
I could choose what I want this button to do.
| | 05:29 | Let's go ahead and say
FormOperations, Open a Form.
| | 05:35 | We'll say Next and I'll choose the
EmployeeDirectory that we created earlier in this course.
| | 05:40 | Let's say Next. I could choose how we
want to display the form--to open the form
| | 05:45 | to a specific data or to open
the form and show all the records.
| | 05:48 | Opening the form and showing all the
records will be the same as double-clicking on
| | 05:51 | it in the Navigation pane.
| | 05:52 | Go and say Next. We can choose how we
want the button to display, either with a
| | 05:56 | photo or I could put Text. And we can
be specific here. Instead of just opening a
| | 06:01 | form, any form, we can we can
specific in what form we're opening.
| | 06:04 | I'm going to say View the
Employee Directory and we'll say Finish.
| | 06:11 | Now I've got a button on my Main
Menu here that will take us directly to
| | 06:15 | my Employee RibbonDirectory.
| | 06:16 | Let's go ahead and move this over here to
the left and we'll add another button below it.
| | 06:21 | I'll go back up to my Design tab on the
Ribbon, click on the Button control, and
| | 06:26 | click to add a button again.
| | 06:28 | This time we'll open up a different form.
| | 06:30 | Let's go ahead and open up our product
Reviews section that we created earlier.
| | 06:33 | Again, I'll go to FormOperations,
open a form, press Next. We'll choose the
| | 06:40 | Reviews form, Next, go ahead and say
show all records, Next. The text for this
| | 06:48 | button will say Enter a Product Review.
| | 06:51 | Go ahead and say Finish to add that button.
| | 06:58 | Now I've got two buttons here.
| | 06:59 | Let's go ahead and add one more button.
| | 07:03 | Click on the Button control one more time.
| | 07:04 | This time I'm going to add a button
down here that closes the database.
| | 07:09 | Click there, we'll go to Application and
then choose the QuitApplication action.
| | 07:14 | We'll go ahead and say Next.
| | 07:16 | We can choose how we want the button to
appear. Again I'm going to choose Text
| | 07:21 | and I'm going to say Exit Database.
| | 07:23 | Go ahead and say Next and Finish.
| | 07:29 | Let's go ahead and take a look at how our form
is working and see if all the buttons work.
| | 07:33 | I'll switch into Form View and now I
can see I've got a Main Menu; it's a
| | 07:37 | free-floating screen here.
| | 07:39 | I can click on View Employee Directory
and it takes me to the EmployeeDirectory.
| | 07:43 | Now we've got a little bit of an issue
here because this Main Menu screen still
| | 07:47 | stays on top of my Employee Directory.
| | 07:49 | So let's go back and edit the
macro a little bit to provide the
| | 07:52 | better functionality.
| | 07:53 | I'll go ahead and close the
Employee Directory from the background.
| | 07:57 | Select my Main Menu again.
| | 07:59 | Because it's a free-floating window, I
can't switch into Design View using the
| | 08:02 | button up here on the Ribbon.
| | 08:04 | I'll move down back into the Main
Menu and I'll right-click here, and I can
| | 08:07 | choose Design View from the pop-up list.
| | 08:11 | Back in Design View I'll select
my command button for the View
| | 08:13 | Employee Directory.
| | 08:14 | I'll click on the Event tab to take a
look at its properties and I can see
| | 08:18 | that the On Click event is set to this
Embedded macro. That was created through the Wizard.
| | 08:22 | If I click on the Build button to
the right here, I can edit that macro.
| | 08:26 | I'll press the Build button to
jump into a macro editing session.
| | 08:30 | Now I want this button to actually do two steps.
| | 08:32 | Instead of just opening the form, the
Employee Directory, I want it to actually
| | 08:37 | close the Main Menu and then
open the Employee Directory form.
| | 08:41 | I'll click on the Record Selector
here to the left of this first action and
| | 08:45 | I'll choose Insert Rows.
| | 08:47 | That'll insert an action row right
before OpenForm, and I'll choose Close.
| | 08:54 | With the Action Close here, Access is
going to ask me for some arguments down
| | 08:58 | below, the Object Type and the Object Name.
| | 09:01 | I can choose that I want to
close the Main Menu, which is a form.
| | 09:04 | So for Object Type I'll select Form and
then Object Name I'll choose MainMenu.
| | 09:10 | Now I've got a button that runs a
macro that runs two action steps.
| | 09:15 | First, it's going to close the Main
Menu form and then it's going to open up
| | 09:18 | the Employee Directory.
| | 09:19 | I can go ahead and close the macro to
save it and say Yes to save changes.
| | 09:24 | Let's go do the same thing for
the Enter a Product Review form.
| | 09:27 | Click on Enter a Product Review.
| | 09:30 | On the Event tab, I'll edit the Embedded Macro.
| | 09:33 | Pressing the build button, I'll add
another line here, I'll click on the Record
| | 09:38 | Selector and add Insert Rows.
| | 09:41 | The Action again is going to be a Close
action, so I'll type in close and press Enter.
| | 09:45 | And down below, my Action Arguments,
which form do I want to close?
| | 09:49 | Well, I want to close that Main Menu form.
| | 09:50 | So I'll select From and Main Menu.
| | 09:55 | Close the macro and save the changes.
| | 09:57 | And now my buttons do two steps. They'll
close the Main Menu, and then they'll open
| | 10:01 | their respective form. Go
ahead and test that out.
| | 10:03 | Go into Form View. Again I've
got this free-floating window.
| | 10:07 | I'll choose View Employee Directory.
| | 10:09 | It's going to ask me if I want to
save changes the first time, because I
| | 10:13 | haven't saved the changes to this form.
Go ahead and say Yes. It will close it
| | 10:17 | and then open the Employee Directory.
| | 10:19 | Now, I would need to provide some
additional navigation to go from my Employee
| | 10:23 | Directory back to the main menu.
| | 10:24 | So let's add another button
to my employee directory here.
| | 10:28 | I'll switch into Design View, I'll take a
button, and I'll add the button down here.
| | 10:33 | We'll once again go to Form Operations,
open a form, Next. This time we are
| | 10:39 | going back to the Main Menu so I'll select it,
Next, the Text will be Return to Main Menu.
| | 10:51 | Next, and we'll finish accepting
that default name.
| | 10:55 | Once again though we want to close this
form first, so we'll edit the Embedded Macro.
| | 11:00 | We'll go to the Event tab, the
On Click Embedded Macro, and build.
| | 11:07 | We could have built this macro manually;
| | 11:09 | we could also use the wizard to give us a start.
| | 11:10 | There's lots of different
ways that we could do this.
| | 11:12 | This time I am going to right-click
here and choose Insert Rows from the menu,
| | 11:15 | instead of inserting a
row up here from the Ribbon.
| | 11:17 | So Insert Rows there, our
Action is going to be Close.
| | 11:23 | This time we can take a
look at some of the hints here.
| | 11:26 | The hint over here for the Close Action
says Select the object type to close.
| | 11:30 | Leave blank to select the active window.
| | 11:33 | Since this button is actually being run
directly on a form, we really don't even
| | 11:37 | need to specify the action arguments
about which form to close. If we leave it
| | 11:40 | blank, it will close the Active form,
which is the form that the button is on, so
| | 11:44 | I'll just close it right here.
| | 11:45 | Close, save my changes, and now I've got
a button here that returns to the Main Menu.
| | 11:51 | I'll close the Property Sheet
and switch back into Form View.
| | 11:57 | Now, I'll test out my
button, return to Main Menu.
| | 11:59 | Again it's going to ask me to save
changes the first time. I'll say Yes, and now
| | 12:04 | it takes me back to my Main Menu.
| | 12:06 | Now I've got a navigation system to go
from the Main Menu. I can go to View the
| | 12:09 | Employee Directory and go back
to the Main Menu when I'm done.
| | 12:13 | The button down here will exit your
database. It'll ask you to save changes if
| | 12:16 | you press it, and there we go.
| | 12:17 | As our database grows and we add
functionality through other forms and reports
| | 12:21 | and queries, we can return to this Main
Menu form and add additional navigation
| | 12:25 | buttons that'll get our users to
where they need to 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, stays
| | 00:04 | on the screen for a few
seconds, and then disappears.
| | 00:07 | You'll probably see 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 start up sequence.
| | 00:17 | In the last movie we
created this Main Menu form;
| | 00:19 | we'll go ahead and
double-click on it to open it.
| | 00:21 | Now what I'd like to do is create a
Splash Screen that opens up first when
| | 00:25 | you open the database.
| | 00:26 | After a few seconds it'll disappear
and then the Main Menu will pop up.
| | 00:30 | Now any time that I can reuse content
that I have already created, I'm going to
| | 00:34 | jump at that chance; it's going to save me time.
| | 00:35 | So I'm going to go ahead and use
this Main Menu as a basis and create a
| | 00:39 | Splash Screen from it.
| | 00:40 | Let's go ahead and close the Main Menu
form and I am going to right-click on it
| | 00:44 | in the navigation pane, and I'll say Copy.
| | 00:46 | Then I'll right click on the
navigation pane again and say Paste.
| | 00:52 | Access is going to ask me what I want to name
this new copy and I'll call it SplashScreen.
| | 00:58 | and say OK.
| | 00:59 | Now let's go ahead and edit this
Splash Screen. I'll right-click on it in the
| | 01:02 | navigation pane and jump
directly to Design View.
| | 01:05 | Now a Splash Screen is not going to
have any buttons on it, so I can go ahead
| | 01:09 | and highlight these and get rid of them.
| | 01:10 | I'll drag a box around all
three buttons and press Delete.
| | 01:14 | Now I want to change some of the
properties for my Splash Screen, so I'll make
| | 01:17 | sure that my Form is selected in the
Property Sheet, and again, if it's not
| | 01:20 | open, you can press F4, Alt+
Enter, or use the button up here.
| | 01:24 | I'll make sure Form is selected in the drop
down menu and I'm going to go to the All tab.
| | 01:29 | Now I can go through this list
and change some of the properties.
| | 01:33 | For the Caption, I don't want anything to
display. I'm going to get rid of Main Menu here.
| | 01:36 | I am going to go down until I
get to the Picture property.
| | 01:39 | Instead of the Ojai500x333 picture,
I'm going to load a different one. Let's
| | 01:44 | press the Build button, and I'll go
to my Exercise Folder which is on the
| | 01:48 | desktop. Inside of Chapter 4, and I want to
choose this OliveSplash500x125 image. Say OK.
| | 01:56 | Now I don't want it appear centered
in the form like this, so the Picture
| | 02:00 | Alignment property is currently set to
Center. I'll change that to Top Left.
| | 02:05 | That'll lock it to the top of my form.
| | 02:06 | Let's go down a little bit further
and I'll find the Border Style property.
| | 02:11 | I'm going to change this from Thin to None, so
there is no border whatsoever on my Splash Screen.
| | 02:16 | Let's go ahead and change some of the
properties for the Detail section of my form.
| | 02:20 | I'll click anywhere on the form here.
That'll select Detail, and it will jump to
| | 02:24 | the Detail property sheet.
| | 02:25 | Here I can set the Height
to 2 inches. I'll type 2.
| | 02:28 | I can change the Background color.
Instead of being white down here, I'll press
| | 02:33 | the Build button, and I'm going
to select this Green 3 color chip.
| | 02:38 | Okay, let's go ahead and move
these labels down a little bit.
| | 02:41 | I'll click and Shift+Click on both labels
and then I'll drag them down to the bottom.
| | 02:45 | Then I'll select anywhere else to deselect them.
| | 02:49 | Now instead of Two Trees Olive Oil
Company Main Menu, I want this to read Two
| | 02:54 | Trees Olive Oil Company Access Database,
so I'll double-click on Main Menu. I'll
| | 02:58 | change this to Access Database.
| | 03:00 | So there is my Splash Screen, I have got
a couple more properties that I want to
| | 03:06 | change that control its startup behavior.
I'll go back to the Form properties by
| | 03:10 | clicking this box up here, or
changing if you're in the Main Menu.
| | 03:14 | On the Event tab, it lists is all
the different ways that you can add
| | 03:17 | interactivity to the form.
| | 03:18 | I'm going to scroll to bottom
and find the On Timer event here.
| | 03:24 | The On Timer event will automatically
fire after a set interval that we'll type in
| | 03:28 | here. Let's go ahead and build the Macro first.
| | 03:32 | So for the On Timer event,
I'll click on the Build button.
| | 03:36 | Access is going to ask us which
building method we like to construct our
| | 03:39 | code. We could either use the Macro Builder, the
Expression Builder, or if we know Visual Basic,
| | 03:44 | we can jump to a Code Builder. I'm
just going to choose the Macro Builder and
| | 03:48 | say OK and now we can build our macro
that's going to happen automatically.
| | 03:53 | The first action is going to be Close.
| | 03:56 | The action arguments, if you remember
for the last movie, I can leave blank.
| | 03:59 | It's going to close the active form
which is going to be the Splash Screen.
| | 04:03 | The next Action is going to be OpenForm.
I'll press Tab. The action arguments for
| | 04:09 | this are going to be which form I want
to open, so I'll click in the Form Name
| | 04:13 | Action Argument here, and way over
here, I'll choose the MainMenu form.
| | 04:17 | So we're going to Close the Splash
Screen and then open the Main Menu.
| | 04:22 | Let's go ahead and close this, and we
will save changes to our macro, so now the
| | 04:27 | On Timer macro has been built.
| | 04:29 | Now I just need to specify the Timer Interval.
| | 04:31 | The Timer Interval is a measurement in
milliseconds, so in order to type in 3
| | 04:35 | seconds, I need to type 3000, 3000
milliseconds. I'll press Enter to finalize
| | 04:41 | that into the properties, and we'll go
check it out and see if it's working.
| | 04:44 | I'll switch into form view, and after
about 3 seconds, Access is going to ask me
| | 04:49 | to save this, because it is the first
time it's being closed, so Yes, I'll save
| | 04:52 | it, and my Main Menu opens.
| | 04:54 | Let's close the Main Menu and try that again.
| | 04:57 | Double-click on the Splash Screen, it
appears centered in the screen, waits
| | 05:00 | three seconds, it closes,
and then the Main Menu opens.
| | 05:03 | So the next thing I want to do is set
my access database to start the Splash
| | 05:08 | Screen when Access starts the program.
| | 05:10 | I'm going to close Main Menu. And I'm going to
go up to the Office button here and I'm going to
| | 05:15 | go into Access Options.
| | 05:18 | I'll click on the Current Database
section, where I can change some of the
| | 05:21 | properties for the current database.
| | 05:23 | This display form property here, I'll
change to automatically load my SplashScreen.
| | 05:29 | So when I load this database in the
future, the SplashScreen will automatically
| | 05:32 | open, it'll wait 3 seconds, close,
and then open up my Main Menu.
| | 05:37 | I can change a couple of other
properties here. For instance, I can change to
| | 05:41 | display the navigation pane. I can turn
that off if I want to keep people out
| | 05:44 | of the navigation pane. Now go ahead and say OK.
| | 05:48 | You must close and reopen the Current Database
for the specified option to take effect. Okay.
| | 05:52 | Now this is a good opportunity, we
can go back to our Main Menu form and test
| | 05:56 | out this Exit Database button.
| | 05:57 | So I double-clicked on Main
Menu and I'll press Exit Database.
| | 06:00 | It's going to ask me if I want to save
the contents of my Clipboard, and I can
| | 06:05 | or can't. It doesn't really matter here.
| | 06:07 | And now I'll open up this Exercise
File again, the 4-2 SplashScreen. I'll
| | 06:11 | double-click on it. You'll notice my
navigation pane is gone. The Splash Screen popped
| | 06:15 | up, waited a few moments, closed,
and now my Main Menu is here.
| | 06:20 | Now my end users have a really clean
interface to move out of the database. I can
| | 06:24 | view the Employee Directory.
| | 06:25 | I can Return to the Main Menu.
| | 06:27 | I can Enter a Product Review or I can
finish my session and Exit the Database.
| | 06:31 | One last thing before we go. I don't
want the navigation pane over here being
| | 06:35 | missing to make it feel like
anything is secured in your database.
| | 06:39 | The shortcut key for the navigation pane is F11.
| | 06:41 | If I press F11, the
navigation pane pops right back.
| | 06:48 | So, this isn't a way to secure your
database. It's just a way to keep things out of
| | 06:51 | sight and possibly out of mind.
| | 06:53 | Another way that you can get around the
startup options. If I close this database again...
| | 06:56 | If I hold down the Shift key while double-
clicking on the file, Access will bypass
| | 07:02 | all of the startup options, so it'll skip
closing the navigation pane and it will skip
| | 07:07 | opening the Splash Screen.
| | 07:08 | So again, this isn't a way to secure
your database in any sense, but it is a way
| | 07:12 | to keep the interface clean for your end users.
| | 07:14 | So a well-thought-out user interface is
something your end users will appreciate.
| | 07:18 | These little bits of polish and the
details that you put in your database can
| | 07:22 | take something that might otherwise
just be a tool and make it something that
| | 07:25 | people actually enjoy using.
| | Collapse this transcript |
| Creating a customer form| 00:00 | So far we've taken a look at how we can
create navigation forms to move around
| | 00:04 | our database, we've created
reference forms in our employee directory,
| | 00:08 | and we've created a form that looks up
orders that include a particular product.
| | 00:11 | Let's now create a form that could be
use to input records into our data tables.
| | 00:15 | We'll create a form that can be used to
use to capture information about our new
| | 00:18 | customers and we'll make use of a
property of the form called Data Entry.
| | 00:23 | This time, instead of creating a form
from scratch, we'll make a use of one of
| | 00:26 | the shortcuts in Access and that's
creating a form built off a table we have
| | 00:29 | selected in our navigation pane. I'll
go ahead and select our DirectCustomers
| | 00:33 | table, go to the Create tab, and press Form.
| | 00:36 | Now Access built us a quick form based
off of our DirectCustomers table. If I
| | 00:41 | scroll down we'll see that includes a
subform down here, this gathering order information.
| | 00:46 | We're going to use this as a basis for
our form. We are going to get rid of the
| | 00:49 | elements that we don't need.
| | 00:50 | Let's go ahead and switch into Design View.
| | 00:51 | I'm going to scroll to the bottom first
and I'm going to get rid of that subreport.
| | 00:57 | So I'll click on it once and press
Delete. We'll scroll up to the top. We'll
| | 01:01 | get rid of everything in the header
section as well. I'll click on the title,
| | 01:05 | delete it, click on the little icon and
delete it, and I'll move this detail up to
| | 01:09 | get rid of that header.
| | 01:11 | Next I want to organize all of
the fields that are on this form.
| | 01:14 | I can select everything at once by
pressing this button here, Select All, or
| | 01:18 | pressing Ctrl+A. I'll select everything.
| | 01:21 | I'll go into the Arrange tab, and I want
to arrange everything to the Shortest.
| | 01:26 | It's going to resize our elements so
they all match the shortest element, which
| | 01:29 | I believe is this Customer ID right
here, so I'll press To Shortest and then
| | 01:33 | they'll all get narrower.
| | 01:34 | Now I'm going to scroll over to the right
and I'm going to make these a little bit less wide.
| | 01:37 | I'll select off of them once and then
highlight these fields over here, so
| | 01:41 | I'm not selecting the labels on the
left. And once I get the a double headed
| | 01:47 | arrow over here, I'll click once and
drag them narrower, like that, and I'll
| | 01:53 | make my form narrower as well. So I'll
go over and drag the edge of the form
| | 01:56 | over as well.
| | 01:57 | Okay. Form is getting a little bit more
manageable now. Let's go ahead and change
| | 02:03 | these titles. Instead of just the
field names that came directly out of the
| | 02:05 | table, we're going to
add spaces in between here.
| | 02:08 | So instead CustID, all one word, I'll
double-click once and then I'll add a space.
| | 02:13 | I'll do the same for FirstName and LastName.
| | 02:21 | Next, I want to change some
of the properties of my form.
| | 02:23 | So I'll make sure I'm selecting the
form properties here by choosing the box
| | 02:26 | between the two rulers. We're
changing it here in the drop-down menu.
| | 02:30 | We'll go to the Format tab first.
| | 02:32 | The Caption for this is going
to read, Add a new customer.
| | 02:35 | We can also turn off our Record
Selectors and Navigation Buttons as well.
| | 02:42 | So Record Selectors: No. Navigation Buttons:
No. Let's go ahead and change to our Data tab.
| | 02:49 | On the Data tab, there is a property
called Data Entry. It's currently set to No.
| | 02:53 | The Data Entry set to No means that
my form is going to be displaying data
| | 02:58 | directly from my data table.
| | 02:59 | If I change this to Yes, it's going
to convert this into a data entry form,
| | 03:03 | which will allow me to add one record
at a time, so I'll change Data Entry to
| | 03:07 | Yes. Let's now go to the Other tab.
| | 03:09 | On the Other tab, I'm going to
change this into a Pop Up form.
| | 03:11 | We're going to change No to Yes.
| | 03:14 | Let's go ahead and check
out how our form is working.
| | 03:16 | I'll click back on my form in the
Detail section, go to the Design, and
| | 03:20 | change the form view.
| | 03:21 | So there is my Pop Up form.
| | 03:24 | One of the things I see I need to
change here is the height. It's pretty tall,
| | 03:27 | But I do need to leave a little
space here because I'm going to add some
| | 03:30 | command buttons down below.
| | 03:31 | But basically it gives me a blank
slate to add a new record into my
| | 03:35 | Direct Customers table.
| | 03:36 | Let's go ahead and right-click anywhere
in here and select Design View. And I am
| | 03:43 | going to bring the bottom up, so I'm
going to drag to the bottom, and I'll move
| | 03:47 | this up here, but I'm still going
to leave some space for my buttons.
| | 03:49 | Okay, let's add a couple of navigation
buttons. Click on the Button here, and we're
| | 03:55 | going to add a button down there to the bottom.
| | 03:58 | Now let me stop here for a moment and
describe what these buttons are going to be doing.
| | 04:02 | At this point we've turned our
form into a data entry point.
| | 04:05 | Values that we type into the text boxes
are going to populate a new record into
| | 04:09 | our Direct Customers table, but because
Data Entry in Access gets saved on the
| | 04:14 | fly, we need to be a little bit
cautious in how we're handling this form, since
| | 04:18 | there's no Save Changes
when it comes to data entry.
| | 04:21 | Simply closing the form commits any
changes to the underlying data table, so
| | 04:26 | when we close this form what we're
really doing is saving changes to the data
| | 04:29 | table and closing the form.
| | 04:31 | If we simply want to back out of any
data entry procedure, we first need to make
| | 04:35 | sure these fields are clear of the values that
might have been typed in before we closed the form.
| | 04:39 | Let's create a couple of buttons
that'll accomplish these tasks so that our
| | 04:43 | end-users won't really have to worry about it.
| | 04:45 | So we're going to add this first
button. This first button is going to say
| | 04:48 | Save Customer. We're going to go to
choose the forms section, and we're going
| | 04:53 | to choose Close Form.
| | 04:55 | Closing the form is going to commit
any changes that we have typed in here to
| | 04:59 | the database while it closes.
| | 05:01 | So simply closing the form will
also accomplish the save task.
| | 05:05 | So Close Form, say Next. I'm going to
choose have my buttons display some text,
| | 05:11 | but instead of closing form I want to
be clear to my end-users what's about to
| | 05:14 | happen, so I'm going to say Save
Customer. That way it's super clear that when
| | 05:19 | they press this button, they're
committing those changes to the table.
| | 05:21 | Let's go ahead and say Next, and I'll
set that default name and say Finish.
| | 05:27 | So there is my Save Customer button.
| | 05:28 | I am going to add one more button in
case they accidentally start typing things
| | 05:31 | in and then decide they don't
want to save that to the database.
| | 05:35 | We'll go back up to the Button
control and we'll add one more down below.
| | 05:39 | This one is going to be a little bit
more complicated in that we need the
| | 05:42 | button to do two steps.
| | 05:44 | First we want it to clear the records
that we might have typed in, and then we
| | 05:47 | want to close the form.
| | 05:48 | So we're going to choose from these
categories here. We're going to choose
| | 05:52 | Record Operations and then
Delete Record. Go ahead and say Next.
| | 05:57 | The text on the button, I don't want it
to say Delete Record, because that's a
| | 06:00 | little bit confusing to our end-users.
So what I want this to say is Cancel
| | 06:04 | Entry. We'll go ahead and
say Next and we'll say Finish.
| | 06:10 | I need to go back and edit the
macro this button is running;
| | 06:13 | I need to add that second step.
| | 06:15 | So while the button is still selected in
my Property Sheet, I'll switch the Event
| | 06:18 | tab and the On Click event has this
embedded macro that we just built with the
| | 06:23 | wizard. I'll click the Build button
here. Access puts all of his error control
| | 06:28 | stuff in here, but I just need to add one step.
| | 06:31 | At the very end of all of this, we're
going to also close the window, so at the
| | 06:35 | bottom here into the Action
column I'll type in Close.
| | 06:40 | The action argument is going to ask me
which Object Type and Name. Again, with
| | 06:44 | the hint down here, I can leave this
blank, and it'll close the active document
| | 06:47 | or the form that it's run on.
| | 06:49 | So I am just going to type in Close for the Action
and then close the macro. It's going to
| | 06:53 | ask me to save those changes and I'll save it.
| | 06:56 | And now this button is running two steps.
| | 06:58 | The first thing it's doing is clearing out
anything that might have been typed in
| | 07:01 | here, and then it's going to close the form.
| | 07:02 | The Save Customer is just going to
simply close the form and if we have data put
| | 07:06 | in here, it is going to
save that to the data table.
| | 07:09 | Okay. Let's go ahead and test this out.
| | 07:10 | I'll switch into form view. My form is
here. I can close it. it's going to ask
| | 07:15 | if I want to save changes, and I'll say Yes.
| | 07:17 | Go ahead and save this as Add a new
customer. That's a good name; I'll just take that.
| | 07:22 | Now, let's go ahead and open it up again.
| | 07:24 | I'll double-click on it, go ahead and
type in a new customer ID. I'm going to
| | 07:28 | type in 10000. We'll type a First Name of
Adam and Last Name of Wilbert. Let's go
| | 07:33 | ahead and save this customer and then go into
my Direct Customers table to find that name.
| | 07:38 | We'll scroll to the bottom of the list, or
actually, we could just jump to the last
| | 07:45 | record; it's probably easier than
scrolling. And we'll see that new record has
| | 07:48 | been added to the bottom here.
| | 07:50 | Now in order to complete the circle, we
could go back into the Main Menu and add
| | 07:53 | a button here that jumps to the Add
Customer Form. Let's go ahead and do that.
| | 07:58 | I'll double-click to open up the Main
Menu, and I'll switch into Design View.
| | 08:02 | And that's going to actually switch
into Design View of the table that I had
| | 08:05 | opened in background.
| | 08:06 | The Main Menu, as you remember, since it's
afloating window, we actually have
| | 08:09 | to right click on it here, and say Design View.
| | 08:12 | So go into Design View, we'll add a
New button, we'll add it to the bottom of
| | 08:18 | the list here. This one is going to go
to Form Operations, Open a Form, Next.
| | 08:24 | It's going to open up the Add New Customer
Form, Next, it'll show all the records, Next.
| | 08:31 | The text is going to be Add a new
customer. Go ahead and say Next and Finish.
| | 08:39 | Last thing we need to do, you might
remember we were going to edit this macro to
| | 08:42 | also close the Main Menu, but since
both the Main Menu and the Add Customer are
| | 08:46 | both pop-up windows, we actually
don't need to do that this time.
| | 08:50 | So switch into Form View, we will test
out our Add a new customer button, it
| | 08:54 | pops up, allows us to enter any values,
we can either Save those Changes or
| | 08:58 | Cancel Entry. I'll click Cancel,
and it takes us back to the Main Menu.
| | 09:03 | Data Entry tasks don't have to be a chore.
With a little thought into the layout
| | 09:07 | of data entry forms, your end-users
will be able to quickly and accurately
| | 09:10 | capture the information that you need.
| | 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 | roles, and bundles all of it together
to documents that are ready to be sent to
| | 00:13 | a printer or a PDF file.
| | 00:15 | In this movie, we're going to take a
look at how Access organizes data into
| | 00:19 | grouping and sorting levels.
| | 00:20 | Now, if you've ever used the Totals
Row within a query, this is going to seem
| | 00:24 | somewhat familiar as the
concepts are really similar.
| | 00:27 | Let's get away from the Olive Oil
Company for just a moment, and open up a
| | 00:30 | sample database that's all about playing cards.
| | 00:32 | Now I've got a table here called Draw.
| | 00:34 | I'm going to double-click on that and
open it, and we can see that this table
| | 00:36 | has 5000 records in it.
| | 00:39 | This table represents 5000 random
card pulls from a deck of playing cards.
| | 00:43 | The first time I randomly drew a card,
I had the 6 of hearts, the second time,
| | 00:47 | the 4 of clubs, and I did this for 5000 times.
| | 00:50 | Let's go ahead and close this
table and we'll open up this query
| | 00:54 | called DrawResults.
| | 00:56 | The DrawResults query summarizes the Draw Table.
| | 00:59 | It counts up the number of times each
card appeared in my 5000 random pulls.
| | 01:04 | The Queen of Diamonds, for example,
showed up 121 times out of 5000. the 4 of
| | 01:09 | clubs showed up 118 times out of 5000.
| | 01:12 | This is the data that we're going
to use to build a simple report.
| | 01:15 | Let's go ahead and close this query.
| | 01:16 | Now, we can create a simple report
by going up to the Create button,
| | 01:21 | making sure that we have something
selected in the Navigation Pane, so I'm going
| | 01:24 | to select this DrawResults, and
then clicking on the Report button.
| | 01:30 | Access will generate a basic report
based off of the data of whatever was
| | 01:33 | selected in the Navigation Pane.
| | 01:34 | Now the default layout with all the
records alphabetized doesn't really allow
| | 01:38 | for a detailed understanding of our data.
| | 01:40 | For instance, looking over this report,
can you easily tell which card appeared
| | 01:45 | most often, or how did the king of
clubs rank out of all of the club cards?
| | 01:50 | The report is not organized very well
at the moment to help us answer these
| | 01:53 | very basic questions.
| | 01:54 | We can fix that using the grouping and
sorting options to better organize this layout.
| | 01:58 | You can find Grouping and Sorting
underneath the Format Tab in the Report Layout Tools.
| | 02:03 | I'll turn on Group & Sort and the
panel will open up here at the bottom.
| | 02:07 | Now, let's first add a sort.
| | 02:09 | We're going to organize our data
based off of the count. We'll add a sort.
| | 02:15 | We'll choose CountOfID, and right
now, it's set to smallest to largest.
| | 02:19 | I'll change that by clicking the down arrow
here to reverse it from largest to smallest.
| | 02:24 | So now, I've got a simple sorting here,
and I can tell the queen of diamonds
| | 02:28 | with 121 pulls, was the most oftenly seen card.
| | 02:31 | Let's go ahead and get rid of this sort, and
we'll take a look at some grouping options.
| | 02:36 | We can delete this sort by clicking on
the X in the far-right side of this bar.
| | 02:42 | Now, let's add a group.
| | 02:43 | I'll click the Add a group button
and this time we want to group all the
| | 02:46 | like values together.
| | 02:48 | In this case, let's group the
cards together by their color.
| | 02:50 | I'm going to click on SuitColor.
| | 02:53 | Now, my records are organized into two groups.
| | 02:55 | First, I have the black cards, the top,
and if I scroll down through my report,
| | 03:00 | I'll find the red cards.
| | 03:02 | Let's add another grouping
level to group by the suit.
| | 03:04 | We'll add group, and this
time we will check SuitName.
| | 03:10 | Now, we've got our cards organized
first by color, and then by suit.
| | 03:14 | The order here is randomized still.
| | 03:15 | So let's go ahead and add a sorting level
to sort by the CountOfID now. Add a sort.
| | 03:22 | Now we'll sort by CountOfID.
| | 03:24 | We'll change it from smallest to
largest to largest to smallest.
| | 03:29 | Now our report has a much more useful
layout that allows us to easily answer
| | 03:32 | the questions about our data.
| | Collapse this transcript |
| Understanding report structure| 00:00 | The structure that Access uses to
assemble the reports on the page is kind of
| | 00:04 | like building a layer cake or maybe
even designing blueprints for a skyscraper.
| | 00:08 | There are multiple sections that get
designed one time each and then Access uses
| | 00:12 | that blueprint over and over again as
it needs each piece for the reports.
| | 00:16 | Let's take a look at how this works.
| | 00:18 | We're back in the Two Trees
database and I've got a query here
| | 00:21 | called OrdersByRegion.
| | 00:22 | We'll go ahead and run that query.
| | 00:25 | The OrdersByRegion query is
summarizing and giving us an account of how many
| | 00:29 | orders have been placed within each
region and division of the company.
| | 00:32 | Let's go ahead and close this query and we'll
use that data table as a basis for our report.
| | 00:39 | With the query still selected in the
Navigation Pane, I'm going to go up to the Create
| | 00:42 | Tab and click on this Report
button in the Report section.
| | 00:45 | That will create a basic
report based off of that data.
| | 00:49 | Let's take a look at this in
Print Preview Mode, and dissect the
| | 00:52 | different elements.
| | 00:53 | We'll switch it here to View > Print Preview.
| | 00:56 | Now, the report is made up of a
bunch of different sections.
| | 01:01 | At the very top of the first page, we
have what's called the Report Header.
| | 01:05 | The Report Header here includes the icon,
the title, and today's date and time.
| | 01:09 | The Report Header appears at the very
top of the very first page of your report.
| | 01:14 | The next element we have is
what's called the Page Header.
| | 01:16 | The Page Header appears
at the top of every page.
| | 01:19 | The Page Header for this report
includes the Year, Region Name, Division Name,
| | 01:23 | and CountOfOrderID fields.
| | 01:25 | These are the headers for columns of data.
| | 01:28 | Below that, I have the Detail section.
| | 01:30 | The Detail section is each record of the report.
| | 01:32 | So each line of this is one instance of
a Detail section, and it's repeating for
| | 01:37 | as many records as I have,
all the way down to the page.
| | 01:39 | When I get to the bottom of the
page, I have the Page Footer section.
| | 01:44 | The Page Footer section for this
report is calculating and showing me what
| | 01:47 | page number I'm on.
| | 01:48 | Let's go ahead and change to page 2 using
this Page Selector down here at the bottom.
| | 01:52 | I'll click Next Page,
and scroll back to the top.
| | 01:55 | Now I'm at the top of the next page,
I've got another instance of the page
| | 02:00 | header again, followed by More Detail sections.
| | 02:03 | And as I scroll down, I get
to the end of the details.
| | 02:06 | When I run out of things to report on,
I've got what's called the Report Footer.
| | 02:10 | The Report Footer for this report is
calculating up the number of rows that I
| | 02:13 | have, and if I scroll at the very
bottom of the page, I've got another
| | 02:16 | instance of the Page Footer.
| | 02:18 | This is showing me I'm on page 2.
| | 02:19 | Let's go ahead and take a look at
what this looks like in Design View.
| | 02:22 | I'll close the Print Preview window.
| | 02:25 | It takes me back into Layout View.
| | 02:27 | I'll go to the View Menu,
and switch to Design View.
| | 02:32 | This is the Design View of
how that report was constructed.
| | 02:35 | It's got sections for the Report Header,
the Page Header, the Detail section,
| | 02:40 | the Page Footer, and the Report Footer.
| | 02:43 | Each element in the design of report
should be as minimized as possible.
| | 02:47 | For instance, if I take this Detail
section and I make it a little bit taller by
| | 02:50 | dragging down here, every time I have a
row in my report, Access is also going
| | 02:55 | to include all this white space.
| | 02:56 | So let's take a look at what this report
looks like now with that small change made.
| | 02:59 | I'll go to the View Menu
and click on Print Preview.
| | 03:03 | I'll scroll back up to the top,
and now my report is a lot different.
| | 03:08 | It's got all this white
space between each record.
| | 03:10 | So a pretty small change
repeated over and over again, has a big
| | 03:13 | effect on our report.
| | 03:14 | Let's close the Print Preview button,
and that'll take us back to Design View. ARibbonnd
| | 03:18 | I'll make this smaller again by clicking
above the Page Footer and dragging back up.
| | 03:23 | Now, in the last movie, we
talked about grouping levels.
| | 03:26 | When it comes to headers and footer
sections, each grouping level can have its
| | 03:29 | own header and footer.
| | 03:30 | We can change the grouping in the
Layout view like we did in the last movie or
| | 03:35 | in Design View with the Group & Sort pane.
| | 03:37 | My Group & Sort pane is already opened.
| | 03:39 | If yours isn't, you can turn it on
using the Group & Sort button here on the
| | 03:42 | Grouping & Totals section of the Ribbon.
| | 03:43 | I'll turn on there.
| | 03:45 | Let's add a group on region name, and
I'll switch into Layout View to take
| | 03:49 | advantage of the fact that the Layout
View will move my design around a little
| | 03:52 | bit as I add a different group.
| | 03:54 | I'll switch my View here to Layout
View and add a group by region name.
| | 04:02 | Access added a grouping name on region,
and it moved the region name into the
| | 04:06 | header for that section.
| | 04:07 | Let's switch back into Design View to
see what that change looked like there.
| | 04:10 | The View dropdown menu, Design view.
| | 04:13 | You can see I have a new RegionName
Header here, and the RegionName moved up
| | 04:17 | from the Detail section
to the RegionName Header.
| | 04:20 | Now, let's see this in Print Preview mode.
| | 04:21 | Go to View > Print Preview and I see that
the Region Name appears at the top of my data.
| | 04:28 | Now as I scroll through here,
| | 04:30 | when I run out of data for the
Midwest, I start the Northeast region.
| | 04:33 | And I have data for that. When I run out
again, I get the South region, and so on.
| | 04:38 | Now, as I scroll through my report, I
lose sight of what the headers mean, so
| | 04:41 | I'm not sure exactly what these columns mean.
| | 04:43 | It would be nice, if I scroll back over
the top here, to move Year, Division Name,
| | 04:48 | and CountOfOrderID down from the Page
Header Section into the header section for
| | 04:53 | the region, so that every time I have a
new region, I'd have another instance of
| | 04:56 | those column headers.
| | 04:57 | Let's go back into Design
View and we'll make that change.
| | 05:01 | Close Print Preview.
| | 05:03 | Now it'll take us back to Design View.
| | 05:04 | Now, when you create a report with
Access's automated tools, it automatically
| | 05:08 | groups all the elements together.
| | 05:10 | Sometimes that makes it a little more
difficult to manually move pieces around.
| | 05:13 | So what I'm going to do is remove that
grouping level, so that I can move pieces
| | 05:17 | around to exactly where I want them.
| | 05:18 | I am going to press Ctrl+A to
select everything on my report.
| | 05:22 | I can also use the Select All button
which is right here in the Control section.
| | 05:26 | I'll go to the Arrange Tab, and in the Control
Layout section, I'll click on the Remove button.
| | 05:30 | That will remove the
grouping for each of these objects.
| | 05:34 | Now, I'll just select any
empty area to deselect everything.
| | 05:37 | Now, I can move Year, Division Name,
and CountOfOrderID--these are the column
| | 05:41 | headers--down a level so they
fall under RegionName Header.
| | 05:45 | So every time I have a new
region, I'll also get those labels.
| | 05:48 | Click Year, and I'll Shift+Click
Division Name, and CountOfOrder ID.
| | 05:54 | Now I'll just move my cursor to the
edge of the box, and I'll drag these down
| | 05:56 | into the section below.
| | 05:57 | Let's go ahead and take a look at
our report now, see how that changed.
| | 06:01 | We'll go to the Design View tab, where I
can find my View Menu and Print Preview.
| | 06:07 | Now I will scroll back up to the top.
| | 06:09 | For every region, I've got a section
with the column headers, and if I scroll
| | 06:13 | down to the Northeast section,
I've got the column headers again.
| | 06:15 | Now, just like we have Report and Page
Footers, we can also add a Footer section
| | 06:20 | that will appear below the Region section.
| | 06:22 | So after I run out of records for
each region, we'll have a footer section
| | 06:26 | before starting the next region.
| | 06:27 | Let's do that in Design View.
| | 06:28 | We'll close the Print Preview, and in
Design View, in my Group & Sort section,
| | 06:34 | where it has Group on RegionName, I'm
going to click the More button here.
| | 06:37 | Over here on the right side, it says,
Without a Footer section and I'm going to
| | 06:41 | change that to With a Footer section.
| | 06:43 | While I am here, I can also change the
Do not group together on one page, and
| | 06:47 | I'll change this to Keep
whole group together on one page.
| | 06:51 | That way, as I get to the end of
the page of my report, I won't have an
| | 06:54 | abandoned header name with the
details on the following page.
| | 06:57 | So now I've got a RegionName Footer
here that will fall at the end of the
| | 07:02 | details for each region before starting the
next region. Let's fill this with some content.
| | 07:07 | Over here where it says with Year
Totaled, I am going to click this downward
| | 07:10 | arrow and I'm going to
uncheck the Show Grand Total button.
| | 07:13 | That will say with no totals now.
| | 07:15 | I'll click off of the bar to
finalize that, and now I can change it.
| | 07:19 | Where it says no totals, I'll click
the downward arrow, we'll Total On:
| | 07:24 | CountOfOrderID, the Type, we will leave
at Sum, so it will count up the number
| | 07:28 | of orders within its region, and
I'll show that in the Group Footer.
| | 07:32 | We'll click on the checkbox there.
| | 07:33 | You notice when I do that, it adds
a calculated field here that says
| | 07:37 | =Sum(CountOfOrderID).
| | 07:39 | I'll click off the box here and
one more time we'll look at this in
| | 07:43 | Print Preview Mode.
| | 07:44 | I'll change my View to Print
Preview, scroll back up at the top.
| | 07:49 | So I've got my header for the
report, I've got the region.
| | 07:53 | Within each region, I have these
details for the years that were being
| | 07:56 | reported on, and I have got a count
of how many orders appeared in any
| | 07:59 | region for the entire reporting
period. And then I also have a count for
| | 08:03 | each region for each year.
| | 08:05 | You'll see the same header and footer
options available to you when you're
| | 08:08 | designing forms as well.
| | 08:10 | But, they're really only applicable to
forms set up with the continuous View
| | 08:13 | property turned on like the
employee directory that we created.
| | 08:16 | So headers and footers are a little more
limited value in forms, but they work the same way.
| | 08:21 | In Reports, however, headers and footers
coupled with grouping and sorting levels
| | 08:25 | 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, all a report knows is formatting.
| | 00:04 | This font, this color, in
this position, and that's it.
| | 00:07 | For all of their data needs,
reports rely on a data source.
| | 00:11 | Data can come either directly from a table
or a little more roundabout from a query.
| | 00:16 | The nice thing about using a query as
a Data Source is that you can bring in
| | 00:19 | fields from multiple related tables,
calculated fields, and expressions and give
| | 00:24 | it all to the report in
one nice and tidy package.
| | 00:26 | When using a query, we have the option
of using a pre-built query that you would
| | 00:31 | find over here the Navigation Pane or
by creating what's called an embedded
| | 00:34 | query, directly inside of the report.
| | 00:37 | Let's take a look at how our reports
get data by having the Wizard walk us
| | 00:40 | through the first few steps.
| | 00:41 | I am going to go up to the Create tab here,
and in the Report section click on Report Wizard.
| | 00:47 | The Report Wizard starts and it looks a lot
like the Query Wizard if you ever used that.
| | 00:52 | And that's because essentially
that's all we were doing here
| | 00:53 | is creating a query to
give the data to the report.
| | 00:56 | Now we are going to work towards
creating a report that takes a look at
| | 00:58 | our customer's orders.
| | 00:59 | I am going to choose from my
drop-down menu, the Orders table.
| | 01:04 | And from the orders table, I am going
to use the OrderID, the OrderDate, the
| | 01:09 | Customer ID and the ProductID.
| | 01:12 | I want to find out some more
information about my customers, so I will go back
| | 01:15 | up here, scroll to the top to find
my Direct Customers table and I will add
| | 01:20 | FirstName, LastName, and State.
| | 01:26 | Finally, I want to find out
how much each customer has spent.
| | 01:28 | And in order to get the price, I have
to get that from the Products table.
| | 01:32 | I will choose the Product's table,
| | 01:34 | Price, and I will move that over as well.
| | 01:37 | Once I have all of the fields
selected, I will go ahead and press Next.
| | 01:40 | The next screen is to ask, How
do we want to view our data?
| | 01:44 | This is similar to the form visit that
we saw earlier, in that it will group by
| | 01:47 | Products or Customers.
| | 01:49 | I am going to choose by Orders and
next, and now it asks if I want to add
| | 01:54 | any grouping levels.
| | 01:55 | I want to group my
customers based off of the state.
| | 01:58 | So I'll add that over there.
| | 01:59 | Go ahead and say Next.
| | 02:01 | We can sort our fields if we would like,
I am just going to go ahead and accept
| | 02:05 | the default and say Next.
| | 02:07 | I will accept the default layout of
Stepped and Portrait, Next, and I will choose
| | 02:12 | the Access 2007 color
scheme. Go ahead and say Next.
| | 02:16 | What title would you like for your report?
| | 02:18 | I want to go ahead and
call this Customers by State.
| | 02:21 | You can go ahead and press
Finish to preview the report.
| | 02:25 | This will open up the report in Print
Preview mode and we can see that we have
| | 02:31 | our Alaska customers first, and if the
report was laid out okay, we'd find some
| | 02:35 | data over here on the side.
| | 02:36 | But unfortunately it's off the edge of
the page. That's something we'll have to fix.
| | 02:40 | Go ahead and close the Print Preview
and that will take us back to Design
| | 02:43 | View for this report.
| | 02:46 | Now first I am going to fix that width.
| | 02:48 | So I am going to go ahead and close my
Property Sheet and I am going to move
| | 02:50 | these fields around a little bit here.
I have got this field here that's
| | 02:53 | super narrow. I believe that's where
my price is showing up. Yep, right there.
| | 02:56 | So I am going to expand Price so I can see it.
| | 03:00 | Now let's take a look at the Print
Preview, changing my view to Print Preview.
| | 03:04 | Now you can see the Price that the
customers spent with the company.
| | 03:07 | Let's go ahead and Close the Print
Preview, and take us back into Report View.
| | 03:12 | Now let's take a look at where the
data is coming from, from this report.
| | 03:15 | And we'll see what the Wizard created for us.
| | 03:17 | I am going to open up the Property
Sheet again, and once again my keyboard
| | 03:20 | shortcut is F4, or Alt+Enter or you can press the
Property Sheet button up here in the Ribbon.
| | 03:25 | We'll switch to the Properties for the report.
| | 03:27 | I can use the drop down menu here and
choose Report or just like in forms, I can
| | 03:32 | click on the black button
between the rulers here.
| | 03:34 | We are going to take a look at the
Data tab for our report, and the Record
| | 03:38 | Source. And we'll see that we have this
Select statement here and this is a query
| | 03:42 | that was built for us.
| | 03:42 | We can click the Build button here on the
right to take a look at the results of that query.
| | 03:47 | I'll click on the b=Build button and we
will see that this is the query that the
| | 03:51 | Report Wizard was making for us.
| | 03:53 | Now if you are familiar with queries in
Access, this will look really familiar to you.
| | 03:57 | If you haven't, I highly recommend the
Access Queries in Depth course here at lynda.com.
| | 04:01 | Queries and Reports really do go
hand-in-hand, so knowing Queries will
| | 04:05 | definitely help you out building your reports.
| | 04:07 | Now one of the major downsides of the
Report Wizard is it doesn't really allow
| | 04:10 | for aggregation or summary data.
| | 04:12 | You don't have that option to add up a
customer's orders, for instance, to report
| | 04:16 | on a Lifetime Spend amount.
| | 04:18 | The Wizard simply returns straight
records, and you can run this embedded query
| | 04:22 | here by clicking the Run button, just
like any other query, and it will show me
| | 04:26 | the data table behind the query
that that report is being built off of.
| | 04:29 | Let's go back into Design View for the query.
| | 04:32 | Now I can start editing this query if I'd like.
| | 04:34 | But I have to warn you here, some of
the fields within the report have already
| | 04:38 | been linked to the fields here in the query.
| | 04:39 | For instance, if I scroll over to the
right here, I have got this field called Price.
| | 04:44 | And if I now decided that I wanted to
summarize based off of Price, and knowing a
| | 04:48 | little bit about queries, I might go up
here to the Totals row and turn that on.
| | 04:52 | And then change Group By to
Sum, and everything looks okay.
| | 04:57 | If I press Run, I now get a
summarized total for each customer.
| | 05:00 | But the problem is that it's
changed the header here to SumOfPrice.
| | 05:04 | There is definitely work arounds, so you
can work around this and fix this up to
| | 05:08 | make sure there's no broken links
between the query and the report, but it's
| | 05:12 | going to take a lot of clicking.
| | 05:13 | I am going to go ahead and back out of
this query without saving these changes
| | 05:18 | and I will say No here to
that not save the change.
| | 05:21 | And that will take me back to my report.
| | 05:22 | So now that we know what powers the
Record Source for our reports, we can
| | 05:26 | smooth out our workflow by first
creating a query that gathers exactly the
| | 05:30 | information that we want, including
aggregation and any calculations. Then build
| | 05:34 | the report on that.
| | 05:35 | This will be the topic of the next movie.
| | 05:38 | And one last thing. The Record Source
property in Reports functions exactly the
| | 05:42 | same way when bringing data into a form.
| | 05:44 | You can go back through some of the
forms that we created throughout this course
| | 05:48 | and explore the Record Source
property to find out if we were using a table
| | 05:51 | reference, a query reference, or an
embedded query to generate those records.
| | 05:55 | Of course with forms, you also have
the option of having an unbound form that
| | 06:00 | isn't connected to any data source,
such as our splash screen or the main
| | 06:04 | menu form that we made.
| | 06:05 | Whereas a report will be kind of
worthless if left unbound from a data source.
| | Collapse this transcript |
| Building reports from queries| 00:00 | Building a report off of 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:09 | By leveraging the power of queries and
feeding their results into the Reports
| | 00:13 | Record source, you'll create a good
foundation from which report design becomes
| | 00:17 | just a matter of dragging and dropping
fields into the appropriate locations.
| | 00:20 | I said this before about Access, but
sometimes all of the wizards and automated
| | 00:25 | object creation features of the program
can really get in the way and make things
| | 00:28 | more confusing than they really are.
| | 00:30 | Once you understand what's going on
behind the scenes, it's often the case that
| | 00:34 | hand-built, from the ground
up, really is the way to go.
| | 00:37 | For this exercise, I have gone
ahead and pre-built a query called
| | 00:41 | TotalSalesCustomers.
| | 00:42 | Let's go ahead and open it
up and take a look at it.
| | 00:45 | This query takes each customer and
finds their total lifetime spent with the
| | 00:49 | company. We also have a field here that
shows what state each customer lives in.
| | 00:54 | Switching to Design View, by going to
the View menu, Design View--and I'm going
| | 01:00 | to close the Property Sheet here--
you can see how this query was built.
| | 01:03 | I've got four different tables that are
all related to each other. I'm pulling in
| | 01:07 | the First and Last Name, the Lifetime
Sales is being calculated or being totaled
| | 01:12 | up, and I've got the StateName here.
| | 01:15 | So this is what we're going to
be using to build our report.
| | 01:18 | Go ahead and close this query.
| | 01:19 | I'm going to go to the Create menu
and in the Reports section, I'm going to
| | 01:24 | click on Report Design.
| | 01:27 | This will give me a
blank page and a fresh start.
| | 01:29 | Now just like when working with forms, we
want to connect this report to its data
| | 01:33 | source and we do that in
the Property Sheet here.
| | 01:37 | I'll open the Property Sheet, and on the
Data tab for my Report I'll choose my
| | 01:41 | Record Source using the drop-down menu
here and I'll choose that query that I
| | 01:45 | just made, qry_TotalSalesCustomers.
| | 01:49 | I'll go ahead and close the Property
Sheet now and I'll take a look at our
| | 01:52 | grouping and sorting options.
| | 01:53 | I'll turn on Group & Sort, and I
want to add a group to my report.
| | 01:57 | We're going to group all of our customers
based off of the state that they live in.
| | 02:01 | I'll add a group and since we've
linked this report to its data source, Access
| | 02:05 | presents me with a list of fields that
are available within that data source, so
| | 02:08 | these are all the fields inside of the query.
| | 02:10 | I am going to choose to Group on StateName.
| | 02:13 | It adds a StateName Header here and if
I scroll down, I'll see I have got a Page
| | 02:19 | Footer here at the bottom.
| | 02:20 | We will move this detail section up a
little bit so I can fit it all on one
| | 02:23 | screen. Maybe a little
more here.
| | 02:26 | Here we go. Okay. So that's the blank report.
| | 02:31 | I've got my Page Header, the
StateName Header that came from this grouping,
| | 02:35 | the Detail section where I put my fields, and
the Page Footer section down at the bottom.
| | 02:40 | Let's go ahead and close the Sort &
Group field. Close it there by clicking
| | 02:43 | on the button again.
| | 02:44 | Now I just need to drag and drop our
fields from the Add Existing Fields window,
| | 02:48 | and I will open that here, to
the appropriate location, Report.
| | 02:52 | I'm going to take the StateName field
and drag and drop it into the StateName
| | 02:57 | Header here. And then I'm going to take the other
three--FirstName, LastName, and Lifetime Sales--
| | 03:03 | and drop those into the Detail section.
| | 03:05 | I can Shift+Click to select the
whole range of them at once by clicking on
| | 03:09 | the first and Shift+Clicking on the
last, and I'll drag all three at once and
| | 03:13 | drop them down here.
| | 03:14 | Let's go ahead and arrange
these around a little bit.
| | 03:16 | The StateName, it's going to be obvious.
I don't need a label here to tell me
| | 03:20 | that this will be a state name, so I'm
going to delete that label and I'll move
| | 03:23 | the StateName over a
little bit and make it wider.
| | 03:28 | FirstName, LastName, and Lifetime Sales
are going to be three columns, so I need
| | 03:32 | to stack these next to each other.
| | 03:34 | These labels here, I'm going to turn
into field headers, and I'm going to put those
| | 03:38 | up in the StateName Header section.
| | 03:41 | I can do that, you would think, by taking
one of these handles here that moves it
| | 03:45 | independently of the object that it's
attached to and maybe just dragging it up.
| | 03:49 | But unfortunately when you get to the
top of the Detail section it just stops,
| | 03:52 | like it's hitting a brick wall.
| | 03:54 | So what I can do is select each one
of these together. I'm going to drag a box
| | 03:57 | to select all three and I'm going to press Ctrl+X
on my keyboard to cut them to the pasteboard.
| | 04:03 | Now I'll come up into the Site
Header section, I'll click in the StateName
| | 04:07 | Header, click there in the Detail
section, and press Ctrl+V to paste them in.
| | 04:12 | Now I just move them around.
| | 04:14 | So FirstName will go maybe here.
LastName, how about there. And Lifetime Sales,
| | 04:23 | we'll drag it out over to the right.
| | 04:27 | I'll move this bar up here and then now
I can align where my data will appear
| | 04:31 | right below the headers.
| | 04:33 | So the FirstName data will go below
the FirstName header, the LastName data
| | 04:39 | below LastName header, and
Lifetime Sales will go right about there.
| | 04:44 | Let's collapse my Detail
section and take a look at the report.
| | 04:48 | I'll go to the View menu and click
into Print Preview mode. So there we are.
| | 04:53 | I've got a basic report going, it's
got my state names listed, and then within
| | 04:58 | each state I've got the
customer and the total lifetime spend.
| | 05:03 | So this is a basic report, it's giving
me the functionality that I need. It doesn't
| | 05:06 | look very pretty though.
| | 05:07 | So let's go back, change a few of the
properties. We'll clean this up a little.
| | 05:10 | I'll close my Print Preview window
and I'll go up here to the Page Header
| | 05:15 | section and I'll add a title.
| | 05:17 | We will make this bigger.
| | 05:19 | Using the Label here I'll drag out a
box to add a title and I'm going to call
| | 05:24 | this Lifetime Customer Spend.
| | 05:30 | I'll change the size of the font here
from 11 to let's say 18 and I'll move my
| | 05:36 | StateName to a Font size of 14 and I
will make it Bold. Drag that up here.
| | 05:44 | I'll move my label here
in the title to the left.
| | 05:47 | Let's go ahead and turn on a
couple of features in my Detail section.
| | 05:50 | You click on the Details.
| | 05:53 | In my Property Sheet here I'll go to
the Format tab and I am going to change
| | 05:57 | the Alternate Black Color here from No
Color. I'll click the Build button and
| | 06:02 | I'm going to choose this Green here, Green 2,
so it will be the alternate color for that bar.
| | 06:07 | I'll also change the background
color of my Page Header here at the top.
| | 06:10 | I'll click in the Page Header section.
| | 06:12 | We'll change the background color from
white, I'll click the Build button again,
| | 06:17 | and I'll choose the darker green, Green 4.
| | 06:20 | I'll click off of that to commit it, and we've
made a couple of changes here to my report.
| | 06:25 | Let's go ahead and take a look
at those changes in Print Preview.
| | 06:28 | And my report is starting to
look a little bit better.
| | 06:30 | I've got state name here.
| | 06:31 | You can see these titles here.
| | 06:34 | I might want to change the sorting
order so that I'm sorting descending based off
| | 06:38 | of the person having the most value,
or the highest lifetime sales, so they
| | 06:42 | appear at the top. And I could to
do that in the Sort & Group section.
| | 06:45 | So one more time I'll close the Print
Preview, go back to Group & Sort, we're
| | 06:49 | going to add a sort now by clicking on Sort.
| | 06:53 | We want to sort on Lifetime Sales.
| | 06:55 | I am going to change from smallest to
largest to from largest to smallest.
| | 07:00 | We'll close Group & Sort.
| | 07:02 | I do want to make these a little bit
more bold, click on each three of those, and
| | 07:08 | say Bold and maybe Underline them
then separate them a little.
| | 07:11 | All right. Let's take a look.
| | 07:14 | Report's looking pretty good.
| | 07:15 | So with a little effort, we've already
got a pretty respectable looking report.
| | 07:19 | By starting from scratch we didn't have
to tweak any of the pre-made pieces that
| | 07:23 | the wizards tend to put in and we were
able to make it to our specifications
| | 07:27 | right from the start.
| | 07:28 | In the next movie, we're going to continue
building on this form by looking at the
| | 07:31 | conditional formatting
rules that we can establish.
| | Collapse this transcript |
| Formatting conditionally | 00:00 | When skimming a long report, it can be
pretty easy to skip right over some very
| | 00:04 | significant numbers.
| | 00:05 | By utilizing the Conditional Formatting
rules within your reports you can make
| | 00:09 | sure that the values that warrant
a second look get noticed.
| | 00:12 | We'll continue building on the
Lifetime Spend Report from the last session.
| | 00:15 | I am going to go ahead and open that here.
| | 00:17 | Now let's say I wanted to highlight
some values from my spending report.
| | 00:20 | I want to highlight all the
values that are below $20.
| | 00:23 | We can do that with conditional
formatting by changing our View to Design View
| | 00:29 | and selecting the Lifetime Sales field
here, and choosing Conditional Formatting
| | 00:32 | in the Font section of the
Design tab. I'll choose Conditional.
| | 00:37 | Now in Condition I can do Field Value Is,
instead of between, I'll change it to
| | 00:43 | less than, and I'll say 20.
| | 00:46 | If the Lifetime Sales is less than $20
then it'll format it by whatever values
| | 00:51 | I've set here. I'm going to
change this font to red and say OK.
| | 00:57 | Let's go ahead and check out this
change in Print Preview mode, and if I
| | 01:01 | scroll down my list I can see a few people
who have spent less than $20 with the company.
| | 01:06 | Now let's say I wanted highlight this
entire row, not just that number. I want
| | 01:10 | to highlight the name as well.
| | 01:11 | I can do that with the
Conditional Formatting as well.
| | 01:13 | We'll close the Print Preview and that
will take us back into Design view.
| | 01:17 | I'm going to select FirstName and
Shift+Click on this LastName so that I
| | 01:22 | can adjust the Conditional Formatting
for both at the same time. I'll press
| | 01:25 | the Conditional button.
| | 01:28 | This time I can't really use the Field
Value Is, because it's going to look
| | 01:31 | for the Field Value of say
the First Name or the Last Name.
| | 01:35 | So if I were to compare the FirstName
field to the value of 20, that's not
| | 01:38 | something that's going to really make sense.
| | 01:41 | What I need to do is change this to
Expression Is, and we could type in an
| | 01:45 | Expression that'll compare this
field here, Lifetime Sales, to 20.
| | 01:50 | So what I need to do is make in a field
reference, and to do that I can use the
| | 01:54 | square bracket and then type in
Lifetime Sales, closing square bracket.
| | 02:00 | Now I can compare this to 20. Is less than 20.
| | 02:04 | So if the Lifetime Sales is less than 20,
then the FirstName and LastName will
| | 02:07 | get formatted as well.
| | 02:09 | I'll change this to red and say OK.
| | 02:13 | Now let's take a look at this in Print Preview.
| | 02:17 | Now the entire row is highlighted red if
the value in Lifetime Sales is less than 20.
| | 02:21 | So using the Expression Builder and
a bit of logical problem solving, the
| | 02:25 | Conditional Formatting rules can
highlight just about any records that you'd
| | 02:28 | like to call special attention to.
| | Collapse this transcript |
| Calculating fields| 00:00 | The data for your reports comes from the
Record Source property, and it's linked
| | 00:04 | to either a table, an embedded query or
in the case of our LifetimeSpend report,
| | 00:10 | from a linked query.
| | 00:11 | We can actually calculate values
right here in our report as well using
| | 00:14 | Unbound text boxes.
| | 00:16 | So we can bring in data that
isn't in the Record Source.
| | 00:19 | I'm going to double-click and open up my
LifetimeSpend report that we've been working on.
| | 00:23 | So far we've got some layout in and
we've got some Conditional Formatting saved.
| | 00:27 | Let's go ahead and change into Design View.
| | 00:28 | What I would like to do now is add in
another level of information to our report by
| | 00:35 | summarizing data within each state.
| | 00:37 | What I'd like to do is get a total
count of how many customers we have in each
| | 00:41 | state and the sum total of all
of the sales within the state.
| | 00:45 | To keep things clean, we'll add a
State Footer and place all of our
| | 00:48 | calculated fields there.
| | 00:49 | We'll need to turn on our Group
& Sort panel to add the footer.
| | 00:52 | I'll go to my on Group StateName, the
More section, and I'll turn without a
| | 00:58 | footer to with a footer.
| | 01:01 | That will add a StateName
footer section here to the bottom.
| | 01:04 | So after I run out of details for
each state, we'll have a StateName Footer
| | 01:08 | before starting the next state.
| | 01:10 | Go ahead and close the Group & Sort
panel, and I'll add in a couple of text
| | 01:14 | boxes that we can
program with some calculations.
| | 01:16 | I'll click on the Text Box control up
here in the Ribbon and I'll add one down
| | 01:21 | into my StateName Footer.
| | 01:22 | I'll go ahead and click another
one and drop that down as well.
| | 01:26 | So I'll have two text boxes. They are
currently unbound so they're not connected
| | 01:29 | to any data source, but they live
now in my StateName Footer here.
| | 01:33 | Now we need to create the calculations
that will appear inside of these text boxes.
| | 01:38 | Let's go ahead and open up our Property Sheet.
| | 01:41 | And for this first text box here on the
left side we're going to go to the Data tab.
| | 01:45 | And in the Control Source section,
normally we might choose a field from this
| | 01:50 | drop-down menu, but I can actually
use the build button to build a control.
| | 01:54 | That's going to bring up the Expression
Builder that will help us format our expression.
| | 01:58 | We're going to choose to count up the
FirstName of our customers to get a total count
| | 02:02 | of how many customers are within each state.
| | 02:04 | We'll go ahead and use the Count
function and that we can find inside the
| | 02:07 | Functions folder, Built-in Functions.
| | 02:10 | If I scroll down the list here to SQL
Aggregate, there is the Count function.
| | 02:15 | I'll double-click on that
to add it to the Expression.
| | 02:17 | Now our Access wants to know,
okay what are we going to count up?
| | 02:20 | I'm going to click here in the middle
and I'll give it a field that it can count.
| | 02:24 | I'm going to go out to my Reports > All
Reports, LifetimeSpend Report, and I want
| | 02:31 | to count up the FirstName, I'll double-
click on that to add at the expression
| | 02:35 | up here on the top.
| | 02:35 | Go ahead and say OK and the Control
Source for my first calculated field is
| | 02:40 | populated here in the Property Sheet.
| | 02:42 | Let's go ahead and change the Text
Box, so we know what this is counting.
| | 02:47 | Change the Text here to
Total Number of Customers.
| | 02:49 | I'm going to move my site footer a
little bit bigger so I have room to work.
| | 02:55 | We can always collapse it back down
again once you've moved everything into
| | 02:57 | the correct position.
| | 02:58 | And I am going to move these fields
around using the handle to move them
| | 03:02 | independently, the dark brown gray
one at the top-left corner, and I'm going
| | 03:07 | to move this calculated field underneath the
label and I'll make it a little bit bigger.
| | 03:12 | Okay, so that's our first box.
| | 03:15 | Let's go ahead and calculate the second one.
| | 03:16 | I'll click that Text Box and in the
Control Source I'll press the build button again.
| | 03:23 | That again brings up the Expression Builder.
| | 03:25 | In this box we're going to take
the sum total of the LifetimeSpend.
| | 03:28 | Basically we want to find out the value
that each State has brought to the company.
| | 03:32 | We'll use this Sum function, which I
could find in Functions > Built-in Functions.
| | 03:37 | Scroll down to SQL Aggregate and Sum.
| | 03:40 | What are we going to add up? I'll
click in that box and then I'll choose the
| | 03:45 | LifetimeSpend field from our report.
| | 03:47 | We go into Reports > All Reports >
LifetimeSpend Report, and I'll choose the
| | 03:54 | Lifetime Sales field.
| | 03:57 | Go ahead and say okay, and that adds
the Expression to our Control Source here.
| | 04:01 | Let's go ahead and rearrange the label
again, so I'll drag this box over to here.
| | 04:07 | I'll make the label bigger and for the
label text, we're going to type in Total Sales.
| | 04:14 | Okay, let's clean these up. Let's move them.
| | 04:19 | I'm going to put Total Sales right
below the Lifetime Sales, and I'll put Total
| | 04:28 | Number of Customers below the customers here.
| | 04:35 | Next I want to change the color of
my site Footer to distinguish it from
| | 04:38 | the Detail section.
| | 04:39 | We'll go ahead and click in the site
Footer background here, under Format;
| | 04:44 | the Background Color is currently set to white.
| | 04:46 | I'll click the build button and I'm
going to choose a light green, this Green 2.
| | 04:51 | Okay, let's go ahead and
see how our report looks.
| | 04:56 | I'll click on the View menu, Print
Preview, and we'll scroll down until we
| | 05:00 | get to the end of Alabama, and we've
got these calculated fields here that are
| | 05:03 | adding up the total dollars and
counting up the total number of customers that
| | 05:06 | we have in each state
before starting the next state.
| | 05:09 | So calculating controls are pretty
straightforward, and the Expression
| | 05:13 | Builder can help you construct the
formula using any of the functions
| | 05:15 | available within Access.
| | Collapse this transcript |
| Adding the finishing touches| 00:00 | No report should be complete without
those finishing touches that make it
| | 00:03 | a polished document.
| | 00:04 | Things such as page numbers, date and
time stamps, and logos help present a
| | 00:08 | professional looking report.
| | 00:10 | In this movie we're going to finish up our
Lifetime Sales Report by adding such elements.
| | 00:14 | I'll double-click on the LifetimeSpend
report here, and we'll switch into Design View.
| | 00:19 | Now adding date and timestamps and page
numbers is nothing more than adding an
| | 00:26 | unbound text box with a calculated control
source, just like we did in the last movie.
| | 00:30 | We could add them manually and type in
the expressions if we knew the proper
| | 00:33 | syntax, but these finishing elements
are so common that Access actually has
| | 00:37 | buttons for these
specifically built right into the Ribbon.
| | 00:40 | For instance, I can click
this button here on the top
| | 00:42 | that says Title to add a title, and we've
already added our own title using a label.
| | 00:46 | I can click this button here that says
Number, and that will insert a page number.
| | 00:51 | And when I click on it, Access will
open up this Page Numbers dialog box.
| | 00:54 | We can format our pages;
| | 00:56 | so for instance, I can just say what
page we are on, or I can say what page we are
| | 00:59 | on out of how many total pages in the report.
| | 01:02 | Access will ask us where it wants to put it,
either in the Page Header or in the Page Footer.
| | 01:07 | I'll choose a Page Footer here.
| | 01:09 | And now we can choose where
we want to align this.
| | 01:10 | Do I want to align to the center of the
page, to the left, to the right, to the
| | 01:15 | inside or outside margin, if you double siding?
| | 01:17 | I am going to choose to the Right.
| | 01:20 | Go ahead and say OK, and Access adds
that calculated control down here in the
| | 01:24 | Page Footer section.
| | 01:25 | Let's go ahead and add a Date &
Timestamp. That's this third button here
| | 01:29 | on Controls Ribbon.
| | 01:30 | I'll click that once. Another dialog box opens.
| | 01:34 | We can choose to either Include a date
or if I uncheck it, to not include the
| | 01:38 | date, to Include the time, or if I
uncheck it, to not include the time.
| | 01:42 | Let's go ahead and put the date stamp on.
| | 01:45 | We have some formatting options for
how we want to format our date, and I am
| | 01:48 | going to choose this middle
one, and go ahead and say OK.
| | 01:52 | Access adds that into the Report Header. It
didn't actually give us a choice this time.
| | 01:56 | And I'm going to go ahead and close
the Property Sheet, so you can this date
| | 01:59 | over here on the right.
| | 02:00 | Okay, let's go ahead and see our report.
| | 02:02 | I'll change into Print
Preview mode. There we go.
| | 02:07 | Our date is here at the top, we've
got our title, and if we scroll to the
| | 02:11 | bottom, I've got our page numbers.
| | 02:13 | Let's add a logo to our report and
clean up a couple of things that I'm
| | 02:16 | noticing, for instance, these white text
boxes back here, because of the colored
| | 02:20 | background, it looks a little bit weird.
| | 02:22 | I also want to add some lines to help
clean up the report and separate the
| | 02:25 | fields from one another.
| | 02:26 | Go back into Design View
by closing Print Preview.
| | 02:32 | I'll add a logo to our report
by pressing the Logo button here.
| | 02:36 | I'll browse into my Exercise Folders
on the desktop. Exercise Folders, we're
| | 02:40 | in Chapter 5, and there's
this TwoTrees logo, I'll say, OK.
| | 02:44 | That's going to add it to the Report
Header section, and I can go ahead and move
| | 02:48 | our title up there as well.
| | 02:49 | Now I'll reduce this a little bit, and I'll
leave it as a green bar at the top of our report.
| | 02:58 | Now let's change these boxes here. They
currently have a white background, and
| | 03:01 | I want to change them so they're transparent.
| | 03:03 | I'll Shift+Click to select both of
them, and I'll go up to the Fill bucket,
| | 03:07 | right up here on the Ribbon, and
I'll change that to Transparent.
| | 03:11 | Finally, I want to add some dividing lines here.
| | 03:13 | I can use a Line object.
Here, I'll click the Line.
| | 03:16 | I'll draw a dividing line across the
bottom of my StateName Footer, and again when
| | 03:22 | the line is horizontal,
| | 03:23 | It's invisible, so it's there, you
just can't see it. And I'll let it go.
| | 03:28 | Now I can format my line, maybe
I'll pick a solid line, about 2 points.
| | 03:34 | I'll change its color here to this
dark green color and I'll move the State
| | 03:40 | Footer all the way up to that line.
| | 03:41 | Let's take a look at our report now with
those changes. Into Print Preview mode
| | 03:47 | to see our final report.
| | 03:48 | We've got a logo with a title.
| | 03:50 | This dark green bar across the top, we
have our state name, our fields here with
| | 03:56 | our calculations and our Conditional Formatting.
| | 03:59 | Then I finish with a dark green bar, and
then we start with the next state name.
| | 04:03 | We've got page numbers here at the bottom.
| | 04:05 | So now that the report is finished, we
can send it to the printer, save it as a
| | 04:09 | PDF, e-mail it out, or
simply save it in our database.
| | 04:13 | Every time we open it, it will fetch
the current snapshot of our business,
| | 04:16 | updating each customer's spend,
reevaluating the conditional formatting, and
| | 04:20 | give us the up-to-date information
in its presentation ready format.
| | Collapse this transcript |
| Populating pre-printed documents| 00:00 | We have been hearing about the so-called
paperless office for quite some time now.
| | 00:04 | But that promise is yet to become a
reality. There's just no getting away from
| | 00:07 | having to fill in paper documents sometimes.
| | 00:10 | Luckily, with a little planning and
help from Access, we can streamline some of
| | 00:14 | the process when it comes time to
filling in preprinted paper documents and
| | 00:18 | forms with information from our database.
| | 00:21 | For this exercise, we'll look at
populating information about our employees into
| | 00:24 | a preformatted government
employment form, such as this Form I-9.
| | 00:28 | The first thing that we need is an
image of our preprinted document.
| | 00:31 | You can get the image in a number of ways;
| | 00:33 | you can scan it in using a
scanner or export it from a PDF file.
| | 00:38 | However you do it, for the best printed
quality, you should capture image at 300
| | 00:42 | pixels per inch, and make sure
that you are not scaling it.
| | 00:45 | Next, we need to prep this image to overcome
what I consider to be bit of a quirk in Access.
| | 00:51 | In Access, you can't set page margins
to zero, so if we import an image to a
| | 00:56 | document that is exactly the same size
as our printer paper, Access will try and
| | 01:00 | wrap that image on to multiple pieces of paper.
| | 01:03 | To overcome this, we need to crop our
image down a quarter of an inch on all sides.
| | 01:08 | So our final image will be
8.0 inches by 10.5 inches.
| | 01:12 | At 300 ppi, this works out to
exactly 2400 pixels by 3150 pixels.
| | 01:20 | And then when we take that image and
place it back inside of Access, it can
| | 01:24 | accommodate the quarter-inch margin that
Access has, and everything lines up just fine.
| | 01:28 | Now if you need additional resources
for getting your images prepared, you can
| | 01:32 | check out a couple of courses at lynda.com.
| | 01:34 | For instance, Scanning Techniques
for Business and Home or Acrobat X tips
| | 01:38 | and Tricks & Tricks.
| | 01:39 | Now once we have our preprinted document
image, it's time to get started in Access.
| | 01:43 | So now that we are back in Access, we
can go up to the Create tab here and
| | 01:48 | start our I-9 Form.
| | 01:49 | I am going to go to the Report Design.
| | 01:53 | The first thing I want to do is
setup the background of the report.
| | 01:55 | I am going to get rid of the Page
Headers here by right clicking on the Detail
| | 01:59 | and saying Page Header/
Footer and turning that off.
| | 02:02 | That will get rid of those.
| | 02:03 | Then I want to double-check my Margins.
| | 02:05 | I am going to go up to Page Setup, into
the Margins section, and I just want to
| | 02:10 | double-check that everything is set to a
quarter-inch margin all the way around,
| | 02:13 | and it is, so that's good.
| | 02:15 | We'll return to the Design tab, and I
am going to open up the Property Sheet.
| | 02:20 | On the Property Sheet we'll go to the
Format tab and we'll change a few of these
| | 02:23 | properties, for instance, the Width.
| | 02:25 | I am going to go ahead
and change this to 8 inches.
| | 02:29 | The image in the background
can be set with a picture.
| | 02:32 | We'll click here where it says none,
and build, we'll browse to our Exercise
| | 02:36 | Files, which are on my Desktop > Exercise
Files > Chapter 5 folder, and it says i-9
| | 02:45 | Cropped. Go ahead and say OK and
that will add it into the background.
| | 02:51 | Now we just need to change this
Picture Size Mode from Clip to Zoom.
| | 02:55 | Then we'll set the Height of our report
and that's a property of the Detail section.
| | 03:00 | So click on the Report down here,
we'll set the height here to 10.5. Now
| | 03:08 | everything is set up on the form
properly and we can start adding our fields.
| | 03:11 | I'll go to my Add Existing Field
section and we'll browse into my Employees
| | 03:15 | table and we can add all of the fields
that we're going to be using on our form.
| | 03:20 | So I'll take FirstName--and I'll just
double-click on these as I go--LastName,
| | 03:25 | Address, City, State, and Zip.
| | 03:29 | We are also going to add MiddleName,
but we'll have to treat that a little bit
| | 03:32 | differently, because this particular
form is asking for the Middle Initial, not
| | 03:35 | the full middle name.
| | 03:36 | But I'll double-click on MiddleName to add it.
| | 03:39 | Okay, now that all the fields are in
our form, I am going to first delete the
| | 03:44 | labels here on the left side.
| | 03:45 | Since we are putting this on a preprinted
document, I don't need additional labels.
| | 03:49 | So I'll drag a box to select
all of them and press Delete.
| | 03:51 | Then, I am going to change the color
of these, so it's a little bit easier to
| | 03:55 | follow on the screen.
| | 03:56 | I am going to highlight all of these
boxes, and I am going to go over here and
| | 04:00 | I am going to change the Font Color to a
bright red and I am going to make them Bold.
| | 04:06 | Okay, now it's time to
start moving things around.
| | 04:08 | On my form, it goes Last Name, then First
Name, then Middle Initial. So I'll start
| | 04:12 | putting the fields in the right positions.
| | 04:14 | I'll move LastName to here,
FirstName will go next to it go over here and
| | 04:22 | MiddleName will move over here.
| | 04:26 | The address will go below all of that,
and below that, City, State, and Zip.
| | 04:32 | I can make a few of these wider, for
instance, I think I'll need my cities to be wider;
| | 04:45 | my Addresses definitely need to be wider.
| | 04:47 | I can adjust this LastName and the
FirstName boxes just to make sure they can
| | 04:52 | accommodate all the data.
| | 04:53 | Now let's go ahead and switch back to
the Property Sheet and we'll deal with
| | 04:55 | this MiddleName one.
| | 04:56 | We'll go to the Property Sheet,
and I'll click on MiddleName.
| | 05:00 | I'll switch to the All tab, so I can
see Name and Control Source together;
| | 05:04 | usually they are on two different tabs.
| | 05:05 | Now the problem is if I change the
Control Source for this particular field here
| | 05:10 | and make a reference to the MiddleName,
the object is also named MiddleName.
| | 05:14 | So Access will create a circular reference here.
| | 05:17 | What I need to do first is change the
name of this object and then I could
| | 05:20 | change its Control Source.
| | 05:22 | So instead of MiddleName,
I'll call this MiddleInitial.
| | 05:26 | And now I am free to change
the Control Source to a formula.
| | 05:29 | I am going to right-click here and say
Zoom, to bring up the Zoom box so we can
| | 05:33 | see what I am doing.
| | 05:34 | I'll change the Font too, to
make it a little bit bigger;
| | 05:36 | I'll say OK to here.
| | 05:37 | So the function that I am
going to use is called Left.
| | 05:42 | What Left does is it extracts
characters from the left side of a string.
| | 05:45 | So I am going to say equals left, and
then open parentheses.
| | 05:49 | I'll open a square bracket, and I'll close the
square bracket around MiddleName.
| | 05:55 | Now I need to tell the function
how many characters to extract.
| | 05:59 | I only want the first one, so
I'll type in a comma and a one.
| | 06:02 | I'll finish the function with a closing
parentheses and we'll say OK.
| | 06:05 | The last thing I want to do is make
sure these boxes are transparent in the
| | 06:09 | background and make sure they
don't have any lines around the edges.
| | 06:13 | So I'll highlight all of them, I'll
change my Line Type here to Transparent, and
| | 06:17 | I'll change my fill color
over here to Transparent as well.
| | 06:22 | Okay, let's go take a look at our form,
I'll switch my View here into Print
| | 06:25 | Preview and we'll see how the form is
laid out and that data is populating in
| | 06:30 | the correct position.
| | 06:31 | Now on this screen here, this form
doesn't look very good. It's hard to read, and
| | 06:34 | that's just a resolution
problem with the Print Preview View.
| | 06:37 | For instance, if I zoom in here to 200%,
you'll notice that the text becomes
| | 06:42 | a lot more legible.
| | 06:43 | Now we have two different
options here as far as printing.
| | 06:46 | We could just print this document as
is and include the form and the data and
| | 06:50 | print it all on a blank piece of paper.
| | 06:52 | The other option is to go back into
our Design View for the report and delete
| | 06:56 | the image from the background.
| | 06:57 | Then we'll just be printing the data on a page.
| | 07:00 | We'll need to load the printer with the
blank forms in order for it to print correctly.
| | 07:03 | So adding a preprinted document into
your Access database is an easy way to make
| | 07:08 | use of the data that you
already have and save you time.
| | Collapse this transcript |
|
|
6. The User ExperiencePrinting reports| 00:00 | We've seen the Print Preview screen
many times throughout this course, but we
| | 00:03 | have never really stopped to look at the Ribbon.
| | 00:05 | Most of the Print Preview buttons
would be pretty obvious in what they do.
| | 00:08 | Let's go ahead and open up our
LifetimeSpend report in Print Preview mode.
| | 00:11 | I can go directly there by right
clicking and saying Print Preview.
| | 00:15 | Now along the top of the Ribbon, we
have options to change the Size and change
| | 00:19 | from Portrait to Landscape.
| | 00:21 | Now these are decisions that might
be better off made in Design View. For
| | 00:25 | instance, if I change to landscape mode,
you'll see that my report actually doesn't
| | 00:28 | take any advantage of the
extra space on the side.
| | 00:30 | I'd have to go into Design View and
reconfigure my report to take advantage of the new page.
| | 00:35 | Let me go ahead and
change that back to Portrait.
| | 00:39 | Here, we have an option to
change to a multi-column report.
| | 00:42 | But again, this is something you would
have to make a design decision on first,
| | 00:45 | before you can properly take advantage of this.
| | 00:47 | Here I have got a copy of my LifetimeSpend
report that I have configured for columns.
| | 00:51 | Let me right click on
that and go to Design View.
| | 00:54 | You can see that I have set
the size to 4 inches wide.
| | 00:56 | Now let's switch into Print Preview
Mode and change it to a multi-column.
| | 00:59 | I will go to the View
menu and say Print Preview.
| | 01:03 | Now I can go to this Columns button
here and change it to 2 columns. I will
| | 01:08 | leave the Column Spacing at a quarter
inch, the Column Width is 4, and I am going
| | 01:12 | to leave the Down and then Across option.
| | 01:14 | Go ahead and say OK.
| | 01:16 | This converts it into a two-column report.
| | 01:18 | If I zoom out, you can see the full report here.
| | 01:20 | Now the two-column report starts at
the top on the left side, goes down to the
| | 01:24 | bottom and then starts again at the top
on the right side and goes to the bottom
| | 01:27 | again before going to the second page.
| | 01:30 | This might make your report a
little bit more confusing to read.
| | 01:32 | So you'd have to make a decision on
whether this is appropriate for your data or not.
| | 01:36 | Let's go ahead and close this one out.
| | 01:39 | Over on the right side, we have
some options to export our report.
| | 01:42 | We can export it to an Excel file or a
PDF file, a Word document, a Text file,
| | 01:47 | and if I say More, I can choose to
export it to other Database, a Snapshot file
| | 01:53 | or maybe even a Word document.
| | 01:55 | Now a lot of these export formats will remove
any of the formatting that you put on there.
| | 01:58 | So let's go ahead and say maybe Excel here.
| | 02:01 | And we can go through the
Wizard to export a file to Excel.
| | 02:04 | I will go ahead and accept the default
location here, but I will say Open the
| | 02:07 | destination file after the
export operation is complete.
| | 02:10 | Go ahead and say OK.
| | 02:13 | And Access exports that file to an
Excel file and opens it up in Excel.
| | 02:16 | When the report comes into Excel, I
have lost all my coloring and font choices
| | 02:21 | that I have made, but it
looks like a basic report here.
| | 02:23 | So I can work with the numbers in Excel.
| | 02:25 | And in Excel, it actually even
copies some of the formulas here.
| | 02:28 | So if I click on this box down here 26D,
you will notice that it also copies the
| | 02:32 | formula which is adding up that column of data.
| | 02:34 | Let's go ahead and then close
Excel here, return back to Access.
| | 02:39 | Technically, you can also print from a form.
| | 02:41 | You won't find Print Preview in the
Form menu for a form. For instance, if I
| | 02:45 | go to this EmployeeDirectory here and
right-click, I don't see Print Preview
| | 02:48 | here, but there is nothing actually
preventing you from printing it from the Office button.
| | 02:52 | Let's go ahead and open that form.
| | 02:54 | And if I go to the Office button, go to
Print, and then Print Preview, it opens
| | 03:00 | up in Print Preview just fine.
| | 03:01 | In fact, this particular form
would probably work out well printed.
| | 03:05 | But that shouldn't be the primary use for forms.
| | 03:07 | Reports are made to be printed and exported.
| | 03:10 | You can use them as a screen-only
presentation. That's really what forms are for.
| | 03:14 | If I go back to my LifetimeSpend report
here, and then close the Print Preview
| | 03:19 | window, and then double click on it to
open in Standard view, you notice that it
| | 03:24 | just scrolls forever. It scrolls down
and down and down, and that's not really a
| | 03:28 | good user interface.
| | 03:30 | The other thing that you can't do
with a report on the screen is filter it.
| | 03:33 | You will notice that the
filtering options are grayed out.
| | 03:35 | You will notice that if I close Print
Preview for the form and then click on
| | 03:38 | it to activate it, I will have some
standard sorting and filtering that I
| | 03:42 | can apply to my form.
| | 03:43 | So if you stick to the rule that forms
are for the screen and reports are for
| | 03:47 | paper, you will be a much
happier database designer.
| | Collapse this transcript |
| Tweaking the design| 00:00 | At the end of Chapter 3, we took a
look at connecting a user input form to a
| | 00:04 | query, which then passed data into a chart.
| | 00:07 | We can do the same thing with a report.
| | 00:09 | I've got a workflow setup here called
SalesByDivision and I'll double click on
| | 00:12 | the form here to open it up and you'll
notice that I have some selection boxes
| | 00:16 | that I can put input into.
| | 00:17 | I am going to go ahead
and choose a few of these.
| | 00:18 | I am going to choose New England for
the Year 2005 and I want to compare that
| | 00:23 | to values for the Mid-Atlantic
in the same Year, in 2005.
| | 00:27 | And if I say Preview Report, the
report opens up and shows me the values
| | 00:30 | that I've selected.
| | 00:31 | I'll zoom in here and say Mid-
Atlantic 2005 and there is those values, New
| | 00:35 | England 2005 and here's those values, and
at the very bottom of the report I have
| | 00:41 | some grand totaling going on.
| | 00:42 | Now, this form and this report have been
refined to the point where they are very attractive.
| | 00:47 | And I know that takes a lot of time
going back and forth between Design View and
| | 00:50 | Preview Mode to make sure that all of
the changes that you're making are in line
| | 00:54 | with the goals of the report.
| | 00:55 | So let's dissect both of these elements
to see how they were designed and some
| | 00:59 | of the decisions that were
made to get them to this point.
| | 01:01 | We'll start where the user starts with the
form, so I'll go ahead and close this report.
| | 01:04 | Now the form here, I am going to
switch into Design View by going to the View
| | 01:09 | menu and changing it to Design.
| | 01:10 | Now I want to take a look at some of
the elements that are here on this form.
| | 01:14 | It's actually a pretty simple form.
| | 01:16 | First at the top, I've got this
rectangle and I've colored it with the same
| | 01:20 | color as the logo image that I've
placed on top. The logo image is here.
| | 01:25 | The image has this little bowl here
that drops below this line of the rectangle
| | 01:29 | and I've blended that into the
background of the form by coloring the detail
| | 01:33 | section to match the same color here.
| | 01:35 | So when I view it in Form view,
this all looks like one complete piece.
| | 01:39 | In the bottom right corner,
I've got this image of the tree.
| | 01:42 | Again the background color of the tree is
the same as the Detail section of the form.
| | 01:46 | Another thing about this tree image,
if I go to the Arrange menu up here and
| | 01:50 | take a look at its Anchoring, you
notice that the Anchoring of that image is to
| | 01:54 | the Bottom Right of the form, so no
matter what size of screen you're viewing
| | 01:57 | this on, the tree will always
appear in the Bottom Right corner.
| | 01:59 | I have a label here that has the title
and that's simply just colored white with
| | 02:04 | a Font Size of 26, Bold and Centered.
| | 02:07 | Then I have these elements
here that the user interacts with.
| | 02:11 | Let's go ahead and open up the Property
Sheet, so we can take a look at those.
| | 02:15 | These first two boxes populate
values of our geographical division.
| | 02:19 | If I look at the Row Source for
those boxes, they are just standard combo
| | 02:22 | boxes? If a look at the Row Source
for these combo boxes I'll see that it's
| | 02:27 | just this lookup here. I can click on it once
and go to the builder to see the query behind it.
| | 02:33 | These combo boxes are built with a
query here that pulls from the States lookup
| | 02:37 | table and it's showing the division name.
| | 02:40 | I'm also using the ID number here to sort
but I am not showing it in the drop-down box.
| | 02:44 | The reason using I am using a
DivisionID here... let me run this query here so we
| | 02:47 | could see the results.
| | 02:48 | It's so that these divisions don't
show up in alphabetical order, but
| | 02:52 | in geographical order.
| | 02:54 | So they run from the East Coast to
the West Coast, which is a much better
| | 02:58 | ordering system than alphabetical for this list.
| | 03:00 | Let's go ahead and close the query.
| | 03:03 | So what happens with this data
once I pick it from the list?
| | 03:05 | If I go to the Other tab,
we'll find the Name field here.
| | 03:09 | The name of that box, this one
here on the top, is cbo_division.
| | 03:13 | I've also given a name to the
one below it, cbo_CompDivision.
| | 03:17 | I make use of these names in the query,
and we'll take a look at that in a moment.
| | 03:22 | I also have Year boxes here and if I
click on that, those are named as well, so
| | 03:26 | cbo_Year and the one below it is cbo_CompYear.
| | 03:29 | If I look at the data for those, again
the Row Source is built off of a query.
| | 03:34 | I can review that query by clicking on
the build button here and I can see that
| | 03:38 | this is simply taking the OrderDate from
the Order's table and it's applying the
| | 03:44 | Year function to extract just the Year.
| | 03:46 | So when I run it, I am just getting a
list of the years that are present in my
| | 03:50 | database. Go ahead and close that.
| | 03:52 | The other two elements on this form are two
command buttons, Preview Report and Run Query.
| | 03:59 | Now both of these have an event
associated with them, an On Click event, and if I
| | 04:04 | click on the Run Query button and take
a look at its event here by clicking on
| | 04:08 | the build button, we'll see that
it's just running an OpenQuery Action;
| | 04:12 | it's such a single step action.
| | 04:14 | The query that it's opening is this
SalesByDivision-complete, and it's opening
| | 04:18 | it in Datasheet View.
| | 04:19 | You can close that.
| | 04:22 | Let's take a look at what the
other button does, Preview Report.
| | 04:25 | Over here in the Property Sheet for
the On Click event, the Embedded Macro,
| | 04:29 | let's take a look at it by clicking
the build button and we can see that this
| | 04:33 | just runs one action as well, OpenReport.
| | 04:36 | The arguments are down here,
the report that it's opening is
| | 04:38 | SalesByDivision.complete and it's
opening it directly in the Print Preview mode.
| | 04:42 | We could change this here using by
using the drop-down menu to open it in a
| | 04:45 | different View mode, for instance
Design View or Report View or Layout View.
| | 04:50 | We can even send it directly to a
printer without any other interaction.
| | 04:53 | So if I left it on Print here, as soon as I
press that button, it's off to the printer.
| | 04:57 | I am going to change that back to Print
Preview and close the macro, and I am not
| | 05:03 | going to save any changes here.
| | 05:04 | Let's close the Property Sheet. So
those are all of the elements on this form.
| | 05:10 | Now they are aligned and they are kind
of organized a little bit, so that it
| | 05:13 | makes it clear for the end
user that works top to bottom.
| | 05:16 | But there is a really simple form;
| | 05:18 | there is really not that many elements on it.
| | 05:19 | Let's go ahead and close out the form
and I am not going to save any changes
| | 05:23 | that I might have made.
| | 05:24 | Now let's take a look at that query real quick.
| | 05:28 | I cover the query in Queries in Depth
here at lynda.com, but I want to go ahead
| | 05:32 | and run it just so we
can see what it looks like.
| | 05:34 | And I'll right click on it and
say go into Design View.
| | 05:38 | Now this particular query is pulling
information from four tables, but we can
| | 05:41 | see some criteria down here
underneath the Year and DivisionName. And let me
| | 05:45 | expand these boxes a little bit.
| | 05:46 | They are actually quite long, so I am
going to move it a little bit further here.
| | 05:52 | And we can see that the criteria
here is coming from the cbo_Year on the
| | 05:58 | SalesByDivision form that we were just
looking at. So this is one of those combo
| | 06:01 | boxes that was selection for the Year.
| | 06:04 | The criteria over here for DivisionName
is coming from those combo boxes about
| | 06:07 | the DivisionName, so it's pulling in
whatever the user selects on that form and
| | 06:11 | pasting it into the query.
| | 06:12 | Let's go ahead and close the
query and take a look at the report.
| | 06:16 | We'll go to the Report here;
| | 06:17 | I'll right click on it and go into Design View.
| | 06:22 | Now this report, again, fairly simple.
It's just organized in a nicely logical way.
| | 06:27 | We've got a Report Header, a Division
Header, a Year Header, a Detail section,
| | 06:32 | a DivisionName Footer, and if I scroll
down we've got a Page Footer and then a
| | 06:37 | Report Footer here.
| | 06:39 | The only coloring on this Report is here
in the background in the Report Header,
| | 06:43 | where I've got this light green
color assigned to the background.
| | 06:44 | I've got a couple of lines here on the
report to help break up the space and
| | 06:48 | they are a little bit hard to see
because they are right at the edges, but there
| | 06:51 | is a dark line here right below
Division Header Name (DivisionName Header). You kind
| | 06:54 | of see it if I click on it there.
| | 06:56 | I also have a small line right below
the word State here and Total Sales.
| | 07:01 | So it's a small black line that
you can kind of see right there.
| | 07:04 | And in fact if I make this section a
little bit bigger, if I drag it down, you
| | 07:07 | can see that line is right there.
| | 07:08 | So I am going to move that back up.
| | 07:11 | The other thing I want to point out
here is if I open up the Property Sheet,
| | 07:14 | we've got some calculated fields. For
instance I've got SumOfPrice here and
| | 07:18 | if I look at the data for that, I can see
that the Control Source is just SumOfPrice.
| | 07:23 | So it's feeding in the
SumOfPrice from the query.
| | 07:26 | But below that in the DivisionName
Footer, I've got some calculated fields that
| | 07:30 | are taking all of the sums that show up
in this Detail section. Remember this is
| | 07:33 | going to appear over and
over again for every record.
| | 07:35 | If I click on this box here, the
Division Sales, it's summing up or adding up
| | 07:41 | all of the boxes that appear in this column.
| | 07:43 | Let me scroll down to the bottom.
| | 07:44 | I've got another one down here in the
Report Footer and if I click on this one
| | 07:49 | here, you notice that the Control Source
of the calculation is exactly the same,
| | 07:54 | it says =Sum([SumOfPrice]).
| | 07:57 | The only difference between these two is that
they are in different locations in the report.
| | 08:00 | This one up here in the DivisionName Footer
is adding up all of the prices in the division.
| | 08:05 | The one down here in the report is adding
up all of the values in the entire report.
| | 08:10 | So the same calculation, but because
they're in different locations, they are
| | 08:13 | adding up different values.
| | 08:15 | So let me go ahead and close these out,
I'll run through the workflow once again
| | 08:17 | just so we can see it in action, I'll
close the Property Sheet, I'll close the
| | 08:21 | Report, I won't save any changes here.
| | 08:24 | We'll go back to the form and
we'll fill in some different values.
| | 08:28 | Let's say this time I am interested in
looking at the South Atlantic States in
| | 08:32 | 2005 and I want to compare those to
the South Atlantic States in 2006.
| | 08:35 | We'll preview the report and then I
get the report for those values. And let's
| | 08:41 | scroll up to the top here.
| | 08:43 | Now the only thing different between
this and some of the forms and reports that
| | 08:46 | we've created in this course is the
amount of time spent fine-tuning the
| | 08:49 | position of the elements, adjusting
colors and fonts, and I am going to admit it,
| | 08:52 | sometimes obsessing over every pixel.
| | 08:55 | Step one in creating your forms and
reports should always be to get the
| | 08:58 | functionality in place.
| | 09:00 | Even if the form is completely and
exceptionally ugly, once it functions
| | 09:03 | properly, all the rest are just moving
around pieces. And that part can take a
| | 09:07 | while as you change your View mode
back and forth between Design View, Layout
| | 09:10 | View, Form or Report View, sometimes
moving elements one step at a time.
| | 09:15 | It can be tedious sometimes, but
the end result will look great and
| | 09:18 | function great.
| | Collapse this transcript |
| Automating the workflow with macros| 00:00 | Before we wrap up this course, I
thought it would be helpful to outline a
| | 00:03 | couple of the Macros that you can build that
will help you connect your form to your reports.
| | 00:07 | As we've 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 of the available
options you need to dig into the designers.
| | 00:17 | The same applies to the Button Wizard.
| | 00:18 | I'm going to go ahead and open up our
MainMenu form that we created earlier, I
| | 00:21 | am going to right-click on
that and say Design View.
| | 00:24 | Now we've already created a couple of
buttons on this MainMenu that connected to
| | 00:28 | other forms, for instance, our View
Employee Directory form, our Product Review
| | 00:32 | form, or our new customer form.
| | 00:34 | Let's add a couple more buttons
that will connect it to other reports that
| | 00:37 | we've made and then we'll organize
this a little bit before we leave.
| | 00:39 | I'll go up to the Button control on
the Ribbon here and I'll click to add
| | 00:43 | one button to my form.
| | 00:45 | The Button Wizard starts and I'm
going to choose Report Operations > Mail
| | 00:49 | Report. Go ahead and say Next, I'm
going to choose the Lifetime Spend Report,
| | 00:54 | go ahead and say Next again.
| | 00:56 | Access asks if we want to display a
Text or a Picture. I'll put on the Text and
| | 01:01 | I'll leave it as Mail Report for now.
| | 01:03 | Go ahead and say Next and
accept the default name, Finish.
| | 01:07 | Access now created us a button called
Mail Report that will e-mail the Lifetime
| | 01:11 | Spend report, but I can get some
additional functionality from that Macro by
| | 01:14 | taking a look at its properties.
| | 01:16 | So I'll make sure that the button is
still selected, I'll go to my Property
| | 01:19 | Sheet, and if it is not on
you can toggle it on here.
| | 01:22 | On the Event tab the On Click has this
embedded macro that the Wizard created.
| | 01:27 | We can edit this Macro using
the build button on the far right.
| | 01:30 | I'll click on that once and that
will start the Macro editing session.
| | 01:33 | The Macro created us this one action
here, SendObject, and it attached this
| | 01:38 | LifetimeSpend Report to it.
| | 01:40 | But we have some additional
options down here below that the Wizard
| | 01:42 | didn't present to us.
| | 01:43 | For instance, I can specify what Output
Format, so I can specify here if I use
| | 01:48 | the drop-down menu that I want
to output this as a PDF file.
| | 01:50 | I can tell it who I want to send
it to, and I'll write test@test.com.
| | 01:55 | I can specify Carbon copy or
Blank carbon copy recipients;
| | 02:00 | I can type in a subject line:
| | 02:02 | Here is the report.
| | 02:05 | I can go down and add Message Text to the
body of the e-mail: The report is attached.
| | 02:12 | And finally at the bottom, I've got an
option that's called Edit Message and it's
| | 02:15 | set to Yes right now.
| | 02:16 | So when I click on that button what
Access will do, it will take that report,
| | 02:19 | this LifetimeSpend Report, it will
convert it to a PDF file, but will then open
| | 02:24 | up Outlook which happens to be the
default e-mail program on this computer.
| | 02:27 | It will create a new e-mail addressed to
test@test.com, it'll put in the Subject
| | 02:31 | line "Here is the report," it will put in
the Message Text, "The report is attached,"
| | 02:35 | and it will attach the PDF file to the e-mail.
| | 02:38 | It will the sit there and wait for
me to finally press the Send button.
| | 02:42 | If I change Edit Message here to No
and save this Macro, now if you click on
| | 02:49 | that button you'll never
actually even see the e-mail.
| | 02:51 | Everything will happen in the
background. The PDF will be created, the e-mail
| | 02:54 | will be created, the file will be
attached, it'll be addressed, and it'll be sent
| | 02:58 | off without any further
interaction on your part.
| | 03:00 | Now I'm going to go ahead and set this
to Yes and make sure I don't send any
| | 03:03 | accidental e-mails here,
let me close this Macro.
| | 03:06 | I'll go ahead and save those changes.
| | 03:09 | And now this button does something
that's more specific than what the Button
| | 03:12 | Wizard created for us in the first place.
| | 03:14 | I am going to go ahead and change its
name a little bit so it's not just Mail
| | 03:16 | Report, which is a little generic.
| | 03:17 | I'll go to the Format tab here and the
Caption is currently set to Mail Report.
| | 03:22 | I could be more specific and I'm going
to change this caption to "email lifetime
| | 03:26 | spend report" and go ahead and press Enter.
| | 03:33 | Now I need to change the size of my
button a little bit to accommodate all of
| | 03:35 | the text, and I can move it down on my form.
| | 03:42 | So there's one button; let's go
ahead again and add another one.
| | 03:44 | I'll go back up, click on
Button control, and add it to my form.
| | 03:48 | This time I am going to go to
Report Operations > Send Report to File. Go
| | 03:53 | ahead and say Next.
| | 03:54 | This time I'm going to choose my
CustomersbyState file. Say Next.
| | 03:59 | I can choose Text again or a Picture.
I'll choose the Text and say Next.
| | 04:04 | I like to choose the Text option
because it allows me to be specific about
| | 04:08 | what this button is going to be doing.
| | 04:09 | Icons can be a little bit vague sometimes.
| | 04:11 | Go ahead and say Finish here, and now
I've got a basic button that will export
| | 04:15 | that report to a file.
| | 04:17 | But just like with this Email button
down below, there is actually a bunch more
| | 04:20 | options that I can use by going
into the Wizard, but just like the email
| | 04:23 | button down below, I actually have a
bunch more options that I can use by
| | 04:27 | looking at the designer.
| | 04:28 | I'll go into the Event tab, the On Click
event. The Macro that's embedded here, I
| | 04:33 | can edit it using the build button again. Build.
| | 04:37 | The action that the Wizard created
was Output 2, and is outputting the
| | 04:41 | CustomersbyState Report.
| | 04:43 | Now if I click that button right now
Access is going to prompt me for a bunch
| | 04:46 | of settings. It wants to know where to put
the file and what kind of file to create.
| | 04:50 | I can specify some of those right there.
| | 04:53 | The Output Format, for instance, maybe
I just want to export this is an Excel
| | 04:56 | file every single time. I'll choose Excel.
| | 04:58 | Output File location, I can specify
a path so I can save it to my local
| | 05:03 | computer in the same place every time,
or I can save it onto a network drive if
| | 05:07 | I want to archive it.
| | 05:08 | If I specify a path here, Access will just
automatically save an Excel file in that location.
| | 05:13 | I can leave either of these blank;
| | 05:15 | I'm going to leave the Output File
location blank, but specify Excel.
| | 05:18 | We'll go ahead and close
this and save the changes.
| | 05:22 | Now this button isn't just sending a
report to a file but it's actually exporting
| | 05:26 | a specific report to a specific file type.
| | 05:28 | We'll go ahead and go back to
our Format tab and change the name.
| | 05:32 | I'll change the name to Export
Customers to Excel, and again I'll change
| | 05:42 | the size of my button.
| | 05:42 | So now I've got a button, I am going
to go ahead and go into Form View here
| | 05:49 | and we'll test it out.
| | 05:50 | If I click on it, it will export that
report directly to an Excel file here,
| | 05:54 | we'll specify that here, and then I'll
tell it where I want to save it. Go ahead
| | 05:58 | and say Cancel here.
| | 05:59 | So now let's go ahead and organize
our buttons a little bit on our form.
| | 06:02 | I'll switch back into Design View and
remember, since this is a floating menu, I
| | 06:05 | can't change it here.
| | 06:06 | I've got to right-click and go to Design View.
| | 06:10 | Now, let's go ahead and
organize these a little bit.
| | 06:12 | I'm going to put them roughly in the
order that I want and then I can go ahead
| | 06:15 | and resize and arrange
them so they're all aligned.
| | 06:18 | First I want to take my employee
directory and put that up in the top.
| | 06:22 | My Add new customer and Export
Customers to Excel kind of go together, so I'm
| | 06:25 | going to put those together here.
| | 06:26 | We'll have Enter a Product Review in
the middle and then we'll put our lifetime
| | 06:31 | spend reports below Customers, since
that kind of goes with customers as well.
| | 06:34 | I'll go ahead and highlight all of
these to select them all at once and I'll go
| | 06:38 | to the Arrange tab up at the top, and
I'll choose this To Widest option and that
| | 06:44 | will size all of the buttons to the same width.
| | 06:47 | Now they're all the same width, I
can align them all to the same edge by
| | 06:50 | clicking left or right. I'll click Left and
that will move them all to the same left edge.
| | 06:56 | Now you can go ahead and space them by
going up to the Equal Vertical alignment here.
| | 07:01 | That will space all the buttons evenly.
| | 07:03 | So there we go, let's go ahead and
switch out of this, I'll go to the Design tab
| | 07:07 | and change my Form View.
| | 07:09 | And there are my buttons. They are all
arranged nicely on the form, and they do
| | 07:12 | different things when I click on them.
| | 07:14 | So starting with the Wizard to create a
basic Macro and then editing it to add
| | 07:18 | additional options is a great way to
add functionality and a finer level of
| | 07:22 | control over your database.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | I want to thank you for joining me here
in Access and I'd like to leave you with
| | 00:03 | these parting thoughts.
| | 00:04 | Form and Report design in Access
is one of those areas that really
| | 00:07 | is interdisciplinary.
| | 00:09 | And you can pick up helpful ideas on
how to structure your content in the most
| | 00:12 | unexpected of places.
| | 00:14 | Start paying close attention to the
experience you have completing forms on the
| | 00:17 | Internet or interacting with
a screen on an ATM machine.
| | 00:20 | If you find an unusually well laid out
financial report, take a few minutes to
| | 00:24 | pick it apart and figure
out why it really works.
| | 00:27 | Inspiration on how to create a better
interactive experience for your end users
| | 00:31 | literally is all around you.
| | 00:32 | I'd like to recommend a couple of
additional lynda.com courses that can help you
| | 00:36 | build on the core concepts of user
interface and would apply to what you've
| | 00:40 | seen here in Access.
| | 00:42 | The first is Web Form Design Best Practices.
| | 00:44 | That course covers the importance of
understanding user behavior and form organization.
| | 00:48 | The second course is Typography
for Web Designers, which explores the
| | 00:52 | fundamentals of organizing text for
increased clarity and organization.
| | 00:56 | So until next time, thanks
again and have a fantastic day.
| | Collapse this transcript |
|
|
