Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Jun 5, 2007

Designing Forms for Efficient and Accurate Data Entry - Page 3

By DatabaseJournal.com Staff

Case Study: Using an Option Group to Select the Shipper

In the Northwind 2007 database, the Orders table has a Shipper ID field that specifies which shipping company to use. There are three shipping companies that the users can select: Shipping Company A, Shipping Company B, and Shipping Company C. (The person at Microsoft who put together the Northwind 2007 database was singularly uncreative when it came to names.) Option buttons can take only numeric values, so you can't use them to assign a text value such as "Shipping Company A" to the Shipper ID field. That's not a problem because the Shipper ID field is designed to store a number: 1 for Shipping Company A, 2 for Shipping Company B, and 3 for Shipping Company C. These numbers correspond to the ID field in the Shippers table. The Shipper and Orders tables have a one-to-many relation based on the ID and Shipper ID fields.

A field that takes one of three numeric choices is perfect for an option group. You set things up as follows:

  • Create an option group and bind it to the Shipper ID field.

  • Add three option buttons for Shipping Company A, Shipping Company B, and Shipping Company C, and assign them the values 1, 2, and 3, respectively.

Figure 3.7 shows the resulting option group in the form.

Figure 3.7
This form uses an option group to choose the shipping method for each order.


Using Lists to Present a Large Number of Choices

Option buttons have three main disadvantages:

  • If a field can take more than about five or six values, option buttons become too unwieldy and confusing for the users.

  • Option buttons can't work with non-numeric values.

  • Users can't enter unique values. This is normally a good thing, but in some instances you might want to give the users the flexibility to choose either a predefined value or to enter a different value.

To solve all these problems, Access offers two different list controls that enable you to present the users with a list of choices:

  • A list box presents a list of choices. These choices are static, meaning that users can't enter any different values.

  • A combo box enables users to either select a value from a drop-down list or (optionally) enter a different value using the associated text box.


Note - Another consideration you need to bear in mind when deciding between a list box and a combo box is the size of each control on the form. A list box is usually large enough to show at least three or four items in the list, whereas a combo box always shows only a single item (the users click the list to choose another). Therefore, the list box always takes up quite a bit more room than the combo box, so keep that in mind when designing your form. If you don't have much room, but you don't want the users to be able to add different values to the field, you'll see later that it's possible to restrict the combo box to just the values in the list.


In both cases, the item the users choose from the list (or the item the users enter in the combo box) is the value that is stored in the bound field. This means that you can use list and combo boxes for any type of value, including numeric, string, and date values.

It's important to note that Access defaults to a combo box when you add to the form a field that is used as part of a relationship with another table. Specifically, if the relationship is one-to-many and the current table is the "many" side, adding the field that corresponds to the common field on the "one" side creates a list that contains all the values from that field.

For example, the Products table has a one-to-many relationship with the Order Details table via the common ID and Product ID fields, respectively. If you're putting together a form based on the Order Details table and you add the Product ID field, Access creates a combo box list and populates it with the values from the Products table's Product Name field. Why Product Name and not Product ID? The reason is that in the design for the Order Details table, the Product ID field's Row Source property (in the Lookup tab) specifies an SQL statement that selects the Product Name field from the Products table:

SELECT ID, [Product Name] FROM Products ORDER BY [Product Name]

The next few sections show you various ways to work with both controls.

Starting the List Box or Combo Box Wizard

The List Box Wizard and Combo Box Wizard make it easy to create a bound list control. Here are the steps to follow to get started with these wizards:

  1. In the Design tab's Controls group, make sure the Control Wizards button is activated.

  2. Click either Combo Box or List Box.

  3. Draw the box on the form. Access starts either the List Box Wizard or the Combo Box Wizard.

These wizards work identically, but the steps you take vary dramatically depending on which option you choose in the initial dialog box. The next three sections take you through the details of each option.

Getting List Values from a Table or Query Field

The most common list scenario is to populate the list box or combo box with values from a field in a specified table or query. For example, if you're putting together an orders form, you'll probably want to include a list that contains all the customer names, so you'll populate the list with the values from the Customers table's Company field.

The following steps show you how to continue with the List Box or Combo Box Wizard to populate a list with values from a table or query field:

  1. In the first wizard dialog box, click the I Want the List Box to Look Up the Values in a Table or Query option and then click Next.

  2. Click the table or query that contains the field you want to use for the list and then click Next.

  3. In the Available Fields list, select the field you want to use and then click > to add it to the Selected Fields list. Click Next.

  4. If you want the list sorted, use the drop-down list to choose the field you selected, click the Ascending (or Descending) toggle button, and then click Next.

  5. Click and drag the right edge of the column header to set the width of the list column and then click Next.

  6. To create a bound list box or combo box, select the Store That Value in This Field option, choose the field you want to use from the drop-down list, and then click Next.

  7. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Specifying Custom List Values

