Copyright 1994-97 Paul D. Sheriff & Associates
All Rights Reserved Worldwide, Reproduction is strictly prohibited.
Three-tier architecture is currently one of the buzzwords in the industry. Well, the Internet may be overshadowing it right now, but it is still a key technology. To begin talking about multitier architecture, there are a few concepts you need to be introduced to first.
In Part 1 of this course, you will be given a refresher on the basics of object-oriented programming (OOP), be shown how to create classes using the Microsoft® Visual Basic® programming system, and be introduced to some reusable classes. You will also see a full application template that you can use for your own application designs.
Object-oriented programming is a method of software design and construction. It is the next logical progression from structured programming that will improve your code reusability and maintainability.
Object-oriented programming is simply a method of designing individual software components (classes) with associated behaviors (methods) and data (properties), then piecing these components together to create a complete application.
Objects are designed and implemented as discreet units that accomplish a specific task or tasks independent of any other objects or code within the application. OOP languages were designed with a windowing environment in mind. The complexities of dealing with windows make the OOP paradigm almost a necessity.
OOP terminology
Simple OO design
To find classes in a project, you first need to analyze what you need an application to accomplish. If you are creating a business application like a tracking system for a dog rescue business, you may identify the following items in the real world:
You may also find the following actions need to be performed:
In the above example, you have identified the objects (Dogs, Breeds, Breeders) and you have identified some actions (methods) (Dogs.Input, Dogs.Print, Breeds.Input, Breeds.Print, Breeders.Input, Breeders.Print). Now you simply need to identify all of the attributes (data or properties) for each object. For the Dogs object you have the following properties:
You can make properties for each of these items in the dog object. You would then identify the properties for each of the other objects in your system.
As you can see, OO design involves looking at things in the real world and modeling them in terms of objects, actions, and data or classes, methods, and properties.
Visual Basic CLS files contain all of the properties and methods for your class. Classes can be created in two different ways in Visual Basic version 5.0. You may select Project | Add Class Module from the Visual Basic menu or you may select the Class Builder Utility from the Add-ins… menu.
Once you create a CLS file, you need to identify what types of internal data you need to hold in this class. You will create Private variables in the General, Declarations area of the class module to hold this data. Then you need to decide to which properties you will give read access and to which properties you will give write access.
You can decide which private data to give read and write access to by using Property Get and Property Let procedures in your class. A Property Get procedure is like a function that will return information about a Private variable. A Property Let is like a sub routine that you can use to set information into a Private variable. There is also a Property Set procedure that can be used to pass in an object reference to your class.
The first class you will build will return a single Dogs Name. You will create a class called Dog containing the property Name. To build this, follow the steps outlined below:
Option Explicit Private pstrName As String
Since you have created this data as private, no other routines outside of this class module will be able to read or write to this private variable. Obviously, one of the reasons to create an object is to allow other modules the ability to read and write to the properties of that object.
Reading properties
After creating the private variable, you need to allow other modules in this project to read this private data. To accomplish this, add a Property Get procedure to this Class module:
Property Get Name() As String Name = pstrName End Property
A Property Get procedure is just like a function in that it returns a value. To return a value from a function, you need to set the function name equal to the value to return. The above code will return the value of the Private variable pstrName since it assigns the value in pstrName to Name.
Tip A Property Get is like a function that returns a value.
Class initialization
When an object is created from a class, all of the private variables are initialized to the default value appropriate for that data type. If you create a Private String variable, it will be initialized to a blank string. If you create a Private Integer variable, it will be initialized to zero. In the above example, you created a Private Date variable, so the default value for this will be 12:00:00am. To initialize this value to today’s date and time, you need to initialize this variable in the Class_Initialize event for the class.
To write the initialization code, select the left combo box from the Class module window and open it so that you can view the Class object. Select this Class object, and you will see the right hand combo box is filled in with the Initialize event. Visual Basic will automatically place a Sub Class_Initialize() into the module. Add the following line of code to this module:
Private Sub Class_Initialize() pstrName = "Rusty" End Sub
Now when you create an instance of this class, the pstrName variable will be defaulted to the string “Rusty.” To test this, let’s use the form that was created by Visual Basic when you first entered the Visual Basic 5.0 design environment. Follow the steps below to create the Dog object:
Private Sub Command1_Click() Dim oDog As New Dog MsgBox oDog.Name End Sub
When you click on this command button, the Class_Initialize() event will be fired just prior to the call to the Name Property Get procedure. This means the pstrName variable will be initialized with the string “Rusty,” then it will be returned from the Name property. The value returned is then passed onto the MsgBox statement for display on the screen.
Writing to properties
Besides just reading values from Private data values in an object, you will also want to write information into those Private data values. For this, you will need to create a Property Let procedure. A Property Let procedure is a sub routine that accepts one parameter; the value to set. The Property Let procedure should have the same name as the Property Get procedure. Using our Name property, the Property Let will look like the following:
Property Let Name(ByVal strName As String) pstrName = strName End Property
A Property Let procedure will always have one parameter being passed to it. The parameter does not need to be passed by value, but it is a good practice. Be aware that passing a parameter by value is much safer and more efficient than passing by reference. The parameter’s type must be the same data type as the return value from the Property Get procedure of the same name.
The Name Property Let code will be fired when you attempt to set a value into the Name property. To see this happen, add the code shown in bold below to the command button you added to the form.
Private Sub Command1_Click() Dim oDog As New Dog oDog.Name = "Spot" MsgBox oDog.Name End Sub
Creating property Get and Let procedures
Instead of typing in the Property Get and Let procedures by hand, you may have the Visual Basic design environment create them for you. When you choose Tools | Add Procedures, you will be prompted for a procedure name and what type of procedure you want to create. Type in a name, then select a Property procedure type. When you press the OK button, the two procedures will be generated in the CLS module you have currently selected in the design environment. No code will be generated in the procedures, but it will create the two stubs for you.
The class builder utility
The Class Builder Utility is an Add-In to the Visual Basic design environment. You can use it to create new classes, and it will take any existing classes in a project and display those classes in a hierarchical form.
To use the Class Builder Utility, select Add-Ins | Add-In Manager… from the Visual Basic menu. Check the box located next to the Class Builder Utility, then click the OK button. Under the Add-Ins menu, you will find a menu for the Class Builder Utility.
You may use this tool to add a new class to the project or change an existing class just by using this interface. The Class Builder Utility will build default Property Get and Property Let procedures for you if you wish. You can also use it to build one class based on the properties of another class. It does not allow inheritance; it only copies the appropriate properties and methods from the other class into the new class.
Methods
A method of a class is a Public Sub or Public Function created in the CLS file. A method is simply a sub routine or function that you would normally see in a BAS file, but instead is tied to the object. These methods generally perform some operation on the data contained within the class. They may or may not return a value from the object. This will be determined by whether or not you write a Sub or a Function for the method name.
Let’s write a method for the Dog class that will return a string showing the dog’s name and what sound the dog makes when it barks. To accomplish this you will need to add a Bark property to the class and initialize the value to something in the Class_Initialize:
Private pstrBark As String Property Get Bark() As String Bark = pstrBark End Property Property Let Bark(ByVal strBark As String) pstrBark = strBark End Property Private Sub Class_Initialize() pstrName = "Rusty" pstrBark = "Howl" End Sub Create a new Public Function in the CLS file called BarkTheDog. Write the code as shown below: Public Function BarkTheDog() As String Dim strMsg As String strMsg = "My Name Is " & pstrName & vbCrLf strMsg = strMsg & " I can " & pstrBark BarkTheDog = strMsg End Function
While this method does not perform anything very useful, it does get across the point that a method is simply code for Visual Basic that operates on data within the class, and can return that data in some other format. On the client side you can invoke this method with the following code:
Private Sub Command1_Click() Dim oDog As New Dog oDog.Name = "Spot" MsgBox oDog.BarkTheDog End Sub
Note You normally do not put any user interface code inside of a class. That is why a string is returned from this method. That leaves it up to the front end how to display the data.
Let’s now build a class that will help us to read and write information in an ASCII text file. This class will encapsulate some of the low-level file I/O statements of Visual Basic so that they can have a more object-oriented flavor.
It can be a real pain dealing with all of the file handles and different methods of opening files, as well as just reading from and writing to ASCII files. To eliminate some of the drudgery, this File I/O class will handle the details for you, so all you need to do is set some properties and invoke some methods.
The following are the Private variables that you will declare in the (general) (declarations) section of this File I/O class:
| Variables | Description |
| pstrFileName | The file name to read/write to and from. |
| pintHandle | The file handle that is retrieved from the operating system. |
| pstrLineIn | A line of text read in. |
| pstrLineOut | A line of text that will be written out. |
| pboolWriteMode | Open file for writing |
| pboolReadMode | Open file for reading |
The following are the Public Properties that will be exposed to the outside world by the File I/O class:
| Property | Description |
| FileName | The filename to read/write to and from. |
| LineJustRead | A line of text read in. |
| LineToWrite | A line of text that will be written out. |
| FileToBig | Returns a True if the file is greater than 32K in length. |
| ReadMode | Open the file for reading. |
| WriteMode | Open the file for writing. |
The following are the methods that will be implemented in the File I/O class:
| Method | Description |
| OpenRecordset() | Opens the file for reading or writing. Returns True if successful. |
| MoveNext() | Retrieves one line of text. Returns True if successful. |
| AddNew() | Writes out the contents of the property pstrLineOut. Returns True if successful. |
| CloseRecordset() | Closes the file. Returns True if successful. |
Let’s now look at the how you would use the File I/O Class in an application. We have left out a lot of the error handling just to keep the examples short and understandable. However, in a production application you would want to have the appropriate error handling in all of these routines.
Writing to a file using the File I/O class
Let’s start out by showing you how to write information from a text box into a text file. The form displayed below has a text box on it that some text can be typed into. It also has a text box that will accept a filename to write information into. After entering this text, a Write button can be clicked to put the information into the appropriate text file.
In the Write command button, you will begin by dimming a variable named oFile as a New clsFileIO. This instantiates a new object based on our File I/O Class of which you may now use the properties and methods of that object to write the data to the text file:
Private Sub cmdWrite_Click()
Dim oFile As New clsFileIO
Dim boolRet As Boolean
With oFile
' Fill in the File Name
.FileName = Trim$(txtFile)
' Fill In the Text To Write
.LineToWrite = Trim$(txtEdit)
' Open File For Writing
.WriteMode = True
If .OpenRecordset() Then
' Write Line Out
boolRet = .AddNew()
' Close The file
boolRet = .CloseRecordset()
End If
End With
End Sub
The oFile.FileName property is filled in with the filename the user typed into the text box. Next the text to put into the file is placed into the LineToWrite property. The file is then opened using the OpenRecordset() method. Notice that prior to opening the recordset, you should set the WriteMode property to True. This informs the OpenRecordset() method in which mode you wish to open the file. The OpenRecordset() method is shown below:
Public Function OpenRecordset() As Boolean
If pstrFileName <> "" Then
pintHandle = FreeFile
pboolEOF = False
If pboolReadMode Then
Open pstrFileName For Input As #pintHandle
Call MoveNext
Else
Open pstrFileName For Output As #pintHandle
End If
OpenRecordset = True
Else
OpenRecordset = False
End If
End Function
Notice the use of the variable pboolReadMode in the OpenRecordset() method. If this variable is set to True, the file will be open for input. If the variable is set to False, the file will be open for output. This is why you must set the WriteMode property to True prior to calling the OpenRecordset() method when you wish to write to a file.
The WriteMode() Property Let procedure will set both the pboolWriteMode and the pboolReadMode variables:
Public Property Let WriteMode(ByVal boolWriteMode As Boolean) pboolWriteMode = boolWriteMode pboolReadMode = Not boolWriteMode End Property
Note that if you set the WriteMode property to True, the pboolReadMode variable will be set to False. This is a good example of one property affecting other data within the class module.
Adding Data to the Text File
Next, the AddNew() method is used to write the data to the text file. The AddNew() method will take whatever data is in the variable pstrLineOut and write it to a text file using the Visual Basic Print statement.
Public Function AddNew() As Boolean
If pintHandle <> 0 Then
Print #pintHandle, pstrLineOut
AddNew = True
End If
End Function
Closing the File
Once the write process is finished, the file should be closed. Our File I/O class implements a CloseRecordset() method to close the file handle:
Public Function CloseRecordset() As Boolean
' Valid File Handle ?
If pintHandle <> 0 Then
Close #pintHandle
pintHandle = 0
CloseRecordset = True
Else
CloseRecordset = False
End If
End Function
The CloseRecordset() method first checks the file handle to see if it is valid. Any number other than 0 is a valid file handle. CloseRecordset() then calls the Close statement to close the particular file handle stored in this object. It also sets the file handle to a zero, so if any other method calls are attempted after the file is closed, no errors will occur. In every method of this class, the file handle is checked to see if it is valid prior to performing any operation on the file.
Reading from a text file
Now that you have seen how to write information into a file, let’s now look at how to read information out of a file. You will still use the File I/O class to accomplish this. You will simply use some other methods for reading the file.
In the Read Text File form, you will select a file to read using the file controls. Once the file is highlighted in the file list box, you click on the Read command button. In the button’s click event is where you will open the file and read in each line until you hit the end of the file:
Private Sub cmdRead_Click()
Dim oFile As New clsFileIO
Dim strValue As String
Screen.MousePointer = vbHourglass
txtEdit = ""
With oFile
' Figure out File Name To Open
If Right$(filList.Path, 1) = "\" Then
' Check for root directory
.FileName = filList.Path & _
FilList.List(filList.ListIndex)
Else
.FileName = filList.Path & "\" & _
filList.List(filList.ListIndex)
End If
' Check For File Too Big - 32K limit on text boxes
If .FileToBig Then
Beep
Screen.MousePointer = vbDefault
MsgBox "File Too Big To Read", , "File Open Error"
Else
' Open The File For Reading
.ReadMode = True
If .OpenRecordset() Then
' Get Each Line
Do Until .EndOfFile()
' Retrieve the Line
strValue = strValue & .LineJustRead & vbCrLf
.MoveNext
Loop
End If
' Close The File
Call .CloseRecordset
' Add String To Text Box
txtEdit = strValue
End If
End With
Screen.MousePointer = vbDefault
End Sub
First fill in the FileName property with the file selected from the controls on the form. Next, set the ReadMode property to true, so the OpenRecordset() method will open the file for reading instead of writing. We can now loop through the file, reading one line of text at a time into the LineJustRead property until the EndOfFile property is set to a True value. A MoveNext() method is called to move to the next line in the text file. After the file has been completely read into memory, the file handle should be closed using the CloseRecordset() method.
The EndOfFile property is used to check for the end of file. You must use the name EndOfFile instead of EOF, because EOF is a function in low-level file I/O and the two would conflict with each other. With Visual Basic, you can use polymorphism, but since EOF is a keyword, it can not do this.
FileToBig property
Prior to reading in the data from the file, you should first check to see if the file is small enough to be put into a text file. In the cmdRead_Click() event, the FileToBig property is queried to see if the file chosen can fit into a text box. Assuming a text box is limited to 32K, you can create the FileToBig property as follows:
Property Get FileToBig() As Boolean
If FileLen(pstrFileName) > 32768 Then
FileToBig = True
Else
FileToBig = False
End If
End Property
MoveNext() method
After the file has been opened using the OpenRecordset() method, a call is made to the MoveNext() method. MoveNext()reads the information from the text file using the Line Input statement in Visual Basic and places that data into the pstrLineIn variable. The MoveNext() method also checks to see if the end of file has been hit. If it has, MoveNext() will return a False value, and the EndOfFile property is set to True. You can use the EndOfFile property to determine whether or not you are at the end of file.
Public Function MoveNext() As Boolean
' Valid File Handle ?
If pintHandle <> 0 Then
' Are we at End Of File ?
If EOF(pintHandle) Then
pboolEOF = True
MoveNext = False
Else
Line Input #pintHandle, pstrLineIn
MoveNext = True
End If
Else
MoveNext = False
pboolEOF = True
End If
End Function
CloseRecordset() method
If you open a file handle using low-level file I/O, you are taking a file handle away from the operating system. File handles are a shared resource among all processes running on your system. If you forget to release a file handle, there is one less that can be used until you restart your system. The CloseRecordset() method should therefore be called after you are through using the file:
Public Function CloseRecordset() As Boolean
' Valid File Handle ?
If pintHandle <> 0 Then
Close #pintHandle
pintHandle = 0
CloseRecordset = True
Else
CloseRecordset = False
End If
End Function
The Class_Terminate Event
The Class_Terminate() event is the destructor in Visual Basic that is called when an object is destroyed. In our File I/O class, a file handle is opened using the OpenRecordset() method. To ensure that this file handle gets closed, you should call the CloseRecordset() method. However, programmers sometimes will get slack or forget to call the CloseRecordset() method. This is one nice advantage to OOP. In the Class_Terminate() event of the File I/O class, you can call the CloseRecordset() method.
Here is the code for the Class_Terminate() event in the File I/O class.
Private Sub Class_Terminate() Dim boolRet As Boolean boolRet = CloseRecordset() End Sub
In this event, the CloseRecordset() method is called to make sure that if the file handle is open, it is closed. A class can always call one of its own methods, instead of having to duplicate code from one method to another.
To try this out, remove the call to the oFile.Close method from the cmdRead_Click() event. Then step through this event procedure and see what happens as you step through the End Sub of that event.
Let’s now write the BARK application using a multitier design. In this version of the BARK application, you will not see any data access objects within any forms. Instead you will create classes for every table you want to access. The forms will simply create module level objects that abstract the things you may wish to do to a table; add, edit, delete, and retrieve data. While this may be a different type of coding than you are used to, I think you will find it is very easy to read, it is extremely reusable, and it simplifies making changes.
Public objects
In a two-tier BARK application, there would probably be dozens of global variables. A good structured/object-oriented program should have very few global variables. Let’s look at the global variables that you will need in a multitier application.
In every client/server application you create, you will need to open a connection to the database. It is best if only one connection is opened per user. This helps keep the client and server memory resources to a minimum. In the multitier sample application, you will simply need one Public Connection object. Create this public variable as well as a Preferences object in the APPCODE.BAS file:
Option Explicit ' Database Connection Object Public goConnect As New DBConnect ' Preferences Object Public goPref As New Preferences
Starting your application
Applications for Visual Basic have the option of starting by displaying a form or by starting with a procedure called Sub Main(). All of your applications should always start with a Sub Main() procedure. Let’s talk about why you should do this.
You should always start your application with Sub Main() as opposed to a normal form. You will probably want to display a splash screen (copyright screen), perform some system initialization, preload the main form, and maybe open your database all before you display the main form. This gives your user some immediate feedback that something is happening when they click on your icon. While they are reading your splash screen, you can be doing some other work.
Sub Main() will always be located in the file APPCODE.BAS. This file is where you can put application-specific information. Since Sub Main() could change from application to application, we need to keep it separate from those routines that are generic and can be used in many applications. Let’s take a look at an example of a Sub Main() so that you get an idea of the things you should include:
Public Sub Main()
Dim strTime As String
Dim boolPerform As Boolean
Screen.MousePointer = vbHourglass
' Set Display Start Time
strTime = Now
' Display Copyright Form
frmCopyright.Show
frmCopyright.Refresh
' Preload Main Form
Load frmMain
' Get Preferences
goPref.ConfigGet
' Get Database Connection Information
goConnect.ConfigGet
If Not goConnect.OnServer Then
If goConnect.DataInit() Then
If goConnect.DataOpen() Then
boolPerform = True
End If
End If
End If
' Display Copyright screen
' for at least 3 seconds
If boolPerform Then
Do Until DateDiff("s", strTime, Now) > 3
DoEvents
Loop
' Unload the Copyright Form
Unload frmCopyright
Else
Beep
MsgBox goConnect.InfoMsg
End If
If boolPerform Then
' Display the Main Form
frmMain.Show
End If
' Have the User Login
If boolPerform Then
' Display the Login Form
frmLogin.Show vbModal
boolPerform = frmLogin.ValidLogon
Set frmLogin = Nothing
End If
' Reset Mousepointer prior to
' displaying the dog form
Screen.MousePointer = vbDefault
If boolPerform Then
' Display the Dogs Form
frmDogs.Show
Else
Unload frmMain
Unload frmCopyright
End If
End Sub
At the very beginning of this routine, the copyright screen, named frmCopyRight, is displayed. Once again this should be a standard name in each of your projects. Next preload the main form called frmMain. In case the frmMain is large, it can take awhile to load, so while the user is looking at the copyright form, you are doing something else. Note that frmMain will not display until we actually perform the Show method on the form.
Next you call a method of the goConnect object called ConfigGet(). This will read in the data values from the registry into the properties of the connection object. Next you call the DataInit() and DataOpen() methods. These methods will initialize the database engine, then open a connection to the database.
The configuration form is used to initialize the database connections and the paths to the graphic files. You will now look at the FormSave procedure that is called when the Save command button is pressed on this form:
Private Sub FormSave()
' Move Data Into Objects
Call FormMove
' Save Database Connection Setting
goConnect.ConfigSave
' Save User Preferences
goPref.ConfigSave
' Close the Form
Call cmdClose_Click
End Sub
The FormSave() procedure calls a routine named FormMove to take all of the data from the form and move it into both a connection object and a preferences object. After moving the data, a method called ConfigSave() is invoked on both objects to save the data. These Classes will be explained in detail later.
Here is the code for the FormMove() procedure:
Private Sub FormMove()
Dim strPath As String
' Put data into Connection Object
With goConnect
.OnServer = Not chkLocal.Value = vbChecked
.DBName = txtLDBName
strPath = Trim$(txtDBPath)
If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
.DBPath = strPath
.UserId = txtLocalUID
.LoginID = txtUID
' Server Information
.DSN = txtSDSN
.DatabaseName = txtSDBName
End With
' Put data into Preferences Object
With goPref
.Picture = txtPicture
.AVI = txtAVI
.Wave = txtWave
End With
End Sub
Now that you have the main form designed and have learned to connect to your database, you should display a login form. A login form is almost always needed for a business application. It is especially needed when logging onto a database server. However, it can also be useful for a Microsoft Access MDB file. We typically store the Login ID on each row that is updated in tables. This provides a simple auditing feature to track who last updated the row.
If you will be using your application to log into a Microsoft Access MDB, you can retrieve a list of users from a table in the database called tblUsers. This table will contain a list of the users you want to allow into your application. After they choose their name, they will be required to put in their password then press the OK button. At this point, the data is validated then they may proceed into the application.
If your application is designed for an ODBC data source, then the user will be prompted for their login id and password:
After they press the OK button, the data input will be put into the appropriate global variables, and then the DataInit() and DataClose() functions will be called to attempt to login to the data source.
Let’s now look at an example of a basic data entry form that will use a multitier architecture. This means that you will find no database method specific code anywhere in the form. The form will concentrate solely on using a class that is a wrapper around the base table used to populate and modify the data on this form. In this chapter, you will see the code for the form. In a later chapter, you will learn how to build the Dog class.
Module level variables
You will need a module level Dog object in the form. This object will perform all of the adding, editing, and deleting of the data from the base table.
Dim moDog As New Dog
When a form is called from a menu, or another form, it is usually done by invoking the Show method on that form:
frmDogs.Show
When this method is invoked on a form, there are certain events that are always fired. What you need to accomplish is to write code to initialize the form to the state in which you want it when it is displayed to the user. This normally means you need to set some module level variables, load some data, and maybe set some command buttons to a certain state.
Form_Load() Event
The first event invoked when a form is displayed using the Show method is Form_Load(). Attempt to keep this procedure as clean and short as possible. This procedure should only contain calls to initialization routines. Remember that this event is only called the first time a form is loaded. If the form is unloaded, then displayed again, this event will be called again:
Private Sub Form_Load() Screen.MousePointer = vbHourglass ' Initialize the Form Call FormInit ' Load Combo Boxes Call ComboLoad ' Load Dogs Call ListLoad Screen.MousePointer = vbDefault End Sub
FormInit() procedure
Two of the first items to take care of when loading a form are initializing any module level variables and positioning the form. In the Form_Load() event procedure, you should always call a routine named FormInit()to perform this initialization. The listing below shows a typical example of an initialization routine.
Private Sub FormInit()
Dim lngRet As Long
' Set tab stops on this list box
lngRet = SendMessage(lstNames.hWnd, _
LB_SETTABSTOPS, 1, 60&)
' Center form
Call FormCenterChild(Me)
End Sub
In this particular form, you will then set up a tab stop in the list box so that you can display both a dog name and the dog’s breed. Next you can pass the form object to the FormCenterChild() method to center this form in the middle of the MDI form.
Combo box loading
After the form has been initialized, the Form_Load() event next loads any combo boxes with information. There are two combo boxes that have to be loaded on the dog form; breeds and breeders. The ComboLoad() procedure will create two objects that can load combo boxes with their appropriate information.
Private Sub ComboLoad() Dim oBreed As New Breed Dim oBreeder As New Breeder ' Load Breeds Call BreedListLoad(cboBreed) ' Load Breeders Call BreederListLoad(cboBreeder) End Sub
Both the Breed and Breeder class have methods that allow the loading of a combo box with the breed name and the breed id and the breeder name and the breeder id. You will learn about these methods in a later chapter.
List box loading
Now it is time to load the list box with dog information. Below is the ListLoad() procedure that is called from the Form_Load() procedure:
Private Sub ListLoad()
' Check to see if Breed is filled in
' Load Dogs
Call DogBreedListLoad(lstNames, moDog.BreedId)
' Force Click Event
If lstNames.ListCount > 0 Then
lstNames.ListIndex = 0
End If
End Sub
In the code above, you are using the module level dog object to load the list of dogs by either the breed id or the dog’s name and breed name. The DogBreedListLoad procedure will load dogs based on whether or not the breed ID is filled in or not. If the breed id is filled in, then only the dogs for that particular breed will be loaded.
This routine will load the Dog ID into the ItemData() property so that you will always have the primary key available to you without having to go back to the object, or worse, back to the server.
Once the data has been loaded into the combo boxes, and the main list box is loaded with dog names and breeds, you should set the highlight of the list box to the first row in the list. By doing this, some data is displayed on the form without the user having to do anything. To accomplish this, you need to invoke the Click event of the list box by setting the ListIndex property to the value zero (0). In the ListLoad() procedure, you will find the following code at the bottom of the procedure:
. . . ' Force Click Event If lstNames.ListCount > 0 Then lstNames.ListIndex = 0 End If
By setting the ListIndex to a valid value, it will trigger the Click event for the list box.
List box click event
In the list box click event, you will need to find the particular dog you have just clicked. In the list portion of the list box is the Dog’s name, but in the ItemData() property is the primary key for the Dog table. You need to grab that key from the ItemData() property and give it to the DogID property of the module level Dog object. You will then invoke a method of the Dog object called Find(), which will find a dog based on the primary key. If the dog is found, the Find() method loads the dog’s information into the object, so you are ready to display that dog’s information:
Private Sub lstNames_Click()
Dim intIndex As Integer
intIndex = lstNames.ListIndex
If intIndex <> -1 Then
moDog.DogId = lstNames.ItemData(intIndex)
If moDog.Find() Then
Call FormShow
Else
' Record has been deleted
MsgBox "Dog has been deleted"
' Remove Dog
lstNames.RemoveItem intIndex
' If still records in list box
If lstNames.ListCount > 0 Then
' Set list box to first row
lstNames.ListIndex = 0
End If
End If
End If
End Sub
The FormShow() procedure is called to take the information from the Dog object and put it into the appropriate controls on the form.
FormShow() procedure
The FormShow() procedure will now be responsible for displaying the dog’s information on the form. Below is an example of the FormShow() procedure:
Private Sub FormShow()
' Stops the Change event from
' firing the ToggleButtons routine
cmdSave.Tag = "Show"
With moDog
lblID = .BreedId & ""
txtName = .DogName & ""
txtColor = .ColorName & ""
txtBark = .BarkType & ""
cboBreed.ListIndex = ListFindItem(cboBreed, .BreedId)
cboBreeder.ListIndex = _
ListFindItem(cboBreeder, .BreederId)
txtBirthDate = .BirthDate & ""
txtCost = .CostAmount & ""
txtPrice = .PriceAmount & ""
If .Sex = "M" Then
optMale = True
Else
optFemale = True
End If
End With
cmdSave.Tag = ""
End Sub
Notice how all of the data is retrieved from the module level Dog object and not from a Recordset object or any other database access method. You will see the actual Dog class in a later chapter.
After all of the code in the Form_Load() procedure has been executed, the Form_Activate() event will fire. In this procedure, you can check to see if any dogs were loaded into the list box. If there were not any, then the user is asked if they wish to add new dogs.
Private Sub Form_Activate()
Dim intResponse As Integer
' No Dogs On File
If lstNames.ListCount = 0 Then
' Clear Any Left Over Data
Call FormClear
' Ask User If They Wish To Add Dogs
intResponse = MsgBox("No Dogs, Add Some", _
vbYesNo + vbQuestion, _
Me.Caption)
If intResponse = vbYes Then
' Go into Add Mode
Call cmdNew_Click
Else
Unload Me
End If
End If
End Sub
If focus is transferred to another form, and the user clicks back on this form, the Form_Activate() event will be fired again.
After the form has been displayed, the user may now add, edit, or delete records using the different command buttons on the form. Let’s first look at the ability to add new records.
cmdNew_Click() event
To add new records, the user will click on the New command button. The Click event for this command button first clears all of the fields on the form to blank values. Next it will toggle the command buttons so that the Save and Cancel buttons are enabled. The string “Add” will be placed into the Tag property of the command button. This will be used when saving the data to determine if you are adding or editing data. Lastly, focus is set to the first control on the form you wish to have the user start entering data into:
Private Sub cmdNew_Click() ' Clear the Data Call FormClear ' Toggle any command buttons Call ToggleButtons ' Set flag for adding cmdNew.Tag = "Add" ' Set focus to the Dog's Name Field txtName.SetFocus End Sub
FormClear() procedure
The FormClear() routine is a very simple procedure that will clear all of the text boxes, combo boxes, and other controls to a valid blank state. Below is an example of a FormClear() routine:
Private Sub FormClear() lblID = "" txtName = "" txtColor = "" cboBreed.ListIndex = -1 cboBreeder.ListIndex = -1 txtBark = "" txtBirthDate = "" txtCost = "" txtPrice = "" optMale.Value = True End Sub
cmdSave_Click event
After the user finishes adding the dog’s information, or editing the dog’s information, they press the Save button to save their changes. The Click event of this command button calls a FormSave() function:
Private Sub cmdSave_Click() If FormSave() Then End If End Sub
FormSave() function
The FormSave() function will first take the data from the form and place it into the module level Dog object. It does this by calling a routine called FormMove. After the data is in the object, you simply need to tell the object what to do with this data. If you are adding data, you will invoke the AddNew() method on the object. If you are editing data, you will invoke the Replace() method on the data.
If either of these routines fails, a False value is returned. You may then display a message box with the actual informational/error message returned from the Dog object. Below is the code to the FormSave() function:
Private Function FormSave() As Boolean
FormSave = True
' Move Data into Dog Object
Call FormMove
If cmdNew.Tag = "Add" Then
' Add New Dog
If moDog.AddNew() Then
lstNames.AddItem moDog.DogName
lstNames.ItemData(lstNames.ListIndex) = moDog.DogId
' Highlight the New Entry
lstNames.ListIndex = lstNames.NewIndex
Else
Beep
MsgBox moDog.InfoMsg
End If
Else
' Edit Current Record
If moDog.Replace() Then
lstNames.List(lstNames.ListIndex) = txtName
Else
Beep
MsgBox moDog.InfoMsg
End If
End If
' Reset buttons and Add flag
Call ToggleButtons
cmdNew.Tag = ""
End Function
FormMove() procedure
This procedure is very simple. It takes the data form the form and puts it into the appropriate properties of the Dog object:
Private Function FormMove()
With moDog
.DogName = Field2Str(txtName)
.BreedId = cboBreed.ItemData(cboBreed.ListIndex)
.BreederId = cboBreeder.ItemData(cboBreeder.ListIndex)
.ColorName = Field2Str(txtColor)
.BarkType = Field2Str(txtBark)
.CostAmount = CCur(Val(txtCost))
.PriceAmount = CCur(Val(txtPrice))
.BirthDate = Field2Str(txtBirthDate)
If optMale Then
.Sex = "M"
Else
.Sex = "F"
End If
End With
End Function
To delete a record from the list of dogs, the user clicks on an entry in the list box, then presses the Delete command button. You write the code for this command button as follows:
Private Sub cmdDelete_Click()
Dim intResponse As Integer
Dim intIndex As Integer
' Ask user if they wish to really delete
intResponse = MsgBox("Delete Current Dog", _
vbYesNo + vbQuestion, _
Me.Caption)
If intResponse = vbYes Then
intIndex = lstNames.ListIndex
With moDog
.DogId = lstNames.ItemData(intIndex)
' Delete the Record
.Delete
End With
' Remove From List Box
lstNames.RemoveItem intIndex
intIndex = ListReposition(lstNames, intIndex)
' See if there are still records
If intIndex <> -1 Then
lstNames.ListIndex = intIndex
Else
Call Form_Activate
End If
End If
End Sub
Notice how the user is asked first if they really with to delete the dog. You must either ask them if they wish to delete the dog or provide them with an Undo feature. If they answer that they do wish to delete the record, then you load the DogID property with the primary key from the ItemData() property and invoke the Delete() method on the Dog object.
When the user is editing the data displayed on the form, you need a mechanism to determine when editing starts and when it stops. When editing begins, you will need to toggle the command buttons to a valid state. A valid state for the command buttons when editing is to disable the New and Delete buttons. Enable the Save button and change the Close button to a Cancel button. When editing stops, you need to change the command buttons to their original state.
Determining when the editing starts takes quite a bit of code. Here is a list of some of the ways you can change the data on a form and start the editing of data:
As you can see, there are many ways to change data on a data entry form. You will need to code for all of these occurences. Of course, once the editing mode has started, you don’t need to keep toggling the command buttons. This only needs to be accomplished one time. Turning off the editing mode is easy. Once the user clicks on the Save button and the data is saved to the base table, the buttons can now be toggled back to their original state.
Text Boxes
In each text box on your form, you will need to write some code for the Change event of that text box. Below is an example of the line you will write:
Private Sub txtName_Change() Call TextChanged End Sub
By adding this one line of code, you can call a procedure that will check to see if you are already in editing mode. If you are not, it will then call a routine to toggle the command buttons to a valid state for editing.
TextChanged() procedure
The TextChanged() procedure should be called from every control that can change the data in the base table. Below is the code for this routine:
Private Sub TextChanged()
If Not cmdSave.Enabled Then
If cmdSave.Tag <> "Show" Then
Call ToggleButtons
End If
End If
End Sub
This routine first checks to see if the Save button is already enabled. If it is, then you know that you are in an editing state. Next it checks to see if you are currently in the FormShow() procedure. Remember that putting new data into the text boxes from the Recordset object will fire the Change event of a text box. At the beginning of the FormShow() procedure, the string “Show” was placed into the cmdSave command buttons’ Tag property. This communicates to this routine not to toggle the command buttons. If however, this value is not set, then you know it is OK to call the ToggleButtons routine.
ToggleButtons() procedure
This procedure is responsible for toggling the buttons from edit mode to normal mode and vice versa:
Private Sub ToggleButtons()
lstNames.Enabled = Not lstNames.Enabled
cmdNew.Enabled = Not cmdNew.Enabled
cmdDelete.Enabled = Not cmdDelete.Enabled
cmdSave.Enabled = Not cmdSave.Enabled
If cmdClose.Caption = "&Close" Then
cmdClose.Caption = "&Cancel"
Else
cmdClose.Caption = "&Close"
End If
End Sub
Check boxes and option buttons
The user can change information by toggling these types of controls via the mouse. Check boxes and Option buttons are all inputs that can be set using the keyboard or the mouse. You will need to call the TextChanged() procedure from the Click event of these controls as well as from the Text Box controls. Below is the code you would write for the two Option buttons on the Dog form:
Private Sub optFemale_Click() Call TextChanged End Sub Private Sub optMale_Click() Call TextChanged End Sub
Combo boxes
For combo boxes that are a style 0 or a style 1, you can use the same type of code in the Change event as you did for text boxes:
Sub cboBreed_Change () Call TextChanged End Sub
There is a problem with this type of code for a combo box that is set to a Style of 2. The Change event is not called for this type of combo box. That is because the text portion of the combo box is not active, thus there is no change that occurs. The event that will fire when you change the data in a combo box is the Click event.
Now you may be tempted to write the following code in the Click event of the combo box:
Sub cboBreed_Click () Call TextChanged End Sub
However, there is another problem with this. Namely, that if the user just clicks on the combo box to see the values, but does not select another value, the Click event fires, which then toggles the command buttons to the editing state, even though the value did not change.
To fix this, you will need to write some slightly different code in the combo box. The trick is to first store the current value in the combo box Text property somewhere, then you can compare that value with the current Text property after the click event fires. If the values are different, then you know the user changed the data. If they are the same, you know that you don’t need to call the routine to toggle the command buttons.
First let’s take the value of the text portion of the combo box and put it somewhere. I will use the Tag property as a temporary holding location. The obvious place to write this code is immediately when the combo box receives focus:
Private Sub cboBreeder_GotFocus() cboBreeder.Tag = cboBreeder.Text End Sub
Next, you write the Click event like the following:
Private Sub cboBreeder_Click()
If cboBreeder.Tag <> cboBreeder.Text Then
Call TextChanged
cboBreeder.Tag = cboBreeder.Text
End If
End Sub
By checking the Tag property against the Text property, you can determine whether or not you need to call the TextChanged() procedure. After you have called the procedure, replace the Tag once again with the new value in the Text property.
There are times when you need to suppress events on a form. While there is no way to turn off these events, you can write them such that nothing happens. An example of this was already shown by setting the Tag property of the cmdSave command button to “Show” when you were putting data into the text boxes from the Recordset object.
Form_KeyDown() event
Another time you may need to turn off events is when the form is minimized on the MDI form. When a form is minimized, it may still have focus. This means the user can type into controls they cannot even see. To avoid this problem, you will need to do the following.
Set the Form’s KeyPreview property to True.
This allows the Form to preview all keystrokes prior to being given to the controls.
Next write the following code in the Form’s KeyDown event:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
' If form is minimized, don't allow editing
If Me.WindowState = vbMinimized Then
KeyCode = 0
End If
End Sub
In this routine, you check to see if the form’s WindowState property is minimized. If it is, set the KeyCode passed into this routine to 0. This cancels the keystroke. Thus, the user cannot change the form when it is minimized.
When the user requests to unload a form, you will need to do some checking to see if they can unload the form at that time. If a user is in the middle of editing data, you will want to ask them what they’d like to do with those edits prior to closing the form.
cmdClose_Click() event
The cmdClose command button is used for two purposes. It depends on what the caption of the button is at the time that determines its purpose. If the Caption property of the cmdClose button is set to “Close,” then the button will unload the form. If the Caption property is set to “Cancel,” then the button will cancel any editing done on the form and reread the Recordset object and put all of the original data back into the controls on the form.
Since this button is used for dual purposes, you need to code for both of those conditions in the button’s Click event:
Private Sub cmdClose_Click()
If cmdClose.Caption = "&Close" Then
Unload Me
Else
' Toggle the buttons to normal state
Call ToggleButtons
' Check to make sure there are
' Dog records
If lstNames.ListCount > 0 Then
Call FormShow
Else
Call Form_Activate
End If
End If
End Sub
As you can see from the above code, you will check the Caption property to see if the value is “Close.” If it is, you then unload the form. If it is not, you then toggle the buttons back to normal mode. Next you check to see if they cancelled an edit on an existing record. If the ListCount property of the list box is greater than zero, then you know that the Recordset object is still on a valid record, and you can just reread this data.
If there are no rows in the list box, this means that the user was adding the first record to this form. If they cancel the adding of the first row, then there are no rows to display, and thus you need to call the Form_Activate() event again to ask them if they want to add new dogs or not.
Form_QueryUnload() event
The Form_QueryUnload() event is called just prior to the Unload() event. This event will give you a chance to stop the unload process from happening. A form can be unloaded in several ways:
If the user is editing data and they close the form in any manner, then you should ask them what they want to do with the data.
Here is the code that you should write in every QueryUnload event on every data entry form:
Private Sub Form_QueryUnload(Cancel As Integer, _
UnloadMode As Integer)
Dim intResponse As Integer
' See if any changes have been made
If cmdSave.Enabled Then
' Ask user what they want to do
' with the changes
intResponse = FormCheckUnload(Me)
Select Case intResponse
Case vbYes
If Not FormSave() Then
Cancel = True
End If
Case vbCancel
Cancel = True
End Select
End If
End Sub
The QueryUnload() event checks the status of the cmdSave button. If it is enabled, then you call a function named FormCheckUnload(). Pass the current form object to this routine and it will display the MsgBox shown in the previous screen shot. Notice that the MsgBox uses the name of the form as its caption.
The user will then respond to this modal dialog box in one of three ways. This value is sent back to the QueryUnload() event and you then evaluate that response. If the Yes button is selected, then the user wants to save the data prior to unloading the form. Of course, to save the data, you need to check it first. If the data is checked and the business rules fail, then the form cannot be unloaded. By setting the Cancel parameter to True, you cancel the unloading of the form.
If the user responds with a No, then the form is simply unloaded and the data is discarded. If the user responds with Cancel, then the Cancel parameter is set to True signaling Visual Basic that this form’s Unload is being cancelled.
FormCheckUnload() function
The FormCheckUnload() function is responsible for making sure the form is in a normal window state. Next it will set that form’s ZOrder property to 0 to make sure it is on top of all other forms. Next, it will pop-up a message box and tell the user that the data has been changed on this form. The user may then choose from one of the three command buttons on this modal dialog form:
Function FormCheckUnload(frmName As Form) As Integer
Dim intResponse As Integer
If frmName.WindowState = vbMinimized Then
frmName.WindowState = vbNormal
End If
frmName.ZOrder 0
intResponse = MsgBox("Data Has Been Changed" & _
vbCrLf & "Save Changes ?", _
vbQuestion + vbYesNoCancel, _
frmName.Caption)
FormCheckUnload = intResponse
End Function
Form_Unload() event
If you have made it all the way through the QueryUnload() event, you are now in the Unload() event. In this event, you would perform any clean up like closing module level Recordset objects, closing database connections, or maybe releasing large module level string variables or other objects.
One of the features of Visual Basic is that module level variables are not destroyed when you unload a form. If you unload a form, then Show it again, the values will be the same value as they were the last time you left.
Since you may be creating several module level variables, including object variables, you must make sure to destroy those variables when you unload a form. The easiest way to accomplish this is to set the form name equal to Nothing:
Private Sub Form_Unload(Cancel As Integer) Set frmDogs = Nothing End Sub
Unfortunately you must use the actual form name here, you cannot use Me. Be sure to do this on every form, as this will help release memory back to Windows.
The AppQuit() function is called to close an application. Never put End statements anywhere in your application. There should only be one End statement in your whole application, and this should be in a routine called AppQuit():
Public Function AppQuit() ' This function should dereference ' any global objects you declare ' Disconnect from the database Call goConnect.DataClose Set goConnect = Nothing Set goPref = Nothing ' Stop the program End End Function
The reason for this function is to give you a chance to close all object database connections, dereference any other global objects you may have opened, and perform any other clean up you need to do for your application. Only after you have done all of this, should you call the End statement. As mentioned before, this forces all global objects to have their destructor functions called prior to being destroyed. If you do not set these objects to Nothing prior to calling the End statement, then none of the destructor functions will be called. If you look at the frmMain forms’ Unload event, you will see that this routine is called from there.
This part of our session presented an overview of OOPs. You learned to create some classes. You also learned how to create some very reusable classes.
In addition, you learned some techniques for handling data entry forms using a multitier architecture. You learned some of the standard routines you will build on every data entry form. You also learned how to handle loading list boxes and displaying data from a data access object. In addition, you learned how to handle keystrokes and mouse clicks within the form. Finally, you saw how to prevent a form from being unloaded if it was in a “dirty” state. These are techniques that you should use in every application you design.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft, Visual Basic, and Windows are registered trademarks of Microsoft Corporation.
Other product and company names listed herein may be the trademarks of their respective owners.