Database in VB Free Tutorial - softlookup.com


Lean Data_VB
First Program
Creating Database
Database Objects
Data Entry Forms
Input Validation
Crystal Reports
Visdata Program
Selecting Data
Micro-Jet Engine
Visual Basic Code
Graphs
List Boxes, Grids
SQL
Error Handling
Updating Database
Normalization
Multiuser
Data Control
ODBC
Replication
Security
 

Free Tutorials
HTML
Learn HTML
Learn CSS
Learn XML
Learn WML
Database
Learn Access
Learn Data-VB
Learn Oracle
Learn SQL
Programming
Learn C++
Learn JavaScript
Learn Vbscript
Learn VisualBasic

Previous Next

Chapter Four  Creating Data Entry Forms with Bound Controls

Chapter Four
Creating Data Entry Forms with Bound Controls

Today's lesson is a review of all the bound data controls that are shipped with Visual Basic Professional. You'll review the special properties, events, and methods that relate to database programming, and you'll create short examples to illustrate how each of the bound controls can be used in your database programs.

You'll also review general rules for designing quality forms for Windows programs, covering alignment, font selection, control placement and spacing, and color choices.

Finally, you'll create a short project that establishes customizable color schemes for your application. This project will show you how to use the Windows Control Panel Color applet to set colors for your applications.

What Are Bound Data Controls?

Before you get into the details of listing the properties, events, and methods of Visual Basic bound data controls, let's review what a bound control is and why it's so useful.

Bound data controls are the same as any other Visual Basic control objects, except that they have been given additional properties, events, and methods that allow you to "bind" them directly to one or more data tables. This binding makes it easy to create data-aware input and display objects that you can use to perform data input and display with very little program code. Using bound controls simplifies your programming chores a great deal. Most bound controls automatically handle the various chores related to processing data entry and display for databases. The bound controls make it easy to write Visual Basic programs that handle all (or nearly all) of the following processes:

  • Loading data from the database into a Visual Basic data object
  • Selecting the data record(s) requested by the user
  • Loading form controls with values in the requested record(s)
  • Trapping simple user input errors
  • Enforcing database integrity rules
  • Updating the data object with modified data from the form controls

You do not need to use bound data controls in your database programs. In fact, as you will see in the lessons next week, there are times when it is better to use unbound controls in your programs. However, when you use unbound controls, you need to take responsibility for handling all the processes outlined in the preceding list. Although this is not an insurmountable task, it's a good idea to take advantage of the power of bound data controls whenever possible. Using the prebuilt and tested bound controls helps you create solid, functional database entry forms in a short period of time.

The Data Control

The Visual Basic data control is the control used to gain access to database tables. The data control allows you to establish a link to a single Dynaset data object in a database. You can have more than one data control in your program and more than one data control on a single form.

Like all Visual Basic controls, there are properties, events, and methods associated with the data control. Because this guide is on databases, this lesson will focus on the properties, events, and methods that are important in dealing with database activity. In the process, you will build a small program that illustrates these database-related aspects of the Visual Basic data control.

Data Control Properties

There are five data control properties that deserve special attention:

  • DatabaseName
  • Exclusive
  • Options
  • ReadOnly
  • RecordSource

There is a sixth data control property that is used only for data access: the Connect property. The Connect property is used when you are accessing non-Microsoft Access databases. You'll learn more about using the Connect property in the lesson on Day 9. Setting DatabaseName and RecordSource Properties The DatabaseName and RecordSource properties were discussed in Day 3. The DatabaseName property contains the name of the database you want to access. In Microsoft Access databases, this would be the complete drive, path, and filename of the Microsoft Access database file. For example, to connect to the guideS5.MDB Microsoft Access database located in the C:\DATA directory, you would set the DatabaseName property to C:\DATA\guideS5.MDB. You can do this through the Property box at design time or through Visual Basic code at runtime.

Let's start a project to illustrate the data control properties, events, and methods. Load Visual Basic and start a new project. Drop a data control on a blank form. For this project, let's accept the default data control name property of Data1.

In Day 3, you set the DatabaseName and RecordSource properties at design time using the Visual Basic properties window. Visual Basic allows you to set most control properties at runtime (that is, while the program is running). The advantage of setting properties at runtime is that you can build programs that allow users to decide what database and data table they want to access. For this project, you'll set these properties at runtime using Visual Basic code.


NOTE: Design time refers to the time when you are designing your Visual Basic application. Runtime refers to the time when your finished application is running.


You will set these data control values in a separate procedure, called OpenDB. To create a new procedure in Visual Basic, double-click anywhere on the form in order to bring up a Visual Basic code window. Now select Add Procedure... from the Visual Basic Tools menu. You'll see a dialog box that asks you for the name of the procedure (see Figure 4.1).

Figure 4.1. Creating a new Visual Basic procedure.

Enter OpenDB. Make sure the radio button for Sub is selected and then click OK. You now see the new Visual Basic procedure header and footer, ready for you to enter your program code.

The following procedure sets the DatabaseName property of the data control on the current form. Please note where we have entered the location of the guideS5.MDB file. You may need to substitute a different path if you installed the database elsewhere on your system.


NOTE: If you install your data files in the same directory as your program files, you can use the App.Path command to identify the data file location. App.Path can be used as part of the database name to identify the database location without having to know the name of the directory in which it is stored. The Path property of the App object returns the drive and directory in which the project has been stored. This methodology is useful when building applications that will be distributed across an organization, or to multiple organizations. App.Path allows you to utilize setup programs that let the user select the directory in which to install the program files. Your data files will be found as long as they are stored with the program files. As an illustration, if we had installed our guideS5.MDB file in the same directory as we saved the current project, we could substitute:

