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:
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:
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.
Table 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).
-
|