1

Week In Review

In the first week, you learned about the relational database model, how to use the Visual Basic database objects access and update existing databases, and how to use the Visual Basic Data Manager program to create and maintain databases. You also learned how to design and code data entry forms, including use of the Visual Basic bound data controls, and how to create input validation routines at the keystroke, field, and form levels. Finally, you learned how to use the Visual Basic Crystal Reports report writer to design simple reports, and you learned how to use the CRYSTAL.VBX control to run those reports from within your Visual Basic programs.

Day 1, "Your First Database Program in Visual Basic"

The first day's lesson gave you a crash course in how to build a fully functional data entry form in Visual Basic with minimal programming code. On Day 1, you learned the following:

• How to use the data control to bind a form to a database and data table by setting the DatabaseName and RecordSource properties.
• How to use the Text box bound input control to bind an input box on the form to a data table and data field by setting the DataSource and DataField properties.
• How to combine standard command buttons and the AddNew and Delete methods to provide Add and Delete record functionality to a data entry form.

Day 2, "Creating Databases"

The lesson on Day 2 concentrated on the fundamentals of relational databases. You learned the following about relational databases:

• A relational database is "a collection of related data."
• The three key building blocks of relational databases are data fields, data records, and data tables.
• The two types of database relationships are one-to-one (which uses qualifier fields) and one-to-many (which uses pointer fields).
• There are two types of key (or index) fields: primary key and foreign key.

You also learned the data field types recognized by Visual Basic 4. You constructed a data entry form that allows you to test the way Visual Basic behaves when attempting to store data entered into the various data field types.

Day 3, "Using the Data Manager"

On Day 3 you learned how to use the Data Manager that comes with Visual Basic 4 to perform the following database maintenance operations on Microsoft Access databases:

• Create new databases
• Open existing databases
• Create, modify, and delete data tables
• Perform data entry on existing tables including adding, updating, finding, and deleting records

You also learned how the Data Manager can be used to perform database operations on non-Microsoft Access databases including the following:

• Creating FoxPro and dBASE databases using the DOS Make Directory (md) operation to create a DOS directory.
• Creating, modifying, and deleting tables in non-Microsoft Access databases.

Day 4, "Visual Basic Database Objects"

In this day's lesson, you learned that there are three types of Visual Basic data objects:

• Table objects are used when you have a large data set and need to do frequent searches to locate a single record. You can use the Visual Basic Seek method and use Visual Basic indexes with the Table object.
• Dynaset objects are used in most cases in which you need read and write access to data sets. The Dynaset uses little workstation memory and allows you to create virtual tables by combining fields from different tables in the same database. The Dynaset is the only data object that allows you to read and write to ODBC data sources.
• Snapshot objects are used when you need fast read-only access to data sets. Snapshot objects are stored in workstation memory, so they should be kept small. Snapshots are good for storing validation lists at the workstation or for small reports.

You also learned that there are two other Visual Basic data objects, both associated with the Visual Basic data control.

• The RecordSet object is the data control equivalent of the Dynaset object. If you use the Visual Basic data control, you can access the RecordSet object to read and set properties of the Dynaset created by the data control.
• The Database object is another subobject of the data control. You can use the Database object to get a list of tables in the database, a list of indexes associated with the tables, and a list of fields in each of the tables.

Day 5, "Creating Data Entry Forms with Bound Controls"

On Day 5, you learned about creating data entry forms with Visual Basic bound data controls.

You learned that the Visual Basic Data Control has five database-related properties. Three refer to the database and two refer to the Dynaset.

The Database properties of the Visual Basic data control are

1. DatabaseName: Used to select the database to access.
2. Exclusive: Used to prevent others from opening the database.
3. ReadOnly: Used to prevent your program from modifying the data in the database.

The Dynaset properties of the Visual Basic data control are

1. RecordSource: Used to select the data table within the database.
2. Options: Used to set ReadOnly, DenyWrite, and AppendOnly properties to the Dynaset.

You learned that the Visual Basic data control has three database-related methods:

1. Refresh: Used to update the data control after setting properties.
2. UpdateControls: Used to read values from the fields in the Dynaset and write those values to the related form controls.
3. UpdateRecord: Used to read values from the form controls and write those values to the related fields in the Dynaset.

You learned that the Visual Basic data control has three database-related events:

• Reposition: Occurs each time the record pointer is moved to a new record in the Dynaset.
• Validate: Occurs each time the record pointer leaves the current record in the Dynaset.
• Error: Occurs each time a database error occurs.

You learned how to use Visual Basic bound form controls to link form input and display controls to data fields in the database.

• Bound text box control: Used for data entry on character and numeric data table fields.
• Bound label control: Used for display-only character and numeric data table fields.
• Bound checkbox control: Used for data entry on the BOOLEAN data type field.
• Bound image control: Used to display images stored in the BINARY data type field.
• The 3-D panel control behaves the same as the label control, and the 3-D checkbox control behaves the same as a standard checkbox control.

You also learned several general rules for creating Visual Basic forms in the Windows 95 style.

1. The default color is light gray for backgrounds.
2. Use the panel3D control to create a palette on which to place all other controls.
3. The default font is 8 point sans-serif, regular.
4. Input areas should have a background that is white; display areas should have a background that is light gray. Display areas should be recessed into the input palette.
5. Use frame controls to group related items on a form.
6. Left-justify all controls, including field prompts. Field prompts should be written in mixed case and followed by a semicolon.
7. Standard spacing and sizing for common controls are as follows:
   • Control height is 330 twips.
   • Command Button width is 1200 twips.
   • Vertical spacing between controls is 90 twips for related items and 210 twips for unrelated items.
   • Border widths (top, bottom, and side) should be 120 twips.

Lastly, you learned how to write code that sets control colors to the Windows 95 default colors, how to create your own custom color scheme, and how to link your control colors to the color scheme selected with the Windows Control Panel color applet.

Day 6, "Input Validation"

On this day, you learned how to perform input validation on data entry forms. You learned that input validation tasks can be divided into three areas:

• Key Filtering: Preventing unwanted keyboard input.
• Field-Level Validation: Validating input for each field.
• Form-Level Validation: Validating input across several fields.

You also learned that there are a few basic questions you can ask yourself when you are developing validation rules for your form.

• Is it a required field?
• What characters are valid or invalid for this field? (Numeric input only, capital letters only, no spaces allowed, and so forth.)
• For numeric fields, is there a high or low range limit? (Must be greater than zero and less than 1000, can't be less than 100, and so on.)
• Is there a list of valid values for this field? (For example, you can only enter Retail, Wholesale, or Other; Name must already be in the customer table.)
• Is this a conditional field? (If users enter Yes in field A, they must enter something in field C.)

You learned how to write keyboard filter validation functions using the Visual Basic KeyPress event. You learned how to write field-level validation functions that check for valid input ranges, input that is part of a list of valid data, and input that is within minimum and maximum length requirements. You also learned how to write validation functions that make sure dependent fields have been filled out properly. Finally, you learned how to create a Visual Basic library module containing validation functions that can be used in any Visual Basic program.

You also applied your knowledge of bound data controls, Visual Basic data entry form design, and validation processing to create the data entry form for the CompanyMaster database.

Day 7, "Creating Reports with Crystal Reports Report Writer"

You wrapped up your first week of study by learning how to use Crystal Reports to create a simple list report using the data tables you created earlier in the week. You also learned that Crystal Reports is a banded report writer. These are the main bands in a report:

• Header and Footer bands appear on every page.
• Detail bands contain the equivalent of a data table record.
• Section bands contain subtotals or groupings of the data.

You also learned that Crystal Reports recognizes three types of fields on the report form:

• Database fields are from attached data tables.
• Text fields are literal text created by the user.
• Formula fields are calculated fields created by the user.

You also learned how to use the CRYSTAL.VBX to run a report from within your Visual Basic program. Finally, you created a generic print report dialog that lets you control the report destination, file type, and number of copies.