cDBName = App.Path + "\guides5.mdb"

for the line:

cDBName = "c:\tysdbvb5\source\data\guides5.mdb"

This would allow us to move and store our programs in any directory without having to worry about changing the pointer to our database.


Place the following code in the general declarations section of your form:

Public Sub OpenDB()
   Dim cDBName As String    ` declare a string variable
   `
   cDBName = "c:\tysdbvb5\source\data\guides5.mdb" ` point to database
   `
   Data1.DatabaseName = cDBName ` set database property
   `
   Data1.Refresh ` update data control properties
End Sub


TIP: When you enter Visual Basic program code, Visual Basic looks for typing errors automatically. Each time you press the Enter key, Visual Basic scans the line, capitalizes Visual Basic reserved words (if everything has been typed correctly), adds spaces between the equal signs, and so on. When you enter code, don't try to capitalize or space properly; let Visual Basic do it for you. That way, if you finish a line and press the Enter key and then notice that Visual Basic has not "edited" for you, you'll know that there is probably something on that line that Visual Basic didn't understand. Now you'll catch your typing errors as you code!


The last line in the procedure forces the data control to update all the new properties that have been set in the routine. Any time you use Visual Basic code to change data control properties, you must invoke the Refresh method to update the data control. This is just one of the data control methods. Other data control methods are discussed throughout today's lesson.


TIP: Notice that in the code example you declare a variable, set the variable to a value, and then set the data control property with the variable. This could all be done in a single line of code. Here's an example:

Data1.DatabaseName= C:\TYSDBVB5\SOURCE\DATA\guideS5.MDB"

By declaring variables and using those variables to set properties, you'll create a program that is easier to understand and modify in the future.


When you set the DatabaseName property, you are telling Visual Basic the database you are using. However, at this point, Visual Basic does not know what data table you want to use with the data control. Use the RecordSource property to indicate the data table you want to access.

Now, modify the OpenDB procedure you created earlier by adding code that sets the RecordSource property of the data control to access the Authors data table. Be sure to declare a variable, initialize it to the correct table, and then use the variable to set the data control property. When you are finished, your procedure should look like the one shown in the following code example:

Public Sub OpenDB()
   Dim cDBName As String    ` declare a string variable
   Dim cTblName As String   ` declare a string variable
   `
   cDBName = "c:\tysdbvb5\source\data\guides5.mdb" ` point to database
   cTblName = "Authors" ` point to authors table
   `
   Data1.DatabaseName = cDBName ` set database property
   Data1.RecordSource = cTblName ` set recordsource property
   `
   Data1.Refresh ` update data control properties
End Sub

Before you get too far into the project, you should save your work. Save the form as BNDCTRL1.FRM and the project as BNDCTRL.VBP. Setting the ReadOnly and Exclusive Properties There are two more data control properties that you'll need to set in this example: ReadOnly and Exclusive. The ReadOnly and Exclusive properties are Boolean (True/False) properties that you can use to limit access to the database. When you set the Exclusive property to True, you are opening the database for your use only. In other words, no one else can open the database (or any of the tables in the database) while you have it open. This is handy when you want to perform major updates or changes to the database and do not want anyone else in the file at the same time.

For the example, you'll open the database for exclusive use. Modify the OpenDB procedure so that it sets the Exclusive property to True. Your code should look like the following code:

Public Sub OpenDB()
   Dim cDBName As String    ` declare a string variable
   Dim cTblName As String   ` declare a string variable
   Dim bExclusive As Boolean ` declare true/false var
   `
   cDBName = "c:\tysdbvb5\source\data\guides5.mdb" ` point to database
cTblName = "Authors" ` point to authors table
   bExclusive = True ` set to exclusive open
   `
   Data1.DatabaseName = cDBName ` set database property
   Data1.RecordSource = cTblName ` set recordsource property
   Data1.Exclusive = bExclusive
   `
   Data1.Refresh ` update data control properties
End Sub


WARNING: When you open the database with Exclusive set to True, no other programs that access the database can be run without errors until you close the database. Use the Exclusive property sparingly!


The ReadOnly property opens the database with read rights only. You will not be allowed to make any changes, additions, or deletions in any table while you have the database open in read-only mode. This is handy when you are using the data for creating a report or for display purposes only. (Read-only mode is faster, too.)


NOTE: Don't confuse the Exclusive property and the ReadOnly property; they are not the same! The Exclusive property makes sure that no one else can access the database while you have it open. The ReadOnly property makes sure that your program cannot update the database while you have it open. The Exclusive property affects everyone who wants to access the database. The ReadOnly property affects only the person running your program.


Again, for this example, you'll open the file as read-only. Make changes to the OpenDB procedure to include variables that set the ReadOnly property to True. When you are done, your code should look something like the following code:

Public Sub OpenDB()
   Dim cDBName As String    ` declare a string variable
   Dim cTblName As String   ` declare a string variable
   Dim bExclusive As Boolean ` declare true/false var
   Dim bReadOnly As Boolean ` declare true/false var

   cDBName = "c:\tysdbvb5\source\data\guides5.mdb" ` point to database
   cTblName = "Authors" ` point to authors table
   bExclusive = True ` set to exclusive open
   bReadOnly = True ` set to read only
   `
   Data1.DatabaseName = cDBName ` set database property
   Data1.RecordSource = cTblName ` set recordsource property
   Data1.Exclusive = bExclusive
   Data1.ReadOnly = bReadOnly
   `
   Data1.Refresh ` update data control properties
