Classes For Computer Worldwide

Friday, 6 July 2012

Ms Access # 9

Introduction

Now that you know how to make forms work better for your user, it is time to think about what a form looks like. This lesson will address the various ways to modify a form layout, including how to add a logo and apply an Auto Format to the form. It will also cover using and formatting titles, as well as moving and applying borders to objects.

Making Forms More Attractive

Since other people will be using the forms in your database to enter data, you may want to make the forms more visually appealing. These types of changes are done to the form in Layout View.
In Layout view, you see the form as it will appear to the user in Form View, so it very useful for setting the size of controls and other objects on the form, and for changing the font, colors, and borders on the form.

To Switch to Layout View

There are three different ways to open the form in Layout View:

Right click on the form tab or on the form name in the object list. Then, select Layout View from the list.
Select Layout View in the Views group on the Ribbon.
Click on the Layout View icon in the bottom right hand corner of the Access 2007 window.

Adding a Logo

Microsoft Access 2007 automatically puts a form icon, like the one below, in the header area of every form that you create.

Form Icon

One way to customize the look of your forms is to remove that icon and replace it with a logo. This is very easy to do using the Logo command.

To Replace the Icon with a Logo

To replace the form icon with a logo of your choice:

Delete the form icon.
Select the Logo command from the Controls group on the Ribbon.

Logo Command

Use the Insert Picture dialog box to locate the picture file you wish to use as the logo. Then, click Ok. The new logo should appear.

You can move and re-size the logo, as well. Moving and re-sizing objects on a form is covered later in this lesson.

Applying a Style with AutoFormat

Another simple way to dramatically change the way your form looks is to apply a style with the AutoFormat command. You could modify the color for each part of the form on your own, but Microsoft Access has already combined colors in several attractive styles. These styles are available under the AutoFormat command.

To Apply an AutoFormat

To apply a pre-set format to the form:

Click on the AutoFormat command on the Ribbon.
Select the desired format. The change will appear when the mouse button is released.

AutoFormat Options

Working with Titles

One of the easiest things to change on the form is the title, which appears in the form header with the icon or logo.

To Change the Form Title

To change the form title:

Click the Title command in the Controls group on the Ribbon.

Title Command

When the title highlight appears, type the new title and hit the enter key on your keyboard.

Note: The title does not affect the name of the form and vice versa.

Changing Text on the Form

Access, like all Microsoft Office 2007 products, allows you to change the font style, text size, text color and alignment. It also lets you apply bold, italics, or underlines to the text. All of this is done from the Ribbon, as seen in the following diagram.

Text Formatting Commands

To Modify Text

To make changes to the format of the text on a form:

Highlight the text
Apply the desired format by doing one or more of the following:
- Select the desired font style from the Font face drop down list.
- Set the size of the text using the Font size selection list.
- Choose a color for the text using the Font color selector.
- Apply either the bold, italics, or underline style.

Additional text formatting options are available in the Font group. The above list includes the most common text modifications.

Working with Objects on a Form

Other ways to modify the way a form looks in Access 2007 include re-sizing and re-positioning the objects that appear on the form, as well as applying borders to objects on the form. All of these tasks are easily done in Layout View.

To Move Objects on a Form

To move an object to another place on the form:

Highlight the object by clicking on it.
Move the object by dragging and dropping it in a new location.

To Re-size Objects on a Form

To change the size of any object on the form:

Highlight the object by clicking on it.
When the cross hair cursor appears, hold down the left mouse button and drag the edge to the preferred size.
Release the mouse button to apply the change.

To Apply a Border to Objects on a Form

Borders can make an object more visible to your form user. To apply a border to any object on a form:

Highlight the object by clicking on it.
Select a width using the Line Width command.
Select a style using the Line Style command.
Select a color using the Line Color command.

Border Commands

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.

Open the Orders Form in Layout View and change the form by:
- Adding a picture using the Logo command.
- Giving the form a new Title.
- Modifying some of the text on the form.
- Applying one of the AutoFormat options.
- Moving or resizing an object on the form.
- Applying a border to an object on the form.

Ms Access # 8

Introduction

You have already seen how a Combo Box control can make a form more user-friendly. In Access 2007, there are several other ways you can modify forms to make them easier to use and, at the same time, increase the integrity of the database. This lesson will explain how to use form properties to limit the actions your form user can take. It will also walk you through hiding fields on a form and adding command buttons to the form.

Making Forms More Usable

