![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Day 10Creating Database Programs with Visual Basic CodeToday 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. Why Use Code Instead of the Data Control?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. Searching for a RecordBefore 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:
Using Move to Navigate RecordsetsThe 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.
After laying out the form, you need to add the code. Enter Listing 10.1 in the general declarations section of the form. This code declares all the form-level variables you use in the project. Listing 10.1. Coding the form-level variables.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. Opening the database and a Dynaset.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
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. Listing 10.3. Coding the cmdMove events.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
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.
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. Using Seek on Table RecordsetsThe 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. Listing 10.4. Coding 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
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).
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. Using Find on Non-Table RecordsetsBecause 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. Listing 10.5. Coding 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
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).
Creating Your Own Bound ControlsUp 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.
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. Preparing the Data Entry FormThe 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.
Before you begin the heavy coding, complete the declaration section of the library routine. Enter Listing 10.6 at the top of the module. Listing 10.6. Coding the global variables.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
Coding the Property Handling RoutinesNow 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. Listing 10.7. Coding the property-handling routines.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
Listing 10.8. Coding the Class_Initialize and Class_Terminate 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
The RSOpen RoutineThe 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.
Listing 10.9. Coding the RSOpen function.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
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.
The RSInit RoutineThe 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. Listing 10.10. Coding the RSInit 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
The RSLocate RoutineThe 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. Listing 10.11. Coding the RSLocate routine.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
The RSRead RoutineNow 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. Listing 10.12. Coding the RSRead function.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
The RSWrite RoutineThe 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.
Listing 10.13. Coding the RSWrite function.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
The RSEnable RoutineTo 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. Listing 10.14. Coding the RSEnable function.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
The RSDelete RoutineThe 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. Listing 10.15. Coding the RSDelete function.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 Other Record RoutinesYou 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. Listing 10.16. Coding the RSClose, RSBack, and RSNext routines.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
Creating Your Own Button Bar RoutinesThe 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.
The BBInit RoutineThe 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. Listing 10.17. Coding the BBInit routine.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
The BBEnable RoutineThe 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. Listing 10.18. Coding the BBEnable routine.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 BBProcess RoutineThe 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. Listing 10.19. Coding the BBProcess routine.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
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. Creating a Data Entry Form with the Library RoutinesNow 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).
|
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.
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |