2015 Diploma IT Skills 1: February 2015

Creating a Form and Subform

See notes from last week!

If you would like to watch the video on YouTube, click here

OR you can watch it below

Creating a Menu

The next stage of our little application is to build a menu that will tie it all together.

From the Create Ribbon, choose Blank Form. Go into Design View and start by adding a label for the main heading.

Once you have the heading, you can add command buttons to open the forms and eventually print reports etc.

Here are the steps to have a button to add and edit members.

You can pretty it up by including a background colour and different colur text etc. As this is our main menu, it would also probably contain our company logo.

Activity 1
Create a menu for our Video Vidiots system with the following buttons.

Open your Member Form
Open your Products Form
Open your Genre Form
Open your Rentals Form
Open your Returns Form

** Advanced Exercise - On your own, try and do the following:

Add a Membership number combo box at the top of the form to select the Member from a list
change the Barcode number in the subform to also be a combo box to select the correct movie from a list
Change TAB order in the subform so that the membership number is first and Date returned last.
Create a Form for RETURNS. This should have a drop down list to select the barcode and display the Rentals in a subform. You can then scroll through the list and select the last one - it should have returned date as blank.

Reports

While I much prefer to use the Form Design mode for creating forms, I am more than happy to use the Report Wizard to create simple reports. Reports are usually designed to be viewed on the screen and then printed, whereas forms are mostly for screen use. Many reports are straightforward listings and will require very little "tweaking".

Simple Report

A simple listing style report is created by using the Report button on the Create Ribbon. Just select the table or query and click the button.

Click on the table that you want to use for your report, and click on report.

This will create a report that contains every field in the table selected. Mine goes over 2 pages, which I don't want, so I will have to modify the design and reduce the width of a couple of fields.

I am happy with the final result as it now fits on a single landscape page.

Activity 2

Create a Report to list of of our products.
Create a Report to list all of our Members.

Custom Report

When you created the Members report, you would have found that by including everything, the report created is very cumbersome. We can create many different reports for different audiences and/or purposes. An example could be a Members report that just contains phone contact information. To do this, use the Report Wizard.

Follow the prompts to create a custom report.

Activity 3

Create a phone contact list for your Members table
Add this report to your Main Menu

*** Advanced Exercise

Create address labels for your members table
Create Barcode labels for your products (include Barcode plus Title) - HINT: you will need a barcode font to have an actual barcode on the labels. Use Avery J8164 labels.

Last week we used Access 2013 to create 2 tables for our Video Vidiots store. We created a Members Table and a Products table. We then designed customised forms for both tables and added 2 members to the members table. You all coped very well with the task, even those that had no or very little exerience with Access.

This week we will continue to build on this database by adding more features to our forms as well as another couple of tables.

Adding Buttons to our Form

The first task we will do today is to add some buttons and simple Macros to our forms.

We want to add buttons to perform the following tasks:

Add
Delete
Next Record
Previous Record
Find
Exit (Close Form)

Start by opening up the Members form in Design View.

For this task, we will use the Button tool. This is what my form looks like before the buttons.

Click on the Button tool to select it. Draw a small rectangle towards the bottom of the screen and follow the prompts.

The Buttons will have generic Commandxx as the name. You can give the buttons a more meaningful name by clicking Next at the above wizard dialog box and then change the name. Then on the final option boxm you can click on Finish. This stage is not compulsory, but can make it easier to identify the button if you wish to change the properties.

Use the same process to add the other buttons.

Use the Button tool, draw a rectangle, select the Delete Record action and if you want, give the button a meaningful name

To add the button to go to the next record, use exactly the same process except choose Go To Next Record from the Record Navigation category.

Just to be different, I have used a picture for this button

And here is my form now with 3 buttons on it.

Change the View to form view to test the buttons.

Activity 1

Add the suggested buttons to your Members Form and your Products Form.

Data Validation with Forms

