Lesson 14

Lesson 15

Creating a Queries in Microsoft Access.

Basic questions

1.     What is the Queries using for?

2.     How ways can be used for creating the queries?

3.     How users can create a Query in Design View?

4.     How users can open and run Query?

5.     How users can modify the Query Results?

6.     How users can use Comparison Operators and Boolean Operators in the Queries?

7.     How users can create a Calculated Field in the Queries?

Using Queries and RecordSets

Query is the way of requesting selected information from a table. When you run a query, a selected set of records, called the Record set, appears. You can then edit or print the Record set.

A query is a question about information in a table or tables. You can use queries to view and analyze data or as a basis for forms and reports. Queries are commonly used to display fields from related tables and enable you to control not only what records display, but also what fields display. For example, you may want a list of the contacts and telephone numbers for a particular region to give to one of your sales representatives. By creating what is called a select query, you can limit the records to the appropriate region and limit the fields to the contact name and telephone number [19].

A query does not contain data. Rather, it is a set of instructions. Access uses these instructions to select and display the appropriate records in a table. Therefore, when you add data to a table, you do not have to update the query. The query always considers all the data in the table. If the new records meet the conditions of the query, they will be included when the query results appear.

When you open or run a query, a RecordSet appears. A RecordSet contains all the fields and records that meet the conditions of the query. While the RecordSet is not a table, it can be used under certain conditions to add and edit records in tables.

Using the Simple Query Wizard

Access provides a Simple Query Wizard that guides you through the steps to create a basic select query. When you use the Simple Query Wizard, you select the table you want to use and the fields you want to display in the query. In the last step, you name the query and then choose whether or not to display the results (the RecordSet) of the query or if you want to go to Design view to change the design of the query. To create query using the Simple Query Wizard [19]:

1.     Select the New button on the Database window toolbar. The New Query dialog box opens.

2.     Select Simple Query Wizard. Simple Query Wizard is selected and a description of the Simple Query Wizard appears in the left panel of the New Query dialog box.

3.     Select OK. The New Query dialog box closes and the Simple Query Wizard opens.

4.     Select the Tables/Queries list. A list of available tables and queries appears.

5.     Select the table or query you want to use as the basis for the query (Figure 325).

6.     Use the single right arrow to add the field(s) you want to display in the query from the Available Fields list box.

7.     Select Next. The second page of the Simple Query Wizard appears.

8.     Type a name for the query. The name appears in the What title do you want for your query? text box.

9.     Select Finish. The Simple Query Wizard closes, the query runs, and the RecordSet appears in Datasheet view.

Figure 325. Selecting the table or query when the Simple Query Wizard used

Creating a Query in Design View

You can create a query in Design view. This option gives you the most flexibility in designing a select query. It allows you to add criteria to select records and sort the resulting RecordSet.

When you create a query in Design view, you use the design grid to set up the query (Figure 326). The field list of the table you want to use in the query appears in the top pane of Design view. You add the fields you want to use in the query to the design grid in the bottom pane of Design view, along with any sort orders or criteria for selecting records. To create a Query in Design view [19]:

Figure 326. A query in the Design view

1.     Click the New button on the Database window toolbar. The New Query dialog box opens.

2.     Select Design View. Design View is selected and a description of Design view appears in the left panel of the New Query dialog box.

3.     Select OK. The New Query dialog box closes, Design view appears, and the Show Table dialog box opens.

4.     Select a table and add it to the query using mouse double click.

5.     Select Close to close the Show Table dialog box. The Show Table dialog box closes.

6.     Select the first field you want to add to the query.

7.     Drag the field to the desired column in the Field row in the design grid. A small box indicating the position of the field moves as you drag and then the field and table names appear in the design grid.

8.     Add other fields to the query as desired.

9.     Click the Save button. The Save As dialog box opens with the insertion point in the Query Name text box. Type a name for the query. Select OK. The Save As dialog box closes and the query is saved.

Opening a Query

When you open a query, Access runs the instructions in the query and displays the resulting RecordSet in Datasheet view. If you have added records since the last time you ran a particular query, the new records will appear if they meet the query criteria. To open query [19]:

·        Select the name of the query you want to open. The query name is selected.

·        Select the Open button on the Database window toolbar. The query runs and the RecordSet appears in Datasheet view.

Adding a Table to a Query

You can use more than one table in a query. The tables must be joined in order for the query to give accurate results. If they are not joined, you can create a join in the top pane of Design view.

When you add more than one table, the field lists appear in the top pane of Design view. If the tables are already related, join lines appear automatically.

Once you have added a table to the query, you can then add fields from the field list to the design grid. The second row in the design grid is the Table row, which indicates from which table the field originates.

When you open Design view to design a new query, the Show Table dialog box opens automatically so that you can add multiple tables. However, when you open Design view to modify an existing query design (i.e., to add a table), you must open the Show Table dialog box manually. For this, click the Show Table button on the Query Design toolbar. The Show Table dialog box opens. Select the name of the table you want to add to the query and press the Add button.

Running a Query

You can run a query directly from Design view to display the RecordSet. For this, Click the Run button on the Query Design toolbar. This option is useful if you want to test the design of the query to see if the resulting RecordSet contains the information you need.

Running a query does not save the design of the query. If you close the RecordSet after running a query, a message box opens, asking if you want to save the changes.

Modifying Query Results

Sorting a Query

When you run a query, the records in the RecordSet appear in the same order in which they appear in the table. You can sort the records by either sorting the RecordSet or assigning a sort order in the query design. You can sort the RecordSet just as you would sort a table. However, you must do this every time you run the query. If you assign the sort order in the query design, Access performs the sort automatically each time you run the query. To set sort order (Figure 327) [19]:

Figure 327. Adding a sort options to the Query

1.     Select the Sort row under the field by which you want to sort. The insertion point appears in the appropriate Sort row field and an arrow appears to the right of the field.

2.     Select the Sort list. A list of sort options appears.

3.     Select Ascending or Descending.

Adding Criteria to a Query

You can enter data in the Criteria row of the query design grid to restrict the number of records that are returned in a query.