You already know that forms can help you increase the integrity of your data by limiting what your user can see and how they can enter data. You saw that using a drop down list can make data entry easy. Now it is time to think about the design of your forms from your form user's perspective.
Let's look at the Orders form in our bookstore scenario. The basic form, which was created with the Form command, looks like this:

Orders Form

This form is where we would want our user, the store employee, to pair a customer with a book to complete an order. Let's look at this form from our user's point of view:

Our employee would be adding new orders, not editing existing ones. We will set form properties to limit this action.
The user will also never need to enter the Order ID number, as this is the number that the database assigns each order record to differentiate it from other orders. We will hide this field.
The Customer ID field and the Book ID field are not very useful as they are, because our employee would have to know each customer's ID number and every Book ID number to be able to enter the data in the format we need. We will make these fields more usable by creating Combo Boxes that help our user select the correct customer and book.
Our user will not need to enter the Order Date, because we want the database to auto-populate this field in the database using today's date. We will set field properties to make this happen.

Setting Form Properties

Access 2007 allows you to set many form properties. Form properties are options that are set in Design View on the Property Sheet, like the one shown below. These options control how the form looks, works, and interacts with the rest of the database.

Property Sheet

To Set Form Properties

To set properties for your form:

View the form in Design View.
Select the Property Sheet command from the Tools group on the Ribbon, as seen above.

Note: The form Property Sheet is also accessible by right clicking anywhere on the form in Design View and selecting Properties from the menu.

When the property sheet opens:
- Make sure Form is selected in the Selection type drop down list
- Set the desired form properties.

Setting Field Properties with the Property Sheet

Tip: Use the tabs to find the specific property you wish to set. Use the help that Microsoft Access 2007 offers you in the bottom left corner of the Access window to know what each property setting does.

Hiding Fields on a Form

There will be times when a field will not be needed on a form. Access 2007 allows you to hide fields by setting the Visible field property in Design View.

To Hide a Field on a Form

Hiding a field makes it invisible on the form when it is viewed in Form View. To hide a field:

In Design View, open the Property Sheet.
Change the Visible property setting to No, as seen below:

Visible Property Setting

Switch to Form View to verify that the field is hidden.

Creating Combo Boxes

For the New Orders form in our bookstore example, we created user-friendly drop down lists using the Combo Box command. These drop down lists help our user identify a specific customer and a specific book using more useful information than the Customer ID and Book ID numbers, as seen below:

New Orders Form with Drop Down Lists

The process for creating combo boxes is covered in Lesson 7.

Setting Field Properties

Sometimes, it may be necessary to set field properties from the form itself. For example, on the New Orders form for our book store shown below, we want to set up the Order Date field to auto-fill with the current date each time an order is entered. This way, our employee will not have to worry about entering a date on the form, because the form will do it for him.

New Orders Form

To Set Field Properties from a Form

To set a field property for a form, in Design View:

Highlight the appropriate field on the form.
Open the Property Sheet.
Verify that the Field Name appears in the Selection type drop down list.
Set the desired field property.

In our example, we want to set the Order Date field to auto-fill with the current date. Refer to the picture below. Notice that we have the Order Date field highlighted on the form itself. On the Property Sheet, we have Order Date showing in the Selection type drop down list.

Setting Properties for Order Date on the New Orders Form

Because we want the Order Date to auto-fill, we have to set the default value to always enter the current date.

To Set a Field to Auto-fill with the Current Date

To set the Order Date field to automatically enter the current date in our database:

Open the Data tab on the field's Property Sheet.

Order Date Properties on New Orders Form

Setting Order Date to Auto-fill with Current Date

Set the Default Value property by clicking on the Expression Builder button.
Use the Expression Builder to enter the expression for the current date. It looks like this:

Expression for Current Date

The Expression Builder contains many commonly used expressions for database functions. Explore them by clicking on the various files in the left hand column.

Creating Command Buttons

Another way to make a form more user-friendly is by adding command buttons to the form. Command buttons are a quick way for your form user to take a specific action. These command buttons are grouped into categories of actions, including:

Record Navigation command buttons -- easily allow your user move among the records in your database.
Record Operation command buttons -- let your user do things like save or print a record.
Form Operation command buttons -- give your user the ability to quickly open or close a form, print the current form, and other actions
Report Operation command buttons -- offer the user a quick way to do things such as preview or mail a report

To Add a Command Button to a Form

To add a command button to a form:

Click the Button command in the Controls group on the Ribbon.

Button Command in Controls Group

