WEBVTT
Kind: captions
Language: en

00:00:06.700 --> 00:00:10.879
We need to work on the Orders form, which
is the most crucial of our forms.

00:00:10.879 --> 00:00:15.860
It's where we'll have our database user, the
store employee, identify what book or books

00:00:15.860 --> 00:00:20.400
are being ordered by which customer.
We already have the basic form set up.

00:00:20.400 --> 00:00:24.809
It was created just like our other forms.
It shows all the fields that are in the Orders

00:00:24.809 --> 00:00:29.460
table, it is currently showing the first record
in the Orders table.

00:00:29.460 --> 00:00:34.480
The setup of this form isn't very useful to
someone who is entering orders...all of these

00:00:34.480 --> 00:00:37.680
IDs don't mean much to the person taking a
new order.

00:00:37.680 --> 00:00:41.910
The only thing a person entering data would
care about is the customer name and the book

00:00:41.910 --> 00:00:45.530
title.
In other words, who ordered what.

00:00:45.530 --> 00:00:52.440
Now, as a database owner, we want to restrict
the use of the form to ONLY entering new records,

00:00:52.440 --> 00:00:56.760
so that the person using the form doesn't
accidentally overwrite an existing order.

00:00:56.760 --> 00:01:00.399
We'd want it to open to the new record each
time.

00:01:00.399 --> 00:01:03.649
So let's set up that level of control right
now.

00:01:03.649 --> 00:01:10.270
First, let's rename the form New Orders so
our user can tell at first glance that he/she

00:01:10.270 --> 00:01:17.270
should be entering only New Orders.
To make it so that records can ONLY be added,

00:01:18.780 --> 00:01:23.600
we have to set the form properties.
You can do that in Design View.

00:01:23.600 --> 00:01:30.100
If the property sheet is not already open click
on the Property Sheet command in the Tools

00:01:30.100 --> 00:01:36.590
group under the Design tab.
Make sure form is selected from the drop down

00:01:36.590 --> 00:01:42.630
since that's where we want to make our changes.
Now we'll set a few options under the Data

00:01:42.630 --> 00:01:45.850
tab.
The text in the lower left corner of the window

00:01:45.850 --> 00:01:52.039
helps you know what each field property does.
So if we click in Data Entry we know it is

00:01:52.039 --> 00:01:59.039
asking you if you want to only allow new records
to be added so we'll change No to Yes.

00:01:59.200 --> 00:02:04.490
We want to be able to allow additions so this
will remain Yes.

00:02:04.490 --> 00:02:11.270
We don't want to allow deletions so we'll
set that to = no, and we also don't want our

00:02:11.270 --> 00:02:16.470
user to edit data from the Orders form so
we'll set that to = no.

00:02:16.470 --> 00:02:19.340
Now we have a form for our employee to enter
New Orders.

00:02:19.340 --> 00:02:25.879
So, now let's look at this form from our user's
point of view...how would he/she want to use

00:02:25.879 --> 00:02:32.379
it when a customer brings a book to purchase/order?
Now you see the default view for entering

00:02:32.379 --> 00:02:36.769
New Orders.
Let's quickly dissect this form.

00:02:36.769 --> 00:02:41.140
Order ID is just the unique number that the
database assigns to each order.

00:02:41.140 --> 00:02:46.299
This isn't an editable field and it isn't
something the user would ever need to touch

00:02:46.299 --> 00:02:53.299
so we'll hide the Order ID.
To do that, switch to Design View, highlight

00:02:53.459 --> 00:03:00.060
the field box, bring up the property sheet
for these boxes if it's not already

00:03:00.060 --> 00:03:07.060
open.
Under the Format tab select no beside Visible.

00:03:09.689 --> 00:03:13.599
Now I'll switch back to Form View
so you can see that the Order ID field is

00:03:13.599 --> 00:03:20.599
hidden.
Next, we see Customer ID and Book ID.

