ComputerBusiness Applications Instructor: Greg Shaw

CGS 2100

Microsoft Access - Tutorial5, Session 5.3

“Creating Advanced Queries and Enhancing Table Design”

Creating a “Lookup Wizard” Field

When we set the Data Type property for a field to a “Lookup Wizard” field, a list arrow will appear in that field when it is selected in Datasheet View. Click the list arrow to see a list of possible values for that field, and click one of the values to store it in the field. You can set it up so that when you select a value from the list, a related value will be stored in the field

Lookup Wizard fields save typing and eliminate mistakes

In the example in Session 5.3, we change the data type of the CustomerID field (of the Contract table)from a text field to a Lookup Wizard field. When we click the arrow, a list of customer names and customer IDs appears. Now we can select a customer by name and have the related ID number stored in the field

  1. Open a table in Design View
  1. In the Data Type column of the field that will be the Lookup Wizard field, click the list arrow and choose Lookup Wizard...

Note that Access will not allow you to change the data type of a field if that field is being used to establish a relationship between two tables (e.g. the CustomerID field is the primary key in the Customer table and the foreignkey in the related Contracts table). If this is the case, you will need to delete the relationship first and then reestablish it

  1. The first step is to specify whether the list of lookup values will come from a table/query or whether you will type them in yourself. Select one and click Next

If you chose the second option - to type in the values yourself -, you will enter the number of columns and the values for those columns. If you chose the first option, you select the table and the field which contains the lookup values. Let’s assume the (first) option was chosen.

  1. Choose the table/query from which the lookup column will come and click Next
  1. Choose the field(s) that will appear in the lookup column and click Next
  1. Specify the sort order (ascending or descending) for the columns that will appear in the lookup column and click Next
  1. If necessary, resize the widths of the fields that will display in the lookup column and click Next
  1. Specify which of the fields chosen in step 5 contains the actual values to be stored in the table. Click Next
  1. Specify the label for your lookup column and click Finish

Input Masks

  • An input mask is a predefined format used to enter and display data in a field. When a field value is entered, Access will make it conform to the input mask
  • Literal display characters are special characters in the input mask that automatically appear at certain positions in the field, like the dashes in a social security number. The user doesn’t type the literal display characters when entering the field value
  • Placeholders, as the name implies, are characters that appear in the input mask but are replaced by the characters the user enters
  • E.g. If we want the data in a Phone Number field to appear like this: (800)555-1212, the parentheses and hyphen would be literal display characters and there would be 10 placeholders for the numeric digits
  • We can create an input mask using the Input Mask Wizard, or enter the mask ourselves in the Input Maskproperty in Design View

Using the Input Mask Wizard

  1. In Design View, click in the Field Name text box of the field for which you want to create the mask
  1. Click in the Input Mask text box in the Field Properties pane, and then click the Build button [...] that will appear
  1. Select one of the predefined masks from the list at the left
  1. To preview how the data will look, type a sample field value in the Try It text box, and then click Next
  1. The next step gives you the option of editing the mask and/or selecting a different placeholder character. Click Next when done
  1. Choose whether you want to store the data with the mask characters included or not, and then click Finish. (The data will always be displayedaccording to the mask, either way)

Because the Input Mask property has been updated, a Property Update Options button will appear to the left of the Input Mask text box. Click the list arrow and choose the objects (tables, forms, reports) in which you want the field values to be automatically updated to conform to the new mask. This is known as property propagation. Property propagation always occurs automatically with queries.

Creating Your Own Input Maskor Editing an Existing One (optional)

  1. In Design View, click in the Field Name text box of the field for which you want to create the mask
  1. Click in the Input Mask text box in the Field Properties pane, and enter the input mask, including placeholders and special characters

For more information, see the table of special characters used to define the mask on page AC 237 (and consult Microsoft Access Online Help)

Identifying Object Dependencies

  • An object dependency exists between two objects when a change in the properties of a field in one object affects the properties of a field in another
  • Here are some examples
  • Because the Customer and Contracttables have a one-to-many relationship, a dependency exists between them
  • Because the Contracttable uses two fields from the CustomersByName query in a Lookup Wizard field, the Contract table depends on that query
  • Any form, query, or report that uses fields from a given table is dependent on that table
  • Because large databases may contain hundreds of objects, it is a good idea to see what dependencies exist among the objects before deleting or modifying any of them
  • To see all the objects that depend on a given object:
  1. Open the object
  2. Database Tools | Show/Hide | Object Dependencies

(theObject Dependencies Task Pane will open)

  1. Make sure the Objects that depend on meoption button is selected
  • Click the Objects that I depend onoption button to see which objects the open object depends on
  • To view multiple levels of dependencies (e.g. Suppose table A depends on table B. What objects does table B depend on?) click the [+] button to the left on any object in the dependency hierarchy in the task pane
  • Click the Things that cause dependencies link at the bottom of the pane for more information

DefiningFieldData Validation Rules

  • Field Validation Rules prevent illegal or meaningless data from being entered into a field of a table
  • In Design View, the validation rule for a given field is entered in the Validation Rule text box for that field
  • In the Validation Text text box, we enter a message that Access will display if the rule is violated
  • Field validation rules are similar to query criteria (see table below for examples)

ValidationRule setting / ValidationText setting
>0 / Please enter a nonzero value
0 or >100 / Value must be either 0 or over 100
“USA” or “Canada” / Value must be either USA or Canada
<#1/1/2008# / Enter a date before 2008
>=#1/1/2008#and
<#1/1/2009# / Date must be in 2008
Between #1/1/2008#and
#12/31/2008# / Date must be in 2008
In (“Fall”, “Spring”,
“Summer”) / Semester must be Fall, Spring, or
Summer
Like “Z????” / Value must be 5 characters
long, beginning with Z
Like “A*” / value must begin with A

Setting Table Data Validation Rules

  • When a validation rule involves more than one field, we cannot use a field validation rule. Instead, we use a table validation rule
  • For example, the Contract table has fields to store the signing date (Signing Date) and starting date (Start Date) for each contract. Naturally, the signing date must come before the starting date

Since this rule involves two fields, we must set a table validation rule

  1. Open the table in Design View
  1. Table Tools | Design | Show/Hide | Property Sheet
  1. In the Validation Ruletext box of the Property Sheet, type the condition, e.g.

[Signing Date] < [Start Date]

Field names must be enclosed in square brackets ([])

  1. Enter an appropriate error message (e.g. “The signing date must be earlier than the starting date”) in the Validation Text text box
  1. Close the Property Sheet and save the table

Working with Memo Fields

  • Memo fields are used for long comments and explanations
  • Memo fields can hold 65,536 characters whereas text fields can hold only 255
  • To view/edit the contents of a Memo field
  1. Click anywhere in the field in Datasheet view
  2. Hold down the [Shift] key and press [F2] to view/edit the entire contents in a Zoom box
  • Formatting a Memo field
  1. In Design View, click anywhere in the row for the Memo field
  2. In the Text Format text box in the Properties pane, click the list arrow and choose Rich Text from the list (The default for memo fields is Plain Text)
  3. Switch to Datasheet View and use any of the formatting commands in the Font group on the Home tab