When the Command Button Wizard opens:
- Select the type of command you want from the Categories list.
- Select the specific action you want the command button to perform from the Actions list.
- Click Next.
In the next Wizard step:
- If you want text to appear on the button, enter it in the Text box.
- If you want a picture to appear on the button, select one using the Browse button.
- Click Next.
In the next Wizard step:
- Give the button a meaningful name.
- Click Finish.

The command button should be operational and appear on the form in Form View.

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.

Open the Orders Form and change the form properties so that it limits the user to adding records only.
Hide the Order ID and Order Date fields.
Set the Order Date field property to have a Default Value of the current date.
Create drop down lists for the Customer ID and Book ID field, making sure they contain helpful information for your user and correctly populate the database with the ID numbers.
Add a command button to the form.

Ms Access # 7

Introduction

Another way to populate a database is with the use of forms. An Access 2007 form helps the person entering data know exactly what information to enter. This lesson will address the benefits of using forms with a database. It will also show you how to set up a basic form for your Access 2007 database, and will show you how the person entering data will use the form to populate or edit data in the database. You will also learn how to enhance a basic form with a drop down list.

Why Use Forms?

In real life, a form is piece of paper that you fill out so that someone can collect and keep track of specific information about you. Only one record, your record, is captured with any given paper form.

Common Form

Access 2007 forms work very much the same way.
In previous lessons, you saw that you can populate a database by entering records into the tables themselves. If the database has hundreds of records and many fields to populate for any given record, a table can be overwhelming to the person entering data. An Access form lets your user enter data one record at a time, without having to see the entire table.

Access Form

An Access 2007 form also lets the person entering data know exactly what information to enter, and can even tell him what that information should look like. Adding certain control components to a form -- like a drop down menu -- can dramatically increase the integrity of the data that is held in a database.
A database owner wants to control the levels of access that other database users have to the data -- because the fewer people that are interacting with the data, the lower the chance that the data can become compromised. Corrupt data is not useful! Forms are one more way that the database owner can limit the actions of the other users. Form properties can be set so users can only enter records or only view records.

Creating a Form

Access 2007 has several automatic tools for creating forms. These tools are located in the Forms group on the Create tab in the Ribbon, as seen below:

Forms Command Group

The Access 2007 forms tools include:

The Form command makes a basic form, showing a single record at a time.
The Split Form command creates a form showing one record on top, and includes the datasheet view of entire source table on the bottom.
The Multiple Items command creates a form that shows all the records at once, which looks very similar to the source table in datasheet view.
The Form Wizard is hidden under the More Forms command. It walks you through the process of creating more customized forms.

To Create a Form using the Form Command

The basic Form command is the one we suggest, because it allows the person entering data to see just one record at a time. It also includes all the fields in your source table for you, and you can modify the layout of the basic form to hide fields or add controls.
To create a form using this command:

Begin by highlighting the table you wish to use as a source table.
With the source table highlighted, select the Form command from the Forms command group in the Create tab on the Ribbon.
The new form is created and opens in the object pane.

The newly created form has the same name as the source table by default. You can give the form a new name by saving the form. You will be prompted to give the form a name.

Using Forms to Enter Data

Populating a database is easy once you have a basic form in place. Record navigation works the same way for forms as it did for tables. The Navigation Bar is located in the bottom left of the object pane. The Navigation Buttons work the same way they did for the tables, also. The following picture shows the navigation buttons for a form.

Form Navigation Buttons

To Add a Record using a Form

To add a record to the database using a form:

Navigate to a new record, either by using the New Record navigation button, or the New command in the Records group on the Ribbon.
Then, simply add the new data.

Your data must be entered using an acceptable format. The acceptable formats were established when the field properties were set.

Finally, you must save the record.
Save by using either the Save command on the Ribbon, or by progressing to another record using the New (Blank) Record navigation button. Moving to a new record saves the most recently entered record. However, it may be necessary to refresh the table in Datasheet View to see the newest record.

To Edit Records using a Form

Just like in a table, the database user can edit records from a form using the Find and Replace command. This command works exactly the same way for a form as it does in a table.

Creating a Drop Down List

Using a drop down list on a form can increase the integrity of the data in the database, because drop down lists force the form user to select one of the pre-set options in the list to populate the field. These types of form controls are relatively easy to set up using the Combo Box.

To Create a Drop Down List using a Combo Box Control

With the form opened in Design View, select the Combo Box command in the Controls group on the Design tab in the Ribbon

Combo Box Command
Drag and drop the Combo Box sizing tool to create the Combo Box where desired on the form.