End Sub

Now, save your work before entering more Visual Basic code. Setting the Options Property All the properties you have set in the previous code relate to the database that Visual Basic is accessing. The Options property of the Visual Basic data control allows you to establish the properties of the Dynaset opened in the RecordSource property of the data control. There are several options that can be set in the Options property of the data control. In today's lesson, you will learn about the three most commonly used options.

Here are the three Options values for the data control that is covered today:

  • dbDenyWrite
  • dbReadOnly
  • dbAppendOnly

These three options are actually Visual Basic constants that are predefined in the language. They are like Visual Basic variables, except that they have a single, set value that cannot be changed. Table 4.1 shows the three constants and their numeric values.

Table 4.1. Dynaset option values.

Dynaset Option Numeric Value
dbDenyWrite 1
dbReadOnly 4
dbAppendOnly 8



Setting the dbDenyWrite option prevents other users from changing the data in the Dynaset while you have it open (similar to the Exclusive database property). The dbReadOnly option prevents you from changing the data in the Dynaset (similar to the ReadOnly database property). The dbAppendOnly option lets you add new data to the Dynaset but does not let you modify or delete existing records.

Setting the dbReadOnly option speeds processing of the Dynaset and is handy for generating displays or reports. The dbDenyWrite option is useful when you want to make major changes to the Dynaset and want to prevent other users from accessing the records in the Dynaset until you are done making your changes. Using the dbAppendOnly option lets you create data entry routines that limit user rights to adding records without deleting or modifying existing ones.

Now you'll add the code that sets the Options property of the data control. You'll notice that you do not have a property for each of the three options. How do you set them individually? You do this by adding up the constants and placing the result in the Options property of the data control.

For example, if you want to open the Dynaset for only appending new records, set the Options property of the data control to dbAppendOnly. If you want to open the Dynaset to deny everyone the right to update the database and to allow read-only access for the current user, set the Options property to dbDenyWrite + dbReadOnly.

For now, set the data control options to DenyWrite and ReadOnly. When you are done, your procedure should look like this:

Public Sub OpenDB()
   Dim cDBName As String    ` declare a string variable
   Dim cTblName As String   ` declare a string variable
   Dim bExclusive As Boolean ` declare true/false var
   Dim bReadOnly As Boolean ` declare true/false var
   `
   cDBName = "c:\tysdbvb5\source\data\guides5.mdb" ` point to database
   cTblName = "Authors" ` point to authors table
   bExclusive = True ` set to exclusive open
   bReadOnly = True ` set to read only
   `
   Data1.DatabaseName = cDBName ` set database property
   Data1.RecordSource = cTblName ` set recordsource property
   Data1.Exclusive = bExclusive
   Data1.Options = dbDenyWrite + dbReadOnly
   Data1.ReadOnly = bReadOnly
   `
   Data1.Refresh ` update data control properties
End Sub

You have now completed the procedure for opening the guideS5.MDB database and creating a Dynaset from the Authors table. The database and the Dynaset will be opened exclusively for read-only access. Only one thing is missing. You must first make sure the OpenDB procedure is executed! Place the following code line in the Form_Load procedure:

Sub Form_Load ()
   OpenDB ` open the database, set dynaset
End Sub

Now save the project and run the program. If you get an error report, review the code examples and then make the necessary changes before going on to the next section, where you'll add a few more routines that illustrate how data control methods work.

Data Control Methods

Most Visual Basic controls have associated methods. Each method can be thought of as a function or process that you can tell the program to run. The Visual Basic data control has several methods, but only three are database related. Here's a list of them:

  • Refresh
  • UpdateControls
  • UpdateRecord

You have used the Refresh method in today's example already. This method is used any time you change any of the properties of the data control. Using the Refresh method updates the data control and forces it to rebuild the Dynaset. This refresh updates not only the behaviors and properties of the Dynaset but also the records in the set. If records are added to the table by another user after your program has created its Dynaset, invoking the Refresh method will make sure your Dynaset contains the most recent records.

The UpdateControls method is used to update any bound input controls. Invoking the UpdateControls method is the same as reading the current record and putting the values in the fields of the data table into the input controls on a form. This happens automatically each time you press the arrow buttons on the data control. But you can force the update to occur any time during the data entry process. It's especially handy if you want to undo user changes to a data record.

Now, add a single field to the form and test the UpdateControls method. Add a text box control to the form and set the DataSource property to Data1. You'll set the DataField property using Visual Basic code in a moment; leave it blank for now. Refer to Figure 4.2 for positioning and sizing the control.

Figure 4.2. Adding the bound text box control.


Now add the following new procedure (BindControls) to your form. Remember, to insert a procedure you need to use the Add Procedure... command from the Tools menu after you have double-clicked the form. This new procedure links the text box to the field in the Dynaset using the DataField property of the text box.

Public Sub BindControls()
   Dim cField1 As String
   `
   cField1 = "Name"
   `
   Text1.DataField = cField1
End Sub

Now, add the BindControls procedure to the Form_Load event to make sure it gets called when the program starts. Your Form_Load event should look like this:

Sub Form_Load ()
   OpenDB ` open the database, set dynaset
   BindControls ` link controls to data fields
End Sub

You need to add a command button to the form to activate the UpdateControls method. Place a single command button on the form and set its Name property to cmdRestore and its caption to &Restore. Also, add the following code line behind the cmdRestore_Click event:

Private Sub cmdRestore_Click()
   data1.UpdateControls ` restore textbox values
End Sub

Your form should look like the one shown in Figure 4.3.

