Today you'll learn how to create complete database entry forms using Visual Basic code instead of the data control. You'll learn how to open a database, establish a Recordset, and prepare a data entry form to allow records to be added, edited, and deleted. You'll also learn how to create a generic record locate routine to use with any data entry form, as well as how to create a set of command buttons to handle all data entry functions. You'll learn about the Visual Basic methods you can use to locate single records and about the Seek method for table objects and the Find and Move methods that you can apply to all Recordsets.
All the routines you create today are generic and portable. You write these routines in an OLE Server library module that you can use in your future database projects. For the lesson today, you'll add these library routines to a new form for the CompanyMaster database project you started last week. When you finish today's exercises, you'll be able to build a fully functional data entry form with less than 30 lines of Visual Basic code.
Before jumping into the code routines, you should know the difference between writing data entry programs with the Visual Basic data control and writing them without the Visual Basic data control. There are advantages and disadvantages to each method.
The advantage of using the data control is that you can quickly put together solid data entry forms without writing much Visual Basic code. This method works well for small, one-time projects that need to be completed quickly. The disadvantage of using the data control is that once the project is completed, it is not always easy to modify the data entry form or adapt the finished form for another data entry project. Also, forms built using the data control are not always easy to debug or maintain because most of the action goes on in the data control itself. If you think your project needs to be modified or maintained by other programmers, the data control might not be your best choice.
The advantage of using complete Visual Basic code to produce data entry forms is that you have total control over all aspects of the process. You decide when to open the database and Recordset, and you control the record read and write operations, too. This capability can be a real advantage in multiuser settings where increased traffic can cause locking conflicts in programs that use the data control. Another advantage of using Visual Basic code for your data entry forms is that you can create generic code that you can reuse in all your database projects. When you have a fully debugged set of data entry routines, you can quickly create new forms without much additional coding. Because the forms rely on generic routines, they are also easy to modify and maintain in the future.
The primary drawback for using Visual Basic code to create data entry forms is that you have to handle all processes yourself; you can assume nothing. For example, locating and updating a single record in a data table requires that you account for all of the following processes:
Add the possibility of user errors and database errors, and you have a good bit of responsibility! And you haven't even seen what you need to do to add a new record to the table or delete an existing one. You also need a way for the user to browse the data. Remember that dropping the data control means your form does not automatically display the VCR-style navigation arrows.
Despite this added responsibility, writing your data entry forms with Visual Basic code gives you much greater control over the process and can result in a form that is easy for both programmers and users to deal with. Even though you have to do a good bit of coding to create new data management routines, you can place most of this new code in an OLE Server DLL that can be reused in future projects with a minimum amount of coding.
Before you create the generic data entry routines, you need to examine an important topic, record searching. Up until now, we have only touched on this issue. You can use one of several methods to search for a record in a Recordset; some are faster than others. Using the most effective method in your Visual Basic programs can make your programs seem fast and solid. Using an ineffective search method can make your program seem slow.
The Visual Basic data-access object interface is a set-oriented interface. It is designed and tuned to quickly return a set of multiple records that meet your search criteria. However, a major part of data entry processing involves key-oriented searches. These are searches for a single, specific record that needs to be updated. Visual Basic offers the following three different approaches to handling key-oriented searches:
The Move methods offer the most basic form of record searching. There are four methods you can apply to the Recordset object:
To practice using these methods, start a new Visual Basic project. Save the form
as FRMMOVE.FRM and the project as PRJMOVE.VBP. Table 10.1 contains
a list of controls to add to the form. Refer to Figure 10.1 as a guide as you lay
out the form.
Figure
10.1. Laying out the frmMove form.
Table 10.1. Controls for project PRJMOVE.VBP.
| Control | Property | Setting |
| VB.Form | Name | frmMove |
| Caption | "MS Jet Move Methods" | |
| ClientHeight | 1470 | |
| ClientLeft | 60 | |
| ClientTop | 345 | |
| ClientWidth | 5400 | |
| VB.CommandButton | Name | cmdMoveLast |
| Caption | "&Last" | |
| Height | 300 | |
| Left | 4080 | |
| Top | 1080 | |
| Width | 1200 | |
| VB.CommandButton | Name | cmdMovePrevious |
| Caption | "&Previous" | |
| Height | 300 | |
| Left | 2760 | |
| Top | 1080 | |
| Width | 1200 | |
| VB.CommandButton | Name | cmdMoveNext |
| Caption | "&Next" | |
| Height | 300 | |
| Left | 1440 | |
| Top | 1080 | |
| Width | 1200 | |
| VB.CommandButton | Name | cmdMoveFirst |
| Caption | "&First" | |
| Height | 300 | |
| Left | 120 | |
| Top | 1080 | |
| Width | 1200 | |
| VB.Label | Name | Label2 |
| BorderStyle | 1 `Fixed Single | |
| Height | 315 | |
| Left | 120 | |
| Top | 600 | |
| Width | 2535 | |
| VB.Label | Name | Label1 |
| BorderStyle | 1 `Fixed Single | |
| Height | 315 | |
| Left | 120 | |
| Top | 180 | |
| Width | 1575 |
Option Explicit ` ` form-level vars ` Dim strDBName As String Dim strRSName As String Dim ws As Workspace Dim db As Database Dim rs As Recordset
Listing 10.2 shows the code that opens the database and then opens a Dynaset for
your use. Add this code to the Form_Load event.
Private Sub Form_Load()
`
` open db and rs objects
`
strDBName = App.Path & "\..\..\data\books5.mdb"
strRSName = "Authors"
`
Set ws = DBEngine.CreateWorkspace("dbTemp", "admin", "")
Set db = ws.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, dbOpenTable)
`
End Sub
This routine initializes the database and Recordset name variables and then creates
the related data objects. Performing this step is similar to setting the DatabaseName,
RecordSource, and RecordsetType properties of the data control.
You need to create a Sub procedure to handle the process of reading the current record and loading the data into the form controls. Create a Private Sub procedure called ReadRow and then add the following code to the routine:
Public Sub ReadRow()
`
` fill controls with current value
`
Label1.Caption = rs.Fields(0)
Label2.Caption = rs.Fields(1)
`
End Sub
This routine copies the first column in the current row of the Recordset to the first form control and then copies the second column of the Recordset to the second form control.
You need to create code for each of the four command buttons on the form. Each button needs to perform two tasks:
The four code pieces in Listing 10.3 do these tasks. Enter the code in that corresponds to the command button into the Click event of that command button. For example, enter rs.MoveFirst and ReadRow into the Click event of the cmdMoveFirst command button. Then enter rs.MoveLast and ReadRow into the cmdMoveLast command button, and so on.
Private Sub cmdMoveFirst_Click()
`
rs.MoveFirst
ReadRow
`
End Sub
Private Sub cmdMoveLast_Click()
`
rs.MoveLast
ReadRow
`
End Sub
Private Sub cmdMoveNext_Click()
`
rs.MoveNext
ReadRow
`
End Sub
Private Sub cmdMovePrevious_Click()
`
rs.MovePrevious
ReadRow
`
End Sub
You need to add two more routines to finish up the project. The following code forces
the first record onto the screen at startup. Add this code to the Form_Activate
event:
Private Sub Form_Activate()
`
cmdMoveFirst_Click
`
End Sub
The last bit of code performs a safe close of the database at the end of the program. Add this code to the Form_Unload event:
Private Sub Form_Unload(Cancel As Integer)
`
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
`
End Sub
Save the form as FRMMOVE.FRM and save the project as PRJMOVE.VBP. When you run the project, you can click the buttons in order to walk the dataset. This project operates the same as the data control arrow buttons.
NOTE: If you click the First button and then immediately click the Previous button, you get a runtime error. This error is caused by attempting to read past the beginning of the dataset. Later today, you'll create a routine that prevents this error from occurring in your programs.
The project you created in this section is a good example of how you can provide users with a way to browse the dataset on a form. In the next section, you see how to give your users the ability to search for a particular record in the dataset.
The fastest way to locate a specific record is to use the Seek method on a table object. The Seek method performs an indexed search for the first occurrence of the record that matches the index criteria. This search uses the type of index used by ISAM-type databases. Indexed searches are easy to perform and are very fast.
Modify the PRJMOVE.VBP project to illustrate index searching by adding another button to the form. Set the button's Name property to cmdSeek and its Caption property to &Seek. Next, add Listing 10.4 to the cmdSeek_Click event.
Private Sub cmdSeek_Click()
`
` use the seek method to locate a record
`
Dim strSeek As String
`
strSeek = InputBox("Enter an Author ID Seek Value:", "Table Seek", "10")
strSeek = Trim(strSeek)
`
If strSeek <> "" Then
rs.Seek "=", strSeek
If rs.NoMatch = True Then
MsgBox "Unable to locate [" & strSeek & "]", vbExclamation, "Table Seek Failed"
Else
ReadRow
MsgBox "Found [" & strSeek & "]", vbInformation, "Table Seek Succeeded"
End If
End If
`
End Sub
Listing 10.4 does three things. First, it prompts the user to enter a value for which
to search. Second, the code confirms that the user entered a value and then performs
the Seek operation. After performing the Seek operation, the code
uses the NoMatch method to get the results of the Seek operation
(this is the third operation performed in this routine). The results of the search
are then posted in a message box. If the search was successful, the new record is
loaded into the form controls.
To make this routine work, you have to make a few changes to code in the Form_Load event. Change vbOpenDynaset to vbOpenTable, and then add the following line to the end of the routine, just after the OpenRecordset line:
rs.Index = "PrimaryKey" ` set index property
Now save and run the project. This time, click the Seek button. When the dialog
box appears, accept the default value of 10 and click OK. You should see a message
telling you that the search was successful (see Figure 10.2).
Figure
10.2. The results of the Seek
method on a table object.
TIP: You can use other comparison values besides = with the Seek method. You can use <, <=, =, >=, or > as a comparison value.
Although Seek is the fastest search method, you can apply it only to Recordsets opened as table objects. If you want to locate a specific record in a Dynaset or Snapshot, use one of the Find methods.
Because Dynaset and Snapshot objects do not use indexes, you cannot use the Seek method to search for specific records within them. The Find method is used to locate specific records in non-table objects (Dynasets and Snapshots). The Find method is a sequential search; it starts at the beginning of the dataset and looks at each record until it finds one that matches the search criteria. Although this method is not as fast as Seek, it is still faster than using the Move methods to handle this operation within your own Visual Basic code.
The syntax for the Find methods is almost identical to the SQL WHERE clause (covered in Day 8). The search string consists of a field (or set of fields) followed by a comparison operator (=,<>, and so on) and a search value (for example, MyRS.FindFirst "Au_ID=13").
There are actually four Find methods: FindFirst, FindPrevious, FindNext, and FindLast. The FindFirst method starts its search from the beginning of the file. The FindLast method starts its search from the end of the file and works its way to the beginning. You can use the FindPrevious and FindNext methods to continue a search that can return more than one record. For example, if you are looking for all the records that have their ZipCode column set to 99999, you could use the FindFirst method to locate the first record and then use the FindNext method to continue the search forward until you reach the end of the dataset. Similarly, you can use the FindLast and FindPrevious methods to perform continued searches starting at the end of the dataset. Although the FindNext and FindPrevious methods are available, it is usually better to create a new Recordset using the Find criteria if you expect to locate more than one record that meets the criteria.
Modify the PRJMOVE.VBP project to illustrate the Find method by adding another button to the project. Set the button's Name property to cmdFind and its Caption property to F&ind. Next, add the code in Listing 10.5 to the cmdFind_Click event.
Private Sub cmdFind_Click()
`
` use the find method for non-table searches
`
Dim strFind As String
`
strFind = InputBox("Enter an Author ID to Find:", "Non-table Find", "13")
strFind = Trim(strFind)
`
If strFind <> "" Then
strFind = "AUID=" & strFind
rs.FindFirst strFind
`
If rs.NoMatch = True Then
MsgBox "Unable to locate [" & strFind & "]", vbExclamation, "Non-ÂTable Find Failed"
Else
ReadRow
MsgBox "Found [" & strFind & "]", vbInformation, "Non-table Find ÂSucceeded"
End If
End If
`
End Sub
Listing 10.5 is almost identical to the code used in the cmdSeek_Click event
(refer to Listing 10.4). Notice that you have to build the criteria string to include
the name of the field you are searching. Because the Find method can be
applied to any field (or fields) in the table, you must supply the field in the search
criteria.
Before saving the project, comment out the line in the Form_Load event
that sets the index. Also, change dbOpenTable to dbOpenSnapshot. Now save and run
the project. When you click the Find button, enter 13 in the input box.
You should see a message telling you that the Find operation was successful
(see Figure 10.3).
Figure
10.3. The results of the non-table Find
method.
Notice that if you click the Seek button, you eventually get an error message. You
cannot apply a Seek method to a non-table object. Also, you cannot apply
a Find method to a table object. Later, you'll learn how to write a single
locate routine that is smart enough to figure out which search method to use for
your Recordset object.
Up to this point, you have been creating your Visual Basic database programs by using the data control as the heart of the system. After learning about the Microsoft JET data engine and covering some basics on searching techniques, you are now ready to create an OLE Server library that allows you to build solid data entry forms without using the data control. The rest of this day is devoted to constructing this OLE Server library.
NOTE: A finished version of the RecObject OLE Server library is contained in the RECOBJECT.CLS class file on the CD that comes with this book.
There is a series of operations that must be handled for any data entry system. First, let's outline these operations, and then you can use that outline as a guide in constructing your library functions. The following is a list of common operations used in almost all data entry forms:
In addition to the record-handling routines, you also build a set of routines to design and manage a command button toolbar. This toolbar provides access to basic data entry functions such as add, edit, delete, and locate, as well as the four browse actions: first, next, previous, and last moves. These three additional routines handle the actions that involve the command buttons:
You design these routines to work with any dataset you select, as well as any form layout you choose, using any input controls (not just the Visual Basic data-bound controls). Also, you construct the routines as a set of methods within the standalone OLE Server. That way, you can add the record-handling routines to all your future programming projects.
Finally, the OLE Server library has a handful of properties that you can use to control the behavior of the record-processing routines. The following is a list of the properties you need with the OLE Server:
In the following sections, you go through the process of building the code library. After the library is built, you build a simple form to add to the CompanyMaster project. This form uses all the library functions covered in this section.
The routines we have designed make a few assumptions about how your data entry forms are constructed. These assumptions are very general and result in a solid, if not flashy, data entry form. After completing these routines, you might want to modify the library functions to add additional features and options that suit your particular data entry needs.
For each data entry form you design using these routines, you need to stay within the following guidelines:
After incorporating these guidelines, you can lay out your forms in any manner you like.
Begin this project by building the library of record-handling functions. Start a new Visual Basic 5.0 ActiveX DLL project. Set the class name to recObject by filling the Name property of the class module.
TIP: Be sure to set the Option Explicit option to On for this project. This option forces you to declare all variables before they are used in your program. Using the Option Explicit setting helps reduce the number of program bugs you create as you enter these routines.
Before you begin the heavy coding, complete the declaration section of the library routine. Enter Listing 10.6 at the top of the module.
Option Explicit
`
` local enumerations
` recordset types
Enum rsType
rsTableType = dbOpenTable
rsSnapShotType = dbOpenSnapshot
rsDynasetType = dbOpenDynaset
End Enum
`
` button alignments
Enum bbAlign
bbTop = 0
bbBottom = 1
bbLeft = 2
bbRight = 3
End Enum
`
` private property storage
Private strWSName As String ` local workspace name
Private strDBName As String ` local database name
Private strRSName As String ` local recordset name/SQL
Private strIndex As String ` local index name
Private blnIndex As Boolean ` use index flag
Private intBBAlign As Integer ` button aligment
Private strFocus As String ` field to get first focus
`
Private ws As workspace
Private db As Database
Private rs As Recordset
Private intRSType As rsType
The first two enumerated values in Listing 10.6 are used throughout the routines
to indicate the types of datasets and the location of the button bar set on the form.
The rest of the values represent local storage for public properties of your OLE
Server class. After you have entered the code in Listing 10.6, save the module as
RECOBJECT.CLS.
Now that you've created the local storage for the properties, you can use the Tools | Add Procedure menu option to create Public property procedures, too. Listing 10.7 shows the code for all the property-handling routines in the library. Use the Property names as a guide in creating the properties with the Tools | Add Procedure menu and then enter a associated code into each of the Property Let and Get methods.
Public Property Get DBName() As Variant
DBName = strDBName
End Property
Public Property Let DBName(ByVal vNewValue As Variant)
strDBName = vNewValue
End Property
Public Property Get RSName() As Variant
RSName = strRSName
End Property
Public Property Let RSName(ByVal vNewValue As Variant)
strRSName = vNewValue
End Property
Public Property Get dbObject() As Variant
dbObject = db
End Property
Public Property Let dbObject(ByVal vNewValue As Variant)
` na
End Property
Public Property Get wsObject() As Variant
wsObject = ws
End Property
Public Property Let wsObject(ByVal vNewValue As Variant)
` na
End Property
Public Property Get rsObject() As Variant
rsObject = rs
End Property
Public Property Let rsObject(ByVal vNewValue As Variant)
` na
End Property
Public Property Get WSName() As Variant
WSName = strWSName
End Property
Public Property Let WSName(ByVal vNewValue As Variant)
strWSName = vNewValue
End Property
Public Property Get rsType() As rsType
rsType = intRSType
End Property
Public Property Let rsType(ByVal vNewValue As rsType)
intRSType = vNewValue
End Property
Public Property Get Index() As Variant
Index = strIndex
End Property
Public Property Let Index(ByVal vNewValue As Variant)
strIndex = vNewValue
End Property
Public Property Get IndexFlag() As Boolean
IndexFlag = blnIndex
End Property
Public Property Let IndexFlag(ByVal vNewValue As Boolean)
blnIndex = vNewValue
End Property
Public Property Get BtnBarAlign() As bbAlign
BtnBarAlign = intBBAlign
End Property
Public Property Let BtnBarAlign(ByVal vNewValue As bbAlign)
intBBAlign = vNewValue
End Property
Public Property Get RSFocus() As Variant
RSFocus = strFocus
End Property
Public Property Let RSFocus(ByVal vNewValue As Variant)
strFocus = vNewValue
End Property
Next you need to add the code for the Class_Initialize and the Class_Terminate
events. See Listing 10.8 for the code for these two events.
Private Sub Class_Initialize()
`
` set inital values
`
intRSType = rsDynasetType
strWSName = "wsTemp"
strDBName = ""
strRSName = ""
`
intBBAlign = bbTop
`
End Sub
Private Sub Class_Terminate()
`
` close out class
`
On Error Resume Next
`
rs.Close
db.Close
ws.Close
Set rs = Nothing
Set db = Nothing
Set ws = Nothing
`
End Sub
Now that you have dealt with the properties, you're ready to start coding the main
record-handling routines. The next several sections contain the code for all the
record-handling routines.
The RSOpen routine handles the opening of an existing database and the creation of a Recordset to hold the selected records. Enter Listing 10.9 into the class module. Be sure to include the Function declaration line. Visual Basic supplies the End Function line automatically.
TIP: You should save your work after entering each coding section to ensure that you do not lose much work if your computer suffers an unexpected crash.
Public Function RSOpen(frmTemp As Object)
`
` create ws, db, and rs objects
`
On Error GoTo LocalErr
`
Dim lngResult As Long
`
Set ws = dbengine.createworkspace(WSName, "admin", "")
Set db = ws.OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strRSName, intRSType)
`
lngResult = RSInit(frmTemp)
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
`
RSOpen = lngResult
Exit Function
`
LocalErr:
RSOpen = Err.Number
`
End Function
This routine accepts the user's form as a parameter, uses the property values to
create a complete database and Recordset connection, and then initializes the data
entry form and fills it with the first record in the dataset.
Another new twist here is that almost all the routines in this library are declared as Functions instead of Subs. These functions return an integer value that indicates whether any errors occurred during the operation. This value gives you a very easy way to check for errors from within Visual Basic code. Note that any error number returned by the Visual Basic code is sent back to the user's program for handling. This is a simple way to pass internal errors out of the class module into the caller's routine.
TIP: You should comment out the On Error lines of your program while you are first entering the Visual Basic code. When the error trap is on, even simple typing errors set it off. During the construction phase, you want the Visual Basic interpreter to halt and give you a full error message. When you are sure you have eliminated all the programming bugs, you can activate the error handlers by removing the comment mark from the On Error program lines.
The RSInit routine clears out any stray values that might exist in the form controls that you are binding to your data table. Remember that you can bind a form control to a dataset column by placing the name of the column in the Tag property of the field. This routine checks that property and, if it contains information, initializes the control to prepare it for receiving dataset values. Enter the code in Listing 10.10 as a new function.
Public Function RSInit(frmTemp As Object)
`
` clear all input controls on the form
`
On Error GoTo LocalErr
`
Dim ctlTemp As Control
Dim strTag As String
`
For Each ctlTemp In frmTemp.Controls
strTag = UCase(Trim(ctlTemp.Tag))
If strTag <> "" Then
ctlTemp = ""
End If
Next
`
RSInit = 0
Exit Function
`
LocalErr:
RSInit = Err.Number
`
End Function
This routine contains a simple loop that checks all the controls on the form to see
whether they are bound to a dataset column. If they are, the control is initialized.
The RSLocate routine prompts the user to enter a value to use as a search criteria on the Recordset. The routine is smart enough to use the Seek method for table objects and the Find method for non-table objects. Add the routine in Listing 10.11 to your module.
Public Function RSLocate(FieldName As String)
`
` search the designated field
`
On Error GoTo LocalErr
`
Dim strSearch As String
`
If blnIndex = True Then
rs.Index = strIndex
End If
`
strSearch = InputBox("Enter Search Value:", "Searching " & FieldName)
strSearch = Trim(strSearch)
`
If strSearch = "" Then
RSLocate = False
Exit Function
End If
`
If rs.Fields(FieldName).Type = dbText Then
strSearch = "`" & strSearch & "`"
End If
`
If blnIndex = True Then
rs.Seek "=", strSearch
Else
rs.FindFirst FieldName & "=" & strSearch
End If
`
If rs.NoMatch = True Then
RSLocate = False
Else
RSLocate = True
End If
`
Exit Function
`
LocalErr:
RSLocate = Err.Number
`
End Function
Notice that if you set the IndexFlag property to True in this routine,
the routine uses the Seek method instead of a sequential Find method.
Also note the check for a text-type search field. If the target field to search has
a dbText data type, the search values are enclosed in single quotes.
Now you get one of the important routines! The RSRead routine takes values from the current record of the dataset and loads them into controls on the form. This is done by checking all the controls on the form for a nonblank Tag property. If a control has a value in the Tag property, the routine assumes that the value is a column name for the dataset. The value in this column is then copied from the dataset into the form control. Add this new routine (shown in Listing 10.12) to your library. Note that this routine is built as a Private Function. You do not want external programs to be able to invoke this function directly.
Private Function RSRead(frmTemp As Object)
`
` move data from recordset to form
`
On Error GoTo LocalErr
`
Dim ctlTemp As Control
Dim strTag As String
Dim strFldName As String
`
For Each ctlTemp In frmTemp.Controls
strTag = UCase(Trim(ctlTemp.Tag))
If strTag <> "" Then
If IsNull(rs.Fields(strTag)) = False Then
ctlTemp = rs.Fields(strTag)
End If
End If
Next
`
RSRead = 0
Exit Function
`
LocalErr:
RSRead = Err.Number
`
End Function
This routine and the next routine (RSWrite) are the heart of the record-handling
functions. When you understand how these routines work, you can build your own customized
routines for handling dataset read and write operations.
The routine in Listing 10.13 performs the opposite function of RSRead (see Listing 10.12). Again, it's a simple loop through all the controls on the form. If a control is bound to a data column, the value in the control is copied to the dataset column for storage.
NOTE: Before you can write to a dataset, you need to invoke the Edit or AddNew methods. After the write operation, you must invoke the Update method to save the changes. You handle these operations in the button set routines later in today's lesson.
Private Function RSRead(frmTemp As Object)
`
` move data from recordset to form
`
On Error GoTo LocalErr
`
Dim ctlTemp As Control
Dim strTag As String
Dim strFldName As String
`
For Each ctlTemp In frmTemp.Controls
strTag = UCase(Trim(ctlTemp.Tag))
If strTag <> "" Then
If IsNull(rs.Fields(strTag)) = False Then
ctlTemp = rs.Fields(strTag)
End If
End If
Next
`
RSRead = 0
Exit Function
`
LocalErr:
RSRead = Err.Number
`
End Function
Private Function RSWrite(frmTemp As Object)
`
` move values in controls to data set
`
On Error GoTo LocalErr
`
Dim ctlTemp As Control
Dim strTag As String
Dim lngAttrib As Long
`
For Each ctlTemp In frmTemp.Controls
strTag = UCase(Trim(ctlTemp.Tag))
If strTag <> "" Then
lngAttrib = rs.Fields(strTag).Attributes
If (lngAttrib And dbAutoIncrField) = 0 Then
If rs.Fields(strTag).DataUpdatable = True Then
rs.Fields(strTag) = ctlTemp
End If
End If
End If
Next
`
RSWrite = 0
Exit Function
`
End Function
An added feature in this routine deserves mention. Because Visual Basic does not
allow you to write to COUNTER data type fields, this routine checks the Attributes
property of each bound column before attempting an update. If the field is a COUNTER
data type, the routine does not attempt to write data to the column. Note again that
the RSWrite routine is built as a Private Function. This function
can be executed only by other methods within your OLE Server class.
To simplify the management of data entry routines, your form allows users to update form controls only after they select the Edit or Add buttons on a form. The RSEnable routine gives you an easy way to turn on or off the Enabled property of all the bound controls on your form. You call this routine often from your button set routines. Add Listing 10.14 to the library.
Public Function RSEnable(frmTemp As Object, Toggle As Boolean)
`
` toggle the controls on/off
`
Dim ctlTemp As Control
Dim strTag As String
`
For Each ctlTemp In frmTemp.Controls
strTag = UCase(Trim(ctlTemp.Tag))
If strTag <> "" Then
ctlTemp.Enabled = Toggle
End If
If UCase(Trim(ctlTemp.Tag)) = UCase(Trim(strFocus)) Then
If Toggle = True Then
ctlTemp.SetFocus
End If
End If
Next
`
RSEnable = 0
Exit Function
`
LocalErr:
RSEnable = Err.Number
`
End Function
Notice that the RSEnable routine checks the Focus property to see which
input field should get the initial focus on the form.
The RSDelete routine performs a delete operation on the selected data record. But before committing the deed, the user is given a chance to reverse the process. Add Listing 10.15 to the library.
Private Function RSDelete()
`
` delete current record
`
Dim lngResult As Long
`
lngResult = MsgBox("Delete current record?", vbYesNo + vbQuestion, rs.Name)
If lngResult = vbYes Then
rs.Delete
End If
`
RSDelete = 0
Exit Function
`
LocalErr:
RSDelete = Err.Number
`
End Function
You need three more routines to complete the record-handling portion of the library. RSClose handles the final closing of the record-handling routines; RSBack and RSNext provide a safe way to process Visual Basic MovePrevious and MoveNext operations without encountering end-of-file errors from Visual Basic. Add these three routines, which are provided in Listing 10.16, to the library.
Public Sub RSClose()
`
` close down object
`
Class_Terminate
`
End Sub
Private Function RSBack()
`
` move back one record
`
If rs.BOF = True Then
rs.MoveFirst
Else
rs.MovePrevious
If rs.BOF Then
rs.MoveFirst
End If
End If
`
RSBack = 0
Exit Function
`
LocalErr:
RSBack = Err.Number
`
End Function
Private Function RSNext()
`
` move to next record
`
If rs.EOF = True Then
rs.MoveLast
Else
rs.MoveNext
If rs.EOF Then
rs.MoveLast
End If
End If
`
RSNext = 0
Exit Function
`
LocalErr:
RSNext = Err.Number
`
End Function
You have just completed the record-handling portion of the library. There are only
three routines left to build. These three routines provide the button set that users
see when they perform data entry operations on your form.
The next three routines handle all the operations needed to add a complete set of command buttons to your data entry form. You can use this set for any data entry form that provides the basic add, edit, delete, find, and browse operations needed for most data entry routines.
Warning: To make these routines work with your programs, you must add a single command button to your form called cmdBtn. Its Index property must be set to 0 to indicate that it is part of a control array. The details of constructing a working form are covered in the "Creating a Data Entry Form with the Library Routines" section of this lesson.
The BBInit routine builds the details of the command button array and places that array on your data entry form. You must first place a single command button on the target form with its Name property set to cmdBtn and its Index property set to 0. This routine creates seven more command buttons, sets their captions and sizes, and places the button set on the top, bottom, left, or right side of the form. You control this feature by setting the BtnBarAlign property you defined earlier. Add this routine (in Listing 10.17) to the OLE Server library module that contains the record-handling routines.
Public Function BBInit(frmTemp As Object)
`
` initialize a button bar on the form
`
Dim intBtnWidth As Integer
Dim intBtnTop As Integer
Dim intBtnleft As Integer
Dim intBtnHeight As Integer
Dim intLoop As Integer
Dim varCap As Variant
`
varCap = Array("&Add", "&Edit", "&Del", "&Find", "&Top", "&Next", Â"&Back", "&Last")
`
` compute btn locations
intBtnWidth = 660
intBtnHeight = 300
`
Select Case intBBAlign
Case bbTop
intBtnTop = 60
intBtnWidth = (frmTemp.ScaleWidth - 60) / 8
If intBtnWidth < 660 Then intBtnWidth = 660
intBtnHeight = 300
Case bbBottom
intBtnTop = frmTemp.ScaleHeight - 360
intBtnWidth = (frmTemp.ScaleWidth - 60) / 8
If intBtnWidth < 660 Then intBtnWidth = 660
intBtnHeight = 300
Case bbLeft
intBtnWidth = 660
intBtnleft = 60
intBtnHeight = (frmTemp.ScaleHeight - 60) / 8
If intBtnHeight < 300 Then intBtnHeight = 300
Case bbRight
intBtnWidth = 660
intBtnleft = frmTemp.ScaleWidth - 720
intBtnHeight = (frmTemp.ScaleHeight - 60) / 8
If intBtnHeight < 300 Then intBtnHeight = 300
End Select
`
` now place buttons on the form
For intLoop = 0 To 7
If intBBAlign = bbTop Or intBBAlign = bbBottom Then
intBtnleft = intLoop * intBtnWidth
Else
intBtnTop = (intLoop * intBtnHeight) + 60
End If
`
On Error Resume Next
With frmTemp
If intLoop <> 0 Then
Load .cmdbtn(intLoop)
End If
.cmdbtn(intLoop).Width = intBtnWidth
.cmdbtn(intLoop).Left = intBtnleft
.cmdbtn(intLoop).Top = intBtnTop
.cmdbtn(intLoop).Height = intBtnHeight
.cmdbtn(intLoop).Caption = varCap(intLoop)
.cmdbtn(intLoop).Visible = True
End With
Next
`
BBInit = 0
Exit Function
`
LocalErr:
BBInit = Err.Number
`
End Function
Listing 10.17 uses the data form's dimensions to calculate the location and size
of the command buttons in the button set. You create a working example of this form
in the section "Creating a Data Entry Form with the Library Routines."
The BBEnable routine is a short routine that allows you to toggle the Enabled property of the command buttons in the button set. This routine is used to turn on or off selected buttons during edit or add operations. Add the routine in Listing 10.18 to the library.
Public Function BBEnable(frmTemp As Object, strList As String)
`
` enable buttons
`
On Error GoTo LocalErr
`
Dim intLoop As Integer
`
strList = Trim(strList)
`
For intLoop = 1 To Len(strList)
If Mid(strList, intLoop, 1) = "1" Then
frmTemp.cmdbtn(intLoop - 1).Enabled = True
Else
frmTemp.cmdbtn(intLoop - 1).Enabled = False
End If
Next
`
BBEnable = 0
Exit Function
`
LocalErr:
BBEnable = Err.Number
`
End Function
The routine works by accepting a series of eight 1s and 0s. Each position in the
eight-byte string represents one of the button bar buttons. If the value is set to
1, the button is enabled. If the value is set to 0, the button
is disabled.
The BBProcess routine handles all the button actions initiated by the user and makes many calls to the other routines in the library. This routine is the high-level method of the class module; it is also the most involved routine in this library. It might look intimidating at first glance. But, after you inspect the first several lines, you see a pattern developing. More than half of the routine is devoted to handling the browse buttons (First, Back, Next, and Last). The rest is used to handle the add, edit, find, and delete operations. Enter Listing 10.19 into the library.
Public Function BBProcess(frmTemp As Object, intBtn As Integer, strSearch As ÂString)
`
` handle all button clicks
`
On Error GoTo LocalErr
Dim lngResult As Long
`
Select Case intBtn
Case 0 ` add/save/cancel
Select Case frmTemp.cmdbtn(intBtn).Caption
Case "&Save" ` save new
lngResult = RSWrite(frmTemp)
If lngResult = 0 Then
rs.Update
End If
If lngResult = 0 Then
lngResult = RSInit(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSEnable(frmTemp, False)
End If
If lngResult = 0 Then
frmTemp.cmdbtn(0).Caption = "&Add"
frmTemp.cmdbtn(1).Caption = "&Edit"
End If
Case "&Add" ` add new
rs.AddNew
lngResult = RSInit(frmTemp)
If lngResult = 0 Then
lngResult = RSEnable(frmTemp, True)
End If
If lngResult = 0 Then
frmTemp.cmdbtn(0).Caption = "&Save"
frmTemp.cmdbtn(1).Caption = "&Cancel"
BBEnable frmTemp, "11000000"
End If
Case "&Cancel" ` cancel edit
rs.CancelUpdate
frmTemp.cmdbtn(0).Caption = "&Add"
frmTemp.cmdbtn(1).Caption = "&Edit"
BBEnable frmTemp, "11111111"
`
lngResult = RSInit(frmTemp)
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSEnable(frmTemp, False)
End If
End Select
Case 1 ` edit/save/cancel
Select Case frmTemp.cmdbtn(1).Caption
Case "&Save" ` save edit
rs.Edit
lngResult = RSWrite(frmTemp)
If lngResult = 0 Then
rs.Update
End If
If lngResult = 0 Then
lngResult = RSEnable(frmTemp, False)
End If
If lngResult = 0 Then
frmTemp.cmdbtn(0).Caption = "&Add"
frmTemp.cmdbtn(1).Caption = "&Edit"
BBEnable frmTemp, "11111111"
End If
Case "&Edit" ` edit existing
lngResult = RSEnable(frmTemp, True)
If lngResult = 0 Then
frmTemp.cmdbtn(0).Caption = "&Cancel"
frmTemp.cmdbtn(1).Caption = "&Save"
BBEnable frmTemp, "11000000"
End If
Case "&Cancel" ` cancel new
rs.CancelUpdate
frmTemp.cmdbtn(0).Caption = "&Add"
frmTemp.cmdbtn(1).Caption = "&Edit"
BBEnable frmTemp, "11111111"
`
lngResult = RSInit(frmTemp)
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSEnable(frmTemp, False)
End If
End Select
`
If lngResult = 0 Then
lngResult = RSInit(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
Case 2 ` delete rec
lngResult = RSDelete()
If lngResult = 0 Then
lngResult = RSEnable(frmTemp, False)
End If
If lngResult = 0 Then
lngResult = RSNext()
End If
If lngResult = 0 Then
lngResult = RSInit(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
BBEnable frmTemp, "11111111"
Case 3 ` find
lngResult = RSLocate(strSearch)
If lngResult = True Then
lngResult = RSInit(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
BBEnable frmTemp, "11111111"
Case 4 ` move to top
rs.MoveFirst
lngResult = RSInit(frmTemp)
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
BBEnable frmTemp, "11111111"
Case 5 ` move next
lngResult = RSNext()
If lngResult = 0 Then
lngResult = RSInit(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
BBEnable frmTemp, "11111111"
Case 6 ` move previous
rs.MovePrevious
lngResult = RSBack()
If lngResult = 0 Then
lngResult = RSInit(frmTemp)
End If
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
BBEnable frmTemp, "11111111"
Case 7 ` move last
rs.MoveLast
lngResult = RSInit(frmTemp)
If lngResult = 0 Then
lngResult = RSRead(frmTemp)
End If
BBEnable frmTemp, "11111111"
End Select
`
BBProcess = 0
Exit Function
`
LocalErr:
BBProcess = Err.Number
`
End Function
NOTE: The routine in Listing 10.19 is the last library function you'll be adding. Be sure to save the updated library file to disk before exiting Visual Basic.
Several aspects of Listing 10.19 need review. First, because you are using a command button array, all operations are dependent on which button was pushed. The outer Select Case structure handles the action. The comment lines show what each button is labeled. However, the captions (and functions) of the first two buttons (Add and Edit) can change during the course of the data entry process. Therefore, these two options have an additional Select Case to check the caption status of the selected button.
There are a great number of If..End If blocks in the code. These blocks are present because you are constantly checking the results of previous actions. They clutter up the code a bit, but they provide solid error-checking capability and program flow control.
Each main section of the outer Select Case performs all the operations needed to complete a user action. For example, the very first set of operations in the routine is the completion of the save operation for an Add command. If you ignore the constant checks of the nResult variable, you see that the essence of this section of the code is as follows:
The save operation is the most complicated process. The locate, delete, and browse operations are much easier to accomplish and require less coding. The key to remember here is that you are providing all the user-level processes of the data control in this set of Visual Basic code. Although it seems to be a large code piece, you can use it in all your Visual Basic projects once you have it on file.
Compile the OLE Server library so you can use it later. Select File | Make prjRecObject.DLL from the main Visual Basic menu and compile the DLL. In future projects, all you need to do is add a reference to this new object, and you'll be ready to create complete data entry forms with very little coding.
Now that you have a solid library set for creating data entry forms, you can build a new form for the CompanyMaster project. To do this, you add a new form to the CompanyMaster project. This form is a simple validation list that you can use to validate input for other portions of the project.
If you haven't done it yet, start Visual Basic and load the MASTER.VBP
project. This project is a copy of the project you built last week. The first thing
you must do is add a reference to the prjRecObject.DLL in the CompanyMaster
project. Select Project | References from the main menu and then locate and select
prjRecObject.DLL (see Figure 10.4).
Figure
10.4. Locating and selecting the prjRecObject
OLE Server DLL.
NOTE: The CD that ships with the book has a completed version of the library. The CompanyMaster that ships with the CD may also have a reference to the old prjRecObject.DLL instead of a pointer reference to your newer version. If you receive errors loading the CompanyMaster project, ignore them and load the new prjRecObject.DLL as planned.
Before you add the new form, you need to add a short menu to the CompanyMaster
main form. You use this menu to call the new form. Open the frmMaster form
and add the menu items listed in Table 10.2. You can also refer to Figure 10.5 as
a guide for building the menu.
Figure
10.5. Adding items to the menu.
Table 10.2. Menu items for the frmMaster form.
| Caption | Menu |
| &File | mnuFile |
| E&xit | mnuFileExit |
| &Lists | mnuList |
| &State/Prov | mnuListStProv |
Private Sub mnuFileExit_Click()
cmdExit_Click ` do the exit!
End Sub
This code calls the existing routine that handles the program exit.
Now you need to add the line of code that calls the new form you are going to create. Enter the following code for the State/Prov menu item:
Private Sub mnuListStProv_Click()
frmStProv.Show 1
End Sub
This code calls the new form and forces it to display as a modal form. Because it is modal, users cannot change the focus within their project until they safely exit this form.
Now that the housekeeping is done, you can build the new form. Use Table 10.3
and Figure 10.6 as guides as you lay out the new validation form.
Table 10.3. Controls for the State/Province list form.
| Control | Property | Setting |
| VB.Form | Name | frmStProv |
| Caption | "State/Province Validation Table" | |
| ClientHeight | 2220 | |
| ClientLeft | 60 | |
| ClientTop | 345 | |
| ClientWidth | 5895 | |
| StartUpPosition | 3 `Windows Default | |
| VB.CommandButton | Name | cmdBtn |
| Index | 0 | |
| VB.TextBox | Name | Text2 |
| Height | 255 | |
| Left | 1440 | |
| Top | 540 | |
| Width | 2775 | |
| VB.TextBox | Name | Text1 |
| Height | 255 | |
| Left | 1440 | |
| Top | 240 | |
| Width | 915 | |
| VB.Label | Name | Label2 |
| Caption | "Complete Name" | |
| Height | 300 | |
| Left | 120 | |
| Top | 600 | |
| Width | 1200 | |
| VB.Label | Name | Label1 |
| Caption | "St/Prov Code" | |
| Height | 300 | |
| Left | 120 | |
| Top | 240 | |
| Width | 1200 |
Figure
10.6. Laying out the State/Province form.
Next, add the code fragments that make this data entry form work. You only have a
few items to add because you're using the prjRecObject library you built earlier
in this lesson. Add Listing 10.20 to the declaration section of the form.
Option Explicit ` Dim objRec As Object Dim lngResult As Long
Create a Sub procedure to handle opening the database and creating the Recordset.
Add the new routine in Listing 10.21 to the form.
Public Sub StartProc()
`
` handle initial startup of form
`
Set objRec = New recObject
`
objRec.DBName = App.Path & "\..\..\data\master.mdb"
objRec.RSName = "StateProvList"
objRec.rsType = rsDynasetType
objRec.RSFocus = "StateProv"
`
objRec.RSOpen Me
objRec.RSEnable Me, False
`
objRec.BtnBarAlign = bbBottom
objRec.BBInit Me
objRec.BBEnable Me, "11111111"
`
End Sub
Listing 10.21 initializes the top-level record object and then sets several properties
of the new object before executing the RSOpen and RSEnable methods.
Then the routine goes on to initialize and enable the button bar for the form.
Next, you need to add code to the Form_Load event that starts this whole process. Enter the code in Listing 10.22 in the Form_Load event window of the form.
Private Sub Form_Load()
`
` set field tags for data binding
Text1.Tag = "StateProv"
Text2.Tag = "Description"
`
` call routine to start recObject library
StartProc
`
End Sub
In Listing 10.22, you set the Tag properties of the two textboxes that are used for
data entry, and then you call StartProc to start up the local copy of recObject.
Now you need to add the routine that makes the buttons call all of the library routines. Add the following code to the cmdBtn_Click event of the form:
Private Sub cmdBtn_Click(Index As Integer)
`
` handle all button selections
`
objRec.BBProcess Me, Index, "StateProv"
`
End Sub
This code is called every time you click any of the eight buttons on the data entry form. The BBProcess routine determines which button was pressed and performs the appropriate actions. Note that you are sending the BBProcess method three parameters: the data entry form, the index value that tells you which button was pressed, and the Search field to use if the user has pressed the Find button.
You need to add a few more lines of code to this form before you are done. First, add code that enables the buttons to automatically resize each time the form is resized. Add the following code to the Form_Resize event:
Private Sub Form_Resize()
`
objRec.BBInit Me
`
End Sub
Finally, add the following code to the Form_Unload event to ensure a safe close of the database when the program ends:
Private Sub Form_Unload(Cancel As Integer)
`
objRec.RSClose
`
End Sub
Save the new form as FRMSTPROV.FRM, and run the project. When the main form comes up, select Lists | StateProv from the menu to start the new form. Your form should look like the one shown in Figure 10.7.
Notice that the button set appears on the bottom of the form. This placement was
handled automatically by the library routines. Resize the form to see how the button
bar automatically adjusts to the new form shape. Finally, click the Add button to
add a new record to the State/Province table. You see the input controls become enabled
and most of the button bar becomes disabled (see Figure 10.8).
Figure
10.7. Running the new State/Province Validation
form.
Figure
10.8. Adding a new record to the State/Province
table.
You can enter values in both fields and then click the Save button or the Cancel
button to undo the add operation. Click Cancel for now. Test out the form by clicking
the Browse and Find buttons. Add a record, edit it, and then delete it. You now have
a fully functional data entry form, and you added less than 30 lines of Visual Basic
code to the master form!
Today you learned how to write data entry forms using Visual Basic code. These topics were covered: record search routines, the creation of a procedure library to handle all data entry processes, and the creation of a working data entry form for the CompanyMaster project.
You learned how to perform single-record searches using the three search methods:
You created an OLE Server library to handle adding, editing, deleting, reading, writing, and locating records in datasets. These routines were written as a generic DLL that you can insert into all Visual Basic programs you write in the future.
You used the new library to add a new form to the CompanyMaster database project. This new form reads a dataset and enables the user to update and browse the table. This new data entry form was built using less than 30 lines of Visual Basic code.
To review the material you learned in this chapter, respond to the following questions and check your answers against the ones provided in Appendix C.
Assume that you complete the CompanyMaster application and add the State/Province form as discussed in this lesson. After distributing this application to your users, you quickly discover that they are having trouble obtaining zip codes for the companies they enter. You decide to help them by adding a form to this application that lists zip codes and their city equivalents.
Use code to modify the CompanyMaster application so that users can select an item from the List menu (call this item ZipCity) that displays zip codes (field name of Zip) and city (field name of City). Use Visdata to add a data table (ZipCity) to MASTER.MDB.