00:03:24.120 --> 00:03:28.689
They obviously aren't very useable like they
are now, but we'll come back to those in a

00:03:28.689 --> 00:03:30.849
minute.
Let's consider the order date field.

00:03:30.849 --> 00:03:36.260
As the database owner, we'd want the date
to default to the current date, since no customer

00:03:36.260 --> 00:03:41.989
will place an order for any day other than
today and we would want that info in our table,

00:03:41.989 --> 00:03:45.540
but the person entering the new order wouldn't
need to enter that.

00:03:45.540 --> 00:03:52.540
To set that up go to Design View.
Highlight the field box, and bring up its

00:03:54.569 --> 00:03:59.439
property sheet.
We want to double check and make sure the

00:03:59.439 --> 00:04:04.810
drop down is set to Order Date.
From the Data tab we want to make sure the

00:04:04.810 --> 00:04:08.510
default value is set up to always enter the
current date.

00:04:08.510 --> 00:04:12.409
We'll use the expression builder to enter
this function.

00:04:12.409 --> 00:04:19.409
The function starts with an equals sign so
I'll go ahead and click equals.

00:04:20.079 --> 00:04:24.230
Next we need to click the common expressions
folder in the first column.

00:04:24.230 --> 00:04:31.230
In the center column we'll click on current
date.

00:04:31.630 --> 00:04:37.350
In the third column we'll click on
Date(). All this equation means is that we

00:04:37.350 --> 00:04:42.360
want this field to always automatically enter
the current order date.

00:04:42.360 --> 00:04:47.090
We're finished with the Data Tab so let's
go to the Format tab to hide this field too

00:04:47.090 --> 00:04:54.090
since we are automatically putting today's
date in.

00:04:55.140 --> 00:05:00.030
Let's take a look at the form in Form View.
Now our form user just has to worry about

00:05:00.030 --> 00:05:04.730
getting the right Customer ID and the right
Book ID but the ID numbers don't help them

00:05:04.730 --> 00:05:09.790
very much as they are, since they'll be dealing
with a real person with a real book, not a

00:05:09.790 --> 00:05:14.990
Customer ID with a Book ID.
To make sure our user enters the correct customer,

00:05:14.990 --> 00:05:18.160
we would want him to choose from our list
of customers.

00:05:18.160 --> 00:05:23.630
But we'd want the database to store the Customer
ID number, not the name -- since that is the

00:05:23.630 --> 00:05:29.470
unique identifier for a customer.
Well, with a combo box control, we can put

00:05:29.470 --> 00:05:34.230
all the information our user needs right on
the New Order form itself.

00:05:34.230 --> 00:05:38.750
This box will let them choose the customer
based on a list of the customer names that

00:05:38.750 --> 00:05:43.910
appear in our Customers table.
And we can set the form controls up so that

00:05:43.910 --> 00:05:49.990
once our user picks a customer from that list,
the customer ID is stored in the Orders table.

00:05:49.990 --> 00:05:54.210
Let's do this now.
Since we're using a combo box, I really don't

00:05:54.210 --> 00:06:00.360
need this text box so I'll delete it.
First we'll click on the Combo Box command

00:06:00.360 --> 00:06:06.750
in the Controls group.
Then we'll move our cursor over the form until

00:06:06.750 --> 00:06:13.750
a cross hair with a combo box icon appears.
Now we're going to draw the combo

00:06:13.990 --> 00:06:18.840
box.
From the Wizard we'll choose the first option.By

00:06:18.840 --> 00:06:24.180
choosing this option we're telling Access
to find what we want the user to see in the

00:06:24.180 --> 00:06:31.180
combo box.
Now we need it to look in the Customers Table.

00:06:33.120 --> 00:06:37.840
And we need all the fields, so that our form
user can see a complete record in order to

00:06:37.840 --> 00:06:43.800
figure out which customer he needs to enter.
We want it to sort alphabetically by last

00:06:43.800 --> 00:06:50.340
name, since that is a logical way for our
user to look up a customer.