Now save and run the project. When the first record comes up, edit the field. Change the name or add additional information to the field. Before you click an arrow button, press the Restore button. You'll see that the data in the textbox reverts to the value initially read into it when you first started the program.

Figure 4.3. Adding a Restore button to the form.


Now, add a button that invokes the UpdateRecord method. The UpdateRecord method tells Visual Basic to save the values of the bound input controls (the textbox in this project) to the Dynaset. Refer to Figure 4.4 for sizing and positioning the button.

Figure 4.4. Adding the Update button to the form.


Using the UpdateRecord method updates the Dynaset without moving the record pointer. Now, add a command button to the form, set its Name property to cmdUpdate and its Caption property to &Update, and then place the following code line behind the button in the cmdUpdate_Click event:

Private Sub cmdUpdate_Click()
   data1.UpdateRecord `write controls to dynaset
End Sub

NOTE: It is important to remember the difference between the UpdateControls method and the UpdateRecord method. The UpdateControls method reads from the data object and writes to the form controls. It updates the controls. The UpdateRecord method reads from the form controls and writes to the data object. It updates the record.


Save and run the project again. This time, after you edit the text box, click the Update button. Now, move the record pointer forward to the next record and then back to the record you edited. What do you see? The record was not updated! Remember, in the OpenDB procedure you set the ReadOnly property of the database to True and turned on the ReadOnly value of the Options property. Now modify the OpenDB procedure and change the ReadOnly property to False and drop the dbReadOnly and dbDenyWrite constants from the Options property by setting the Options property to 0.

When you rerun the program, you can now edit the text box, restore the old value with the Restore button, or save the new value with the Update button. You can also save the new value by moving the record pointer.

This last behavior of the data control can cause some problems. What if you changed a field and didn't want to save the changes, but instead of clicking the Restore button, you moved to the next record? You would change the database and never know it! In the next section, you'll use one of the data control's events to help you avoid just such a situation.

Data Control Events

All Microsoft Windows programs contain events. These events occur each time the computer senses that a user clicks a button or passes the mouse over an object on the form, or when any other process occurs. When an event takes place, the Windows operating system sends a message that tells all processes currently running that something has happened. Windows programs can then "listen" for messages and act, based on their programming code, when the right message comes along.

In Visual Basic, you can create program code that executes each time a specific event occurs. There are three data control events that relate to database functions:

  • Reposition
  • Validate
  • Error

The Reposition event occurs each time the data control moves to a new position in the Dynaset. The Validate event occurs each time a data control leaves the current record. The Error event occurs each time a database error occurs when the arrow buttons on the data control are used to move the record pointer. Visual Basic automatically creates procedure headers and footers for all the events associated with a control. When you place a data control on your form, Visual Basic creates the procedures Data1_Reposition, Data1_Validate, and Data1_Error.

Now, add some code to the project that will tell you when an event occurs. First, you need to get a message box to pop up each time you reposition the record pointer using the arrow buttons on the data control. To do this, place the following code in the Data1_Reposition event:

Private Sub Data1_Reposition()
   MsgBox "Repositioning the pointer..."
End Sub

Next, to get a message box to pop up each time you leave a record using the data control's arrow buttons, place the following code in the Data1_Validate event:

Private Sub Data1_Validate(Action As Integer, Save As Integer)
   MsgBox "Validating Data..."
End Sub

Now save and run the project. You'll notice that the message from the Reposition event is the first thing you see after the program begins. This is because the pointer is positioned on the first record in the Dynaset when the Dynaset is first created. (See Figure 4.5.)

Figure 4.5. The Reposition event at the start of the program.


After you click the OK button in the message box, you'll see the Visual Basic form with the data control. Click one of the arrow buttons. You'll see that the message from the Validate event pops up. This message is sent before Visual Basic leaves the current record. (See Figure 4.6.)

Figure 4.6. The Validate event message.


After you click the OK button in the message box, you'll see the message from the Reposition event again. This is the event message sent when Visual Basic reads the next record.

You might have noticed that the header for the Validate event contains two parameters: Action and Save. These two parameters can be used to learn more about what action is currently being attempted on the data control and can give you control over whether the user should be allowed to save the new data to the Dynaset. These parameters are set by Visual Basic while the program is running. You can read the values in these parameters at any time during the program. For now, you'll explore the Action parameter. The next set of code adds a routine to the Validate step that pops up a message box each time the arrow buttons of a data control are clicked.

Just like the Options property constants, Visual Basic also provides a set of predefined constants for all the possible Action values reported in the Validate event. Although these constants are handy, they are not very useful to users of your programs. The following code example shows you how to translate those constants into a friendly message using a string array. Add the following line to the general declarations section of the form.

Option Explicit
Dim VldMsg(4) As String ` declare message array

Now add the following procedure, which loads a set of messages into the array you declared previously. These messages are displayed each time the corresponding action occurs in the Validate event. Notice that you are using the predefined Visual Basic constants.

Public Sub MakeVldMsgArray()
    VldMsg(vbDataActionMoveFirst) = "MoveFirst"
    VldMsg(vbDataActionMovePrevious) = "MovePrevious"
    VldMsg(vbDataActionMoveNext) = "MoveNext"
    VldMsg(vbDataActionMoveLast) = "MoveLast"
End Sub

Update the Form_Load event to call the MakeVldMsgArray procedure. You can see that MakeVldMsgArray has been added at the start of the event. Here's the code:

