Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!:Database Basics
function GetCookie (name) { var arg = name + "="; var alen = arg.length; var clen = document.cookie.length; var i = 0; while (i < clen) { var j = i + alen; if (document.cookie.substring(i, j) == arg) { var end = document.cookie.indexOf (";", j); if (end == -1) end = document.cookie.length; return unescape(document.cookie.substring(j, end)); } i = document.cookie.indexOf(" ", i) + 1; if (i == 0) break; } return null; } var m1=''; var gifstr=GetCookie("UsrType"); if((gifstr!=0 ) && (gifstr!=null)) { m2=gifstr; } document.write(m1+m2+m3);            Keyword Title Author ISBN Publisher Imprint Brief Full  Advanced      Search  Search Tips Please Select ----------- Components Content Mgt Certification Databases Enterprise Mgt Fun/Games Groupware Hardware IBM Redbooks Intranet Dev Middleware Multimedia Networks OS Prod Apps Programming Security UI Web Services Webmaster Y2K ----------- New Titles ----------- Free Archive To access the contents, click the chapter and section titles. Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6! (Publisher: The Coriolis Group) Author(s): Peter G. Aitken ISBN: 1576102815 Publication Date: 08/01/98 function isIE4() { return( navigator.appName.indexOf("Microsoft") != -1 && (navigator.appVersion.charAt(0)=='4') ); } function bookMarkit() { var url="http://www.itknowledge.com/PSUser/EWBookMarks.html?url="+window.location+"&isbn=0"; parent.location.href=url; //var win = window.open(url,"myitk"); //if(!isIE4()) // win.focus(); } Search this book:  














Previous
Table of Contents
Next




To create the form, select Form from the Insert menu. Set the form’s Name property to lstForm, and its Caption property to List All Recordings. Save the form with the name FRMLIST2. Add a List Box and a Command Button control. Set the Command Button’s Caption property to &Close, making sure that the List Box’s Sort property is set to True. Don’t worry about the size and position of these controls, as they will be set in code. Listing 19.17 shows the objects and properties for this form.
Listing 19.17 Objects and properties in FRMLIST2.FRM.


Begin VB.Form lstForm
Caption = “List All Recordings”
Begin VB.CommandButton cmdClose
Caption = “&Close”
End
Begin VB.ListBox List1
End
End


The code in FRMLIST2.FRM contains only four event procedures. The Click procedure for the Command Button is simple, using only the Unload statement to unload the form and return the focus to the program’s main form.
The Form_Load procedure is somewhat more complex. Here you need to extract the Title field data from all of the records in the Recordings table and load them into the List Box (which automatically sorts them). Start by saving the current record’s bookmark, so you can return to it when you are finished:


Bookmark = Form1.Data1.Recordset.Bookmark


Next, we want to move to the first record in the Recordset, then loop one at a time through all of them. To do this, you need to know how many records exist. You can obtain this information from the Recordset object’s RecordCount property. Before you read this property, however, you must move to the end of the Recordset with the MoveLast method. Why? A Dynaset-type Recordset does not necessarily read all of the table’s records into memory at once; until it has actually read the entire table, it will not have an accurate record count. Therefore, you need to force a read to the end of the table with the MoveLast method. Now the RecordCount property will contain an accurate count of the records. After storing that value, you can use MoveFirst to move to the first record:


Form1.Data1.Recordset.MoveLast
NumRecs = Form1.Data1.Recordset.RecordCount
Form1.Data1.Recordset.MoveFirst


You are now ready to load the List Box. Using the Clear method first—to be sure the List Box is empty—you loop through the table, reading the Title field from each record and loading it into the List Box with the AddItem method. Finally, you return to the original record that was saved:


List1.Clear

For i = 0 To NumRecs - 1
x = Form1.Data1.Recordset.Fields(“Title”).Value
If IsNull(x) Then x = “”
Form1.Data1.Recordset.MoveNext
List1.AddItem x
Next i

Form1.Data1.Recordset.Bookmark = Bookmark


Note the complex line of code that actually retrieves the Title data from the current record and stores it in the string variable x. It may be easier to read this line from right to left: “The Value of the Field named Title in the Recordset that is associated with the Data control named Data1 on the form named Form1.”


TIP:  Control Names And Forms
Note the syntax you use to refer to a control on another form. A control name by itself automatically refers to the current form—that is, the form whose module contains the code. To refer to a control on a different form, precede the control name with the form name and a period. If the form referred to has not yet been loaded, it will be loaded (but not displayed).


How do you go about displaying a record on the main form when the user clicks on its title in the list? My approach is to use the FindFirst method to search for the record. Because the title was retrieved directly from the database table, you know a match will occur when we search for that title. You should place the required code in the List Box’s Click event procedure. When the user selects an item in a List Box—either by clicking the mouse or using the keyboard—the Click event is generated. Obviously, the program will permit either mouse or keyboard input. The numerical position of the selected item in the list is returned by the ListIndex property. To get the item itself, we use the ListIndex property as the index to the List Box’s List property. The following code places the title from the List Box in a query string:


Template = “[Title] = ” & Chr$(34) & List1.List(List1.ListIndex) & Chr$(34)
Form1.Data1.Recordset.FindFirst Template


Finally, you must code the form’s Resize event procedure, which sets the List Box and Command Button sizes to fill the form. We have already reviewed this procedure, so I won’t go into detail. The complete code for FRMLIST2.FRM is presented in Listing 19.18.
Listing 19.18 Code in FRMLIST2.FRM.


Option Explicit

Private Sub cmdClose_Click()

Unload lstForm

End Sub

Private Sub Form_Load()

Dim x As Variant, NumRecs As Integer
Dim i As Integer, Bookmark As String

Bookmark = Form1.Data1.Recordset.Bookmark
Form1.Data1.Recordset.MoveLast
NumRecs = Form1.Data1.Recordset.RecordCount
Form1.Data1.Recordset.MoveFirst

List1.Clear

For i = 0 To NumRecs - 1
x = Form1.Data1.Recordset.Fields(“Title”).VALUE
If IsNull(x) Then x = “”
Form1.Data1.Recordset.MoveNext
List1.AddItem x
Next i

Form1.Data1.Recordset.Bookmark = Bookmark

End Sub

Private Sub Form_Resize()

List1.Left = 0
List1.TOP = 0
List1.Width = lstForm.ScaleWidth
List1.Height = lstForm.ScaleHeight * 0.9
cmdClose.TOP = List1.Height + 1
cmdClose.Left = 0
cmdClose.Width = lstForm.ScaleWidth
cmdClose.Height = lstForm.ScaleHeight * 0.1

End Sub

Private Sub List1_Click()

Dim Template As String

‘ Set up the template and perform the search. Since
‘ we know that there must be a matching title we do not
‘ have to provide for the possibility of no match.

‘ We use Chr$(34) to enclose the search template in double
‘ quotes. This permits the template to contain single quotes, which
‘ is not possible if the template itself is enclosed in double quotes.

Template = “[Title] = ” & Chr$(34) & List1.List(List1.ListIndex) & Chr$(34)
Form1.Data1.Recordset.FindFirst Template

End Sub


This completes our musical recordings database program. It provides all the functionality of the address list program, but with fewer lines of code. The executing program, with both forms displayed, is shown in Figure 19.11. The ease with which we created this program reflects the power of Visual Basic’s Data control and the data-aware controls that can be linked to it. Even so, several features that would be desirable in even a simple database program are missing. No error trapping exists, which is perhaps the most serious omission. Error trapping permits a program to deal gracefully with errors, such as a corrupt database file, disk problems, and the like. Likewise, we have no data validation, ensuring that the user does not enter invalid data—for example, a title that is longer than the maximum 40 characters permitted by the table definition. We will delve into these and other important database topics in the following chapters.

Of course, this is a simple database program that requires none of the sophisticated and complex features of many commercial applications. Visual Basic’s database tools provide for these as well, and we’ll see in the next several chapters how to approach the design of a database program with a variety of features that would be required in a real-world project you might create for a paying client.

Figure 19.11  The musical recordings database program in operation.




Previous
Table of Contents
Next






Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.