home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
(Publisher: The Coriolis Group)
Author(s): Peter G. Aitken
ISBN: 1576102815
Publication Date: 08/01/98

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 22
Forms And Fields, Fields And Forms

To avoid certain pesky problems, you can use field properties to match the tables to the data. You also need to design forms for viewing and manipulating the data.

In modern databases, a field is not just a simple container into which you can toss a chunk of data. Fields have numerous properties that you can set so that the characteristics of the field match the needs of the database. This is an important part of database programming, because improperly set field properties can lead to a lot of problems down the road. After covering fields, this chapter moves on to a very important—and I think fun—part of the project: designing the forms.

Field Properties

Each field in a table definition has a set of properties. Three of these properties were used to design the structure of the database tables in Chapter 21: the Name property, specifying the field name; the Type property, specifying its data type; and (for Text fields only) the Size property, specifying the field size or maximum number of characters the field can hold. Fields have several other properties, and using them judiciously is an important part of database design. This section first looks at these properties, and then shows you how to set them for the demonstration project.

Validation Properties

The term validation refers to the process of ensuring that the data entered in a field meets certain criteria. Some validation rules are quite simple, such as requiring that the field contain some data and not be left empty. Other validation rules can be much more involved, such as requiring that the entry in a Part Number field consists of two letters, followed by a four-digit number. An Access database (the format you are using for the GrapeVine project that was started in the previous chapter) provides several properties that determine if and how a field’s data is validated:

  ValidationRule—An SQL statement used to validate the data that is entered into a field. When the SQL statement is applied to the data in the field, it must evaluate to True; otherwise, an error message is displayed (see the ValidationText property). The ValidationRule property takes the form of an SQL WHERE clause minus the WHERE keyword.
  ValidationText—The error text that is displayed when the conditions specified by the ValidationRule property are not met.
  ValidateOnSet—If this property is True, the validation rule (if any) associated with the field is applied as soon as the field’s value is set or changed. If set to False, validation occurs only when the entire record is updated (along with validation for other fields).
  Required—This property determines whether the field can contain a Null value. Null is a special Visual Basic value that is used to indicate missing or unknown data, and it is designated by the Null keyword. A Null value in a field is not the same as a blank field, a zero-length string, or a zero value. Some fields, particularly those defined as the primary key, cannot contain Null values.
  AllowZeroLength—This property specifies whether a zero-length string (“”) is permitted in a type Text or Memo field.

Using these validation properties is one approach to performing validation checks on data. Sometimes, however, using the Validate event is preferable. This event is covered later in the chapter.

Other Field Properties

You should also know about several miscellaneous field properties:

  OrdinalPosition—This property specifies the relative position of the field in the table, with zero being the first position. When fields are displayed—for example, in a DataGrid control—the default display order is determined by the ordinal positions of the fields, which are determined by the order in which they were added to the table during database design.
  DefaultValue—A string or a Basic expression that evaluates to the appropriate data type for the field. When a record is created, this value is automatically entered in the field, and can be changed by the user, if desired.
  CollatingOrder—This property specifies the order used when sorting records that are based on text data in the field. This property is an integer value that indicates the language rules to be used for sorting. The default is dbSortGeneral (value = 1033), which uses the general sort order appropriate for English, French, German, Portuguese, Italian, and Modern Spanish. Specialized collating orders are available for Dutch, Greek, Hebrew, and several other languages (see Visual Basic Help for details).
  DataUpdatable—This property specifies whether the data in the field can be changed.

Setting Field Properties For The Demonstration Database

Now that you know what the various field properties do, you can set them for the GRAPEVINE database. The procedure for setting field properties is as follows:

1.  In Visual Basic, select Visual Data Manager from the Add-Ins menu.
2.  In VisData, use the File Open command to open the GRAPEVINE.MDB database.
3.  The Database Window displays the names of the four tables that you previously created: Customers, Wines, Items, and Invoices. Click on the plus sign (+) next to the name of the table with the field properties you want to edit.
4.  Under the table name, you should see a Fields heading. Click on the plus sign next to the heading to view a list of the table’s fields, as shown for the Customers table in Figure 22.1.
5.  Click on a field name, or the plus sign next to it, to open a list of its properties. Figure 22.2 shows the properties of the CustID field in the Customers table (before editing the properties).
6.  Double-click on a property name to open a dialog box where you can edit the property setting. If you try to edit a read-only property, a message to that effect is displayed.
7.  Click on OK when you finish editing the property, to close the dialog box. Click on Cancel to retain the original property setting.


Figure 22.1  The fields in the Customers table.


Figure 22.2  The properties of the CustID field in the Customers table.

8.  Repeat Steps 6 and 7 to edit other properties of this field.
9.  Repeat Steps 5 through 8 to edit the properties of other fields in the current table, as needed.
10.  Repeat Steps 3 through 9 to edit the properties of fields in another table.
11.  As you work, click on the minus sign next to an expanded heading to collapse it and hide the subheadings.
12.  When finished, select Exit from the File menu to close VisData and return to Visual Basic.


TIP:  Finding Your Way Around VisData

The Database Window in VisData is arranged hierarchically. The most basic display consists of the database’s table names plus an entry called Properties, under which you find the properties that apply to the database as a whole. Click on the plus sign next to a table name to see three headings—one each for Fields, Indexes, and Properties. At this level, the Properties heading contains those properties that apply to the specific table. Open the Fields heading for a list of the table’s fields, and then open an individual field for a list of its field-specific properties. Double-click on a property name to edit the property.


For your database, only relatively minor changes to the default property settings are required. All of your data validation will be done in code, so you don’t need any entries to the ValidationRule or ValidationText properties. The required property changes are listed in the following section. Using the procedures just explained, make the following changes:

  In the CUSTOMERS table—CustID, Company, Address, City, State, and ZIP are all required fields, so Required should be True. Because you can’t be sure each firm has a specific contact person and phone number, these fields may be left blank, with the property setting of Required = False.
  In the WINES table—All fields in this table are required entries except for RetailPrice. Required should be True for all fields except RetailPrice, where Required = False.
  In the INVOICES table—The InvNo field properties do not need to be changed. For the other three fields in this table, set Required to True.
  In the ITEMS table—The ItemNo field properties do not need to be changed. The other three fields in this table are required, so set Required to True.

That’s it—all field properties are set as needed. You can now turn your attention to the database’s indexes.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.