Private Sub Form_Load()
   MakeVldMsgArray ` create message array
   OpenDB ` open the database, set dynaset
   BindControls ` link controls to data fields
End Sub

Now you need to add the one bit of code that will be executed each time the Validate event occurs. This code displays a simple message each time you click the arrow buttons of the data control. The actual message is determined by the Action value that Visual Basic passes to the Validate event. The Action value is, of course, determined by the arrow button on the data control that you click while the program is running.

Now you have to replace the "Validating data" message that you entered in the previous example. Here's the new code:

Private Sub Data1_Validate(Action As Integer, Save As Integer)
    MsgBox VldMsg(Action) ` message based on user action
End Sub

Save and run the program to see a message box that tells you what you probably already know! There are several other actions that can occur during the Validate event. You'll explore these actions on Day 5.

For the rest of the project, comment out the Validate event code and the Reposition event code. Now you'll concentrate on adding additional Visual Basic bound controls to the project.

The Bound Text Control and the Bound Label Control

There are no database-related methods or events associated with the bound text control or bound label control. And there are only two properties of the bound text control and the bound label control that are database related:

  • DataSource
  • DataField

The DataSource property is the name of the data control that maintains the link between the data table and the text or label control. The DataField property identifies the actual field in the data control Dynaset to which the text box or label control is bound. You cannot set the DataSource property at runtime--it's a design time-only property. You can, however, set the DataField property at either runtime or design time.

Bound text controls give you the ability to add input fields to your data forms that automatically link to the Dynaset defined in the data control. Bound label controls are handy when you want to display information without letting users update it. You've already added a bound text control to the project, so now add a bound label control, too.

You'll add the label control to display the AuID (the author ID) field of the Authors table. This will give users the chance to see the author ID but not update it. Add a label control to the form, and set its DataSource property to Data1. Also, set the BorderStyle property to Fixed Single to make it look similar to a text box control. Refer to Figure 4.7 for positioning and placement.\

Figure 4.7. Adding the bound label control.


Now update the BindControls procedure to set the DataField property of the label control. Your code should look like this:

Public Sub BindControls ()
   Dim cField1 As String
   Dim cField2 As String
   `
   cField1 = "Name"
   cField2 = "AuID"
   `
   Text1.DataField = cField1
   Label1.DataField = cField2
End Sub

Now save and run the project. You'll see that the label control contains the values stored in the AuID field of the Dynaset. As you move through the Dynaset using the arrow buttons, the label control is updated just as the text control is updated.

The Bound Checkbox Control

The bound checkbox control is basically the same as the text control. It has no special database-related events or methods and has the same two database-related properties: DataSource and DataField. The difference between the text box control and the checkbox control is in how the data is displayed on the form and saved in the Dynaset.

Checkboxes are linked to Boolean data type fields. You'll remember that these fields can only hold -1 or 0. Checkboxes do not display -1 or 0. They display an empty box (0) or a checked box (-1). By clicking the display of the checkbox, you can actually update the Boolean value of the bound Dynaset field.

Using Figure 4.8 as a guide, add a checkbox control to the form. Set its DataSource property to Data1 and its Caption property to Under Contract. You do not need to set the DataField property at this time. This will be done by modifying the BindControls procedure.

Figure 4.8. Adding the bound checkbox control.


Now, update the BindControls procedure to link the checkbox control to the Contracted field in the Authors table. When you are done, your BindControls procedure should look like this:

Public Sub BindControls ()
   Dim cField1 As String
   Dim cField2 As String
   Dim cField3 As String
   `
   cField1 = "Name"
   cField2 = "AuID"
   cField3 = "Contracted"
   `
   Text1.DataField = cField1
   Label1.DataField = cField2
   Check1.DataField = cField3
End Sub

Save and run the project. You will see that some checkboxes are turned on, and some are turned off. You now have a bound checkbox control!

The Bound OLE Control

The Visual Basic OLE control has no database-related events or methods and only two database-related properties:

  • DataSource
  • DataField

Like the bound checkbox control, the OLE control has unique behaviors regarding displaying bound data. The OLE control is used to display OLE objects that are stored in an MDB file by Microsoft Access. This control cannot be used to display binary pictures saved directly to an MDB file by an application other than Access.

Now, let's add an OLE control to the form and bind it to a field in the Authors table. Drop an OLE control on the form and select the Cancel button in the Insert Object dialog box when you are prompted for the Object Type. Now, set the OLE control's DataSource property to Data1, and its SizeMode property to 1 - Stretch. Refer to Figure 4.9 for control sizing and placement. We will bind this control to the Cover field with Visual Basic code in the following section, so, leave the DataField property empty.

After you add the control to the form, update the BindControls procedure to bind the OLE control to the Cover field in the Authors table. When you're done, the procedure should look like this:

Public Sub BindControls ()
   Dim cField1 As String
   Dim cField2 As String
   Dim cField3 As String
   Dim cField4 As String
   `
   cField1 = "Name"
   cField2 = "AuID"
   cField3 = "Contracted"
   cField4 = "Cover"
   `
   Text1.DataField = cField1
   Label1.DataField = cField2
   Check1.DataField = cField3
   OLE1.DataField = cField4
End Sub


Figure 4.9. Adding the bound OLE control.


Save and run the project. You'll now see icons displayed in the top-right corner of the form (only for the first few records). These icons are stored in the binary data type field of the database. Note that you don't have to do any fancy "loading" of the picture into the OLE control, because the data control binding handles all that for you!

When you run your completed project, it should look like the one shown in Figure 4.10.

Figure 4.10. The completed project.


You have just completed a form that contains bound controls for handling text, numeric, Boolean, and OLE object data types stored in a database.

General Rules for Designing Quality Forms

