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 record adds, edits, and deletes.
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'll create today will be generic and portable. You'll write these routines in a library module that you'll be able to 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, it's important to talk about the difference between writing your data entry programs with and 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 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 to 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 will need to be modified or maintained by other programmers in the future, 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 complete control over all aspects of the process. You decide when to open the database and recordset, and you control the record reads and writes, too. This
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'll 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'll need to do to add a new record to the table or delete an existing one. You'll also need a way for the user to browse
the data. Remember that dropping the data control means your form will not automatically display the "VCR-style" navigation arrows.
Despite this added responsibility, writing your data entry forms with Visual Basic code can give you much greater control over the process and will result in a form that is easy for both programmers and users to deal with.
Before you create the generic data entry routines, you need to look at an important topic: record searching. Up until now, we have only touched on this issue. There are several methods you can use to search out a record in a recordset. Some are faster
than others. Using the correct method in your Visual Basic program can make your programs seem fast and solid. Using the "wrong" search method can give your program the needless reputation of being a plodder.
To illustrate these methods, let's start a new Visual Basic project. Table 11.1 contains a list of controls to add to the form. Refer to Figure 11.1 as a guide as you layout the form.
| Control | Property | Setting |
| Form | Name
Caption | frmMove
Chapter 11 Move Demo |
| Command Button | Name
BorderStyle
Caption
Height
Left
Top
Width | cmdMoveFirst
Fixed Single
&First
300
120
120
1200 |
| Command Button | Name
BorderStyle
Caption
Height
Left
Top
Width | cmdMovePrevious
Fixed Single
&Previous
300
120
480
1200 |
| Command Button | Name
Caption
Height
Left
Top
Width | cmdMoveNext
&Next
300
120
840
1200 |
| Command Button | Name
Caption
Height
Left
Top
Width | cmdMoveLast
&Last
300
120
1200
1200 |
| Label | Name
Height
Left
Top
Width | Label1
300
1500
120
1200 |
| Label | Name
Height
Left
Top
Width | Label2
300
1500
480
2400 |
Figure 11.
Layout of the frmMove form.
After laying out the form, you need to add the code. Enter Listing 11.1 in the general declarations section of the form (this declares all the form-level variables you'll use in the project).
Listing 11.1 Coding the form-level variables.
Option Explicit
'
' form-level vars
'
Dim cDBName As String ' database name
Dim dbName As DATABASE ' database object
Dim cRSName As String ' record set name
Dim rsName As Recordset ' record set object
Listing 11.2 opens the database and then opens a Dynaset for your use. Add this to the Form_Load event:
Listing 11.2. Opening the database and a Dynaset.
Private Sub Form_Load()
'
' open db and open recordset
'
cDBName = App.Path + "\ch1101.mdb"
cRSName = "Authors"
'
Set dbName = OpenDatabase(cDBName)
Set rsName = dbName.OpenRecordset(cRSName, 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:
Sub ReadRow()
'
' reads current row into form controls
'
Label1 = rsName.Fields(0)
Label2 = rsName.Fields(1)
End Sub
This routine simply 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 place code behind each of the four command buttons on the form. Each button needs to perform two tasks:
- Reposition the pointer as requested.
- Read the data from the new current row.
The following four code pieces do these tasks. Enter each of the code lines in Listing 11.3 into the Click event of each corresponding command button:
Listing 11.3. Coding the cmdMove... events.
Private Sub cmdMoveFirst_Click()
rsName.MoveFirst ' position pointer
ReadRow ' load controls
End Sub
Private Sub cmdMoveLast_Click()
rsName.MoveLast ' position pointer
ReadRow ' load controls
End Sub
Private Sub cmdMoveNext_Click()
rsName.MoveNext ' position pointer
ReadRow ' load controls
End Sub
Private Sub cmdMovePrevious_Click()
rsName.MovePrevious ' position pointer
ReadRow ' load controls
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 ' force first record up
End Sub
The last code you'll add 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)
dbName.Close ' close database
End Sub
Save the form as CH1101.FRM and save the project as CH1101.VBP. When you run the project, you'll be able to click the buttons in order to walk the data set. This operates the same as the data control arrow buttons.
NOTE:If you click the First button and then immediately click the Previous button, you'll get a runtime error. This is caused by attempting to read past the beginning of the data set. Later in this day, you'll create a routine that prevents
this from occurring in your programs.
These are good examples of how you can provide users with a way to browse the data set on a form. In the next section, you will see how to give your users the ability to search for a particular record in the data set.
Using Seek on Table Recordsets
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 is the type of index used by
ISAM-type databases. Indexed searches are easy to perform and are very fast.
Now you'll modify the CH1101.VBP project to illustrate index searching by adding another button to the form. Set its Name property to cmdSeek and its Caption property to &Seek. Next, add Listing 11.4 to the cmdSeek_Click event:
Listing 11.4. Coding the cmdSeek_Click event.
Private Sub cmdSeek_Click()
'
' get input and peform table seek
'
Dim cSeek As String
'
cSeek = InputBox("Enter Au_ID Seek Value:", "Table Seek")
cSeek = UCase(Trim(cSeek))
'
If Len(cSeek) <> 0 Then
rsName.Seek "=", cSeek
'
If rsName.NoMatch = True Then
MsgBox "Unable to Locate [" + cSeek + "]", vbCritical,
"Failed Table Seek"
Else
ReadRow ' load record
MsgBox "Found [" + cSeek + "]", vbInformation,
"Successful Table Seek"
End If
End If
'
End Sub
Listing 11.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, the NoMatch
method is used to get the results of the Seek (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, too.
Before this routine will 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[el]
line:
rsName.Index = "PrimaryKey" ' set index property
Now save and run the project. This time, click the Seek button. When the dialog appears, enter 13 and click OK. You should see a message telling you that the search was successful. (See Figure 11.2.)
Figure 11.2
The results of the table Seek operation.
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, it can only be applied to recordsets opened as table objects. If you want to locate a specific record in a dynaset or snapshot, you can use one of the Find methods. I cover the Find
methods in the next section.
Using Find on Non-Table Recordsets
Because dynaset and snapshot objects do not use indexes, the Seek method cannot be used to search for specific records. The Find method is used to locate specific records in non-table objects (dynasets and snapshots). The Find
method is not as fast as the indexed Seek method. The Find method is a sequential search. It starts at the beginning of the data set and looks at each record until it finds one that matches the search criteria. Although this is not as fast as
Seek, it is still faster than using the Move methods to handle this within your own Visual Basic code.
The syntax for the Find methods is almost identical to the SQL WHERE clause (covered in Day 9). The search string consists of a field (or set of fields) followed 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. The FindPrevious and FindNext methods can be used 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 data set. Similarly, you can use the FindLast and
FindPrevious methods to perform continued searches starting at the end of the data set. 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.
Now you'll modify the CH1101.VBP project to illustrate the Find method by adding another button to the project. Set its Name property to cmdFind and its Caption property to F&ind. Next, add Listing 11.5 to the cmdFind_Click event:
Listing 11.5. Coding the cmdFind_Click event.
Private Sub cmdFind_Click()
'
' get input and peform table seek
'
Dim cFind As String
'
cFind = InputBox("Enter Au_ID Find Value:", "Non-Table Find")
cFind = UCase(Trim(cFind))
'
If Len(cFind) <> 0 Then
cFind = "Au_ID=" + cFind ' build criteria string
rsName.FindFirst cFind
'
If rsName.NoMatch = True Then
MsgBox "Unable to Locate [" + cFind + "]", vbCritical,
"Failed Non-Table Find"
Else
ReadRow ' load record
MsgBox "Found [" + cFind + "]", vbInformation,
"Successful Non-Table Find"
End If
End If
'
End Sub
Listing 11.5 is almost identical to the one used in the cmdSeek_Click event (See Listing 11.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 vbOpenTable to vbOpenSnapshot. 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 11.3.)
Figure 11.3
The results of the Find method.
Notice that if you click the Seek button, you'll 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, I'll show you how to write a single
locate routine that is smart enough to figure out which search method to use for your recordset object.
Using Bookmarks Before Searching Recordsets
There is one more item to cover before you leave the topic of record searching. That item is the use of bookmarks. Visual Basic uses bookmarks to remember a specific location in a data set. Bookmarks can be used for all types of recordsets (table and
non-table objects).
When you use Seek or Find, you are actually moving the record pointer. If the search is successful, the record pointer is now resting at the found record. If the search fails, the record pointer is resting at the end of the data set. (If
you use the FindLast method, failed searches leave you at the beginning of the data set.) It is very annoying to users to see a Search failed message and then see their data entry form updated with a new record! Before performing a search
(Seek or Find method), you should always bookmark your location. Then, if the search fails, you can restore the record pointer to the starting location.
Now you'll modify the cmdFind_Click event to include bookmarking to restore the record pointer on failed searches. Listing 11.6 shows the updated cmdFind_Click event:
Listing 11.6. Modifying the cmdFind_Click event.
Private Sub cmdFind_Click()
'
' get input and peform table seek
'
Dim cFind As String
Dim cBMark As String ' added for bookmarks
'
cFind = InputBox("Enter Au_ID Find Value:", "Non-Table Find")
cFind = UCase(Trim(cFind))
'
If Len(cFind) <> 0 Then
cBMark = rsName.Bookmark ' added for bookmarks
cFind = "Au_ID=" + cFind
rsName.FindFirst cFind
'
If rsName.NoMatch = True Then
MsgBox "Unable to Locate [" + cFind + "]", vbCritical,
"Failed Non-Table Find"
rsName.Bookmark = cBMark ' added for bookmarks
ReadRow ' re-load the data ' added for bookmarks
Else
ReadRow ' load record
MsgBox "Found [" + cFind + "]", vbInformation,
"Successful Non-Table Find"
End If
End If
'
End Sub
In Listing 11.6, the lines with the comment added for bookmarks are the new lines of code. Save and run the program. This time, enter 300 after clicking the Find button. Although you do not see any difference on-screen after the failed
search, the record pointer has been restored to its original position. The same process should be used when executing a Seek method.
Creating Your Own Bound Controls
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 a set of routines that allow you to build solid data entry forms without using the data control. The rest of this day is devoted to constructing these routines.
NOTE:A finished version of the record-handling module is contained in the LIBREC.BAS module file on the CD that ships with this book.
There are a series of operations that must be handled for any data entry system. I'll outline these operations, and then you'll 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:
- RSOpen: This routine opens a database and selects a set of records for processing.
- RecInit: This routine initializes the data entry form and prepares the on-form controls for reading and writing data records.
- RecLocate: This routine provides a front end for performing Seek and Find operations on the data set.
- RecRead: This routine reads the selected record and loads the on-form controls with the contents of the data fields.
- RecEnable: This routine handles the enabling and disabling of the input controls to manage user updates to the data form.
- RecWrite: This routine copies the values from the data entry form back to the data set for storage.
- RecDelete: This routine gives the user the power to delete the current record from the data set.
In addition to the record-handling routines, you'll also build a set of routines to design and manage a command button toolbar. This toolbar will provide 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. Here are three additional routines:
- BtnBarInit: This routine creates the button set on your form. You'll be able to place the button set on the top, bottom, left, or right side of the form.
- BtnBarEnable: This short routine allows you to temporarily disable selected buttons on the bar to make sure the user does not mistakenly invoke a search action in the middle of an update action.
- BtnBarProcess: This is the heart of the data entry form. This routine links the button set with the previously mentioned record functions to provide a complete customized data entry form for your applications.
You'll design these routines to work with any data set you select, as well as any form layout you choose, using any input controls (not just the Visual Basic data bound controls). Also, you'll construct the routines as a set of stand-alone functions
that you can incorporate into all your future programming projects.
NOTE:First, you'll through the process of building the code library first. Then, after the library is built, you'll build a simple form to add to the CompanyMaster project. This form will use all the library functions covered here.
Preparing the Data Entry Form
The routines you have designed will make a few assumptions about how your data entry forms will be constructed. These assumptions are very general and will 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'll need to stay within the following guidelines:
- Each data entry form will correspond to a single data set. This is simple when dealing with table-type data sets. You can design a single form for each table. If you need to perform data entry on a set of columns that are the result of a multiple-table
SQL JOIN operation, you can use the data set produced by the JOIN as the basis for the data entry form.
- Each data entry form will contain a control array of eight command buttons named cmdBtn(0) through cmdBtn(7). This is the button set expected by all the routines you'll build.
- Every column in the data set row that requires data entry will be represented by a single input control on the form. The control and the field will be related by placing the column name in the Tag property of the input control. This is how
you'll be able to bind your input controls to your data set.
That's about it for the assumptions. Each form represents a data set, each form must have an eight-button control array defined, and each data field that will be updated will be represented by a single input control identified by placing the data set
column name in the Tag property of the input control. After that, you can layout your forms in any manner you like.
You'll build the library of record-handling functions first. Start a new Visual Basic project. Open up a BAS module by selecting Insert | Module from the main Visual Basic menu. Set the module name to modRecLibrary by placing the mouse over the
open module and clicking the right (alternate) mouse button to bring up the context menu. Select Properties from this menu, and fill in the Name property on the property sheet.
TIP:Be sure to set the Option Explicit option to On for this project. This will force 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'll create
as you enter these routines.
Before you begin the heavy coding, complete the declaration section of the library routine. Enter Listing 11.7 at the top of the module.
Listing 11.7. Coding the global variables.
' global const for rec routines
'
Global Const recOK = 0 ' all ok value
Global Const recNotFound = -1 ' record not found
'
' button bar alignment constants
'
Global Const btnAlignTop = 0 ' algin btns on top
Global Const btnAlignBottom = 1 ' align btns on bottom
Global Const btnAlignLeft = 2 ' align btns on left
Global Const btnAlignRight = 3 ' align bnts on right
Not much here, really. The first two values are used throughout the routines to indicate the status of an operation. The final four values are used to control how the button set will appear on your data entry form.
After you have entered the preceding code, save the module as LibRec.bas. The next several sections contain the code for all the record-handling routines.
The RSOpen Routine
This routine handles the opening of an existing database and the creation of a recordset to hold the selected records. Enter Listing 11.8 on a blank line in the module. Be sure to include the Function declaration line. Visual Basic supplies the
End Function line automatically.
TIP:It is a good idea to save your work after each coding section. This ensures that you do not lose much work if your computer suffers an unexpected crash.
Listing 11.8. Coding the RSOpen Function
Function RSOpen(cDBName As String, cRSName As String, nRSType As Integer,
dbResult As DATABASE, rsResult As Recordset) As Integer
'
' opens database, selects recordset
'
' *** NOTE *******************************
' You must call this routine at least once
' before you use any other functions. The
' first time, you must include valid
' cDBName, and cRSName values. If you want
' to open additonal recordsets on the same
' database, pass an empty cDBName along with
' the previously created dbResult object
' and a new cRSName/rsResult set.
' ********************************************
'
' inputs:
' cDBName name of database to open
' cRSName name of table or SQL Select
' nRSType recordset type constant
'
' outputs:
' dbResult resulting database
' rsRecordset resulting dataset
' RSOpen recOK if no errors
'
On Error GoTo RSOpenErr
'
If Len(Trim(cDBName)) <> 0 Then
Set dbResult = DBEngine.Workspaces(0).OpenDatabase(cDBName)
End If
Set rsResult = dbResult.OpenRecordset(cRSName, nRSType)
'
RSOpen = recOK ' no errors, all ok
GoTo RSOpenExit
'
RSOpenErr:
RecError Err, Error$, "RSOpen"
RSOpen = Err ' problem!
GoTo RSOpenExit
'
RSOpenExit:
'
End Function
The comment lines at the beginning of the module explain most of what is going on here. This routine reads the string values you send it and returns fully initialized Data Access Objects for your use. Notice that if you pass a blank cDBName
value, the routine will attempt to open the recordset using the previously opened database. This makes it easy to create multiple data sets from the same database.
Another new twist here is that almost all the routines in this library are declared as Functions instead of Subs. These functions will return an integer value that indicates whether any errors occurred during the operation. This gives you
a very easy way to check for errors from within Visual Basic code. Also, you have built a simple error handler for those times when things will go wrong. You'll cover error handling in depth in Day 14, "Error Trapping." For
now, just remember that what you are doing here is creating a set of routines to trap and report any errors that might occur during the running of the program.
TIP:It is a good idea to comment out the On Error[el] lines of your program while you are first entering the Visual Basic code. When the error trap is on, even simple typing errors will set it off. During the construction phase,
you'll 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[el] program
lines.
The RecInit Routine
This next 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 data set 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 data set values. Enter the code in Listing 11.9 as a new function:
Listing 11.9. Coding the RecInit function.
Function RecInit(frmName As Form) As Integer
'
' clears any values from bound controls
'
' Inputs:
' frmName name of form to initialize
'
' Outputs:
' RecInit recOK if no errors
'
On Error GoTo RecInitErr
'
Dim ctlTemp As Control
Dim cTag As String
'
For Each ctlTemp In frmName.Controls
cTag = UCase(Trim(ctlTemp.Tag))
If Len(cTag) <> 0 Then
ctlTemp = ""
End If
Next
'
RecInit = recOK ' all ok
GoTo RecInitExit
'
RecInitErr:
RecError Err, Error$, "RecInit"
RecInit = Err ' report error
GoTo RecInitExit:
'
RecInitExit:
'
End Function
This routine contains a simple loop that checks all the controls on the form to see whether they are bound to a data set column. If they are, the control gets initialized.
The RecLocate Routine
This 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 11.10
to your module.
Listing 11.10. Coding the RecLocate function.
Function RecLocate(cFldName As String, rsName As Recordset,
cIndex As String) As Integer
'
' prompt user to enter info on record to locate
'
' inputs:
' cFldName Name of Field to Search
' rsName Name of recordset to search
' cIndex Name of index to use (for table-type only)
'
' outputs:
' RecLocate RecOk - if found
' RecNotFound - if not found
'
On Error GoTo RecLocateErr
'
Dim cSearch As String
Dim nIndex As Integer
Dim cBookMark As String
'
If Len(Trim(cIndex)) <> 0 Then
rsName.Index = cIndex
nIndex = True
Else
nIndex = False
End If
'
cSearch = InputBox("Enter Search Value:", "Searching " + cFldName)
cSearch = Trim(cSearch)
'
cBookMark = rsName.Bookmark
'
If Len(cSearch) = 0 Then
nResult = recNotFound ' report nomatch
Else
If nIndex = True Then
rsName.Seek "=", cSearch
Else
If InStr(cSearch, ",") <> 0 Then
cSearch = "'" + cSearch + "'"
End If
cSearch = cFldName + "=" + cSearch
rsName.FindFirst cSearch
End If
'
If rsName.NoMatch = True Then
MsgBox "Unable to Locate [" + cSearch + "]",
vbInformation, "Searching " + cFldName
nResult = recNotFound ' report nomatch
Else
nResult = recOK ' report match
End If
End If
'
If nResult = recNotFound Then
rsName.Bookmark = cBookMark
End If
'
RecLocate = nResult
GoTo RecLocateExit
'
RecLocateErr:
RecError Err, Error$, "RecLocate"
nResult = Err
GoTo RecLocateExit
'
RecLocateExit:
'
End Function
Notice that if you pass an index name with this routine, it will be used with the Seek method instead of a sequential Find method. Also note the use of the Bookmark property to return the record pointer to its starting location if the
search fails.
The RecRead Routine
Now you get one of the important routines! This routine takes values from the current record of the data set 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, it is assumed that the value is a column name for the data set. The value in this column is then copied from the data set into the form control. Add this new routine (Listing 11.11) to your library.
Listing 11.11. Coding the RecRead function.
Function RecRead(frmName As Form, rsName As Recordset) As Integer
'
' read a record of data and update the controls
'
' Inputs:
' frmName Name of form to load
' rsName Name of recordset to read
'
' Outputs:
' RecRead recOk - if no errors
'
On Error GoTo RecReadErr
'
Dim ctlTemp As Control
Dim cTag As String
Dim cFldName As String
'
For Each ctlTemp In frmName.Controls
cTag = UCase(Trim(ctlTemp.Tag))
If Len(cTag) <> 0 Then
ctlTemp = rsName.Fields(cTag)
End If
Next
RecRead = recOK ' all ok
GoTo RecReadExit
'
RecReadErr:
RecError Err, Error$, "RecRead"
RecRead = Err
GoTo RecReadExit
'
RecReadExit:
'
End Function
This routine and the next routine (RecWrite) are the heart of the record-handling functions. When you understand how these routines work, you'll be able to build your own, customized, routines for handling data set read and write operations.
The RecWrite Routine
This routine (see Listing 11.12) performs the opposite function of RecRead. Again, it's a simple loop through all the controls on the form. If they are bound to a data column, the value in the control is copied to the data set column for storage.
NOTE:Before you can write to a data set, you need to invoke the Edit or AddNew methods. After the write, you must invoke the Update method to save the changes. You'll handle these operations in the button set routines
later in this chapter.
Listing 11.12. Coding the RecWrite function.
Function RecWrite(frmName As Form, rsName As Recordset) As Integer
'
' update current record with data from controls
'
' Inputs:
' frmName Name of form w/ bound controls
' rsName Name of recordset to update
'
' Outputs:
' RecWrite recOK - if no errors
'
On Error GoTo RecWriteErr
'
Dim ctlTemp As Control
Dim cTag As String
Dim lAttrib As Long
'
For Each ctlTemp In frmName.Controls
cTag = UCase(Trim(ctlTemp.Tag))
If Len(cTag) <> 0 Then
lAttrib = rsName.Fields(cTag).Attributes
If (lAttrib And dbAutoIncrField) = False Then
rsName.Fields(cTag) = ctlTemp
End If
End If
Next
'
RecWrite = recOK ' all ok
GoTo RecWriteExit
'
RecWriteErr:
RecError Err, Error$, "RecWrite"
RecWrite = Err
GoTo RecWriteExit
'
RecWriteExit:
'
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 will not attempt to write data to the column.
The RecEnable Routine
To simplify managing data entry routines, your form will only allow users to update form controls after they select the Edit or Add buttons on a form. This routine gives you an easy way to turn on or off the Enabled property of all the
bound controls on your form. You'll call this often from your button-set routines.
Add the function in Listing 11.13 to the library:
Listing 11.13. Coding the RecEnable function.
Function RecEnable(frmName As Form, nToggle) As Integer
'
' toggles input controls on/off
'
' Inputs:
' frmName form with bound controls
' nToggle enable on/off (True/False)
'
' Outputs:
' RecEnable recOK if no errors
'
On Error GoTo RecEnableErr
'
Dim ctlTemp As Control
Dim cTag As String
'
For Each ctlTemp In frmName.Controls
cTag = UCase(Trim(ctlTemp.Tag))
If Len(cTag) <> 0 Then
ctlTemp.Enabled = nToggle
End If
Next
'
RecEnable = recOK ' all ok
GoTo RecEnableExit
'
RecEnableErr:
RecError Err, Error$, "RecEnable"
RecEnable = Err
GoTo RecEnableExit
'
RecEnableExit:
'
End Function
The RecDelete Routine
This routine performs a delete operation on the selected data record. But before committing the deed, the user is given the chance to reverse the process. Add Listing 11.14 to the library:
Listing 11.14. Coding the RecDelete function.
Function RecDelete(rsName As Recordset) As Integer
'
' delete the current record
'
' Inputs:
' rsName Recordset that holds rec to del
'
' Outputs:
' RecDelete recOK if no errors
'
On Error GoTo RecDeleteErr
'
nResult = MsgBox("Delete Current Record?", vbInformation +
vbYesNo, rsName.Name)
If nResult = vbYes Then
rsName.DELETE
End If
'
RecDelete = recOK ' all ok
GoTo RecDeleteExit
'
RecDeleteErr:
RecError Err, Error$, "RecDelete"
RecDelete = Err
GoTo RecDeleteExit
'
RecDeleteExit:
'
End Function
Other Record Routines
You need three more routines to complete the record-handling portion of the library: RecError handles any errors that occur; RecBack and RecNext 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 (Listing 11.15) to the library:
Listing 11.15. Coding the RecError routine.
Sub RecError(nErr, cError, cOpName)
'
' report trapped error to user
'
' Inputs:
' nErr Error Number
' cError Error Message
' cOpName Function/Sub that raised error
'
Dim cErrMsg As String
'
cErrMsg = "Error:" + Chr(9) + Str(nErr) + Chr(13)
cErrMsg = cErrMsg + "Text:" + Chr(9) + cError + Chr(13)
cErrMsg = cErrMsg + "Module:" + Chr(9) + cOpName
'
MsgBox cErrMsg, vbCritical + vbOKCancel, "RecError"
'
End Sub
Function RecBack(rsName As Recordset) As Integer
'
' move to previous record in set
'
' inputs:
' rsName name of recordset
'
' outputs:
' RecBack recOK if no errors
'
On Error GoTo RecBackErr
'
If rsName.BOF = True Then ' past start?
rsName.MoveFirst ' move to first rec
Else
rsName.MovePrevious ' move to prev rec
If rsName.BOF Then ' past start?
rsName.MoveFirst ' move to first rec
End If
End If
'
RecBack = recOK ' all ok
GoTo RecBackExit
'
RecBackErr:
RecError Err, Error$, "RecBack"
RecBack = Err
GoTo RecBackExit
'
RecBackExit:
'
End Function
Function RecNext(rsName As Recordset) As Integer
'
' move to the next record in the set
'
' inputs:
' rsName name of the recordset
'
' outputs:
' RecNext recOK if no error
'
On Error GoTo RecNextErr
'
If rsName.EOF = True Then ' past end?
rsName.MoveLast ' move to last rec
Else
rsName.MoveNext ' move to next rec
If rsName.EOF Then ' past end?
rsName.MoveLast ' move to last rec
End If
End If
'
RecNext = recOK ' all ok
GoTo RecNextExit ' leave here
'
RecNextErr:
RecError Err, Error$, "RecNext"
RecNext = Err
GoTo RecNextExit
'
RecNextExit:
'
End Function
You have just completed the record-handling portion of the library. Only three routines must still be built. These three routines provide the button set that users will see when they perform data entry operations on your form.
Creating Your Own Button Bar Routine
The next three routines handle all the operations needed to add a complete set of command buttons to your data entry form. This set can be used for any data entry form that provides the basic add, edit, delete, find, and browse operations needed for
most data entry routines.
WARNING:Before these routines can work with your programs you'll need to add a control array of eight command buttons. These routines assume the array is called cmdBtn(0) through cmdBtn(7). You'll cover the details of
constructing a working form in the "Creating a Data Entry Form with the Library Routines" section of this chapter.
The BtnBarInit Routine
This routine builds the details of the command button array and places that array on your data entry form. You must first place an eight-button control array on the form called cmdBtn(0) through cmdBtn(7). This routine can place the button
set on the top, bottom, left, or right side of the form. You control this feature by setting the alignment parameter using one of the constants you defined earlier.
Add this routine (Listing 11.16) to the library module that contains the record-handling routines.
Listing 11.16. Coding the BtnBarInit routine.
Sub BtnBarInit(frmName As Form, nAlign As Integer)
'
' sets up button bar where indicated
'
' *** NOTE ******************************
' Before you can call this routine, *
' you must first add a command button *
' control array to frmName. This array *
' MUST be called cmdBtn. There MUST be *
' eight buttons in the array. *
' ***************************************
' Inputs:
' frmName Name of form to use
' nAlign location of bar
' 0 = btnAlignTop
' 1 = btnAlignbottom
' 2 = btnAlignLeft
' 3 = btnAlginRight
'
'On Error GoTo BtnBarInitErr
'
Dim nBtnWidth As Integer
Dim nBtnTop As Integer
Dim nBtnLeft As Integer
Dim nBtnHeight As Integer
Dim x As Integer
Dim cCap(7) As String
'
cCap(0) = "&Add"
cCap(1) = "&Edit"
cCap(2) = "&Del"
cCap(3) = "&Find"
cCap(4) = "&Top"
cCap(5) = "&Next"
cCap(6) = "&Back"
cCap(7) = "&Last"
'
' calc button locations
Select Case nAlign
Case Is = btnAlignTop
' align top
nBtnTop = 60
nBtnWidth = (frmName.ScaleWidth - 60) / 8 ' width of each button
If nBtnWidth < 660 Then
nBtnWidth = 660 ' no smaller than 660 wide
End If
nBtnHeight = 300 ' default hieght
Case Is = btnAlignBottom
' align bottom
nBtnTop = frmName.ScaleHeight - 360
nBtnWidth = (frmName.ScaleWidth - 60) / 8 ' width of each button
If nBtnWidth < 660 Then
nBtnWidth = 660 ' no smaller than 660 wide
End If
nBtnHeight = 300 ' default height
Case Is = btnAlignLeft
' align left
nBtnWidth = 660
nBtnHeight = (frmName.ScaleHeight - 60) / 8
If nBtnHeight < 300 Then
nBtnHeight = 300 ' no smaller than 300 high
End If
nBtnLeft = 60
Case Is = btnAlignRight
' align right
nBtnWidth = 660
nBtnHeight = (frmName.ScaleHeight - 60) / 8
If nBtnHeight < 300 Then
nBtnHeight = 300 ' no smaller than 300 high
End If
nBtnLeft = (frmName.ScaleWidth - 720)
End Select
'
' now place the buttons on form
For x = 0 To 7
If nAlign = btnAlignTop Or nAlign = btnAlignBottom Then
nBtnLeft = x * nBtnWidth ' calc left location
End If
If nAlign = btnAlignLeft Or nAlign = btnAlignRight Then
nBtnTop = x * nBtnHeight + 60 ' calc top location
End If
'
frmName.cmdBtn(x).Width = nBtnWidth
frmName.cmdBtn(x).Left = nBtnLeft
frmName.cmdBtn(x).TOP = nBtnTop
frmName.cmdBtn(x).Height = nBtnHeight
frmName.cmdBtn(x).Caption = cCap(x)
frmName.cmdBtn(x).Visible = True
Next x
'
GoTo BtnBarInitExit
'
BtnBarInitErr:
RecError Err, Error$, "BtnBarInit"
GoTo BtnBarInitExit
'
BtnBarInitExit:
'
End Sub
Listing 11.16 uses the data form's dimensions to calculate the location and size of the command buttons in the button set. You'll create a working example of this in the section "Creating a Data Entry Form with the Library Routines."
The BtnBarEnable Routine
This is a short routine that allows you to toggle the Enabled property of the command buttons in the button set. This will be used to turn on or off selected buttons during edit or add operations. Add the routine in Listing 11.17 to the library:
Listing 11.17. Coding the BtnBarEnable routine.
Sub BtnBarEnable(frmName As Form, cList As String)
'
' turns on(1)/off(0) cmdbuttons
'
' inputs:
' frmName name of form that holds controls
' cList bit list for toggling
' "1" = turn on
' "0" = turn off
' "1010" = turn 1&3 on, 2&4 off
'
On Error GoTo BtnBarEnableErr
'
Dim x As Integer
'
cList = Trim(cList)
For x = 1 To Len(cList)
If Mid(cList, x, 1) = "1" Then
frmName.cmdBtn(x - 1).Enabled = True
Else
frmName.cmdBtn(x - 1).Enabled = False
End If
Next x
'
GoTo BtnBarEnableExit
'
BtnBarEnableErr:
RecError Err, Error$, "BtnBarEnable"
GoTo BtnBarEnableExit
'
BtnBarEnableExit:
'
End Sub
The BtnBarProcess Routine
This routine handles all the button actions initiated by the user and makes many calls to the other routines in the library. It is the high-level routine of the module. This 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'll 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 11.18 into the library:
Listing 11.18. Coding the BtnBarProcess routine.
Sub BtnBarProcess(nCmdBtn As Integer, frmName As Form, rsName As Recordset,
cSearch As String, cIndex As String)
'
' handle button clicks
'
' *** NOTE ******************************
' Before you can call this routine, *
' you must first add a commandbutton *
' control array to frmName. This array *
' MUST be called cmdBtn. There MUST be *
' eight buttons in the array. *
' ***************************************
' inputs:
' nCmdBtn button that was pressed
' frmName name of form that holds controls
' cSearch search field(s)
' cIndex search index (table-type sets only)
'
On Error GoTo BtnBarProcessErr
'
Select Case nCmdBtn
Case Is = 0
' peform add/save/cancel
Select Case frmName.cmdBtn(nCmdBtn).Caption
Case Is = "&Save"
nResult = RecWrite(frmName, rsName)
If nResult = recOK Then
rsName.UPDATE
End If
If nResult = recOK Then
nResult = RecInit(frmName)
End If
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
If nResult = recOK Then
nResult = RecEnable(frmName, False)
End If
If nResult = recOK Then
frmName.cmdBtn(0).Caption = "&Add"
frmName.cmdBtn(1).Caption = "&Edit"
BtnBarEnable frmName, "11111111"
End If
Case Is = "&Add"
rsName.AddNew
nResult = RecInit(frmName)
If nResult = recOK Then
nResult = RecEnable(frmName, True)
End If
If nResult = recOK Then
frmName.cmdBtn(0).Caption = "&Save"
frmName.cmdBtn(1).Caption = "&Cancel"
BtnBarEnable frmName, "11000000"
End If
Case Is = "&Cancel"
rsName.CancelUpdate
frmName.cmdBtn(0).Caption = "&Add"
frmName.cmdBtn(1).Caption = "&Edit"
BtnBarEnable frmName, "11111111"
'
nResult = RecInit(frmName)
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
If nResult = recOK Then
nResult = RecEnable(frmName, False)
End If
End Select
Case Is = 1
' perform edit/save/cancel
Select Case frmName.cmdBtn(1).Caption
Case Is = "&Save"
rsName.Edit
nResult = RecWrite(frmName, rsName)
If nResult = recOK Then
rsName.UPDATE
End If
If nResult = recOK Then
nResult = RecEnable(frmName, False)
End If
If nResult = recOK Then
frmName.cmdBtn(1).Caption = "&Edit"
frmName.cmdBtn(0).Caption = "&Add"
BtnBarEnable frmName, "11111111"
End If
Case Is = "&Edit"
nResult = RecEnable(frmName, True)
If nResult = recOK Then
frmName.cmdBtn(1).Caption = "&Save"
frmName.cmdBtn(0).Caption = "&Cancel"
BtnBarEnable frmName, "11000000"
End If
Case Is = "&Cancel"
rsName.CancelUpdate
frmName.cmdBtn(1).Caption = "&Edit"
frmName.cmdBtn(0).Caption = "&Add"
BtnBarEnable frmName, "11111111"
'
nResult = RecInit(frmName)
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
If nResult = recOK Then
nResult = RecEnable(frmName, False)
End If
End Select
'
If nResult = recOK Then
nResult = RecInit(frmName)
End If
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
Case Is = 2
' perform delete
nResult = RecDelete(rsName)
If nResult = recOK Then
nResult = RecEnable(frmName, False)
End If
If nResult = recOK Then
nResult = RecNext(rsName)
End If
If nResult = recOK Then
nResult = RecInit(frmName)
End If
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
BtnBarEnable frmName, "11111111"
Case Is = 3
' perform find
nResult = RecLocate(cSearch, rsName, cIndex)
If nResult = recOK Then
nResult = RecInit(frmName)
End If
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
BtnBarEnable frmName, "11111111"
Case Is = 4
' perform move top
rsName.MoveFirst
nResult = RecInit(frmName)
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
BtnBarEnable frmName, "11111111"
Case Is = 5
' perform move next
nResult = RecNext(rsName)
If nResult = recOK Then
nResult = RecInit(frmName)
End If
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
BtnBarEnable frmName, "11111111"
Case Is = 6
' perform move back
nResult = RecBack(rsName)
If nResult = recOK Then
nResult = RecInit(frmName)
End If
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
BtnBarEnable frmName, "11111111"
Case Is = 7
' perform move last
rsName.MoveLast
nResult = RecInit(frmName)
If nResult = recOK Then
nResult = RecRead(frmName, rsName)
End If
BtnBarEnable frmName, "11111111"
End Select
'
GoTo BtnBarProcessExit
'
BtnBarProcessErr:
RecError Err, Error$, "BtnBarProcess"
GoTo BtnBarProcessExit
'
BtnBarProcessExit:
'
End Sub
NOTE:This is the last library function you'll be adding. Be sure to save the updated library file to disk before exiting Visual Basic.
There are several aspects to Listing 11.18 that need review. First, because you are using a command button array, all operations are dependent on which button was pushed. This is handled by the outer Select Case structure. 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.
You'll also notice a great number of If[el]End if blocks in the code. These are present because you are constantly checking the results of previous actions. It clutters up the code a bit but provides very solid error checking 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'll see that the essence of this section of the code is as follows:
- Write the record to the data set (RecWrite).
- Commit the changes (rsName.Update).
- Initialize the form controls (RecInit).
- Read the current record into the form (RecRead).
- Disable data entry in the fields (RecEnable False).
- Reset the command button labels and enable all the buttons.
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, when you have it on file, you'll be able to use it in all your Visual Basic projects.
And that's what you'll do next.
Creating a Data Entry Form with the Library Routines
Now that you have a solid library set for creating data entry forms, let's build a new form for the CompanyMaster project. To do this, you'll add a new form to the CompanyMaster project. This form will be a simple validation list that can be used 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 is a copy of the project you built last week.
The first thing you must do is add the LIBREC.BAS file to the project by selecting the File | Add File menu option. Then locate the LIBREC.BAS file you created earlier today. There is a version of this library in the Chap11 directory.
Modifying the Master Form
Before you add the new form, you need to add a short menu to the CompanyMaster main form. You'll use this menu to call the new form. Open the frmMaster form and add the menu items listed in Table 11.2. You can also refer to Figure 11.4 as a guide for building the menu.
Table 11.2. Menu items for the frmMaster form.
| Caption | Menu |
| &File | mnuFile |
| —E&xit | mnuFileExit |
| &Lists | mnuList |
| —&State/Prov | mnuListStProv |
Figure 11.4
Building the menu for the frmMaster form.
After building the menu, add the following code behind the Exit menu item.
Private Sub mnuFileExit_Click()
cmdExit_Click ' do the exit!
End Sub
This code simply calls the existing routine that handles the program exit.
Now you need to add the line of code that will call the new form you are going to create. Add the following code behind 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 will not be able to change the focus within their project until they safely exit this form.
Building the State/Province List Form
Now that the housekeeping is done, you can build the new form. Use Table 11.3 and Figure 11.5 as guides as you layout the new validation form.
Table 11.3. Controls for the State/Prov list form.
| Control | Property | Setting |
| Form | Name
Caption
Height
Left
Top
Width | frmStProv
State/Province Validation Table
1755
1545
1110
5835 |
| TextBox | Name
Height
Left
Tag
Top
Width | Text1
300
120
StateProv
120
1200 |
| TextBox | Name
Height
Left
Tag
Top
Width | Text2
300
120
Description
540
2400 |
In addition to the controls listed in Table 11.3, you need to add an array of eight command buttons. To do this, add a single command button to the form (it doesn't matter where you place it on the form), and set its Name property to cmdBtn. Click on
the button to give it the focus, and click the right mouse button to bring up the context menu. Select Copy from the menu. Now bring up the context menu and select Paste. You are prompted by Visual Basic to confirm that you want to create a control
array. Click Yes. Do this six more times, until you have eight command buttons on your form named cmdBtn(0) through cmdBtn(7).
TIP:You won't be manipulating any of the buttons in edit mode. If you like, you can place these buttons off-screen by hiding them below the edge of the display area. To do this, enlarge the form, move the buttons to the new area, and then
resize the form to its original shape. Even though you can't see the buttons, Visual Basic will be able to find them at runtime.
Figure 11.5
Laying out the State/Prov list form.
Let's add the code fragments that will make this data entry form work. You only have a few items to add because you'll be using the ModLibRec library you built earlier in this chapter. Add Listing 11.19 to the declaration section of the form.
Listing 11.19. Coding the form-level variables.
Option Explicit
'
' form level vars
'
Dim dbFile As DATABASE
Dim cDBName As String
Dim rsFile As Recordset
Dim cRSName As String
Dim nBtnAlign As Integer
Dim nResult As Integer
Create a Sub procedure to handle opening the database and creating the recordset. Add the following new routine (Listing 11.20) to the form:
Listing 11.20. Coding the StartProc routine.
Sub StartProc()
'
' open db and rs
'
' on error goto StartProcErr
'
cDBName = App.Path + "\master.mdb"
cRSName = "StateProvList"
'
nResult = RSOpen(cDBName, cRSName, dbOpenDynaset, dbFile, rsFile)
If nResult = recOK Then
nResult = RecInit(Me)
End If
'
If nResult = recOK Then
nResult = RecRead(Me, rsFile)
End If
'
GoTo StartProcExit
'
StartProcErr:
RecError Err, Error$, "StartProc"
GoTo StartProcExit
'
StartProcExit:
'
End
Listing 11.20 initializes two variables and then uses the new library routines to open the database, initialize the form, and load the first record into the controls.
Next, you need to add code to the Form_Load event that will start this whole process. Enter Listing 11.21 in the Form_Load event window of the form.
Listing 11.21. Coding the Form_Load routine.
Private Sub Form_Load()
'
' initialize and start up
'
StartProc ' open files
nResult = RecEnable(Me, False) ' turn off controls
nBtnAlign = btnAlignBottom ' set aligment var
BtnBarInit Me, nBtnAlign ' create button set
BtnBarEnable Me, "11111111" ' enable all buttons
End Sub
In Listing 11.21, you call the database startup routine, disable the form controls, create the button set on the bottom of the form, and then enable all buttons for the user.
Now you need to add the routine that will make the buttons call all the library routines. Add the following code to the cmdBtn_Click event of the form:
Private Sub cmdBtn_Click(Index As Integer)
'
' this one line handles all button calls!
'
BtnBarProcess Index, Me, rsFile, "StateProv", ""
End Sub
This single line is called every time you click any of the eight buttons on the data entry form. The BtnBarProcess routine determines which button was pressed and performs the appropriate actions.
You need to add two more lines of code to this form before you are done. First, add a line of code that will allow the buttons to automatically resize each time the form is resized. Add the following code to the Form_Resize event.
Private Sub Form_Resize()
BtnBarInit Me, nBtnAlign ' repaint buttons
End Sub
Finally, add the following line to the Form_UnLoad event to ensure a safe close of the database when the program ends.
Private Sub Form_Unload(Cancel As Integer)
dbFile.Close ' safe close
End Sub
Save the new form as MAST04.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 in Figure 11.6.
Figure 11.6
Running the new State/Province list form.
Notice that the button set appears on the bottom of the form. This 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 StateProv table. You'll see the input controls become enabled and most of the button bar gray out(see Figure 11.7).
Figure 11.7
Adding a new record to the StateProv 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!
Summary
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 creating a working data entry form for the
CompanyMaster project.
You learned how to perform single-record searches using the three search methods:
- The Move methods for browsing the data set.
- The Seek method for indexed table objects.
- The Find methods for non-table objects (dynasets and snapshots).
You created several routines to handle adding, editing, deleting, reading, writing, and locating records in data sets. These routines were written as generic procedures that can be called from all Visual Basic programs you write in the future.
You used the new library routines to add a new form to the CompanyMaster database project. This new form reads a data set and allows the user to update and browse the table. This new data entry form was built using less than 30 lines of Visual Basic
code.
Quiz
- What are the advantages and disadvantages of using the Data Control rather than code to manage Visual Basic database applications?
- What is the main advantage of using code to produce data entry forms?
- Which approach to searching for a data record—the Move, Find, or Seek method—most resembles the SQL WHERE clause?
- On what kind of recordsets can the Seek method be used to search for records?
- What are the four Move methods that can be applied to the Recordset object?
- Which of the Find methods starts its search from the beginning of the recordset? Which of the Find methods starts its search from the end of the recordset?
- Which item do you utilize to remember a specific location in a data set?
- What is the fastest search method to locate a record in a data set?
- How do you create a control array in Visual Basic?
- What method(s) do you need to invoke prior to using the Update method to write to a data set?
Exercises
Assume that you complete the Company Master application and add the State/Prov form as discussed in this chapter. 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.
Utilize code to modify the Company Master 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.