If the items you want to appear in your list don't exist in another table or query, you need to specify them by hand. Here are the steps to follow to continue with the List Box or Combo Box Wizard and populate a list with custom values:

  1. In the first wizard dialog box, click the I Will Type in the Values That I Want option and then click Next.

  2. For each value you want to add, type the item text and press Tab. Click Next when you're done.

  3. To create a bound list box or combo box, select the Store That Value in This Field option, choose the field you want to use from the drop-down list, and then click Next.

  4. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Getting List Values from the Current Table

Sometimes the values you want in your list already exist in the form's underlying table or query. For example, if your form uses the Customers table, you might want to set up a list for the Job Title field and use the unique values in that to populate the list. (This example illustrates when you might want to use a combo box, because a new customer contact could have a title other than the ones in the list.) Note, however, that the list you create using this method will always be an unbound control.

The following steps show you how to continue with the List Box or Combo Box Wizard to populate a list with values from a field in the form's current data source:

  1. In the first wizard dialog box, click the Find a Record on My Form Based on the Value I Selected in My Combo Box option and then click Next.

  2. In the Available Fields list, select the field you want to use and then click > to add it to the Selected Fields list. Click Next.

  3. Click and drag the right edge of the column header to set the width of the list column and then click Next.

  4. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Creating a Multiple-Column List

Sometimes displaying a single column of values in a list might not be enough. For example, if you're working with data from the Northwind 2007 Products table, displaying just the Product Name field might not give the users enough information. Instead, you might also want to show the users the corresponding Category or Supplier value (using an inner join query for the latter) for each product.

→ To learn about inner joins, see "Establishing Table Relationships." (Chapter 12)

You can do this by adding one or more columns to the list and then specifying which of those columns contains the value you want to store in your form's bound field. Here are the steps to follow:

  1. Draw a list box or combo box on the form to launch the List Box or Combo Box Wizard.

  2. In the first wizard dialog box, select the I Want the List Box to Look Up the Values in a Table or Query option and then click Next. (Note that you can also display multiple columns using the Find a Record on My Form Based on the Value I Selected in My Combo Box option.)

  3. Select the table or query that contains the field you want to use for the list and then click Next.

  4. In the Available Fields list, for each field you want to display in the list, select the field and then click > to add it to the Selected Fields list. Click Next.

  5. You sort the list on multiple fields by using separate drop-down lists to choose each field and its sort order. Click Next.

  6. Click and drag the right edge of each column header to set the width of the list columns. Note, too, that you can also change the column order by clicking and dragging the column headers left or right. Click Next.

  7. To create a bound list box or combo box, select the Store That Value in This Field option, choose the field you want to use from the drop-down list, and then click Next.

  8. In the final wizard dialog box, use the text box to edit the label text that appears above the list and then click Finish.

Figure 3.8 shows a form that uses a two-column combo box to display both the Product Name field and the Category field from the Products table.

Figure 3.8
This combo box uses multiple columns to display both the Product Name field and the corresponding Category field.

Modifying List Box and Combo Box Properties

If you want a bit more control over the list layout and data, you need to tweak the control properties. Here's a list of the properties to work with:

  • Control Source (Data tab)—The field in which the selected list item will be stored.

  • Row Source Type (Data tab)—Choose Table/Query for values that come from a table or query field; choose Value List for values that you enter by hand; choose Field List to populate the list with field names from a table or query.

  • Row Source (Data tab)—This value depends on the Row Source Type value:

    • Table/Query—Enter an SQL SELECT statement that specifies the field you want to use to populate the list (along with any criteria you want to use). Alternatively, click the ellipsis button (...) and use the Query Builder to specify the table, field, and criteria. When you close the Query Builder, Access converts your selections into an SQL SELECT statement.

    • Value List—Enter the values with which you want to populate the list, separated by semicolons.

    • Field List—Enter the name of the table or query that contains the field names with which you want to populate the list.


    Note - If you want to display a multiple-column list, specify each field that you want to include in the list after the SELECT verb in the SQL statement, as in this example:

    SELECT CategoryName, Description FROM Categories;

    Alternatively, use the Query Builder to add each field to the criteria grid.


  • Bound Column (Data tab)—If Row Source Type is Table/Query and the Row Source SELECT statement specifies only a single field, the Bound Column value should always be 1. If the Row Source proeprty specifies two or more fields (for a multiple-column list), set Bound Column to the number of the field that contains the value you want to store in the current table (1 is the first field, 2 is the second field, and so on).

  • Limit To List (Data tab)—This is a combo box-only property. When the value is Yes, the users can only select values from the list; when the value is No, the users can enter new values.

  • Column Count (Format tab)—The number of columns in the list box.

  • Column Heads (Format tab)—If this property is Yes, the list columns are displayed with headers, whereby each header contains the name of the field.

  • Column Widths (Format tab)—The width, in inches, of each column, separated by semicolons.

  • List Rows (Format tab)—This is a combo box-only property, and it specifies the number of items the users see when they click the list.

  • Multi Select (Other tab)—This is a list box-only property. If this property is None, users can select only one item at a time; if this value is Simple, users can select multiple items by clicking them; if this value is Extended, users must hold down the Ctrl key to select multiple items (or hold down Shift to select multiple items that appear consecutively in the list).



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date