Now that you know how to use the Visual Basic data controls, it's time to learn about form design. Microsoft encourages developers to adhere to a general set of guidelines when designing the look and feel of their programs. In this project, you'll focus on the layout and design of quality forms. We will define guidelines for the following aspects of form design:

  • Control placement and spacing
  • Label alignment
  • Standard fonts
  • Use of colors

The guidelines set here will be used throughout the remainder of the projects in this guide.


NOTE: The style guidelines used in this guide adhere to the look and feel of Microsoft Windows 95. Even if you are still using Windows 3.1 or Windows for Workgroups, we encourage you to adopt the Windows 95 layout standards because many of the people using your programs may already be running Windows 95 on their PCs.


Guidelines for Win95-Style Forms

There are a few general guidelines for developing Win95-style forms. The primary areas to consider are listed in Table 4.2. This table describes the standard measurements Microsoft recommends for form controls. It also contains recommended spacing for these controls. Refer to Figure 4.11 when reading this section. This figure shows an example of a data entry form that is built using the Windows 95 standards described in this section.

Figure 4.11. A Win95-style input form.

The Default Form Color When you first start your form, set its BackColor property to light gray. Set the BackStyle property for labels to Transparent so that the background color can show through. For controls that do not have a BackStyle property (such as checkbox controls and radio button controls), set the BackColor property to light gray. The gray tones are easier to read in varied light. Using gray tones also reduces the chance that a user who experiences color-blindness will have difficulty with your input screens. Using the SSPanel Control to Lift Input Areas Off the Page Use the SSPanel control to create a palette on which to place all display and input controls. Do not place buttons or the data control on the palette unless they act as part of the input dialog box (see Figure 4.11). Use only one palette per form. The palette is not the same as a frame around a set of controls. The palette is used to raise the set of controls up from the page. This makes it easy for the user to see that these controls are grouped together and that they deserve attention.

The SSPanel is a Sheridan 3D control. It may not appear in your toolbox when you first start Visual Basic 5. To add it, right click the Toolbox and select Components.... Now enter a check next to Sheridan 3D Controls. If you do not see the words Sheridan 3D Controls, then click the Browse button and find the file for them--THREED32.OCX. The Default Font Use 8-point sans serif, regular (not bold) as the default font for all controls. If you want to use larger type in a title, for example, do so sparingly. Keep in mind that the default font is a proportionally spaced font. The space taken up by the letter W is greater than the space taken up by the letter j. This can lead to difficulty aligning numbers and columnar data. If you are doing a lot of displays and lists that include numeric amounts or other values that should line up, you should consider using a monospaced font such as Courier or FixedSys. Input Areas and Display Areas Use the color white to indicate areas where the user can perform input. If the field is for display purposes only, set it to gray (or to the form color if it is not gray). This means that all labels should appear in the same color as the form background (such as gray labels for gray forms). Also, make all display-only areas appear recessed on the palette. All text boxes that are active for input should appear white. This makes the action areas of your form stand out to the user. By keeping to the standard of white for input controls and gray (or form-colored) for display-only controls, users will not be so quick to attempt to edit a read-only control. Using the Frame Controls to Group Related Information When placing controls on a form, you should group related items together by enclosing them within a frame control. This frame control is sometimes called a group box because it boxes in a group of related controls. The frame caption is optional, but it is recommended. Using the frame control to group related items helps the user to quickly understand the relationship between fields on the form. Alignment of Controls on the Form All controls should be left-justified on the form. Show a clean line from top to bottom. This makes it easy to read down a list of fields quickly. Try to avoid multicolumn labels. If you must have more than one column of labels and input controls, be sure to left-align the second column, too. Standard Sizing and Spacing for Controls All controls should have standard height, spacing, and width where appropriate. Microsoft publishes its Win95 spacing standards in pixels or DLU (dialog units). Because Visual Basic controls work in twips instead of pixels, you need to know that one pixel equals 15 twips. Table 4.2 shows the recommended spacing and sizing for various controls on a form. Use these as a guide when creating your forms.

Table 4.2. Control spacing and sizing.

Form Control Size/Spacing
Control height 300 twips
Command button width 1200 twips
Vertical spacing between controls 60 twips for related items
90 twips for unrelated items
Border widths (top, bottom, and side) 120 twips



Notice that the height of all controls is the same. This makes it easy to align controls on a form regardless of their type (command buttons, textboxes, checkboxes, and so on). The recommended spacing between controls seems quite wide when you first begin designing forms with these standards. However, you'll find that once you get the hang of these numbers, you'll be able to put together very clean-looking forms in a short amount of time.

Colors

Color standards for Win95 are quite simple--use gray! Although Microsoft recommends the gray tones for all forms, the color settings are one of the most commonly customized GUI properties in Windows programs. In this section you will learn two ways you can approach adding color to your applications: system colors and custom colors.

First, put together a simple form using Table 4.3 and Figure 4.12 as a guide. Remember that you are building a Win95-style form! You won't spend time linking the input controls to a data control right now--just concentrate on building the form and adding color-switching capabilities.


