Session 2 - Access forms and Macro Basics

Let's start this week with the class activity that we didn't complete last week - just to refresh all the wonderful goodness of Access.

Class Exercise

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.

1. Create the Manor House Rescue database
2. Create the Animals Table - See Whiteboard for table details
3. Create a Form that can be used to add animals

More on Access

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 experience 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 box 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 new Members - you should have 10 or so members in your table

Use your Products Form to add 30 Products to the table