Combo Box Sizing Tool

The Combo Box Wizard appears.

Combo Box Wizard

Choose the desired option from the Wizard, and click Next.
Because the middle option was selected in the example above, the Wizard progresses to the next step, which asks for the values to be typed into a small table.

Combo Box Wizard

Next, the Wizard asks what to do with the entered values. Access can either remember the values for later use, or can populate a field with the entered values. Use the drop down list to select which field Access should use to store the values.

Combo Box Wizard

Once the desired option has been selected, click Next.
Finally, the Wizard gives the Combo Box a generic name, which may be meaningful to you later if there is ever a need to adjust the properties of this or another Combo Box. If you choose, give the Combo Box a name and click Finish.

Whatever name is entered will appear as a label on the form. This label may be deleted, if desired.

Switch to Form View to see how the Combo Box works. The Category drop down list appears on our Books form, as seen below.

Books Form Drop Down Category List

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.

Scroll through the customer records using the Customers form.
Create a basic Books form using the Form command.
Use the new Books form to enter and save the following data:
- Title: The Secret Streets of Savannah
  Author: Amy Little
  Category: Travel
  Price: $34.99
- Title: Cars and Trucks
  Author: Jonathon Bradley
  Category: Kids
  Price: $14.99
Using the Combo Box command, create a drop down list on the Books form for the following Categories:
- Fiction
- Non-Fiction
- Kids
- History
- Technology
- Home & Garden
- Travel
- Food
Add another record to the database using the Books form with the drop down Category selector.
Use the Find and Replace command to change the price of all books that are $14.99 to be $16.99.

Ms Access # 6

Introduction

Access 2007 databases hold the actual data records inside tables. You can add, edit, and delete records directly from these tables. This lesson will show you how to work in the tables to add new records, as well as how to edit existing records using commands like Copy and Paste and Find and Replace. It will also discuss the dangers involved in deleting records from a table and the importance of setting validation rules and other field properties and to ensure that data is valid.

Adding Records to Tables

When you enter records into your table, you are populating the database. In Access 2007, you can do this a few different ways.
To Add Records in the New Record Row:

Click the record row with the asterisk that appears at the bottom of the table.

New Record Row

Type the data into the appropriate fields.
Hit Enter or the Tab key to move to the next field.

To Add Records with the New Record Navigation Button:

Click the New Record button in the navigation bar. The navigation bar is located in the bottom left corner of the open object pane.

New Record Navigation Button

Type the data into the appropriate fields.
Hit Enter or the Tab key to move to the next field.

To Add Records with the New Record Command:

Click the New Record command in the Records group in the Ribbon.

New Record Command

Type the data into the appropriate fields.
Hit the Enter or the Tab key to move to the next field.

Editing Records in Tables

Sometimes it is necessary to edit records in the database. Like with every other task in Access 2007, this can be done several different ways.
To Edit a Record Directly:

Scroll through the records or use the Navigation Buttons in the navigation bar to find the record to edit.

Navigation Buttons

Click the cell that contains the information that must be edited. A pencil icon appears to indicate edit mode.

Edit Record Mode

Type the new information into the field.
Click outside of the record row to apply the change.

To Edit a Record using Find and Replace:

Click the Find command in the Find group on the Ribbon.

Find Command

The Find and Replace dialogue box opens.

Find and Replace Dialog Box

Tell Access what to find by typing it into the Find What: area.
Type the replace term in the Replace With: area.
Tell Access where to look with the Look In: drop down list. Tip--The first choice in, the drop down is the field you were last in in the table.
Tell Access what to Match: Any part of the field, the whole field, or just the start of the field.
Tell Access how to Search: Up finds records above the cursor, Down finds records below the cursor, and All searches all records.
Click on one of the action options:

Find Next will find the next instance of the word in the table.
Replace will put the new word into the table, overwriting what is currently there.
Use Cancel to stop the edit process.

CAUTION: DO NOT use Replace All , as it will overwrite every instance of the Find term in the table, which can have a serious impact on your data.

To Copy and Paste a Record:

Select the record that you want to copy. Right click and select Copy.
Select the new record row. Then right click and select Paste. The record information appears, with a new record ID number.

To Delete a Record:

Select the record that you want to delete. Then, right click and select Delete Record.
A dialog box appears, telling you the action can not be undone and asking if you are sure you want to delete the record.

Delete Record Dialog Box

There may be other records that rely on the record you are trying to delete. DO NOT delete a record without knowing how it will impact the rest of your database.