00:06:50.340 --> 00:06:56.500
Here we can set the column width, so that
it all looks nicely compact on our form.

00:06:56.500 --> 00:07:03.500
Double click the column edges to get it to
best fit.

00:07:05.720 --> 00:07:11.230
We also have to move the last name column
over to the left, so that we see the last

00:07:11.230 --> 00:07:18.080
name listed first in the drop down list.
Next we want to tell Access to store the data

00:07:18.080 --> 00:07:22.990
so we'll select the second option.
This option is asking which value from the

00:07:22.990 --> 00:07:27.680
orders table we want stored in the orders
table and we want to store our Customer's

00:07:27.680 --> 00:07:34.180
ID number, so select Customer ID from this
list.

00:07:34.180 --> 00:07:40.400
This is the label for the combo box.
We'll type in Customer here.

00:07:40.400 --> 00:07:44.970
Looks like we're finished here.
We'll go to Form View to take a look.

00:07:44.970 --> 00:07:49.000
If you want you can move the label and the
combo box but we'll take a look at formatting

00:07:49.000 --> 00:07:54.450
in our next lesson.
We'll do the same thing for the Book ID...our

00:07:54.450 --> 00:07:59.220
form user isn't going to know the book ID
of the book, so we'll want him to be able

00:07:59.220 --> 00:08:06.190
to search on the Book Title and Author.
I'll go ahead and finish this up really quickly.

00:08:06.190 --> 00:08:11.100
One more enhancement that would make this
form more usable is to add a button so that

00:08:11.100 --> 00:08:16.050
when our form user enters a new record, they
can hit this button to save the record to

00:08:16.050 --> 00:08:19.780
the Orders table and advance to the next new
record.

00:08:19.780 --> 00:08:25.060
Here is how you do that.
Click Button on the Controls portion of the

00:08:25.060 --> 00:08:29.200
Ribbon.
Move your mouse over the form until the cross

00:08:29.200 --> 00:08:33.820
hair with the button icon appears.
Draw your button wherever you want it to appear

00:08:33.820 --> 00:08:37.409
on the form.
You can always move this button later.

00:08:37.409 --> 00:08:44.190
When our button is pushed we want it to save
the current record and advance to a new record

00:08:44.190 --> 00:08:50.380
so from the categories list we'll select Record
Operations and from the Actions list we'll

00:08:50.380 --> 00:08:56.920
select Go To Next Record.
The next screen lets us decide whether or

00:08:56.920 --> 00:09:02.050
not we want text or a picture on the button.
I want the text to read "Submit";

00:09:02.050 --> 00:09:07.260
because that's what we generally see on forms
where we have to enter and send information.

00:09:07.260 --> 00:09:12.840
Once a user clicks Submit, the information
will be saved to the Orders table.

00:09:12.840 --> 00:09:17.990
Also, by hitting submit we advance to the
next blank record.

00:09:17.990 --> 00:09:23.090
On the next screen we can choose to give this
button a special name.

00:09:23.090 --> 00:09:27.590
This could be handy if you have several buttons
and you need to know which is which if you

00:09:27.590 --> 00:09:32.370
ever have to edit the buttons later.
I'll type in Submit here.

00:09:32.370 --> 00:09:39.370
Let's go back to Form View and test out our
new form! So now if Jimmy Smith purchases

00:09:40.130 --> 00:09:45.190
Computer Basics it will be much easier to
enter and save the new record to our Orders

00:09:45.190 --> 00:09:49.530
Table.
As I said before, by clicking Submit, Access

00:09:49.530 --> 00:09:56.110
automatically saves the record to the table
but you may have to click Refresh All in the

00:09:56.110 --> 00:10:00.180
Orders table to see the change.
So you've learned how to make forms really

00:10:00.180 --> 00:10:04.570
user friendly by adding command buttons and
setting form properties.

00:10:04.570 --> 00:10:08.010
In our next lesson we'll learn how
to make this form look good.