Home

Getting Assistance With Data Entry

 

Introduction to Built-In Functions

 

Character Retrieval

In order to create expressions that complete a database as we saw in previous lessons, you can use various functions available from Microsoft Access. These are referred to as built-in functions.

The Chr() function is used to retrieve a character based on an ASCII character number passed to the function. It could be used to convert a number to a character. It could also be used to break a line in a long expression. The syntax of this function is:

Chr(Number)

Based on the table of ASCII characters, a call as Chr(65) would produce the letter A. Not all ASCII characters produce a known letter. For example, when Chr(10) is used in a string, it creates a "new line".

The Input Box

Although most of the user's data entry will be performed on fields positioned on a form, you can display a special form to the user and expect a value. This form (actually a dialog box) is called an input box. To support this, Microsoft Access provides a function called InputBox. The basic syntax of the InputBox() function is:

InputBox(prompt)

To use an input box, you can access the event of a control in the Property Sheet and type the code as we will see. As an alternative, after accessing the event of the control, click its ellipsis button, which would display the Expression Builder. In the left list, expand the Functions node and expand Built-In Functions. In the middle list, click InputBox and notice its syntx in the bottom part:

Expression Builder

The required argument passed to the InputBox() function is a message that would be displayed to the user. When the input box comes up, a form with a message and a text box would display to the user.

To display an input box to the user, place a control, such as a Button, to a form. Then, access the Property Sheet for the control. In the Property Sheet, use an appropriate field, such as On Click from the Events tab, and assign the function to the field. An example would be:

=InputBox("Enter the Radius of the Circle")

Message Box

When the user clicks the button, an input box would display:

When an input box comes up, it does not display a caption in the title bar. If you want, you can display a title of your choice. To do this, pass a second argument to the InputBox() function. Here is an example:

=InputBox("Enter the Radius of the Circle","Geometric Figures")

Input Box

This would produce:

When an input box comes up and you present a request, the user may not know what type of value you are expecting. To guide the user, you can provide a sample or default value. This value would display in the text box and it would be selected so the user can type to replace it.

To provide a default value to the input box, you can pass a third argument to the InputBox() function. Here is an example:

=InputBox("Enter the Radius of the Circle","Geometric Figures",10.5)

When the input box is called from this function, it would display as follows:

Message Box

The Message Box

 

Introduction

A message box is a Windows (operating system)-created form used to display some information to the user. As opposed to a regular form, the user cannot type anything on the box. There are usually two ways you can use a message box: you can simply display a message to, or request an answer from, the user.

Practical Learning: Introducing the Message Box

  1. Start Microsoft Access
  2. To create a new database, in the File Name, replace the contents of the text box with Exercise1
  3. Click Create
  4. Close the default table without saving it

Creating a Message Box

To create a message box, you have various options.

To create a message box using a macro, after starting a new macro:

  • In the left window, click the arrow of the combo box and select MessageBox
  • In the right window, expand Actions and expand User Interface Commands. Drag MessageBox to the left window

Message Box

To manually create a message box, use a function called MsgBox. If you only want to display a message, the syntax you would use is:

MsgBox(Message To Display)

This function takes only one required argument, the message to display. If you are creating the message box using a macr, type the message in the Message text box. If you are manually typing the code, pass it to the parentheses of the function. The message can be passed between double-quotes.

To display a message to the user, place a command-based control, such as a Button, to a form. Then, access the Property Sheet for the control. In the Events tab of the Properties, click On Click and assign the function to the field. An example would be:

=MsgBox("Remember to submit your time sheet")

Message Box

When the user clicks the button, a message box would come up:

If you want to display the message box on various lines, edit the string to include a call to Chr(10). Here is an example:

MsgBox("Remember to submit your time sheet" + Chr(10) 
       "Only time sheets received on time will be honored", )

This would produce:

The message to display can also come from another control on the form. For example, you can retrieve the value of a control and pass it as argument to the MsgBox() function.

The simplest message box displays only a message to the user, with one button marked OK. If you want, you can display more than one button on the message box. To support this, you can bed the following syntax of the MsgBox() function:

MsgBox(Message to display, Options)

