Microsoft Access Final

Actual instructions with screenshots attached to this question.

Final Project

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

designs.

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.

Part2:

Create the Customer Rental Form shown below. Example 1

Example 2

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

form wizard)

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

the screen.

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

about.

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

view!

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

tool.

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.

Explore.

Part 3

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

button.

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!

Part 4

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

shown above.)

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.