To select records that meet a single value, you enter the value in the Criteria row under the appropriate field. Access automatically inserts quotation marks (” “) around alphanumeric entries and number symbols (#) around date entries. If the entry is numeric, the number appears without quotation marks. When you run the query, only those records with values that match the criteria appear in the RecordSet (Figure 328).

Figure 328. Adding Criteria to a Query

Adding a Record Using a Query

You can use a query to update records in related tables. When you enter information into the join field for a primary table, Access automatically locates the corresponding information in the related table and enters it into the record. For example, the Orders and Customers tables are related. When you enter the customer ID number in the appropriate field in the Orders table, Access completes the customer name and address information automatically through the relationship with the Customers table.

You can always edit information in queries based on a single table. In queries based on related tables, Access must be able to determine the relationship type (i.e., one-to-one or one-to-many), or you cannot edit information. In queries with tables that have a one-to-one relationship, you can always edit data. In queries with tables that have a one-to-many relationship, you may not be able to edit data at times. For example, this can happen if the join field from the “many” table is not in the query, since Access cannot create new records in the “many” table. For example, Access cannot create new orders for a customer if the query is based on the Customers table unless the Customer ID field from the Orders table is included in the query.

Using Operators in Queries

Using Comparison Operators

You can enter criteria in the Criteria row of the query design grid in order to select specific records. The simplest criteria requires that records match a single value to be included in the RecordSet.

You can also use comparison operators to select a specific group of records in a table. For example, if you want to find all customers with credit limits less than $1000, or all customers with a contract date on or before January 1997, you can write an expression that defines the criteria using a combination of comparison operators and field values, such as <1000 or <=1/1/97. Comparison operators are symbols that represent conditions recognized by Access. The available comparison operators are [19]

 

 

You can use a comparison operator to compare a specified value with all the values in a field. When you run the query, only the records with values meeting the criteria you specify appear in the RecordSet. To use Comparison Operators (Figure 329):

Figure 329. Using Comparison Operators in the Query

1.     Select the Criteria row under the desired field. The insertion point appears in the Criteria row under the appropriate field.

2.     Type a comparison operator and the value for the criteria. Press [Enter]. The criteria is entered into the design grid.

Using an AND Condition

Many times, a query requires more than one condition to obtain the desired result. For example, if you want to find all customers in PA with sales to date over $10,000, you would need two conditions: State=PA and Sales to Date>10000. The records must meet both conditions in order to be included in the RecordSet. To combine two criteria in this way, you use the And logical operator.

You can use the And operator in a single field or in different fields (Figure 330). In a single field, you can use the And operator to find records that fall into a range. For example, to find customers whose contract dates fall between 9/1/99 and 9/30/99, you type both criteria on a single line in the Criteria row under the appropriate field (i.e., >=9/1/99 And <=9/30/99 in the Contract Date field) [19].

The And operator also allows you to impose conditions in two different fields. For example, to find customers in PA with sales to date over $10,000, you type each criterion on a single line in the Criteria row under the appropriate fields (i.e., =PA in the State/Province field and >10000 in the Sales to Date field).

Figure 330. Using an AND Condition in the Query

Using an OR Condition

Many times, a query requires more than one condition to obtain the desired result. For example, if you want to find all customers in PA or all customers with sales to date over $10,000, you would need two conditions: State=PA as well as Sales to Date>10000. The records only need to meet one of the conditions in order to be included in the RecordSet. To combine two criteria in this way, you use the Or logical operator.

You can use the Or operator in a single field or in different fields (Figure 331). In a single field, you type the criteria on two separate lines under the same field. In different fields, you type the criteria on two separate lines under the appropriate fields. For example, to find all customers with contract dates on or before 1/1/99 or credit limits above $3,000, you type <=1/1/99 in the Criteria row under the Contract Date field and >3000 in the or row under the Credit Limit field.

Figure 331. Using an OR Condition in the Query

Using a Wildcard Character

Wildcard characters are used in a query to find records when the criteria contains a pattern (such as all last names beginning with M) or is only partly known (such as the proper spelling – Kline or Klein). Wildcards take the place of one or several letters in a Text field or numbers in a Date/Time field.

The two most common wildcards are listed in the following table [19]:

 

Designing Advanced Queries

Setting Top Values in a Query

You can limit the results of a query so that only the highest or lowest values for a field appear in a RecordSet. For example, you can set the top values of a Quantity Sold field to 10 to find the top ten best selling products. You can limit the number of records to a specific number or a percentage of all records being queried (i.e. top 25%). The field for which you are setting the top or bottom values must be sorted. If the field is sorted in descending order (Z to A, 9 to 0), the top values will be found. If the field is sorted in ascending order (A to Z, 0 to 9), the bottom values will be found.

Creating a Calculated Field

You may want to use field values in a table to calculate new values, such as multiplying the value in the Participants field by the value in the Cost field to calculate the total cost (Figure 332). Access allows you to use expressions to calculate new field values. The expression can also include a single field, which has a value that needs to be adjusted, such as doubling a Cost field to calculate values for a price increase. In expressions, field names are enclosed in square brackets ([ ]); numbers are not. For example, to calculate a double in the amount of a course cost and display the results in a column you want to name Tuition Increase, you would enter Tuition Increase:2*[Cost] in the design grid. The colon is used to separate the columame from the expression [19].

You create calculated fields in queries. You can also use criteria to remove nonessential records, thereby allowing the query to run faster. The results can then be used to generate a report. To create calculated fields:

Figure 332. Creating a Calculated Field

1.     Select the Field row in the first blank column. The insertion point appears in the first blank Field row and an arrow appears to the right of the field.

2.     Type the new field name and a colon (:). Type the expression required to perform the calculation. Press [Enter]. The calculated field is entered into the design grid.

3.     Switch to Datasheet view to view the Query results.

Creating a Function Query

Access allows you to create a query that groups records by a selected field and applies a function that calculates values on other fields in the query according to your needs. For example, you can group records in a table by state and then select the Count function to find out how many customers (records) are in each state (field). You can also group by customer name (field) and calculate the Sum of each customer’s orders (record values). To use this features you need select the Totals command in the View menu.

There are several types of functions from which you can choose. The most commonly used functions are listed in the following table:

 

Creating a Parameter

If you want to run a query with different criteria each time, you can create a parameter query. A parameter query is a query that prompts the user for information when the query is run (Figure 333). Access then uses the information as the criteria and runs the query. The resulting RecordSet only includes those records that meet the criteria. This option allows you to avoid displaying the query in Design view each time you want to change the criteria.

You enter the text that will display in the prompt in the Criteria row under the appropriate field in the design grid, followed by a colon (:) and enclosed in square brackets ([ ]). You can set up a parameter query to prompt the user for more than one piece of information as well [19].

Figure 333. A query with the Parameter

Creating a Concatenation in a Query

Access allows you to combine two or more fields into one field. This process, known as concatenation, enables you to add field name text strings to one another. The text strings can follow each other with or without spaces. You can add other characters between the text strings if needed. For example, you can combine the individual City, State, and Postal Code fields into one field called Address. You can have the comma and space characters appear between the field text strings. This concatenation process can be performed by creating a query to combine two or more fields [19].

When typing expressions for concatenation, the first part of the expression defines the name of the new field. The second part of the expression defines the fields which are to be concatenated. These field names must be surrounded by brackets. The ampersand (&) appears between the field name brackets. Any additional characters that are to appear between the fields are surrounded by double quotes. For example, the expression Names: [First Name] &” – “&[Last Name] creates a new field called Names (Figure 334). The new field contains the first name and last name separated by a space, a hyphen, and a space.

Figure 334. Creating a Concatenation in a Query

Using Multiple Tables in a Query

There may be instances when you need to add more than one table or query to a query. In order to do this, you need to ensure that the field lists are joined to each other with a join line so that Access knows how to connect the information.

If tables in a query are not joined to one another, Access does not know which records are associated with which, so every combination of records between the two tables would appear in the query. Therefore, if each table had 50 records, the query’s results would contain 2500 records (50×50), thereby rendering useless results.

If you previously created a relationship between tables in the Relationship window, Access automatically displays join lines when you add related tables in query Design view. If you have not previously created relationships, Access automatically creates joins if tables were added to a query as long as the tables each have a field with the same or compatible data type and one of the join fields is a primary key. If tables added to the query do not include any fields that can be joined, you have to add one or more extra tables or queries to serve solely as a bridge between the tables containing the data you want to use.

Design of Fields on the Forms and Reports in Microsoft Access.

Basic questions

1.     What is the Forms and Reports? How it’s purposes?

2.     How user can create a Forms and Reports in Design View?

3.     How Accessories user can use to Form and Report Design?

4.     What are the Field List, the Toolbox and the Properties Window?

5.     How user can insert Controls to the Form and Report in Design View?

6.     How user can edit and move Controls on the Form and Report in Design View?

Fields Design Techniques

As a database is expanding, so are its objects. For example, many of the tables created by the Database Wizard lack some fields that otherwise would not suit a particular scenario. As we learned already, you can add new fields to a table. Since such new fields are not added to the corresponding forms or reports, you have to insert these fields in the appropriate objects. Even though the Form Wizard or the Report Wizard can be used to create quick objects, you still have the option of changing or improving their look by moving fields around and changing the sequence of fields’ navigation [23].

The modification and design of a form or a report is done in Design View. A form or report in Design View displays the same window controls we have used when performing data entry. This time, these controls are equipped with handles that allow you to move them anywhere on the object.

To perform your design, a form is equipped with two rulers, one horizontal on the top section of the form, and one vertical on the left section, to help you be more precise with dimensions. To assist with controls design, you can use a toolbox equipped with buttons, text boxes and other items to give a lot of flexibility to your application. You also have the originating table, the field list, on your screen in case you want to add an item that you did not include in the beginning. When you do not need any or both of these two items, you can hide them by clicking their buttons on the toolbar.

Form and Report Design Accessories

During form design, there are accessories you will display and dismiss at will. These are the Field List, the Toolbox, and the Properties window. All of these objects are modeless windows. Therefore, you can display one at a time or all of them at the same time and continue designing your form. The Field List is a small window that displays a list of items that are related to the form you are building. These items are from the source data that could be a table or a query.

The Field List

To display the Field List (Figure 351), on the Form Design toolbar, you can click the Field List button. When the Field List is displaying on your screen, the button appears clicked. To dismiss the Field List, click the same button. You can also close it by clicking its Close button. When the list is not displaying, its button looks “normal”.

You can also perform these operations of displaying and dismissing the Field List using the menu bar under View. You can resize the list as needed by dragging one of its borders or corners [23].

                  

Figure 351. The Field List (left) and The Toolbox (right)

The Toolbox

The Toolbox presents a list of some of the most common window controls, Some of these controls can be associated with fields of your Field List; some others can be configured to relate to another existing control on the form. Yet, another control can behave independently of anything else that is on the form.

To display the Toolbox (Figure 351), on the Form Design toolbar, click the Toolbox button. You can also close it using either the same button on the toolbar, or clicking its own Close button. You can also use the menu under View.

The Properties Window

Figure 352. The Properties Window

Made of five property pages, this window displays the characteristics associated with the object or the control that is selected on the form. To get the Properties window of the properties associated with a control, right-click that control and click Properties.

To display the Properties window for the form (Figure 352), double-click the button that is at the intersection of both rulers . You can also click the Properties button on the Form Design toolbar to toggle the Properties windows [23].

Forms, Reports, and Data Existing Fields

As mentioned already, on forms and reports, you can create fields that do not exist on tables or queries, as we have done so far in this lesson. Alternatively, when in the New Form or the New Report dialog box (Figure 344), you can select the object that holds the list of fields in the combo box.

If you had started the design of a form or report already, you can display its Properties window and select an object in the Record Source combo box (Figure 353).

Figure 353. Selecting an object in the Record Source combo box

Forms, Reports, and Queries Fields

If you want to use just a few fields from a table or an existing query, you can set it as the source of a form or report. Otherwise, you can create a new list of fields that are retrieved from a table or an existing query. To do this, you must build a query.

To build a new query that would serve as the source of a form or report, when in Design View, display the Properties window for the form or report and click the ellipsis button  of its Record Source. Then proceed as we learned to add or insert fields for a query. Once you have finished, you can close the query. It would become the source of data for the object [23].

If the form or report had already been created with fields that do not exist on the list, these fields on the form or report would become “orphans”. For example, suppose you create a form or report based on a Record Source such as Employees (Figure 354).

Because you can, suppose you don’t want to start the design of a new form but instead decide to change the Record Source of the form to something else like Customers (Figure 355).

Consequently, the fields that were created from the Employees set of record but that don’t exist on Customers would have lost their “ties”. When such a form or report displays, the “orphaned” controls would display #Name? Error (Figure 356).

Figure 354. A form based on a Record Source such as Employees

Figure 355. Mistake: changing the Record Source of the form to something else

Figure 356. Mistake: “orphaned” controls on the form

Form and Report Design: Controls Insertion

When designing a form or a report, one of the most usual actions you will perform consists of inserting items from the Field List to the form or the report. To add a field, you can drag it from the list to the desired section on the form or report. To drag many fields at the same time, first select them.

To select all items at the same time on the Field List, you can [23]:

·        Double-click the title bar of the field list

·        Click the item on one end of the list, press and hold Shift, and click the item on the other end. In fact, you can use this same process to select fields in a range.

To select fields at random, press and hold Ctrl, then click each one of the desired fields. If you had selected an item but want to remove it from the selection. While still holding Ctrl, you can click the undesired item.

After selecting items on the Field List, to add them to a form or report, you can drag one of them and drop it on the form or report.

Rulers and Dimensions

Since the rulers are dimensionally configured, there are divisions inside of the rulers to help you be more precise. Between two numbered dimensions, there are 7 marks that create 8 divisions. The mark in the middle, a little taller than the others, represents the middle of two dimensions. In this site, the middle division will be referred to as Ѕ. The first division on the right side of a number represents 1/8 of a dimension, the 2nd represents 2/8 = ј of a dimension, the 3rd represents 3/8, and that is why the 4th represents 4/8 = Ѕ. This is how we will refer to these dimensions.

Form and Report Design: Controls Selection

To manipulate controls on the form, you will regularly need to select them. When one control is selected, you can change only its characteristics. When many controls are selected, you can make a change that would impact all of them.

To select a control, you can just click it. A control that is selected displays 8 handles around it:

To select more than one control, at random, click one, then press and hold Shift. While holding Shift, click each of the desired controls. After selecting the controls, release Shift.

To select controls aligned vertically, click inside of the horizontal ruler above the highest positioned control: everything that would be touched by the fake line will be selected. In the same way, you can select controls that are horizontally aligned by clicking inside the vertical ruler.

To select many controls in the same area on the form, click next to one of them and draw a fake rectangle that covers each of the desired controls. All controls touched by the fake rectangle would be selected when you release the mouse [23].

To select all controls on the form, press Ctrl + A. You can also click inside of one of the rulers on one end and drag to the other end.

Form and Report Design: Controls Moving

Once a control is selected, as your mouse moves over a selected control, its pointer displays a different cursor. Two of these cursors can be used to move a control [23]:

 

Form and Report Design: Controls and Sections Sizing

Resizing an object allows you to change its height or its width. This can be applied to forms, reports, their sections, or the controls they are hosting. To physically change the width of a form or a report, position the mouse on its right border until the cursor turns into a vertical beam with a double-horizontal arrow. Then click and drag left or right until you are satisfied (Figure 357).

The height of a form or report is controlled by its sections. For this reason, each section controls its own height. The total heights of all sections constitute the height of the form. Based on this, to resize a form, you must actually resize one or some of its sections. To heighten a section on a form or a report, position the mouse on the lower portion of the section. For the headers or the Detail sections, that will be the upper border of the lower bar. For the lowest section, the mouse must be positioned on the lower border. The cursor turns into a narrow horizontal line with a vertical double-arrow (Figure 357) [23].

To change the width of the form or report and the height of the lowest section at the same time, you can position the mouse on the lower-right corner. The cursor would change into a small square with 4 arrows. You can then click and drag left, right, up, down, or diagonally (Figure 357).

         

Figure 357. A Sections Sizing in the Form design View

When the mouse moves over a selected control and reaches one of the handles, the mouse pointer displays a double-arrow cursor. The possible mouse pointers are:

 

 

Although these pointers can be used to resize one control, they can also be applied to a group of controls.

Form and Report Design: Controls Deletion

If you have added a control or it was created by the Form Wizard or the Report Wizard, you can delete it. You can also delete a group of controls in one step.

·        To remove a control from the form or report, first click it and press Delete. If you click a text box or a control that is accompanied by a label and delete it, its label is deleted also.

·        To remove a group of controls, select them and press Delete.

Tab Order

The controls on a form are aligned in the order you desire for their sequence. Unfortunately, when you add a control on the form that already has other controls, regardless of the section or area you place the new control, it is sequentially positioned at the end of the existing controls. If you don’t fix it, the data entry personnel could have a hard time figuring out how the sequence should be followed. When writing Visual Basic code, you will also find out that the sequence of navigation of controls on a form is very important.

The sequence of controls navigation is set using the Tab Order dialog box. The Tab Order dialog box is available when the form is opened in Design View. Once in Design View, either [23]:

·        on the menu bar, click View -> Tab Order;

·        right-click on the form and click Tab Order.

The simplest and quickest way to rearrange the order of items is to click the Auto Order button. Sometimes, you will not like the arrangement made by the Tab Order dialog box. To rearrange items manually, move a row or a group of rows using the same technique we used to move field names on a table’s Design View.

Opening an “Un-Openable” Form

Some of the forms created by the Database Wizard were programmed not to be opened from the Database Window, for good reasons. To explain why, here is an example.

Imagine that you create a form to process credit card payments when a customer has chosen to perform such a form of payment. The form used to process this payment should be opened only when the customer has decided to make a payment with a credit card. The user does not need to open such a form from the Database Window. Therefore, you would prevent this form from being accessed in any other way than from the form that is supposed to request its service. In the Rockville Techno database, such forms are the Project (since every project is related to a particular client, the Projects form should be opened from a chosen client), the Payment (unless performing a payment, and we want a particular payment to be related to the appropriate project, the Payment form should not be opened otherwise), and etc forms [23].

To open such a form, you have two alternatives: from its parent form or in Design View. To open such a form in Form View (or in Datasheet View if that is the way the database developer wanted to display it), open its parent form, and click the appropriate button to open the desired form (all these dependent forms have their button on the parent form). To open the form in Design View, you don’t have to have its parent form; you can:

1.     right-click the desired form in Database Window and click Design View,

2.     click the form to select it and click the Design button on the Database Window.

Adding Special Controls to Forms

Basic questions

1.     Using Special Form Controls

2.     Creating a List Box or a Combo Box

3.     Creating an Option Group

4.     Adding Command Buttons

Using Special Form Controls

So far, you’ve taken a look at adding controls to a form that directly relate to fields that exist in an associated table or tables. This means that unless the control is linked to a table’s field that uses the AutoNumber data type, you are going to have to type all the data that you enter into the form (exactly as you would in the table).

Fortunately, Access offers some special form controls that can be used to help you enter data. For example, a list box contains a list of entries for a control from which you must choose when entering data. All you have to do is select the appropriate entry from the list. Other special controls also exist that can make it easier to get your data into the form. These controls are [19]:

·        List Box— Presents a list from which you choose an item.

·        Combo Group— Like a list box, but you can type in other entries in addition to those on the list.

·        Option Group— Provides you with different types of input buttons (you can select only one type of button when you create an Option group). You can use option buttons, toggle buttons, or check boxes.

·        Command Button— Performs some function when you click it, such as starting another program, printing a report, saving the record, or anything else you specify.

Figure 358 shows some special controls in the Form view. In this lesson, you create each of these control types.

Figure 358. Special controls can make data entry easier.

All these special controls can be created using the buttons on the Toolbox. Wizards are also available that walk you through the steps of creating each of these special control types. To use the wizard for a particular special control, make sure that the Control Wizards button is activated on the Toolbox. Figure 359 shows the Toolbox and the buttons that you are working with in this lesson.

Figure 359. To use wizards, make sure that the Control Wizards button is selected.

Creating a List Box or a Combo Box

A list box or a combo box can come in handy if you find yourself repeatedly typing certain values into a field. For example, if you have to enter the name of one of your 12 branch offices each time you use a form, you might find it easier to create a list box containing the branch office names, and then you can click to select a particular name from the list. With a list box, the person doing the data entry is limited to the choices that display on the list.

A combo box is useful when a list box is appropriate, but it’s possible that a different entry might occasionally be needed. For example, if most of your customers come from one of six states, but occasionally you get a new customer from another state, you might use a combo box. During data entry, you could choose the state from the list when appropriate and type a new state when it’s not. The combo box only allows data to be entered that is not on the list if you select the I Will Type In the Values That I Want option when you are creating the combo box (this is discussed in the set of steps that follow).

Follow these steps to create a list box or combo box from Form Design view [19]:

1.     Make sure that the Control Wizards button on the Toolbox is selected.

2.       Click the List Box or Combo Box button in the Toolbox. The mouse pointer changes to show the type of box you selected.

3.     Drag your mouse to draw a box on the grid where you want the new element to be placed. When you release the mouse button, the list or combo box wizard starts.

4.     On the wizard’s first screen (Figure 360), click the option button I Will Type In the Values That I Want. Then click Next.

5.     On the next screen, a column of boxes (only one box shows before you enter your values) is provided that you use to enter the values that you want to appear in the list. Type them in (as shown in Figure 361), pressing the Tab key after each one. Then click Next.

6.     On the next screen, you choose the option of Access either remembering the values in the list for later use (such as in a calculation) or entering a value selected from the list in a particular field. Because you are using this box for data entry, select Store That Value in This Field, and then choose a field from the drop-down list that is supplied. For example, if you want this list to provide data from your Product Description field, select it in the drop-down list. Click Next to continue.

7.     On the next screen, type the label text for the new list or combo box control.

8.     Click Finish. Your new list or combo box appears on your form. This box will show a list, so expand the control box as shown in Figure 362.

Figure 360. The wizard walks you through the steps of creating a list box or a combo box.

Figure 361. Type the values for the list or combo box.

Figure 362. Your list or combo box appears on the form grid.

Note. Another Way to Enter Values List boxes and combo boxes can also be set up so that they pull their list of values from an existing table in the database (or a query that you’ve created). Select I Want the List Box to Look Up the Values in a Table or Query on the first wizard screen, and then specify the table or query that should supply the values for the list. A third option for setting up your list box or combo box is to allow the box to pull its list of values from a control field in the form. For example, product names could be pulled from a Product Name field in the form. Use the Find a Record on My Form Based on the Value I Selected in My List Box option on the Wizard screen to have the list pulled from a form field.

Note. Adding Columns to the List or Combo Box You can add additional columns to the list box that allow you to include additional data or information related to the names or data that is included in the first column of the list or combo box. For example the first column might include product names; a second column could include the price of each of the products. In most cases, however, you will find that you only work with a List or Combo box that contains one column.

Note. Tying a List or Combo Box to a Field The best way to approach list and combo boxes is to create a form that includes all the fields from a particular table. Then, you can delete the controls for fields in the Form Design view that you want to “re-create” as list or combo boxes. You then store the values from the list or combo box in one of the fields that you removed from the form.

Note. Where Are My Values? Don’t be alarmed that the values you entered for the control don’t appear in the box in the Design view. The values will be available when you switch to the Form view and do data entry on the form.

Note. You can easily switch between a list box and a combo box, even after you create it. In Form Design view, right-click the control, click Change To from the shortcut menu that appears, and select a new control type.

Creating an Option Group

Another useful special control is the option group. An option group provides different types of buttons or input boxes that can be used to quickly input information into a form. An option group can use one of the following types of buttons [19]:

·        Option buttons— A separate option button is provided for each choice you supply on the form. To make a particular choice, click the appropriate option button.

·        Check boxes— A separate check box is provided for each item you place in the option group. To select a particular item, click the appropriate check box.

·        Toggle buttons— A button is provided for the response required, which can be toggled on and off by clicking the button.

Option groups work best when a fairly limited number of choices are available, and when you create your option group, you should select the type of button or box that best suits your need. If you have several responses where only one response is valid, use option buttons. If you have a situation in which more than one response is possible, use check boxes. Toggle buttons are used when only one response is possible, and a toggle button responds to a “yes or no” type question. The option button is then turned on or off with a click of the mouse.

Note. Other Options   You can create a series of option buttons or check boxes using the Option Group button, or you can opt to directly create option buttons or check boxes by clicking the required button (the Option button or the Check Box button, respectively) on the Toolbox.

To create an Option Group control (you will create a control that uses option buttons), follow these steps [19]:

1.     Make sure that the Control Wizards button in the Toolbox is selected.

2.      Click the Option Group button on the Toolbox. Your mouse pointer changes to show the Option Group icon.

3.     Drag your mouse pointer on your form to draw a box where you want the option group to appear. When you release the mouse button, the wizard starts.

4.     The wizard prompts you to enter the labels you want for each button (or check box or toggle button), as shown in Figure 363. You will need a label for each button that will appear in the group. These labels should be the same as the type of data you would normally insert into the field you are building the option group for (which you will specify in step 7). Enter the labels needed, pressing Tab after each one; then click Next.

5.     On the next screen, you can select one of the labels that you input in step 4 as the default choice for the option group. Specify the label, and then click Yes, the Default Choice Is. Or click No, I Don’t Want a Default As the Other Possibility. Then click Next.

6.     On the next screen, the wizard asks what value you want to assign to each option (such as 1, 2, and so on). These values provide a numerical equivalent for each label you listed in step 4 and are used by Access to store the response provided by a particular option button or check box. You should use the default values that Access provides. Click Next to continue.

7.     On the next screen, you decide whether the value that you assigned to each of your option labels is stored in a particular field or saved by Access for later use. Because you are using the option group to input data into a particular field, be sure the Store the Value in This Field option button is selected. This stores the data that the option group provides in a particular field. Select the field from the drop-down list provided. Then, click Next to continue.

8.     On the next screen, select the type of control (option button, check box, or toggle button — see Figure 364) you want to use and a style for the controls; then click Next.

9.     On the last screen, type a label for the new control. Then click Finish.

Your new option control appears on the grid area of the form. All the different option values that you entered appear in the control. When you switch to the Form view to enter data, you can use the various option buttons or check boxes to select an actual value for that particular field.

Figure 363. Enter the labels you want for each option here.

Figure 364. You can choose different input controls for your Option group.

Adding Command Buttons

Another special control type that you can add to your form is a command button. Command buttons are used to perform a particular action. For example, you could put a command button on a form that enables you to move to the next record or to print the form. Access offers different command button types that you can place on your forms [19]:

1.     Record Navigation— You can add command buttons that allow you to move to the next, previous, first, or last record.

2.     Record Operations— You can make buttons that delete, duplicate, print, save, or undo a record.

3.     Form Operations— Command buttons can print a form, open a page (on a multiple page form), or close the form.

4.     Application— Command buttons can exit Access or run some other application.

5.     Miscellaneous— Command buttons can print a table, run a macro, run a query, or use the AutoDialer to dial a phone number specified on a form.

Note. Form headers or footers make a great place to put any command buttons that you create. Placing them in the header makes it easy for you to go to the top of the form and click a particular command button.

To place a command button on a form, follow these steps:

1.     Be sure that the Control Wizards button in the Toolbox is selected.

2.      Click the Command Button in the Toolbox. Your mouse pointer changes to show the Command Button icon.

3.     Click your form where you want the command button to appear (such as the header of the form). The Command Button Wizard opens.

4.     On the first wizard screen, select an action category in the Categories list, and then in the Actions box (Figure 365), select the action that the button should perform. Then click Next.

5.     On the next screen, you can select to have either text or a picture appear on the command button. For text, choose Text and then enter the text into the appropriate box. To place a picture on the button, select Picture and then select a picture from the list provided (you can use your own bitmap pictures on the buttons if they are available; use the Browse button to locate them). Then click Next.

6.     On the next screen, type a name for your new button. Then click Finish. The button appears on your form. You can move it around like any other control.

Figure 365. Choose what action you want the command button to execute.

Design of Fields on the Forms and Reports in Microsoft Access.

Basic questions

7.     What is the Forms and Reports? How it’s purposes?

8.     How user can create a Forms and Reports in Design View?

9.     How Accessories user can use to Form and Report Design?

10. What are the Field List, the Toolbox and the Properties Window?

11. How user can insert Controls to the Form and Report in Design View?

12. How user can edit and move Controls on the Form and Report in Design View?

Fields Design Techniques

As a database is expanding, so are its objects. For example, many of the tables created by the Database Wizard lack some fields that otherwise would not suit a particular scenario. As we learned already, you can add new fields to a table. Since such new fields are not added to the corresponding forms or reports, you have to insert these fields in the appropriate objects. Even though the Form Wizard or the Report Wizard can be used to create quick objects, you still have the option of changing or improving their look by moving fields around and changing the sequence of fields’ navigation [23].

The modification and design of a form or a report is done in Design View. A form or report in Design View displays the same window controls we have used when performing data entry. This time, these controls are equipped with handles that allow you to move them anywhere on the object.

To perform your design, a form is equipped with two rulers, one horizontal on the top section of the form, and one vertical on the left section, to help you be more precise with dimensions. To assist with controls design, you can use a toolbox equipped with buttons, text boxes and other items to give a lot of flexibility to your application. You also have the originating table, the field list, on your screen in case you want to add an item that you did not include in the beginning. When you do not need any or both of these two items, you can hide them by clicking their buttons on the toolbar.

Form and Report Design Accessories

During form design, there are accessories you will display and dismiss at will. These are the Field List, the Toolbox, and the Properties window. All of these objects are modeless windows. Therefore, you can display one at a time or all of them at the same time and continue designing your form. The Field List is a small window that displays a list of items that are related to the form you are building. These items are from the source data that could be a table or a query.

The Field List

To display the Field List (Figure 351), on the Form Design toolbar, you can click the Field List button. When the Field List is displaying on your screen, the button appears clicked. To dismiss the Field List, click the same button. You can also close it by clicking its Close button. When the list is not displaying, its button looks “normal”.

You can also perform these operations of displaying and dismissing the Field List using the menu bar under View. You can resize the list as needed by dragging one of its borders or corners [23].

                  

Figure 351. The Field List (left) and The Toolbox (right)

The Toolbox

The Toolbox presents a list of some of the most common window controls, Some of these controls can be associated with fields of your Field List; some others can be configured to relate to another existing control on the form. Yet, another control can behave independently of anything else that is on the form.

To display the Toolbox (Figure 351), on the Form Design toolbar, click the Toolbox button. You can also close it using either the same button on the toolbar, or clicking its own Close button. You can also use the menu under View.

The Properties Window

Figure 352. The Properties Window

Made of five property pages, this window displays the characteristics associated with the object or the control that is selected on the form. To get the Properties window of the properties associated with a control, right-click that control and click Properties.

To display the Properties window for the form (Figure 352), double-click the button that is at the intersection of both rulers . You can also click the Properties button on the Form Design toolbar to toggle the Properties windows [23].

Forms, Reports, and Data Existing Fields

As mentioned already, on forms and reports, you can create fields that do not exist on tables or queries, as we have done so far in this lesson. Alternatively, when in the New Form or the New Report dialog box (Figure 344), you can select the object that holds the list of fields in the combo box.

If you had started the design of a form or report already, you can display its Properties window and select an object in the Record Source combo box (Figure 353).

Figure 353. Selecting an object in the Record Source combo box

Forms, Reports, and Queries Fields

If you want to use just a few fields from a table or an existing query, you can set it as the source of a form or report. Otherwise, you can create a new list of fields that are retrieved from a table or an existing query. To do this, you must build a query.

To build a new query that would serve as the source of a form or report, when in Design View, display the Properties window for the form or report and click the ellipsis button  of its Record Source. Then proceed as we learned to add or insert fields for a query. Once you have finished, you can close the query. It would become the source of data for the object [23].

If the form or report had already been created with fields that do not exist on the list, these fields on the form or report would become “orphans”. For example, suppose you create a form or report based on a Record Source such as Employees (Figure 354).

Because you can, suppose you don’t want to start the design of a new form but instead decide to change the Record Source of the form to something else like Customers (Figure 355).

Consequently, the fields that were created from the Employees set of record but that don’t exist on Customers would have lost their “ties”. When such a form or report displays, the “orphaned” controls would display #Name? Error (Figure 356).

Figure 354. A form based on a Record Source such as Employees

Figure 355. Mistake: changing the Record Source of the form to something else

Figure 356. Mistake: “orphaned” controls on the form

Form and Report Design: Controls Insertion

When designing a form or a report, one of the most usual actions you will perform consists of inserting items from the Field List to the form or the report. To add a field, you can drag it from the list to the desired section on the form or report. To drag many fields at the same time, first select them.

To select all items at the same time on the Field List, you can [23]:

·        Double-click the title bar of the field list

·        Click the item on one end of the list, press and hold Shift, and click the item on the other end. In fact, you can use this same process to select fields in a range.

To select fields at random, press and hold Ctrl, then click each one of the desired fields. If you had selected an item but want to remove it from the selection. While still holding Ctrl, you can click the undesired item.

After selecting items on the Field List, to add them to a form or report, you can drag one of them and drop it on the form or report.

Rulers and Dimensions

Since the rulers are dimensionally configured, there are divisions inside of the rulers to help you be more precise. Between two numbered dimensions, there are 7 marks that create 8 divisions. The mark in the middle, a little taller than the others, represents the middle of two dimensions. In this site, the middle division will be referred to as Ѕ. The first division on the right side of a number represents 1/8 of a dimension, the 2nd represents 2/8 = ј of a dimension, the 3rd represents 3/8, and that is why the 4th represents 4/8 = Ѕ. This is how we will refer to these dimensions.

Form and Report Design: Controls Selection

To manipulate controls on the form, you will regularly need to select them. When one control is selected, you can change only its characteristics. When many controls are selected, you can make a change that would impact all of them.

To select a control, you can just click it. A control that is selected displays 8 handles around it:

To select more than one control, at random, click one, then press and hold Shift. While holding Shift, click each of the desired controls. After selecting the controls, release Shift.

To select controls aligned vertically, click inside of the horizontal ruler above the highest positioned control: everything that would be touched by the fake line will be selected. In the same way, you can select controls that are horizontally aligned by clicking inside the vertical ruler.

To select many controls in the same area on the form, click next to one of them and draw a fake rectangle that covers each of the desired controls. All controls touched by the fake rectangle would be selected when you release the mouse [23].

To select all controls on the form, press Ctrl + A. You can also click inside of one of the rulers on one end and drag to the other end.

Form and Report Design: Controls Moving

Once a control is selected, as your mouse moves over a selected control, its pointer displays a different cursor. Two of these cursors can be used to move a control [23]:

 

Form and Report Design: Controls and Sections Sizing

Resizing an object allows you to change its height or its width. This can be applied to forms, reports, their sections, or the controls they are hosting. To physically change the width of a form or a report, position the mouse on its right border until the cursor turns into a vertical beam with a double-horizontal arrow. Then click and drag left or right until you are satisfied (Figure 357).

The height of a form or report is controlled by its sections. For this reason, each section controls its own height. The total heights of all sections constitute the height of the form. Based on this, to resize a form, you must actually resize one or some of its sections. To heighten a section on a form or a report, position the mouse on the lower portion of the section. For the headers or the Detail sections, that will be the upper border of the lower bar. For the lowest section, the mouse must be positioned on the lower border. The cursor turns into a narrow horizontal line with a vertical double-arrow (Figure 357) [23].

To change the width of the form or report and the height of the lowest section at the same time, you can position the mouse on the lower-right corner. The cursor would change into a small square with 4 arrows. You can then click and drag left, right, up, down, or diagonally (Figure 357).

         

Figure 357. A Sections Sizing in the Form design View

When the mouse moves over a selected control and reaches one of the handles, the mouse pointer displays a double-arrow cursor. The possible mouse pointers are:

 

 

Although these pointers can be used to resize one control, they can also be applied to a group of controls.

Form and Report Design: Controls Deletion

If you have added a control or it was created by the Form Wizard or the Report Wizard, you can delete it. You can also delete a group of controls in one step.

·        To remove a control from the form or report, first click it and press Delete. If you click a text box or a control that is accompanied by a label and delete it, its label is deleted also.

·        To remove a group of controls, select them and press Delete.

Tab Order

The controls on a form are aligned in the order you desire for their sequence. Unfortunately, when you add a control on the form that already has other controls, regardless of the section or area you place the new control, it is sequentially positioned at the end of the existing controls. If you don’t fix it, the data entry personnel could have a hard time figuring out how the sequence should be followed. When writing Visual Basic code, you will also find out that the sequence of navigation of controls on a form is very important.

The sequence of controls navigation is set using the Tab Order dialog box. The Tab Order dialog box is available when the form is opened in Design View. Once in Design View, either [23]:

·        on the menu bar, click View -> Tab Order;

·        right-click on the form and click Tab Order.

The simplest and quickest way to rearrange the order of items is to click the Auto Order button. Sometimes, you will not like the arrangement made by the Tab Order dialog box. To rearrange items manually, move a row or a group of rows using the same technique we used to move field names on a table’s Design View.

Opening an “Un-Openable” Form

Some of the forms created by the Database Wizard were programmed not to be opened from the Database Window, for good reasons. To explain why, here is an example.

Imagine that you create a form to process credit card payments when a customer has chosen to perform such a form of payment. The form used to process this payment should be opened only when the customer has decided to make a payment with a credit card. The user does not need to open such a form from the Database Window. Therefore, you would prevent this form from being accessed in any other way than from the form that is supposed to request its service. In the Rockville Techno database, such forms are the Project (since every project is related to a particular client, the Projects form should be opened from a chosen client), the Payment (unless performing a payment, and we want a particular payment to be related to the appropriate project, the Payment form should not be opened otherwise), and etc forms [23].

To open such a form, you have two alternatives: from its parent form or in Design View. To open such a form in Form View (or in Datasheet View if that is the way the database developer wanted to display it), open its parent form, and click the appropriate button to open the desired form (all these dependent forms have their button on the parent form). To open the form in Design View, you don’t have to have its parent form; you can:

3.     right-click the desired form in Database Window and click Design View,

4.     click the form to select it and click the Design button on the Database Window.

Adding Special Controls to Forms

Basic questions

5.     Using Special Form Controls

6.     Creating a List Box or a Combo Box

7.     Creating an Option Group

8.     Adding Command Buttons

Using Special Form Controls

So far, you’ve taken a look at adding controls to a form that directly relate to fields that exist in an associated table or tables. This means that unless the control is linked to a table’s field that uses the AutoNumber data type, you are going to have to type all the data that you enter into the form (exactly as you would in the table).

Fortunately, Access offers some special form controls that can be used to help you enter data. For example, a list box contains a list of entries for a control from which you must choose when entering data. All you have to do is select the appropriate entry from the list. Other special controls also exist that can make it easier to get your data into the form. These controls are [19]:

·        List Box— Presents a list from which you choose an item.

·        Combo Group— Like a list box, but you can type in other entries in addition to those on the list.

·        Option Group— Provides you with different types of input buttons (you can select only one type of button when you create an Option group). You can use option buttons, toggle buttons, or check boxes.

·        Command Button— Performs some function when you click it, such as starting another program, printing a report, saving the record, or anything else you specify.

Figure 358 shows some special controls in the Form view. In this lesson, you create each of these control types.

Figure 358. Special controls can make data entry easier.

All these special controls can be created using the buttons on the Toolbox. Wizards are also available that walk you through the steps of creating each of these special control types. To use the wizard for a particular special control, make sure that the Control Wizards button is activated on the Toolbox. Figure 359 shows the Toolbox and the buttons that you are working with in this lesson.

Figure 359. To use wizards, make sure that the Control Wizards button is selected.

Creating a List Box or a Combo Box

A list box or a combo box can come in handy if you find yourself repeatedly typing certain values into a field. For example, if you have to enter the name of one of your 12 branch offices each time you use a form, you might find it easier to create a list box containing the branch office names, and then you can click to select a particular name from the list. With a list box, the person doing the data entry is limited to the choices that display on the list.

A combo box is useful when a list box is appropriate, but it’s possible that a different entry might occasionally be needed. For example, if most of your customers come from one of six states, but occasionally you get a new customer from another state, you might use a combo box. During data entry, you could choose the state from the list when appropriate and type a new state when it’s not. The combo box only allows data to be entered that is not on the list if you select the I Will Type In the Values That I Want option when you are creating the combo box (this is discussed in the set of steps that follow).

Follow these steps to create a list box or combo box from Form Design view [19]:

9.     Make sure that the Control Wizards button on the Toolbox is selected.

10.   Click the List Box or Combo Box button in the Toolbox. The mouse pointer changes to show the type of box you selected.

11. Drag your mouse to draw a box on the grid where you want the new element to be placed. When you release the mouse button, the list or combo box wizard starts.

12. On the wizard’s first screen (Figure 360), click the option button I Will Type In the Values That I Want. Then click Next.

13. On the next screen, a column of boxes (only one box shows before you enter your values) is provided that you use to enter the values that you want to appear in the list. Type them in (as shown in Figure 361), pressing the Tab key after each one. Then click Next.

14. On the next screen, you choose the option of Access either remembering the values in the list for later use (such as in a calculation) or entering a value selected from the list in a particular field. Because you are using this box for data entry, select Store That Value in This Field, and then choose a field from the drop-down list that is supplied. For example, if you want this list to provide data from your Product Description field, select it in the drop-down list. Click Next to continue.

15. On the next screen, type the label text for the new list or combo box control.

16. Click Finish. Your new list or combo box appears on your form. This box will show a list, so expand the control box as shown in Figure 362.

Figure 360. The wizard walks you through the steps of creating a list box or a combo box.

Figure 361. Type the values for the list or combo box.

Figure 362. Your list or combo box appears on the form grid.

Note. Another Way to Enter Values List boxes and combo boxes can also be set up so that they pull their list of values from an existing table in the database (or a query that you’ve created). Select I Want the List Box to Look Up the Values in a Table or Query on the first wizard screen, and then specify the table or query that should supply the values for the list. A third option for setting up your list box or combo box is to allow the box to pull its list of values from a control field in the form. For example, product names could be pulled from a Product Name field in the form. Use the Find a Record on My Form Based on the Value I Selected in My List Box option on the Wizard screen to have the list pulled from a form field.

Note. Adding Columns to the List or Combo Box You can add additional columns to the list box that allow you to include additional data or information related to the names or data that is included in the first column of the list or combo box. For example the first column might include product names; a second column could include the price of each of the products. In most cases, however, you will find that you only work with a List or Combo box that contains one column.

Note. Tying a List or Combo Box to a Field The best way to approach list and combo boxes is to create a form that includes all the fields from a particular table. Then, you can delete the controls for fields in the Form Design view that you want to “re-create” as list or combo boxes. You then store the values from the list or combo box in one of the fields that you removed from the form.

Note. Where Are My Values? Don’t be alarmed that the values you entered for the control don’t appear in the box in the Design view. The values will be available when you switch to the Form view and do data entry on the form.

Note. You can easily switch between a list box and a combo box, even after you create it. In Form Design view, right-click the control, click Change To from the shortcut menu that appears, and select a new control type.

Creating an Option Group

Another useful special control is the option group. An option group provides different types of buttons or input boxes that can be used to quickly input information into a form. An option group can use one of the following types of buttons [19]:

·        Option buttons— A separate option button is provided for each choice you supply on the form. To make a particular choice, click the appropriate option button.

·        Check boxes— A separate check box is provided for each item you place in the option group. To select a particular item, click the appropriate check box.

·        Toggle buttons— A button is provided for the response required, which can be toggled on and off by clicking the button.

Option groups work best when a fairly limited number of choices are available, and when you create your option group, you should select the type of button or box that best suits your need. If you have several responses where only one response is valid, use option buttons. If you have a situation in which more than one response is possible, use check boxes. Toggle buttons are used when only one response is possible, and a toggle button responds to a “yes or no” type question. The option button is then turned on or off with a click of the mouse.

Note. Other Options   You can create a series of option buttons or check boxes using the Option Group button, or you can opt to directly create option buttons or check boxes by clicking the required button (the Option button or the Check Box button, respectively) on the Toolbox.

To create an Option Group control (you will create a control that uses option buttons), follow these steps [19]:

10. Make sure that the Control Wizards button in the Toolbox is selected.

11.  Click the Option Group button on the Toolbox. Your mouse pointer changes to show the Option Group icon.

12. Drag your mouse pointer on your form to draw a box where you want the option group to appear. When you release the mouse button, the wizard starts.

13. The wizard prompts you to enter the labels you want for each button (or check box or toggle button), as shown in Figure 363. You will need a label for each button that will appear in the group. These labels should be the same as the type of data you would normally insert into the field you are building the option group for (which you will specify in step 7). Enter the labels needed, pressing Tab after each one; then click Next.

14. On the next screen, you can select one of the labels that you input in step 4 as the default choice for the option group. Specify the label, and then click Yes, the Default Choice Is. Or click No, I Don’t Want a Default As the Other Possibility. Then click Next.

15. On the next screen, the wizard asks what value you want to assign to each option (such as 1, 2, and so on). These values provide a numerical equivalent for each label you listed in step 4 and are used by Access to store the response provided by a particular option button or check box. You should use the default values that Access provides. Click Next to continue.

16. On the next screen, you decide whether the value that you assigned to each of your option labels is stored in a particular field or saved by Access for later use. Because you are using the option group to input data into a particular field, be sure the Store the Value in This Field option button is selected. This stores the data that the option group provides in a particular field. Select the field from the drop-down list provided. Then, click Next to continue.

17. On the next screen, select the type of control (option button, check box, or toggle button — see Figure 364) you want to use and a style for the controls; then click Next.

18. On the last screen, type a label for the new control. Then click Finish.

Your new option control appears on the grid area of the form. All the different option values that you entered appear in the control. When you switch to the Form view to enter data, you can use the various option buttons or check boxes to select an actual value for that particular field.

Figure 363. Enter the labels you want for each option here.

Figure 364. You can choose different input controls for your Option group.

Adding Command Buttons

Another special control type that you can add to your form is a command button. Command buttons are used to perform a particular action. For example, you could put a command button on a form that enables you to move to the next record or to print the form. Access offers different command button types that you can place on your forms [19]:

6.     Record Navigation— You can add command buttons that allow you to move to the next, previous, first, or last record.

7.     Record Operations— You can make buttons that delete, duplicate, print, save, or undo a record.

8.     Form Operations— Command buttons can print a form, open a page (on a multiple page form), or close the form.

9.     Application— Command buttons can exit Access or run some other application.

10. Miscellaneous— Command buttons can print a table, run a macro, run a query, or use the AutoDialer to dial a phone number specified on a form.

Note. Form headers or footers make a great place to put any command buttons that you create. Placing them in the header makes it easy for you to go to the top of the form and click a particular command button.

To place a command button on a form, follow these steps:

7.     Be sure that the Control Wizards button in the Toolbox is selected.

8.      Click the Command Button in the Toolbox. Your mouse pointer changes to show the Command Button icon.

9.     Click your form where you want the command button to appear (such as the header of the form). The Command Button Wizard opens.

10. On the first wizard screen, select an action category in the Categories list, and then in the Actions box (Figure 365), select the action that the button should perform. Then click Next.

11. On the next screen, you can select to have either text or a picture appear on the command button. For text, choose Text and then enter the text into the appropriate box. To place a picture on the button, select Picture and then select a picture from the list provided (you can use your own bitmap pictures on the buttons if they are available; use the Browse button to locate them). Then click Next.

12. On the next screen, type a name for your new button. Then click Finish. The button appears on your form. You can move it around like any other control.

Figure 365. Choose what action you want the command button to execute.