Actual instructions with screenshots attached to this question.
Part 1 (Videos Form)
Create this form to search for the title of a video. Create the necessary
Video table (with about 5 or 6 records in the interest of time) with the fields
shown. Use any images you can find. Make the form look professional and
not be the default design. You can use themes and colors for quick elegant
The rest of the final project is detailed below. You will need the Video table
you made for the form above, to complete the project as well as two
additional tables (a customer table and a rentals table). You only need to
enter a few records to demonstrate that it is working.
Create the Customer Rental Form shown below. Example 1
Not all the subform fields are showing and they are in a different order than the one above.Here is a guide to the steps:
Three tables are created, and related – Customers, Rentals, Videos( similar
to the past exercises).
The Phone # is the ID# for the customers table, and the Video table of
course has its own ID#. In the example 1 above, the Video ID# is simple
whereas in Example 2, the Video ID# is more realistic like V-765 or SD-351.
(The images are screen shots from different students.)
When creating the form/subform above, by using Create, More
Forms…, Form Wizard, be sure to select the customers table first and send
ALL the fields over to the right, then select the Rentals table and send all
the fields over EXCEPT the customer ID field, then select the videos table
and send over only the fields you need and be sure NOT to send the Video ID
(you don’t want the user typing into this field by mistake! Remember there
is another Video ID field in the rentals table that was already sent to the
Once the form/subform is created, you can customize it, and there is quite
a bit of this to do. Some of the features, you haven’t done before.
In design view, first add the search control for the customer. Be sure to
show the Phone # along with the name as in the graphic for Example 2
above. Then create the combo box for the Video ID. Start by deleting the
Video ID field in the subform, and replace it with the Combo Box form control. When prompted, select to display the Video ID as well as the Video
Title as shown below. It is crucial, when prompted, to select the choice that
stores the data (Video ID), in the Video ID field of the rentals table.
Also add the calculated fields for # of Days and total. You can add these
fields anywhere in the subform you like. As you must have noticed by now,
the layout of the subform in design view, has no bearing whatsoever on the
actual layout when you view the form. This is because the default view for
the subform is set to datasheet view and a grid style or table, is forced on
the subform. But sometimes we want the subform to appear the way it is
arranged in the design view.
Now for the fun part! You are now going to make the subform appear as it
does in the design view.
Select the subform by clicking the square in the top left corner where the
ruler guides meet. Show the property sheet. It appears on the right side of
Select the ALL tab on the property sheet, and locate the Default View, and
change it to Continuous Form. If you view the form now, it will reflect the
actual layout in design view. View the form so you know what we are talking
The reason we are changing the view is so you can add a column total to the
subform. The rules for column totals (subtotal for videos rented for this
customer) in a form are very strict. They must be in the form footer to work
correctly and there is no form footer in datasheet view! This was the same
situation we encountered in reports.
The next rule for subtotals is, you cannot specify the name of a calculated
control in the SUM function. Only real field names are allowed. So if you
have a column like we had previously for a line total such as Price * Qty, and
you name it Total, you cannot specify =SUM([Total]) as Total is a calculated
field, not a real field from a table! However you can specify =SUM([Price] *
[Qty]) so it’s not so bad. Keep in mind that Form footers only show in form,
or continuous form, view. Not in datasheet view!
The last part to the design is to move the fields around so they look like
mine (in the graphic below) in a straight row without the text labels next to
them. We are doing this because we like the display of the datasheet view
which shows rows of related records all at once. We like seeing all the orders a customer placed, but we also need a footer! So we will be using
continuous form view, and re-arranging the fields to look like datasheet
Some of you are using an older version of Access (2003) and so you will have
to manually delete each field label (select the top right corner of the field
label before you press delete, so that you do not delete the whole object –
field and label), then you have to drag the fields around to appear in a
straight line, then remake the labels in the header section using the Label
In Access 2007 and later, there is a tool on the Arrange tab called Tabular.
If you simply select all the fields (click the top right corner where there is a
cross icon to select all the fields, or use shift+click to do a multiple
selection). Then click the tabular tool now, all the fields selected will
appear in a straight line and their corresponding labels will be placed in the
header section! Very cool! See the graphic below for the tabular tool and
the select all fields icon.Now all you have to do is size the fields and drag them over to the left
margin so they fit better on the screen.
Now add your Total to the footer by using the =SUM( ) function. You can
peek at mine in the graphic.
Use the property sheet to set the currency formats. Make sure the Date
Picker (calendar control) is working. Also be sure to spend time,
considerable time I’m sure, making the form look very similar to mine in
that it is easy to read and use. Everything is positioned, sized correctly and
neatly on the screen.While you’re on the property sheet, let’s make sure you have experience
with this feature. You should prevent accidental typing into the video title
and price fields. This could change the data in your inventory table. So
protect these fields. Lock them. Disable them. See if you find the property
to do this. Those words are hints. You may want to experiment with the two
properties that can do the job for you, and choose the one you like the best.
It is easier to find them on the Data tab of the property sheet.
One last fancy feature. Use conditional formatting to set a color if the Days
are greater than 4. So be sure to have some records with days that are 5 or
more. Below is a graphic to start you out on using the conditional formatting
which you will find on the tool ribbon in design view:Once you click the conditional formatting tool on the Format tab, the dialog
box shown will popup. Click New Rule and the rest is self-explanatory.
Now for some Macros as promised:
1. On the Videos Form from Part 1, add a command button (the button
tool with the XXXX on it) to open the Customer Rental Form created in
Part 2, for ordering/renting a video. The idea is that the store clerk
could browse or search videos for information and then click this new
button to jump directly to the Customer Rental Form to place a rental
order. This type of macro using the button tool is very simple and
completed by just making selections from the wizard. See image
below:Choose your macro task from the categories. Then choose the specific task
form the pane on the right. Click Next to answer questions about which form
to open if that’s what you’re doing, then choose a picture or text for the
2. On the Customer Rental Form used for ordering/renting, add a macro
that will pop-up a message box reminding the clerk to check for ID if
the Video attempting to be ordered/rented, has a rating of “R”. This
one is similar to the one you did for adding 10% to the Discount field
when the Last Name equaled a specified name. You will use the same
IF statement, but this time you will use the messagebox action instead
of the setvalue action. The user only chooses the video for the popup
message to appear. They do not type into the rating field!
Create a Main Form with a menu of buttons to:
1. Search Videos – i.e. open you beautiful Videos form
2. Place an Order – i.e. open your Customer Rental Form
3. Open a report organized (grouped) by Videos with the customers
who ordered it under the video name
4. Quit Access
All these macros are simple button macros that are created when you use
the button tool in the form with the wizard turned on. Just look through all
the choices for the macros to open forms, open reports and quit Access.
The form should look like this, but hopefully prettier.To make this menu form of buttons show up automatically when Access
opens your database,
1. Click the Microsoft Office Button (2007), File for 2010 version, and
then click Access Options.
2. Click Current Database, and then in the Display Form list, select the
form that you want to display when the database starts (the form
3. Click OK, and then close and reopen the database and the form above
should automatically display. Cool, right?
Here is one from a student who took this to the next level.That’s it! Good luck and have some fun with this. You have two full weeks to
complete and submit.