The second argument allows you to specify the button(s) to display. To do this, pass a constant integer as argument from the following table:

Value Buttons
0
1
2
3
4
5

Here is an example:

=MsgBox("Do you want to submit your time sheet?",4)

Message Box

This would produce:

If you provide a value other than those in the list, the message box would display only the OK button.

Besides displaying a button, the second argument is also used to display an icon. To get an icon, you add one of the following values:

Value Icon Suited when
16 Warning Warning the user of an action performed on the database
32 Asking a question that expects a Yes or No, or a Yes, No, or Cancel answer
48 Exclamation A critical situation or error has occurred. This icon is appropriate when informing the user of a termination or deniability of an action
64 Information Informing the user of a non-critical situation

To use one of these icons, add (a simple addition) its value to that of the desired button or combination of buttons from the previous table. Here is an example created with

=MsgBox("Do you want to submit your time sheet?", 4 + 32)

This is the same as:

=MsgBox("Do you want to submit your time sheet?", 4 + 32)

Message Box

This would produce:

When the buttons of a message box display, if the message box has more than one button, one of them has a thick border. This button is referred to as the default button. If the user presses Enter, such a button would be activated. Besides selecting the buttons and displaying an icon, the second argument can also be used to specify what button would have focus, that is, which one would have a thick border and would be applied if the user presses Enter. The default button is specified using one of the following values:

Value If the message box has more than one button, the default button would be
0 The first button
256 The second button
512 The third button
768 The fourth button

To specify this option, add the number to the second argument.

When a message box comes up, it displays a title as Microsoft Office Access. If you want, you can specify your own title. To support this, you can pass a third argument to the MsgBox() function. This third argument is referred to as the caption. The syntax to use is:

MsgBox(Message to display, Options, Caption)

Pass the third argument as a string, for example in double-quotes. Here is an example:

=MsgBox("Do you want to submit your time sheet?",
	4 + 64,
	"Georgetown Cleaning Services")

This would produce:

Because MsgBox is a function, you can retrieve the value it returns and use it as you see fit. The value this function returns corresponds to the button the user clicked on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox() function can return one of the following values:

If the button(s) displayed was(were) And if the user clicked The MsgBox() function returned
1
1
2
3
4
5
6
7
2
6
7
4
2
   

Practical Learning: Creating Message Boxes

The purpose of this exercise is to let you monitor the sequence of events of a form (whiat evnt comes after which one?)

  1. On the Ribbon, click Create
  2. In the Macros & Code section, click Macro Macro
  3. Click the arrow of the combo box and select MessageBox
  4. In the Message text box, type A record has been accessed
  5. In the Type combo box, select Information
  6. In the Title text box, type Microsoft Access Form Events

    Message Box
  7. Close the macro window
  8. When asked whether you want to save, click Yes
  9. Set the name to mcrCurrent and press Enter
  10. In the same way, create new macros as follows (use the same Type = Information and the same title = Microsoft Access Form Events for all of them):
     
    Message Name
    The form is loading to occupy the computer memory. mcrLoad
    The form is opening. mcrOpen
    The form is closing. mcrClose
    The form is resizing itself to signal its borders. mcrResize
    The form has been activated and is now the front runner. mcrActivate
    The form is either being closed or being sent behind another window. mcrDeactivate
    The form is unloading to be removed from the computer memory. mcrUnload
  11. On the Ribbon, click Create
  12. In the Forms section, click Form Design
  13. Double-click the button at the intersection of the rulers
  14. In the Property Sheet, click Event
  15. Click On Current and type mcrCurrent
  16. Click On Load and type mcrLoad
  17. Click On Open and type mcrOpen
  18. Click On Close and type mcrClose
  19. Click On Resize and type mcrResize
  20. Click On Activate and type mcrActivate
  21. Click On Deactivate and type mcrDeactivate
  22. Click On Unload and type mcrUnload
  23. Close the form
  24. When asked whether you want to save, click Yes
  25. Set the name to FormEvents and click OK
  26. In the Navigation Pane, double-click the FormEvents form
  27. Watch the sequence of message boxes and click OK on each
  28. Close the form
 
   
 

Introduction to Data Types

 

Visually Specifying a Data Type