TIP: Here are a few suggestions to help you build the form:

    • Before you begin placing controls on the form, set the Grid Height and Grid Width properties on the General Tab in the Tools | Options menu item to 60 each. This will give you a smaller grid to work with and will make it easier to place controls on the form.
    • Place the SSPanel you will use for your palette on the form first. Then place all other controls directly on the palette. Do not place controls on the palette by double-clicking the tool in the tools window or by using the Copy command. Click the control icon once and then paint the control on the palette with the mouse. This sets the control as a "child" of the palette. Now, any time you move the palette, the controls will move along with it.
    • Place the bound command buttons on the palette one after the other without setting any properties. When you want to set the command button properties, click one of the command buttons and then hold the Shift key while you click each of the other three. Now you can use the properties window to set values for all four of the controls at once. Set the command button's FontBold, Height, and Width properties this way to save time.
    • You can easily set border widths if you remember that the grid dots appear every 60 twips on the form. All border widths should be set at 120 twips. This Microsoft standard makes it easy to distinguish separate controls and keeps a nice border around the form and around palettes and frames. Because border widths should be set at 120 twips, make sure that you can see two grid dots between the edge of the form and the edge of any other control (panel, command button, and so on).
    • Remember that controls should be separated from each other by at least 90 twips. The value of 90 twips is an odd value when compared to the 60 twips between items and the 120 twips between borders. This odd spacing causes the user to break up the sections of the form a bit. This makes it easy for the user to see the separation between controls. When placing controls in a vertical line, use the Top property to determine where the control appears on the form. Because each control is 330 twips in height and the controls must be 90 twips apart, add 420 twips (330 + 90) to the Top value to determine where the next control should appear underneath.


Figure 4.12. The color-switching project.

T
able 4.3. Controls for the Color-Switching project.

Control Property Setting
Form Caption Color-Switching
Name frmColor
SSPanel Caption (set to blank)
Name pnlPalette
Text box Name txtOneLine
FontBold False
Height 300
Width 1800
SSPanel Name pnlDisplayOnly
FontBold False
Height 300
Width 1800
Caption SSPanel1
BevelInner 1 - Inset
BorderWidth 1
Alignment 1 - LeftMiddle
Label Caption Prompt1:
FontBold False
BackStyle 2 - Transparent
Label Caption Prompt2:
FontBold False
BackStyle 2 - Transparent
Data Control Caption Data
FontBold False
Height 300
Width 1800
Command Button Name cmdDefault
Caption &Default
FontBold False
Height 300
Width 1200
Command Button Name cmdSystem
Caption &System
FontBold False
Heigth 300
Width 1200
Command Button Name cmdCustom
Caption &Custom
FontBold False
Height 300
Width 1200
Command Button Name cmdExit
Caption E&xit
FontBold False
Height 300
Width 1200



Save the form as COLORS.FRM and the project as COLORS.VBP. You have built a form that has three command buttons: Default, System, and Custom. You'll add code to the project that makes each of these buttons change the color scheme of the form. First, you'll add the code that sets the colors to the Win95 default: light gray. Standard Colors First, create a Visual Basic constant to represent the hex value for light gray, white, and black. Here's the code:

Option Explicit
`
` constant for colors
Const LIGHT_GRAY = &HC0C0C0
Const WHITE = &HFFFFFF
Const BLACK = &H0

Next, add a new procedure, SetColors, that sets the colors of the form. Because you'll be using this code to set more than one color scheme, add a parameter called nSet to the procedure header. You only have one set right now, but you'll add others soon. The following code sets the BackColor property of the form and data control to light gray:

Sub SetColors (nSet As Integer)
   `
   ` set to default colors
   If nSet = 0 Then
      pnlDisplayOnly.BackColor = LIGHT_GRAY
      pnlPalette.BackColor = LIGHT_GRAY
      frmColor.BackColor = LIGHT_GRAY
      Data1.BackColor = LIGHT_GRAY
      `
      txtOneLine.BackColor = WHITE
      txtOneLine.ForeColor = BLACK
   End If
End Sub

Finally, add a single line of code to the Default command button to execute the SetColors procedure.

Sub cmdDefault_Click ()
   SetColors 0
End Sub

Save and run the project. You'll now see that the background for the form and the data control are set to light gray when you click the Default button. The form now meets the default color standards for Win95 forms. Custom Colors You may want to set your own customized colors for your form. The following code will do just that. Suppose you want the background to appear in red and the text to appear in blue.

First, add the constants for blue and red to your declaration section:

Option Explicit
`
` constant for colors
Const LIGHT_GRAY = &HC0C0C0
Const WHITE = &HFFFFFF
Const BLACK = &H0
Const BLUE = &H800000
Const RED = &H80

Next, modify the SetColors procedure to include your new colors. Notice that you now need to set both the ForeColor and the BackColor properties of all the controls along with the BackColor of the form itself. This time, you'll set the colors to the custom set if the parameter is set to 1. Here's the code:

Sub SetColors (nSet As Integer)
   `
   ` set to default colors
   If nSet = 0 Then
      pnlDisplayOnly.BackColor = LIGHT_GRAY
      pnlPalette.BackColor = LIGHT_GRAY
      frmColor.BackColor = LIGHT_GRAY
      Data1.BackColor = LIGHT_GRAY
      `
      txtOneLine.BackColor = WHITE
      txtOneLine.ForeColor = BLACK
   End If
   `
   ` set to custom colors
   If nSet = 1 Then
      pnlDisplayOnly.BackColor = RED
      pnlPalette.BackColor = RED
      frmColor.BackColor = RED
      Data1.BackColor = RED
      `
      txtOneLine.BackColor = WHITE
      txtOneLine.ForeColor = BLUE
   End If
End Sub

Now, add the following code to the Custom button:

Sub cmdCustom_Click ()
   SetColors 1
End Sub

Save and run the program to see the results. Not such a good color scheme, you say? Well, some may like your custom setting; some may want to keep the default setting. Now you can select the scheme you want with a click of the mouse! System Colors As you can see in the previous code example, some color schemes can be less than perfect. Many programmers add routines to allow users to customize the color scheme to their own taste. The easiest way to do this is to let Windows set the color scheme for you. The code example that follows uses the color scheme selected through the Windows 95 Display applet. This is an excellent way to give your users the power to customize their application color without writing a lot of Visual Basic code.

There are several Windows constants for the system colors that are set by the Control Panel program. For this example, you'll use only three. The following code shows a modified declaration section with the Windows system color constants added:

Option Explicit
`
` constant for colors
Const LIGHT_GRAY = &HC0C0C0
Const WHITE = &HFFFFFF
Const BLACK = &H0
Const BLUE = &H800000
Const RED = &H80
`
` windows system color values
Const WINDOW_BACKGROUND = &H80000005     ` Window background.
Const WINDOW_TEXT = &H80000008            ` Text in windows.
Const APPLICATION_WORKSPACE = &H8000000C ` Background color of MDI apps