Record Numbering after Deleting Records

When you delete a record, that record number is permanently deleted from the database table. If you delete the last record from a table, and then add a new record, your new record numbers will appear to be out of sequence.

Data Validation

Data Validation is a very important database concept. It is the process by which Access tests the data that is being entered into the database, to make sure it is in an acceptable, or valid, format.
Imagine that one of your database users has entered an order date of January 4, 2008 in the month/date/year format, as 01/04/2008. Another user has entered an order placed on that same date in the day/month/year format, as 04/01/2008. Now, if the database is tracking all sales for the month of January 2008, it may not show both orders as placed in January, even though both were placed on the same date.
Access 2007 allows you to set field properties and data validation rules, to force the person entering data to follow a specific format.

Data Types and Validation Rules

Data Validation begins when data types are set during the process of building tables and fields. For example, if a field data type had been set to Currency and a text value is entered in that table field during data entry, Access will not accept an invalid format and will display a validation error, like the one below.
Mismatched Data

Data Validation Error Message

Data validation is accomplished by setting data validation rules and other field properties for various fields.

Setting Data Validation Rules

In Design View, highlight the field that requires a validation rule.
In the Field Properties section at the bottom half of the window, set your validation rule using the Expression Builder. The expression builder offers common syntax to set up a data validation rule.

Use Expression Builder to Enter Validation Rule

Validation Rule for Category Field

Validation rules work most easily with numeric fields. Rules for text fields require you to enclose each acceptable value inside its own quotation marks, separating them with Or, as seen above.

Validation Text

Validation Text is a specialized error message that you can set to have Access tell the person entering data the specific way you would like them to enter it.
To set the Validation Text, enter the error message exactly as you want it to appear to your user in the row directly beneath the Validation Rule row in the Field Properties section of Design View. For the validation rule that we set for Category, you would set the validation text like this:
Category Validation Text

Validation Text for Category Field

The following image shows the resulting error message that the user would see when they have broken the Category validation rule:
Error Message for Category Rule

Error Message Showing Validation Text

Using Field Properties to Ensure Data Integrity

Another way to ensure data integrity is by setting field properties such as Field Size, Format, and Required.

Field Size can be set to hold a specific number of characters, up to as many as 255 for text fields. If you were using a text field to hold the two letter state postal abbreviation, the field size could be set to 2 to ensure that no one enters a full state name into this field.
The Format field property can be set to display text or numbers in a standardized way. For example, text can be set to show as all uppercase and numbers can be set to show scientific numbers, percentages, or decimals.
Set the Required property to Yes if you want the person entering data to be required to enter something in the field. Choose No if the person entering data is allowed to leave the field blank.

These are just some ways Access helps you ensure that data being entered into your database is valid.

Data Validation

Data Types and Validation Rules

Data Validation Error Message

Data validation is accomplished by setting data validation rules and other field properties for various fields.

Setting Data Validation Rules

In Design View, highlight the field that requires a validation rule.
In the Field Properties section at the bottom half of the window, set your validation rule using the Expression Builder. The expression builder offers common syntax to set up a data validation rule.

Use Expression Builder to Enter Validation Rule

Validation Rule for Category Field

Validation rules work most easily with numeric fields. Rules for text fields require you to enclose each acceptable value inside its own quotation marks, separating them with Or, as seen above.

Validation Text

Validation Text for Category Field

The following image shows the resulting error message that the user would see when they have broken the Category validation rule:
Error Message for Category Rule

Error Message Showing Validation Text

Using Field Properties to Ensure Data Integrity

Another way to ensure data integrity is by setting field properties such as Field Size, Format, and Required.

Field Size can be set to hold a specific number of characters, up to as many as 255 for text fields. If you were using a text field to hold the two letter state postal abbreviation, the field size could be set to 2 to ensure that no one enters a full state name into this field.
The Format field property can be set to display text or numbers in a standardized way. For example, text can be set to show as all uppercase and numbers can be set to show scientific numbers, percentages, or decimals.
Set the Required property to Yes if you want the person entering data to be required to enter something in the field. Choose No if the person entering data is allowed to leave the field blank.

These are just some ways Access helps you ensure that data being entered into your database is valid.

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.

Open the database, and add records using the new record navigation button
Add a record using the New Record command in the Ribbon
Edit a record using the Find and Replace command.
Copy and Paste a record in one of the tables
Set a validation rule and validation text for a field in one of your tables. Then, break the rule when entering data and see the resulting message.