A data type represents the kind of information that a particular field should, would, or must hold. Every field in your database objects (tables, forms, and reports) should have the right type and you should select the most appropriate of them. This helps both you and the user who performs data entry. Furthermore, a good design will cause you less headache when creating calculated expressions.

Microsoft Access can assist you with allowing or excluding categories of data in database fields. Based on this, you can make sure that the user would not type a contract's date in a project's contact name. To manage different types of information you enter in a database, Microsoft Access helps you organize data by categories.

Practical LearningPractical Learning: Introducing Data Entry Assistance

  1. To create a new database, in the File Name, replace the contents of the text box with Real Estate1
  2. Click Create
  3. Double-click ID, type PropertyID, and press Enter
  4. Right-click the Table1 tab and click Save
  5. Set the name to Properties and click OK
  6. Right-click the Properties tab and click Design View

Setting the Data Type of a Field

To make your database efficient, in some circumstances, or depending on the project (or customer), you should exercise as much control as possible on data entry. This aspect is mostly controlled at two levels: tables and forms.

We know that, when creating a table in Datasheet View, to create a column, you can click Click to Add, select a field type from the list:

Field Templates

Type a name for the column. After creating a column, if you want to change it, on the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select from the list.

Besides the Datasheet View, you can use the Design View of a table to specify the type of value that a field can hold. To do this, after specifying the column name, click the arrow of the corresponding box in the Data Type column and select the desired type:

Data Type

After a field has been configured on a table, when that field is used in a form, it would respect the formatting that was done on the table. Even if you create an unbound field on a form, you can still control how it accepts or rejects data. Therefore, data entry can also be configured at the form level. To provide this functionality, a form provides the same properties as the lower portion of the table's Design View.

Practical Learning: Introducing Data Types

  1. Click under PropertyID and add the following fields
     
    Field Name
    Property #
    Address
    City
    State
    ZIP Code
    Bedrooms
    Bathrooms
    Year Built
    Market Value
  2. Notice that the default data type for all the fields is Text
  3. Save the table

Data Type Properties

To further control how data is entered and/or how it is displayed on a field, both the table in Design View and the Property Sheet in Form Design provide special fields.

Because the list of data types may appear short to you, each data type provides some configuration that allows you to control how data for that field either would be selectively entered or would display. To do this in the Datasheet View of a table, click any field under the column header. On the Ribbon, click Fields. Use the options in the Properties, the Formatting, and the Field Validation tabs:

Fields

To control the properties of a table in the Design View, after selecting a data type in the Data Type column, in the lower section of the table, you can format or further configure the field. The lower part of the table Design View is made of two sections: the property pages on the lower left and the properties help section on the lower right:

Table Properties

The kind of Data Type you set for a field in the upper section controls what displays in the lower section of the view. The General property page controls the features of the selected data type. The options in the General property page depend on the data type that was specified:

Table Properties

The Properties of a Table

The Lookup tab allows you to specify a feature that is particular to the specific data type that was specified. For some data types, it would display a combo box. For some other data types, the Lookup property page would be empty.

Routine Assistance With Data Entry

 

A Default Value for a Column

Data entry consists of typing values in data fields or selecting values from bound controls. Some records happen to have the same value for a particular field, or most records hold a common value for a certain field. When designing a table, you can assign the most commonly used value to such a field so the user would not have to type it. The user would change the value only if it is different from the usual. For example, when creating a database for a small company, all employees may have the same telephone number but with individual extensions. When a new record is being entered, the value would be set already for the field.

To specify a regularly used value for a field:

  •  While the table is displaying in the Datasheet View, on the Ribbon, click Fields. On the Properties section, click Default View. This would open the Expression Builder with = in the main text box. If you know the constant value you want to use, type it. Then click OK
  • If the table is displaying in the Design View, in the top section, click the field. In the lower section of the window, click Default Value and type the desired value

The default value should be appropriately typed:

  • If the field is text-based, you can type the default text included in double-quotes. Examples are "M", "Virginia", "(301) 122-4738"
  • If the field is numeric, you can type the number
  • If the field is date-based and you want to specify a constant (fixed), type it between two # signs. An example would be #2/5/1998#