Next, you'll add code to the SetColors routine that sets the colors to the Windows system colors.

Sub SetColors (nSet As Integer)
   `
   ` set to default colors
   If nSet = 0 Then
      pnlDisplayOnly.BackColor = LIGHT_GRAY
      pnlPalette.BackColor = LIGHT_GRAY
      frmColor.BackColor = LIGHT_GRAY
      Data1.BackColor = LIGHT_GRAY
      `
      txtOneLine.BackColor = WHITE
      txtOneLine.ForeColor = BLACK
   End If
   `
   ` set to custom colors
   If nSet = 1 Then
      pnlDisplayOnly.BackColor = RED
      pnlPalette.BackColor = RED
      frmColor.BackColor = RED
      Data1.BackColor = RED
      `
      txtOneLine.BackColor = WHITE
      txtOneLine.ForeColor = BLUE
   End If
   `
   ` set to system colors
   If nSet = 2 Then
      pnlDisplayOnly.BackColor = APPLICATION_WORKSPACE
      pnlPalette.BackColor = APPLICATION_WORKSPACE
      frmColor.BackColor = APPLICATION_WORKSPACE
      Data1.BackColor = APPLICATION_WORKSPACE
      `
      txtOneLine.BackColor = WINDOW_BACKGROUND
      txtOneLine.ForeColor = WINDOW_TEXT
   End If
End Sub

Finally, add this line of code to the System button to activate the system color scheme:

Sub cmdSystem_Click ()
   SetColors 2
End Sub

Save and run the program. When you click the System button, you'll see the color scheme you selected in the Control Panel as the color scheme for this application. Now, while the program is still running, start the Control Panel application and select a new color scheme for Windows. Your Visual Basic program instantly changes its own color scheme!

Summary

Today you have learned the following about creating data entry forms with Visual Basic bound data controls.

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 DatabaseName, which is used to select the database to access; Exclusive, which is used to prevent other users from opening the database; and ReadOnly, which is used to prevent your program from modifying the data in the database.
  • The Dynaset properties of the Visual Basic data control are Recordsource, which is used to select the data table within the database; and Options, which is used to set ReadOnly, DenyWrite, and AppendOnly properties to the Dynaset.

The Visual Basic data control has three database-related methods:

  • Refresh updates the data control after setting properties.
  • UpdateControls reads values from the fields in the Dynaset and writes those values to the related form controls.
  • UpdateRecord reads values from the form controls and writes those values to the related fields in the Dynaset.

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.

The Visual Basic bound form controls can be used to link form input and display controls to data fields in the database.

  • The bound textbox control is used for data entry on character and numeric data table fields.
  • The bound label control is used for display-only character and numeric data table fields.
  • The bound checkbox control is used for data entry on the Boolean data type field.
  • The bound OLE control is used to display OLE objects stored directly in an MDB file by Microsoft Access.
  • The Three-D panel control behaves the same as the label control; the Three-D checkbox control behaves the same as a standard checkbox control.

You have also learned the following general rules for creating Visual Basic forms in the Windows 95 style:

  • The default color is light gray for backgrounds.
  • The SSPanel control is used to create a palette on which to place all other controls.
  • The default font is 8-point sans serif, nonbold.
  • Input areas should have a white background, and display areas should have a light gray background. Also, display areas should be recessed into the input palette.
  • Frame controls are used to group related items on a form.
  • All controls, including field prompts, should be left justified. Field prompts should be written in mixed case and followed by a semicolon.
  • The standard spacing and sizing of common controls should be as follows:
    • The control height is 300 twips.
    • The command button width is 1200 twips.
    • The vertical spacing between controls is 60 twips for related items and 90 twips for unrelated items.
    • The border widths (top, bottom, and side) should be 120 twips.

Finally, 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.

Quiz

1. How do you establish a database name for a data control using Visual Basic code?

2. What property do you set to define a table in Visual Basic code?

3. What is the main difference between the UpdateControls and the UpdateRecord methods?

4. What two values can a bound checkbox produce?

5. What property do you use to bind a control to a field in a table?

6. What is the standard form color for Windows 95 applications? What is the standard color of the input areas? What is the standard color of display-only text? How are labels aligned?

Exercises

1. Write Visual Basic code to set the properties to open a database (named STUDENTS.MDB) for a data control named Data1.

2. Modify the code you wrote in the first exercise and set the properties to open a table (Addresses) in STUDENTS.MDB.

3. Modify the code you wrote in the second exercise by binding controls to the data fields in the Addresses table. Include fields for StudentID (which you should declare as cField1), Address (cField2), City (cField3), State (cField4), and Zip (cField5).
 

Previous Next





|  About us | Categories | New Releases | Most Popular | Web Tutorial | Free Download | Drivers |



2019 Soft Lookup Corp. Privacy Statement