We need to work on the Orders form, which is the most crucial of our forms.
It's where we'll have our database user, the store employee, identify what book or books
are being ordered by which customer. We already have the basic form set up.
It was created just like our other forms. It shows all the fields that are in the Orders
table, it is currently showing the first record in the Orders table.
The setup of this form isn't very useful to someone who is entering orders...all of these
IDs don't mean much to the person taking a new order.
The only thing a person entering data would care about is the customer name and the book
title. In other words, who ordered what.
Now, as a database owner, we want to restrict the use of the form to ONLY entering new records,
so that the person using the form doesn't accidentally overwrite an existing order.
We'd want it to open to the new record each time.
So let's set up that level of control right now.
First, let's rename the form New Orders so our user can tell at first glance that he/she
should be entering only New Orders. To make it so that records can ONLY be added,
we have to set the form properties. You can do that in Design View.
If the property sheet is not already open click on the Property Sheet command in the Tools
group under the Design tab. Make sure form is selected from the drop down
since that's where we want to make our changes. Now we'll set a few options under the Data
tab. The text in the lower left corner of the window
helps you know what each field property does. So if we click in Data Entry we know it is
asking you if you want to only allow new records to be added so we'll change No to Yes.
We want to be able to allow additions so this will remain Yes.
We don't want to allow deletions so we'll set that to = no, and we also don't want our
user to edit data from the Orders form so we'll set that to = no.
Now we have a form for our employee to enter New Orders.
So, now let's look at this form from our user's point of view...how would he/she want to use
it when a customer brings a book to purchase/order? Now you see the default view for entering
New Orders. Let's quickly dissect this form.
Order ID is just the unique number that the database assigns to each order.
This isn't an editable field and it isn't something the user would ever need to touch
so we'll hide the Order ID. To do that, switch to Design View, highlight
the field box, bring up the property sheet for these boxes if it's not already
open. Under the Format tab select no beside Visible.
Now I'll switch back to Form View so you can see that the Order ID field is
hidden. Next, we see Customer ID and Book ID.
They obviously aren't very useable like they are now, but we'll come back to those in a
minute. Let's consider the order date field.
As the database owner, we'd want the date to default to the current date, since no customer
will place an order for any day other than today and we would want that info in our table,
but the person entering the new order wouldn't need to enter that.
To set that up go to Design View. Highlight the field box, and bring up its
property sheet. We want to double check and make sure the
drop down is set to Order Date. From the Data tab we want to make sure the
default value is set up to always enter the current date.
We'll use the expression builder to enter this function.
The function starts with an equals sign so I'll go ahead and click equals.
Next we need to click the common expressions folder in the first column.
In the center column we'll click on current date.
In the third column we'll click on Date(). All this equation means is that we
want this field to always automatically enter the current order date.
We're finished with the Data Tab so let's go to the Format tab to hide this field too
since we are automatically putting today's date in.
Let's take a look at the form in Form View. Now our form user just has to worry about
getting the right Customer ID and the right Book ID but the ID numbers don't help them
very much as they are, since they'll be dealing with a real person with a real book, not a
Customer ID with a Book ID. To make sure our user enters the correct customer,
we would want him to choose from our list of customers.
But we'd want the database to store the Customer ID number, not the name -- since that is the
unique identifier for a customer. Well, with a combo box control, we can put
all the information our user needs right on the New Order form itself.
This box will let them choose the customer based on a list of the customer names that
appear in our Customers table. And we can set the form controls up so that
once our user picks a customer from that list, the customer ID is stored in the Orders table.
Let's do this now. Since we're using a combo box, I really don't
need this text box so I'll delete it. First we'll click on the Combo Box command
in the Controls group. Then we'll move our cursor over the form until
a cross hair with a combo box icon appears. Now we're going to draw the combo
box. From the Wizard we'll choose the first option.By
choosing this option we're telling Access to find what we want the user to see in the
combo box. Now we need it to look in the Customers Table.
And we need all the fields, so that our form user can see a complete record in order to
figure out which customer he needs to enter. We want it to sort alphabetically by last
name, since that is a logical way for our user to look up a customer.
Here we can set the column width, so that it all looks nicely compact on our form.
Double click the column edges to get it to best fit.
We also have to move the last name column over to the left, so that we see the last
name listed first in the drop down list. Next we want to tell Access to store the data
so we'll select the second option. This option is asking which value from the
orders table we want stored in the orders table and we want to store our Customer's
ID number, so select Customer ID from this list.
This is the label for the combo box. We'll type in Customer here.
Looks like we're finished here. We'll go to Form View to take a look.
If you want you can move the label and the combo box but we'll take a look at formatting
in our next lesson. We'll do the same thing for the Book ID...our
form user isn't going to know the book ID of the book, so we'll want him to be able
to search on the Book Title and Author. I'll go ahead and finish this up really quickly.
One more enhancement that would make this form more usable is to add a button so that
when our form user enters a new record, they can hit this button to save the record to
the Orders table and advance to the next new record.
Here is how you do that. Click Button on the Controls portion of the
Ribbon. Move your mouse over the form until the cross
hair with the button icon appears. Draw your button wherever you want it to appear
on the form. You can always move this button later.
When our button is pushed we want it to save the current record and advance to a new record
so from the categories list we'll select Record Operations and from the Actions list we'll
select Go To Next Record. The next screen lets us decide whether or
not we want text or a picture on the button. I want the text to read "Submit";
because that's what we generally see on forms where we have to enter and send information.
Once a user clicks Submit, the information will be saved to the Orders table.
Also, by hitting submit we advance to the next blank record.
On the next screen we can choose to give this button a special name.
This could be handy if you have several buttons and you need to know which is which if you
ever have to edit the buttons later. I'll type in Submit here.
Let's go back to Form View and test out our new form! So now if Jimmy Smith purchases
Computer Basics it will be much easier to enter and save the new record to our Orders
Table. As I said before, by clicking Submit, Access
automatically saves the record to the table but you may have to click Refresh All in the
Orders table to see the change. So you've learned how to make forms really
user friendly by adding command buttons and setting form properties.
In our next lesson we'll learn how to make this form look good.