Instead of a constant value, you can use a value that depends on a built-in function. If you are using the Expression Builder, in the left list, expand Functions and expand Built-In Function. In the middle list, click the category the function belongs to. From the right list, double-click the desired function, then configure the function appropriately.

Practical Learning: Setting Default Values

  1. In the top section, click State
  2. In the bottom section, click Default Value and type MD
  3. Save the table
  4. To switch it to Datasheet View, in the lower-right section, click the Datasheet View button Datasheet View

Validation Rule

A validation rule is a condition that the value entered in a field must meet in order to be valid. To create a validation rule:

  • If the table is displaying in the Datasheet View, on the Ribbon, click Fields. In the Field Validation section, click Validation and click Field Validation Rule

Validation

  • If the table is displaying in the Design View, click the field. In the lower section of the window, click Validation Rule and click its ellipsis button

In both cases, the action would open the Expression Builder where you can create a rule.

Validation Text

When the condition in the Validation Rule field is not respected, you can display a message box to let the user know. To create the message:

  • If the table is displaying in the Datasheet View, on the Ribbon, click Fields. In the Field Validation section, click Validation and click Field Validation Message. This would open the Enter Validation Message where you can type the desired message:

Enter Validation Message

  • If the table is displaying in the Design View, click the field. In the lower section of the window, click Validation Text and type the desired message

Value Required for a Field

If you think that there must be an entry for a particular field for each record, you can require it from the user. When performing data entry, after creating a record, the user cannot move to the next record if a value for that field has not been provided. You can specify that the value of a field is required when creating a table, whether you are working in the Datasheet View or the Design View of the table:

  • If you are creating or configuring a table in the Datasheet View, to specify that the values of a field are required, click any cell of the column. On the Ribbon, click Fields. In the Field Validation section, click the Required check box. By default, the Required check box is not checked
  • If you are creating or configuring a table in the Design View, in the top section, click the field. In the bottom section, click the arrow of the Required field and set it to Yes. Its default value is No

Practical Learning:  Specifying Required Fields

  1. On the table, click Property # and, on the Ribbon, click the Fields tab
  2. In the Field Validation section, click the Required check box
  3. To change the view, in the Views section of the Ribbon, click Design View Design View
  4. In the upper section of the table, click Market Value
  5. In the lower section of the table, double-click the Required field to set its value to Yes
  6. Save the table

Indexed Fields

When data is entered in fields, it is possible to have the same value for a field in different records, such as two people who live in the same state. This is considered as a duplicate value. In some other cases, this may not be acceptable. For example, you may not want two employees to have the same employee number. 

You can specify the indexation of a field when creating a table either in the Datasheet View or the Design View of the table. If you are creating or configuring a table in the Datasheet View, to specify the indexation of a field, click any cell of the column. Then, on the Ribbon, click Fields. In the Field Validation section, click the check box of the Unique option

If you are creating or configuring a table in the Design View, in the top section, click the field. In the bottom section, click the arrow of the Indexed property. This characteristic can be set using one of 3 values:

  • If set to No (its default), no duplicate value checking will be done
  • If you want the database engine to check for duplicate but not necessary take any action, set this property to Yes (Duplicates OK)
  • If you do not want a duplicate value of the same field in different records, set the field's Indexed property to Yes (No Duplicates)

Practical LearningPractical Learning:  Controlling Indexed Fields

  1. In the upper section, click Property #
  2. In the lower section, click Indexed to display its combo box. Click the arrow of the Indexed combo box and select Yes (No Duplicates)
  1. To change the view, in the Views section of the Design tab of the Ribbon, click Datasheet View Datasheet View
  2. When asked whether you want to save the table, click Yes

Data Entry With a Lookup Field

 

Introduction

So far, to enter a string in a table, we assumed that the user would type it. In some cases, this is fine. Sometimes, you may want to limit the number of entries that a user can enter for a field. For example, if you create a list of students and you want the user to specify their gender, if you leave it up to the person doing data entry, you may end up with entries such as W, Girl, Female, G, Woman, or F. Although all these could indicate that the records designate a female, when performing data entry, these entries would create confusion and unpredictable results. Fortunately, Microsoft Access provides various solutions to this type of problem.

