Applications Session 2 - Access Forms

Forms

All of the options for creating forms are in the Forms section on the Create ribbon.

The first button, just labelled Form, will create a basic Form where all of your fields and data entry areas are the same size. BOOOOOORRRRRRRIIIIINNNNGGGGGG!!!!! There is not even a heading :(

SO we are going to create our first form using the second button - the one labelled Form Design.

Start by clicking the button - this will create a blank form and will put you into Design View.

See image below for details on relevant buttons and features of Form Design

Once you have the canvas to the correct size, the first thing you need to do is to link the form to the Table.. This form will be used to add data to the MEMBERS table, so we need to link it the that table.

Click on Property Sheet to open the properties box. Click on the Data Tab, Select Form from the drop down menu showing all objects on the form, and make sure that the RECORD SOURCE is MEMBERS.

Close the Property Sheet box by clicking back on the button.

Open the Field List by clicking the Field List button.

Now it is time to start getting creative.

I like to put a heading on my form - the best way to do this is by using the LABEL control

Click the Label Button and draw an area on your canvas. It doesn't matter if it is not exactly where you want it as you can move it at any time.

To keep it simple, we can just put the heading as Members. Use the Property Sheet to change the font and colours etc.

In the Property Sheet you can change the background colour, foreground colour, Font, Size etc. Play....Experiment..... If you really really stuff it up, close the form without saving and start again.

Do the same thing with the Fields. Display the Field List and add the fields to the Canvas.

Use the arrow control to select each one and move them around to where you want them.

Each field that you drag onto the screen has a LABEL and the FIELD so it is 2 items. They can be moved together if you drag from the field rather than the label.

Use the larger "handle" in the top left hand corner to move each component. Use the Grid lines to help line stuff up.

Change the View to Preview mode to see what it will look like when entering data.

When you are happy with the form, Save it, call it Members and Close the form.

**** If you are confident with creating a form, repeat the process to create a form for entering Products.

**** Add some data into your tables - 5 Members and 10 Products should be enough for now



If you would like to see this again, you can watch the video on YouTube

Video Vidiots Database Status

You should have 2 tables:

• Customers/Members table 
• Product Table

You should have 2 forms:

• Customers/Members Data Entry
• Products Data Entry

You should have some data in your tables

• Minimum 5 Members
• Minimum 10 Products

Practice exercise

Let's put our database skills to work!
Manor House Animal Rescue is a small not for profit organisation operating in the Bass Coast region of Victoria. The organisation rescues and rehabilitates unwanted or lost animals - mostly dogs, cats and chickens, but some of the local injured wildlife also occasionally come into their care.

Manor House Animal Rescue would like a database that will allow them to keep a record of all of the animals in their care, details of any actions taken with the care of the animals, and details of rehoming or release.

Where to Start!!

• How many tables will we need in our database?
• What fields, field types will we need in each table?
• What forms will we need for entering data?
• What reports will we need?
• What items will we need on the menu - what actions do we want people who are working at the centre to be able to do?

To start with, we will need to create 2 tables:

• An Animals detail table which will contain the basic identifying information about each animal.
• A "transaction" file that will keep track of any actions that are taken with each animal

These will have a ONE-To-MANY relationship as there could be multiple actions taken against each animal in care. We will learn about relationships very soon

1. Create the Manor House Rescue database
2. Create the Animals Table
3. Create a Form that can be used to add animals
4, Test out your form by adding an"animal" or two

Animals

What information do we need to keep!!

*** We will do this exercise on the "board" ***

*** Please keep this file as we will also build on this database in future lessons ***

Applications Session 1: Introduction to Access 2013

What is Access?

Access is Microsoft's Database Application and it is used to store and manipulate data.

A database is made up of one or more tables that are usually related or have something in common.

Each Table is made up of fields and records. The fields have to be defined when creating your table.

It behaves a bit differently to the other applications as you have to save the file before you even get started.

DATA is saved automatically as you go, but you will need to save the file if you make changes to any of the views - tables, forms, reports, queries etc.

Another thing about Access is that you also have to know what you want to store in each of the tables. With Word and Excel, you can kind of make it up as you go along, adding headings and pages when and where you want. With Access, it becomes more difficult to do this and so you should spend some time planning before you actually put information in. A large piece of paper or a whiteboard will do the trick!!

Today we will start with some basics and over the next few weeks, we will build up to a quite sophisticated system. Your main assessment task for this part of the unit will be to build a Digital Repository. If you are doing the Digital Repositories class, you may use this assessment task for that unit as well.

Class Scenario

The Video Vidiots store is about to open up in Cranbourne and they need a simple database system to keep track of their customers and their video/DVD/Games collection. As this store is the brainchild of your youngest sibling, somehow you got roped in to designing and setting up the database for the store.

Where to Start!!

• How many tables will we need in our database?
• What fields, field types will we need in each table?
• What forms will we need for entering data?
• What reports will we need?
• What items will we need on the menu - what actions do we want people who are working at the store to be able to do?

To start with, we will need to create 2 tables: Members and Products What information do we need to keep about our members?

Membership Number
Title
First Name
Last Name
Address Line 1
Address Line 2
Suburb
State
Postcode
Home Phone
Mobile
Email Address
Membership Type

** you may add anything else that you think might be important

** In Database Terminology, these are called FIELDS. A RECORD is created when you complete all of the fields for one item.

N.B. The field type of all of these will be Short Text. When designing a database, it is generally recommended that number fields that will not be used in calculations, such as postcode and phone numbers, be stored as text.

Products

What information do we need to keep about our products?

Barcode
Title
Product Type
Rating
Genre
Date Released
Number of items

** you may add anything else that you think might be important

Creating the Database and the Members Table

Start by opening Access 2013.

When prompted, choose Blank Database and then give the database a name.

Once you have created your database, you are ready to create your first table.

You can create your table in Table View or in Design View. Using Design View gives you more control and features that you can set.

In Table View, the idea is to click on each Column Heading, Select the field type, and then type in the field name. Once you have done one column heading (or field), you can move onto the next.

In Design View, you have the field names in the top half of the screen and the settings or properties in the bottom half. You can switch between views by clicking the Design View button. When you go into Design View, you will need to save your table. The first time you save your table, you will be prompted to give your table a name.

Continue to add the required fields to the table

Once you have finished, you can save and then close your newly created table.

Create the Products Table

The Products table can be created using either of the above techniques.

To create a new table, Click on the Create Tab and select Table.

With our Products Table, we will need to change a couple of the field types.

Date Released - Date/Time
Number of Items - Number

Save the file when prompted and call it Products. You should now have 2 tables.

ISN'T ACCESS AWESOME!!!

Click on the little X to close the table. - Make sure that it is the X for the table and not for Access, which is right at the top of the screen.

If you would like to see this again - and why wouldn't you - you can watch the video tutorial.

Primary Key

You probably noticed that, by default, Access creates a field called ID with a type of Autonumber. It does this for a reason, which I don't really agree with, but I do agree with the concept of a primary key.

It is important for most databases that there is a unique identifier for each of the items in the database. The Primary Key is the term that Access uses for the unique identifier in a database.

Think about some of the databases that you may belong to!!

Do you drive a car? Every vehicle in Victoria has a number of unique identifiers - VIN, Chassis number, and importantly Registration plates - how else are those nasty speed cameras gonna fine you!!

Are you a student (silly question!!)? Your student card shows your unique student identifier for Chisholm. Do you have a medicare card? Drivers License? Credit Card? Bank Account? All these databases have unique identifiers.

In our Members table, we need to create a Primary Key. There was one created automatically, but it is an autonumber field, which will automatically increment by 1 for each new member. As we have nice cards printed with barcodes on them, we want to be able to create our own Membership Number as the unique identifier.

Same goes for our Products Table - each item has a barcode on it. We do not want an autonumber ID field - we need to be able to put our own identifier in for each Product.

Start by opening up the Members table. The easiest way to do this is to double-click the name in the left hand column.

Go into Design View by clicking the Design View button (left top under the word file).

Delete the ID field by selecting the row and clicking on Delete Row.

Once the row has disappeared, we need to make our Membership Number the Primary Key field.

Select the Membership Number field and click on the primary key button.

Once you have made the Member Number the Primary Key, Close and Save the table when prompted.

Repeat the process for the Products table

Entering Data

You have created 2 tables, but at this stage, they are empty. We need to start to put some information in to them. We will start by doing this using Table View.

If not already open, double-Click the Members Table to open it in Table View. Your cursor will be in the first field ready for your data. Type in a membership number and press TAB to move to the next column.

The first member should be YOU.

 

Add a second Member - use the details of another family member (or make it up).

When you have added the second record, close the table.

Entering in data using this method can be awkward as the fields are all the same size and your data may disappear from view as you are typing it. It is much better and easier to USE A FORM to enter data into your tables. A BASIC FORM can be created using the Wizard, or you can design a fabulous form from scratch.

You could also create a form using the Wizard or a Basic form and then use your creative touches to modify and personalise it.

We will continue with forms in our next lesson.