There are a number of ways to validate your data as it is being entered. You can add validation into the design of the table, or you can add it to the form. Today, we will add it to the form.

Setting a Default Value

A simple way to make sure that a field has data in it is to set a default value. This is great if you think that many of your records will use the default value.

As we are a video store, mostly renting to people who live in the local area, the state will most of the time be Victoria. So why not set that as default.

Open your Member Form and go into Design View

Select the State field

Click on the Properties and under the Data TAB, type in Victoria for the Default Value

When you go to add another customer, you will see that the State field will have Victoria already typed in.

Activity 2

Make Victoria the default value for state on your Members form

Using a Combo Box

That's all we really need to do to the Members File. The next "tweak" we will make is to be allow the Genre to be selected from a list. We can do that by including the Genres in as items in the list, or by linking a table that contains the genres. As this is an ADVANCED class, we will do the latter!!

So the first thing is to create the table - we will call it Genres (yeah I know, not very original). The Genres table contain 2 fields, the Genre and a description.

Note: Remember, to create a table, look under the Create section on the ribbon, and change to Design View. You could also create a form for ease of data entry and consistency!!

Here is what mine looks like. Two fields with Genre as a Primary Key.

And with a couple of Genres added

So now to link it to our Form. Load the Products form and go into Design View.

Select and remove the existing Genre field.

Select the Combo Box tool and draw on your form where the Genre field was located.

Follow the prompts from the wizard. Firstly you want to get values from an existing table. The reason we do this rather than type in a list is so that we can easily add more Genres by adding them to the table and not having to go into design view and change the property of the field.

If you use the TAB key to navigate your form, you should also change the TAB order as this field will now be at the end.

Now to test the Form!!

and there we have our drop down list using a file that can be easily updated and integrated into our end product.

Activity 3

Create the Genres Table and add a few Genres

Create a simple Genres Forms

Create the Genre drop down list on your Products Form

Try adding another Genre using your form and retest the Products form.

Combo Box list

Because the Ratings are set values, we can use a combo box to validate the data, but we don't need to link it to another table.

Open the Products form in Design View (if not already)

Click on the Rating field to remove it.

Drag a combo box to the same location.

Choose the second item - I will type in a list of items.

You may need to check the TAB order and make any changes.

Activity 4

Add a Combo Box for Rating

Use your Members Forms to Add 8 new Members - you should then have 10 members in your table

Use your Products Form to add 30 Products to the table

Creating our Transaction table

The idea of our database is to keep our Members details, our Products details, and to keep track of borrowings!!

For this to happen, we need another table that will create a record every time an item in rented out.

What do we need in this table?

Date Borrowed (Date)
Date Due (Date)
Date Returned (Date)
Product barcode (Short Text)
Member Number (Short Text)

Creating our Rentals Form

Setting up the Relationships

The Rentals form is the one that we will use every time a member borrows an item. Before we can create the form, we need to set up a relationship between the Rentals table and the Members and Product tables.

From the Database Tools tab, select Relationships

You will then be able to place the 3 tables on the relationship canvas.

To create the relationship, Drag the Membership Number from the Members Table on to the Member Number in the Rentals Table

Once you click Create, you will see the line connecting the 2 tables

Repeat the process with the barcode field in the Products table and the Product Barcode in the Rentals table

If you have done it correctly, it should look similar to the above screen.

Save and close the relationship window

Create the Form

Design a Blank Form using the Members table as the data source.

Add some basic information - enough to identify the member so perhaps address and mobile number

Use the Subform tool to draw the subform underneath the member details and follow the wizard prompts

I like to use the Membership number as a drop down box to select a member from.

Use the Combo Box tool and follow the wizard prompts

Test out the form.

Activity 5

Create the Rentals table
Create the relationship
Create the Rentals Form/subform
Test the form by selecting a member and "lending" a product to them. At this stage you will need to make a note of a valid product barcode