On a table, you can create a list of values that the user would select for a field, instead of typing the value. Such a field is referred to as lookup. You can create the field whether in the Datasheet View or in the Design View.

The Lookup Field as a Combo Box

A lookup field appears as a combo box. To use it, the user can click the arrow and select from the list. There are various ways you can create a lookup field. In the Datasheet View:

  • Click Click to Add. In the window that appears, click Lookup & Relationship

Lookup & Relationship 

  • Click a box under Click to Add. On the Ribbon, click Fields. In the Add & Delete section, click More Fields. In the window that appears, click Lookup & Relationship

In both cases, this would start the Lookup Wizard. In the first page of the wizard, you must select the second radio button. In the second page of the wizard, you will be asked to provide a value for each item that will eventually show in the list. To do that, you would click under Col1, type a value, press and down arrow key, and continue populating the list. When the list is complete, you can click Next, give it a name, and click Finish.

Microsoft Access provides a few configured lookup fields you can add to a table. To use one of them, display the table in Datasheet View. Click a cell under Click to Add. On the Ribbon, click Fields. In the Add & Delete section, click More Fields. In the list that appears, click Category, Priority, or Status. You can keep the list or change it. To change the list, click the arrow of the combo box. Under the list, a button would appear:

Table Lookup Field

You can then click that button. An Edit List Item dialog box would come up:

To change an item, select it in the list and replace it with a new string. To add a new item, click under the other items or click the end of the last item, press Enter, and type the desired item. The Default Value combo box allows you to specify what item would display as the default.

To create a lookup field in the Design View of a table, after specifying a name for the column, set its Data Type to Lookup Wizard... This would start the Lookup Wizard. You use exactly as we described the Lookup Column from the Datasheet View.

Practical Learning: Setting a Preset List Of Items

  1. The Properties table should still be opened in Datasheet View.
    On the table, click the cell under Property #
  2. On the Ribbon, click Fields
  3. In the Add & Delete section, click More Fields and click Lookup & Relationship
  4. In the first page of the wizard, click the second radio button: I will type in the values that I want
     
  5. Click Next
  6. In the second page of the wizard, click the first empty field under Col1 and type Single Family
  7. Press the down-arrow key and type Townhouse
  8. Press the down-arrow key and type Condominium
  9. Press the down-arrow key and type Unknown
     
  10. Click Next
  11. For the column label, type Property Type and click Finish
  12. On the table, click the cell under Property Type
  13. In the Add & Delete section of the Fields tab of the Ribbon, click More Fields. Scroll down. In the Quick Start section, click Category
  14. While the new column is still selected, in the Properties section of the Ribbon, click Name & Caption Name & Caption
  15. Change the Name to Condition
  16. Change the Caption to Condition
  17. In the Description text box, type Possible Values are Excellent, Good Condition, Needs Some Repair, Bad Shape
     
    Enter Field Properties
  18. Click OK
  19. Click the arrow under the Condition column header and click the button that appears
     
  20. Press Ctrl + A to select everything in the list and press Delete
  21. Type Unknown and press Enter
  22. Complete the list with the following items Excellent, Good Condition, Needs Some Repair, Bad Shape
  23. Click the arrow of the Default Value combo box and select Unknown
     
  24. Click OK
  25. Enter a few records

Lesson Summary

  

Exercises 

 

World Statistics

  1. Open the World Statistics1 database
  2. Open the Countries table in Design View
  3. Use the Lookup Wizard on the GovernmentType column to create a list of the government types. The options will be Republic, Islamic Republic, Monarchy, Communist State, Other, and Unknown. Set the Default Value to "Unknown"
  4. Save and close the table

US Senate

  1. Open the US Senate1 database 
  2. Open the Senators table
  3. Use the Lookup Wizard on the SeatingStatus column to create a list of the status. The options will be Active, Retired, Removed, and Deceased
  4. Use the Lookup Wizard on the Gender column to create a list of the genders. The options will be Male, Female, and Unknown. Set the Default Value to "Unknown"
  5. Save and close the table
 
 
   
 

Previous Copyright © 2010-2012 FunctionX Next