Chapter 3, "Using Visual Basic's Data Control," introduced you to the Data control and to bound (data-aware) TextBox controls. Visual Basic 3.0's Standard Edition included bound CheckBox, Image, Label, PictureBox, and TextBox controls; the
Professional Edition added VBXs for the MaskEdBox, and the 3D SSCheck and SSPanel controls. If your Visual Basic 3.0 application needed more sophisticated bound controls, you had to purchase VBXs from third-party control vendors. The Professional and
Enterprise versions of Visual Basic 4.0 now include a complete set of advanced bound controls implemented as 16-bit and 32-bit OLE Controls. To compensate for the loss of revenue brought about by Microsoft supplying DBGrid, DBCombo, and DBList controls,
third-party OCX suppliers offer a variety of enhanced data-aware OLE Controls.
A controversy exists among professional database developers and administrators regarding the use of bound controls for updating tables in production multiuser applications and especially in client/server front-ends. The primary issue relates to users
having a "live" connection to multiple database tables that, under some conditions, might jeopardize referential integrity or, to a lesser extent, data consistency. Jet 3.0's intrinsic transaction processing of action queries, combined with
automatic enforcement of referential integrity by Jet 2.x and 3.0 databases and client/server RDBMSs, lessen the risks previously associated with Visual Basic's (and Access's) bound controls. The primary advantage of advanced data-aware controls is
rapid application development (RAD). Thus, many developers use bound controls to develop application prototypes, then convert the prototypes to production applications that substitute unbound for bound controls. Passing values from unbound controls through
VBA code to Edit. . .Update or AddNew. . .Update operations, or to executable SQL statements lets you "wrap" update operations within explicit transactions.
Regardless of your stand (or your clients' or employer's position) on the bound-versus-unbound issue, every Visual Basic developer should understand the workings of data-bound controls. Data-bound controls let you quickly create prototypes of database
front-ends to which you later can add VBA code to upgrade the front-ends to production specifications. This chapter covers use of the basic DBGrid, DBCombo, and DBList controls plus specialized applications for the Image and PictureBox controls.
Applications for the bound OLE container control are described in the "Using Visual Basic 4.0's OLE Container Control" section of Chapter 14, "Integrating Database Front-Ends with OLE 2.1." Use of the DBGrid and DBCombo controls with
client/server databases is one of the subjects of Chapter 20, "Creating Front-Ends for Client/Server Databases," and Chapter 22, "Using the Remote Data Object."
The graphic design of the DBGrid control (Dbgrid32.ocx and DBGRID16.OCX) is related closely to Access 95's datasheet view of a subform control. When porting an Access application to Visual Basic, the similarity in the appearance and behavior of the
DBGrid control to Access 95's datasheet view is advantageous. Figure 9.1 shows a DBGrid control whose Data control is bound to the Customers table of Access 95's Northwind.mdb sample database. Figure 9.2 illustrates the datasheet view of an Access 95
subform, created by the Control Wizard, which also is bound to the Customers table of Northwind.mdb.
Figure 9.1. Emulating an Access 95 subform with a DBGrid control bound to a Data control.
Figure 9.2. The Access 95 subform that serves as a model for the Visual Basic form of Figure 9.1.
The preceding form and the following code examples are incorporated in the DBGrid1.vbp project in the \DDG_VB4\32_bit\Chaptr09 folder of the accompanying CD-ROM. An abbreviated version of Access 95's Northwind.mdb containing only the Customers table is included in this folder.
Adding the total number of records in the Data control's Recordset object to a label and displaying the record number corresponding to the current record requires adding the following variable declaration and simple event-handling procedures for the
Data control:
Dim fLoaded As Boolean
Private Sub dtcCustomers_Reposition()
If fLoaded Then
dtcCustomers.Caption = dtcCustomers.Recordset.AbsolutePosition
End If
End Sub
Private Sub Form_Activate()
dtcCustomers.Recordset.MoveLast
lblRecCount.Caption = "of " & dtcCustomers.Recordset.RecordCount
dtcCustomers.Caption = 1
dtcCustomers.Recordset.MoveFirst
fLoaded = True
End Sub
To bind a DBGrid control to a Data control you set the value of the DataSource property to the Name property value of the Data control. Unfortunately, the DataSource property of all bound controls is read-only in run mode, so you must change the
RecordSource property (and the DatabaseName property, if necessary) of the bound Data control if you want to use a single DBGrid control to display data from several sources. Like Access's datasheet view, an updatable Recordset object lets you add, edit,
and delete records with the DBGrid control. However, you must explicitly enable updating of the table(s) that underlie the Recordset by setting the value of the AllowAddNew and AllowDelete properties of the DBGrid to True in design or run mode. (The
default value of the AllowUpdate property is True.)
Visual Basic 4.0's DBGrid control does not support the built-in lookup feature of Access 95's datasheet view. As an example, the CustomerID and EmployeeID fields of the Orders table of Northwind.mdb (run with Access 95) look up the corresponding CompanyName and LastName plus FirstName field values of the Customers and Employees tables, respectively. When viewing a Jet 3.0 database table with the lookup feature enabled for one or more foreign key fields, the foreign key value (not the lookup value) appears in the DBGrid control's column(s).
The following sections describe how to use the bound DBGrid control in simple Visual Basic 4.0 database applications. You also can use the DBGrid as an unbound control, a subject that is beyond the scope of this chapter. (Search online help for
"DBGrid, unbound" to display a list of the properties of the unbound DBGrid control in the Topics Found dialog.)
The Properties window for the DBGrid control does not provide access to individual columns for formatting purposes. When you add a DBGrid control to a form, the default appearance of the control in design mode consists of two empty column headers and
two empty rows (see Figure 9.3). To format the DBGrid control, right click the surface of the control to display the pop-up menu, then choose Retrieve Fields to populate the column headers in design mode. Figure 9.4 shows the headings retrieved for the
first five columns of a DBGrid bound to a Data control with its RecordSource property set to the Titles table of the Biblio.mdb sample database.
To add a caption to the DBGrid control, change the column headings, or set the font attributes for the column headings and/or the remainder of the grid, then right-click the DBGrid control to open the pop-up menu and choose Properties. The Apex Data
Bound Grid Control Properties sheet provides the following four pages to format the DBGrid control:
Figure 9.7 shows the first four columns and first 12 rows of the Titles table of the Biblio.mdb database displayed by the dbgTitles control in run mode. The bold attribute is applied to the caption bar and the column headers font. The column width of
the Title and Year Published columns is adjusted in run mode for improved readability, but column width values are not persistent. Once you've adjusted column widths for readability, you can determine the new Width property value in twips for each Column
object using the Debug Window, as illustrated by Figure 9.8. Copy the entries in the Debug Window to the Form_Activate event handler to make the column width settings persistent.
Figure 9.7. The formatted DBGrid control in run mode.
Figure 9.8. Determining the adjusted width of DBGrid columns in the Debug Window.
When you paste the entries from the Debug Window to the Form_Activate event handler, ? becomes Print and the Width values appear in red below the Print statement. Only minor editing is needed to convert the Debug Window text to valid VBA code.
Following is an example of code to set the Width property of columns 0 through 2 and 4 through 7 for the dbgTitles control:
Private Sub Form_Activate() 'Set width of columns for readability dbgTitles.Columns(0).Width = 3345 dbgTitles.Columns(1).Width = 570 dbgTitles.Columns(2).Width = 1170 dbgTitles.Columns(4).Width = 1850 dbgTitles.Columns(5).Width = 1500 dbgTitles.Columns(6).Width = 3000 dbgTitles.Columns(7).Width = 3000 End Sub
The DBGrid control shares many properties, methods, and events with the unbound Grid control. The DBGrid control, however, offers a number of features that reduce the amount of VBA code necessary to alter the display of data or modify the displayed
data. The following sections describe how to sort grid data by column values, hide individual columns, and validate edits to the underlying Recordset.
Users of applications with DBGrid controls are likely to want to sort the underlying Recordset on fields other than the primary key field (if a primary key field exists). Visual Basic's online help suggests use of the HeadClick event to sort the rows
of the database in accordance with the values in the clicked column. A quick and easy method of sorting the Recordset of the Data control would appear to be use of the Sort property of the Recordset object of the Data control. (The Sort property, which is
valid only for Recordset objects of the Dynaset and Snapshot types, uses as its value the ORDER BY clause of an SQL statement, without the ORDER BY prefix.)
Unfortunately, changing the value of the Sort property doesn't work with bound controls; instead, you must change the value of the RecordSource property of the Data control and then apply the Refresh method. (If you apply the Refresh method to the Data
control after setting the Sort property value, the Sort property value is reset to Null.) The following event-handling subprocedure sorts the records in dbgTitles when you click a column header:
Private Sub dbgTitles_HeadClick(ByVal intColIndex As Integer)
'The following statement doesn't work
'dtcTitles.Recordset.Sort = "[" & _
_ ' dbgTitles.Columns(intColIndex).DataField & "] ASC"
'The following two statements do work
dtcTitles.RecordSource = "SELECT * FROM Titles ORDER BY [" & _
dbgTitles.Columns(intColIndex).DataField & "] ASC"
dtcTitles.Refresh
End Sub
Figure 9.9 shows the result of clicking the column header of the Title field. If you click the column header of the Comments field, you receive a Can't sort on Memo or OLE object ([Comments]) error message. You can disable sorting on Memo and OLE
Object fields by checking the field data type with the following code:
Private Sub dbgTitles_HeadClick(ByVal intColIndex As Integer)
'Sort on fields other than OLE Object (11) or Memo (12) types
If dtcTitles.Recordset.Fields(intColIndex).Type < 11 Then
dtcTitles.RecordSource = "SELECT * FROM Titles ORDER BY [" & _
dbgTitles.Columns(intColIndex).DataField & "] ASC"
dtcTitles.Refresh
Else
'Add "nocando" message box, if desired
End If
End Sub
Figure 9.9. The result of using the HeadClick event to sort the DBGrid's Recordset by clicking a column header.
You can control the number of columns displayed in run mode by manipulating the Columns collection of the DBGrid control. The simplest method of hiding one or more columns is to set the value of the Visible property of the appropriate Column object to
False. Hiding columns is useful in situations where the display area is limited and users must scan specific columns to find a particular record. The following two event-handling subprocedures hide selected columns and display all columns in
response to clicking the Some Columns and All Columns command buttons (see Figure 9.10).
Private Sub cmdAllCols_Click()
'Display all columns
Dim intCol As Integer
cmdSomeCols.Enabled = True
For intCol = 1 To dbgTitles.Columns.Count - 1
dbgTitles.Columns(intCol).Visible = True
Next intCol
cmdAllCols.Enabled = False
End Sub
Private Sub cmdSomeCols_Click()
'Hide unneeded columns
cmdSomeCols.Enabled = False
dbgTitles.Columns(1).Visible = False
dbgTitles.Columns(4).Visible = False
dbgTitles.Columns(5).Visible = False
dbgTitles.Columns(6).Visible = False
dbgTitles.Columns(7).Visible = False
cmdAllCols.Enabled = True
End Sub
Figure 9.10. Hiding columns of a DBGrid control.
An alternative to hiding and showing columns is to use the Remove and Add methods of the Columns collection. Removing and adding Column objects is a cumbersome and slower process that requires application of the Rebind and Refresh methods to ensure
that the proper columns appear. When adding a Column object to the Columns collection, you must set the value of the DataField property to the name of the corresponding field of the underlying Recordset and set the Visible property to True. (The
default value of the Visible property of an added column is False.)
Unfortunately, the DBGrid control does not support mixing bound and unbound columns. You can add an unbound Column to the Columns collection, but you cannot use the Value or Text property of the Column to display calculated values.
The DBGrid control has a variety of events that are triggered when you edit the underlying Recordset object. Table 9.1 lists the events that are triggered when you update a column value or delete, insert, or update a row in a DBGrid control. You add
validation code to the Before. . . events to ensure data consistency and to preserve referential integrity. (The "Validating Data with Visual Basic Code" section of Chapter 3 provides an example of typical validation code.) The Before. . . events
include a Cancel argument that, by setting Cancel = True, lets you abort the action in case the entry fails the validation test.
| Event | When Triggered |
| BeforeColUpdate | After altering the value of a cell and moving to a new cell, but before the content of the DBGrid buffer changes |
| AfterColUpdate | After changing the content of the DBGrid buffer for the updated column |
| BeforeDelete | After selecting a row and pressing the Delete key, but before the row is deleted from the Recordset |
| AfterDelete | After deletion of the row from the Recordset |
| BeforeInsert | After entering at least one character in a column of the tentative append record, but before the row is added to the Recordset |
| AfterInsert | After the row is added to the Recordset |
| BeforeUpdate | After changing the value in any column and moving to a new record, but before the Recordset is updated |
| AfterUpdate | After updating the row of the Recordset |
The code in Listing 9.1 shows examples that display message boxes to identify the point at which each event listed in Table 9.1 is triggered.
Listing 9.1. Code to create message boxes identifying the occurrence of Before. . . and After. . . events of the DBGrid control.
Private Sub dbgTitles_BeforeColUpdate(ByVal intColIndex As Integer, _
varOldValue As Variant, intCancel As Integer)
Dim strWarn As String
strWarn = strMsg & "update the column value from '" & _
varOldValue & "' to '" & _
dbgTitles.Columns(intColIndex).Value & "'?"
If MsgBox(strWarn, vbYesNo + vbQuestion, "Confirm Update") = _
vbNo Then
intCancel = True
End If
End Sub
Private Sub dbgTitles_AfterColUpdate(ByVal intColIndex As Integer)
MsgBox "Column " & intColIndex + 1 & " updated.", _
vbInformation, "Update Confirmation"
End Sub
Private Sub dbgTitles_BeforeDelete(intCancel As Integer)
Dim strWarn As String
strWarn = strMsg & "delete this row?"
If MsgBox(strWarn, vbYesNo + vbQuestion, "Confirm Delete") = _
vbNo Then
intCancel = True
End If
End Sub
Private Sub dbgTitles_AfterDelete()
MsgBox "Row deleted.", vbInformation, "Delete Confirmation"
End Sub
Private Sub dbgTitles_BeforeInsert(intCancel As Integer)
Dim strWarn As String
strWarn = strMsg & "add this new row?"
If MsgBox(strWarn, vbYesNo + vbQuestion, "Confirm Insert") = _
vbNo Then
intCancel = True
End If
End Sub
Private Sub dbgTitles_AfterInsert()
MsgBox "New row added.", vbInformation, "Insert Confirmation"
End Sub
Private Sub dbgTitles_BeforeUpdate(intCancel As Integer)
Dim strWarn As String
strWarn = strMsg & "update this row?"
If MsgBox(strWarn, vbYesNo + vbQuestion, "Confirm Update") = _
vbNo Then
intCancel = True
End If
End Sub
Private Sub dbgTitles_AfterUpdate()
MsgBox "Row updated.", vbInformation, "Update Confirmation"
End Sub
The preceding code examples are incorporated in the DBGrid2.vbp project in the \DDG_VB4\32_bit\Chaptr09 folder of the accompanying CD-ROM. A Jet 3.0 version of Biblio.mdb also is included in this folder. Data type prefixes have been added to names of arguments of the Before. . . events in accordance with the variable naming conventions used in this book.
DBCombo controls, and to a lesser extent, DBList controls, are useful for creating picklists that select a particular set of records to populate a DBGrid control. The following three sections describe how to use a DBCombo to supply the criterion for a
SQL WHERE clause that determines the content of a DBGrid control, a dual DBCombo approach to selecting a particular record for populating a set of bound text boxes, and using a DBCombo control to set the value of a foreign key field.
In the majority of cases, a DBCombo control's list is populated by a field of a base table. Unlike Access 95's ComboBox control, you can't specify more than one field to appear in a DBCombo list, nor can you set multiple field values as items in a
DBList control. Therefore, you must take advantage of the special properties of the DBCombo or DBList control that give you access to a value that doesn't appear in the control's list. Table 9.2 lists the special properties you use for a picklist created
from a DBCombo or DBList control.
| Property | Purpose |
| RowSource | The name of the Data control that provides the Recordset used to populate the control's list. |
| ListField | The name of the field of the Recordset whose members appear in the selection list. |
| BoundColumn | The name of the field of the Recordset that is used in a SQL WHERE clause to specify the row(s) returned to another Data control or to a dbName.OpenRecordset statement in VBA code. |
| BoundText | The value of the BoundColumn returned when the user makes a selection. |
| Style | The type of list (drop-down combo, simple combo, or drop-down list); the Style property applies only to combo boxes. The drop-down list is most commonly used for picklists. |
The Publishers table of the Biblio database is typical of base tables that use a primary key field (PubID) to join related tables and a descriptive field (Name or Company Name) for visual identification of the record. The DBCombo box
displays the descriptive field in its list element, but usually you must provide the primary key value for use in the SQL WHERE clause. To use a DBCombo control to select a set of records, follow these general steps:
Figure 9.11 shows an example of a simple form that displays in a DBGrid control records from the Titles table of the Biblio database corresponding to the publisher's name selected from the combo box list. In this case, ListFieldName is the Name
field and BoundFieldName is the PubID field of the Publishers table.
You can use an unbound ComboBox control that you populate with code in the Form_Load or Form_Activate event handler. Examples of uses of unbound ComboBox controls for populating grids appear in Chapter 22.
Listing 9.2 shows the code required to initialize the DBCombo control and populate the DBGrid control based on the selection made in the DBCombo control. The intArea (Area) argument of the Click event for a DBCombo control isn't documented in the
online help topic for the event. A value of dbcAreaList (2) corresponds to a click on the list element of the DBCombo control. See the help topic for "DBList and DBCombo Controls Constants" for other allowable values of the argument.
Listing 9.2. Code to use a DBCombo control to select records that populate a DBGrid control.
Option Explicit
Dim fWidthSet As Boolean
Private Sub dbcPublishers_Click(intArea As Integer)
If intArea = dbcAreaList Then
'Area in list clicked
Dim strSQL As String
dtcTitles.Caption = "Titles from " & dbcPublishers.Text
strSQL = "SELECT * FROM Titles WHERE PubID = " _
& dbcPublishers.BoundText
dtcTitles.RecordSource = strSQL
dtcTitles.Refresh
dtcTitles.UpdateControls
If Not fWidthSet Then
'Set the column widths once
Call SetColumnWidths
fWidthSet = True
End If
End If
End Sub
Private Sub SetColumnWidths()
'Set widths of columns for readability
dbgTitles.Columns(0).Width = 3500
dbgTitles.Columns(1).Width = 570
dbgTitles.Columns(2).Width = 1170
dbgTitles.Columns(4).Width = 1850
dbgTitles.Columns(5).Width = 1500
dbgTitles.Columns(6).Width = 3000
dbgTitles.Columns(7).Width = 3000
End Sub
Private Sub Form_Activate()
'Initialize the DBCombo control
dbcPublishers.SetFocus
SendKeys "{F4}{Down}{Enter}", True
Call dbcPublishers_Click(2)
End Sub
The preceding code example is incorporated in the DBCombo1.vbp project in the \DDG_VB4\32_bit\Chaptr09 folder of the accompanying CD-ROM.
You can use two DBCombo controls in tandem to select a specific record for display and editing with bound text boxes. The form shown in Figure 9.12 is created with Visual Basic 4.0's Data Form Designer with two DBCombo controls added. The left DBCombo
control selects the publisher; selecting a publisher populates the right DBCombo control with the titles from the publisher.
Figure 9.12. Using two DBCombo controls to select a single record for display by bound text boxes.
Listing 9.3 shows only the Click event-handling subprocedures for the two DBCombo controls that appear in Figure 9.12. Note that single quotes (') surround the BoundText String value to match entries in the ISBN primary key field of the Titles
table. The single quotes are not required for the numeric (Long Integer) PubID field of the Publishers table.
Listing 9.3. Click event handlers for two DBCombo controls that select a single record for display and editing.
Private Sub dbcPublishers_Click(intArea As Integer)
If intArea = dbcAreaList Then
'Area in list clicked
Dim strSQL As String
strSQL = "SELECT Title, ISBN FROM Titles WHERE PubID = " & _
dbcPublishers.BoundText
dtcTitles.RecordSource = strSQL
dtcTitles.Refresh
dtcTitles.UpdateControls
If dtcTitles.Recordset.RecordCount > 0 Then
'Display the first selection
dbcTitles.SetFocus
SendKeys "{F4}{Down}{Enter}", True
Call dbcTitles_Click(2)
End If
End If
End Sub
Private Sub dbcTitles_Click(intArea As Integer)
'Area in list clicked
If intArea = dbcAreaList Then
Dim strSQL As String
strSQL = "SELECT * FROM Titles WHERE ISBN = '" & _
dbcTitles.BoundText & "'"
dtcBook.RecordSource = strSQL
dtcBook.Refresh
dtcBook.UpdateControls
End If
End Sub
The preceding code example is incorporated in the DBCombo2.vbp project in the \DDG_VB4\32_bit\Chaptr09 folder of the accompanying CD-ROM.
Another common application for the DBCombo control is to supply a valid entry for a foreign key field during the editing process. Picking from a list of valid entries, rather than typing a value, ensures a match between primary and foreign key values.
Matching foreign to primary key values prevents creation of "orphan" records and maintains referential integrity when you use a DBCombo control of the drop-down list style. (Specifying the drop-down list style is equivalent to setting to
True the LimitToList property of Access 95's bound ComboBox control.)
The first three steps to add a DBCombo to update a field value are the same as for a DBCombo used as a picklist, but are repeated for completeness:
Figure 9.13 illustrates a sample form with a DBCombo control added by following the preceding steps, with the exception of step 6. The RecordSource property of the Title Data control (dtcTitlePub) is qryTitlePub, a query created in Access 95 with the
following SQL property value:
SELECT DISTINCTROW Titles.ISBN, Titles.Title, Titles.PubID,
Publishers.Name, Titles.[Year Published], Titles.Subject,
Titles.Description, Titles.Notes, Titles.Comments
FROM Publishers INNER JOIN Titles
ON Publishers.PubID = Titles.PubID;
The preceding updatable query simply adds the Name field of the Publishers table for verification when the PubID value is changed. The DBCombo is populated by the qryPublishers query, the RecordSource property of the dtcPublishers control, that adds
the sort order on the Name field of the Publishers table with the following Jet SQL statement:
SELECT DISTINCTROW Publishers.PubID, Publishers.Name FROM Publishers ORDER BY Publishers.Name;
Figure 9.13. Updating the publisher ID value from a selection in a DBCombo control.
The DBCombo3.vbp project that includes the form shown in Figure 9.13 is located in the \DDG_VB4\32_bit\Chaptr09 folder of the accompanying CD-ROM. The underlying queries are added to the Biblio95.mdb database that is located in the same folder.
As you select successive rows in the DBGrid control, the name of the publisher of the selected title appears in the text box element of the DBCombo control. If you select a different publisher name, the entry in the selected row doesn't change to
correspond with the selection until you move the record pointer to another row. The lack of a visual clue to a change in the PubID and Name cells of the edited row is likely to confuse data entry operators. Add the following code to update the record
immediately upon making a selection in the DBCombo control:
Private Sub dbcPublishers_Click(intArea As Integer)
If intArea = dbcAreaList Then
'The following code updates the record immediately
If dtcTitlePub.Recordset.AbsolutePosition = _
dtcTitlePub.Recordset.RecordCount - 1 Then
dtcTitlePub.Recordset.MovePrevious
dtcTitlePub.Recordset.MoveNext
Else
dtcTitlePub.Recordset.MoveNext
dtcTitlePub.Recordset.MovePrevious
End If
End If
End Sub
A more logical method for forcing an immediate update is application of the UpdateRecord method to the Data control. Alternatively, you might consider applying the Edit and Update methods in succession to the Recordset of the Data control. Either of these two approaches results in an Object Doesn't Support this Property or Method message. Interestingly, the UpdateRecord method works as advertised in online help, but you can't eliminate the error message with an On Error Resume Next instruction. (The error message isn't trappable.)
Visual Basic 4.0 includes bound PictureBox and Image intrinsic controls that you can use as an alternative to the bound OLE container control, described in the "Using Visual Basic 4.0's OLE Container Control" section of Chapter 14. Although
neither the PictureBox nor the Image control technically is an "advanced OLE control," the subject of this chapter, both controls store their data in fields of Jet's OLE Object field data type, called a LongBinary field by Visual Basic. The
advantage of PictureBox and Image controls is that these controls consume fewer system resources than the OLE container control and its associated OLE full server, usually Windows 95 Paint for bitmap images. Both PictureBox and Image controls accommodate
bitmap (.bmp, .dib, and .ico) and vector image (.wmf) files. (A .wmf file also can include a bitmapped image.)
The Image control is a "lightweight" object; it consumes fewer resources than a PictureBox control and paints faster, too. An Image control provides the ability to scale the image to fit the confines of the control by setting the value of the
Stretch property to True; the PictureBox control does not offer a Stretch property. Thus, the sample video image database application discussed in the following two sections uses the Image control.
There are a variety of applications for image databases, ranging from simple photographic cataloging systems to full-fledged workflow applications. One of the more interesting applications for image controls is capturing and storing picons,
which are still images representing the beginning (and often the ending) frame of a live video image. Very small picons often are used in video editing applications to identify video clips (an individual segment of recorded video content) on a
timeline. Adobe Premiere, as an example, uses picons to identify clips added to either of the two video timelines of its Construction window. The VidLog16.vbp application, whose sole form appears in Figure 9.14, is a simple 16-bit Visual Basic 4.0
application that logs the beginning and ending times of Hi8 analog video clips to produce a batch (.pbl) file that automates Adobe Premiere 4.2's digital video capture process. VidLog is the subject of an article titled "Use Jet for Realtime Device
Control" that appeared in the April 1996 issue of Visual Basic Programmer's Journal.
Figure 9.14. The VidLog form displaying a picon for a video clip.
VidLog is designed to control the operation of a Sony Hi8 videotape player called the Vdeck, which uses the Sony ViSCA (Video Systems Control Architecture) protocol. Sony discontinued production of the two primary ViSCA products, the CVD-1000 Vdeck and
the VI-1000 Vbox last September, but tens of thousands of these devices remain in use today. (The XV-D1000 video mixer and special effects generator has an undocumented ViSCA interface.) ViSCA lets you connect to a COM port up to seven Vdecks and/or Vboxes
in a daisy chain arrangement. The Vbox converts the ViSCA command set to Sony's consumer LANC (Local Application Numeric Code, also called Control-L) protocol that's standard on most Sony camcorders and high-end VCRs. The Vdeck and the Sony EV-S5000 and
EV-S7000 Hi8 VCRs can read and record RC timecode, which is a necessity for accurate logging of videotapes. You need a Vbox to control Hi8 camcorders, the new DCR-VX1000 consumer Digital Video (DV) camcorder, or an EV-S5000/EV-S7000 VCR with ViSCA.
Videotape timecode is based on the SMPTE (Society of Motion Picture and Television Engineers) HH:MM:SS:FF format, where FF represents video or movie frames.
Windows 95 includes a built-in ViSCA driver (MCIVISCA.DRV) for the Media Control Interface (MCI), so you can use the 16-bit mciSendString or 32-bit mciSendStringA functions to issue simple English-language commands to ViSCA devices, which MCI calls the
vcr device type. A full description of the MCI command string syntax for the vcr device type is included in the "Media Control Interface" section of the Win32 SDK and Chapter 15, "MCI VCR Services," of the Video for
Windows 1.1 SDK on the MSDN Development Library CD-ROM. Before you can use either function, you must declare two function prototypes (mciSendString and mciGetErrorString) contained in 32-bit WINMM.DLL or 16-bit MMSYSTEM.DLL; both of these libraries are
installed during Windows 95 setup. Listing 9.4 shows the prototype declarations for the two functions needed for 32-bit and 16-bit applications that use MCI command strings.
Listing 9.4. Function prototype declarations required to use MCI command strings.
Option Explicit
#If Win32 Then
'Windows 95 MCI functions
Declare Function mciSendString Lib "winmm.dll" _
Alias "mciSendStringA" _
(ByVal lpstrCommand As String, _
ByVal lpstrReturnString As String, _
ByVal uReturnLength As Long, _
ByVal hWndCallback As Long) As Long
Declare Function mciGetErrorString Lib "winmm.dll" _
Alias "mciGetErrorStringA" _
(ByVal dwError As Long, _
ByVal lpstrBuffer As String, _
ByVal uLength As Long) As Long
#Else
Declare Function mciSendString Lib "mmsystem" _
(ByVal lpstrCommand As String, _
ByVal lpstrReturnString As String, _
ByVal uReturnLength As Integer, _
ByVal hWndCallback As Integer) As Long
Declare Function mciGetErrorString Lib "mmsystem" _
(ByVal wError As Long, _
ByVal lpstrBuffer As String, _
ByVal uLength As Integer) As Integer
#End If
Public mciCommand As String 'Command
Public mciReturn As String * 256 'Return string
Public mciErrString As String * 256 'Error string
Public mciError As Long 'Error number
#If Win32 Then
Public mciReturnLen As Long 'Length of return string
Public mcihWnd As Long 'Callback window handle
Public mciErrLen As Long 'Length of error string
Public mciErrResult As Long 'Return value of error
#Else
Public mciReturnLen As Integer
Public mcihWnd As Integer
Public mciErrLen As Integer
Public mciErrResult As Integer
#End If
The VidLog16.vbp project is located in the \DDG_VB4\16_bit\Chaptr09 folder of the accompanying CD-ROM. VidLog16.mdb, located in the same folder, includes samples of 320- by 240-pixel live video captures using the Play Inc.'s Snappy video capture device. If you have a Snappy, you can download SNAPPY.VBX, a component of SNAP_SDK.EXE, from Play, Inc.'s Web site, http://www.play.com. (Code to copy to the Clipboard images captured by the Snappy is commented in VidLog16.frm.) To use the VidLog application, you need at least one ViSCA device connected to a COM port. If run VidLog without a ViSCA device, click OK when the Please insert a tape in the drive message appears.
You set up the vcr device (alias Vdeck in VidLog) in the Form_Load event handler of the main VidLog form, as shown in Listing 9.5.
Listing 9.5. Typical initialization code for the MCI vcr device type.
Private Sub Form_Load()
'Set up initial conditions
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
fMarkIn = True
txtIn.Font.Bold = True
fMarkOut = True
txtOut.Font.Bold = True
dtcLog.EOFAction = 0
'Open the ViSCA device
Call SendCommand("Open vcr1 alias Vdeck")
'Check for presence of tape in drive
Call SendCommand("Status Vdeck media present")
If txtReturn.Text <> "true" Then
'No tape in drive
MsgBox "Please insert a tape in the drive.", 0, "Drive Not Ready"
End If
'Turn on ViSCA RCTC detection
Call SendCommand("Set Vdeck time mode detect")
'Set the time code to SMPTE drop-frame (29.97 fps)
Call SendCommand("Set Vdeck time format smpte 30 drop")
'Set screen display to timecode
Call SendCommand("Set Vdeck index timecode")
'Turn on timecode display, if available
Call SendCommand("Index Vdeck on")
'Set the preroll time to 20 frames
Call SendCommand("Set Vdeck preroll 00:00:00:20")
'Clear the Clipboard
Clipboard.Clear
End Sub
VidLog's behind-the-scenes data acquisition activity consists of a Timer control that interrogates the Vdeck device to determine the tape position. Every 100 milliseconds (about 3 video frames), the timSMPTE_Timer event handler interrogates the Vdeck,
which returns the current tape position as a SMPTE HH:MM:SS:FF string to the txtReturn text box. If the in and/or out points of the video clip aren't set, the current tape position is continuously written to the bound txtIn and/or txtOut text boxes with
the following code:
Private Sub timSMPTE_Timer()
'Load timecode for in and out point text boxes
'Timer interval is set at 100 ms (3+ frames)
Call SendCommand("Status Vdeck Position")
'Don't overwrite existing clip in and out points
If Not fMarkIn Then
txtIn.Text = txtReturn.Text
End If
If Not fMarkOut Then
txtOut.Text = txtReturn.Text
End If
End Sub
Marking the in and out points with the Mark In and Mark Out command buttons while the tape is moving can cause up to a four-frame offset. The tape position buttons at the bottom of the form are the primary means to obtain frame-accurate clips; you use
the Pause, Forward, and Reverse buttons to move to a specific frame with the Step command, as shown in Listing 9.6. Alternatively, you can type the timecodes in the In and Out text boxes. Clicking the Test Clip button plays the clip so you can visually and
aurally check your in and out points.
Listing 9.6. Event handlers for controlling operation of a MCI vcr device.
Private Sub cmdTest_Click()
#If Win32 Then
'Cue the deck to play from in point to out point
Call SendCommand("Cue Vdeck from " & txtIn.Text & _
" to " & txtOut.Text & " preroll wait")
Call SendCommand("Play Vdeck")
#Else
'Cue doesn't work in 16-bit mode, so play the clip
Call SendCommand("Play Vdeck from " & txtIn.Text _
& " to " & txtOut.Text)
#End If
End Sub
Private Sub cmdLoad_Click()
'Rewind to 2 minute standard start position
Call SendCommand("Set Vdeck time mode detect wait")
Call SendCommand("Seek Vdeck to 00:02:00:00")
End Sub
Private Sub cmdReverse_Click()
If fMarkIn And fMarkOut Then
'Rewind in 10X mode
Call SendCommand("Play Vdeck reverse scan")
Else
'Single step reverse
Call SendCommand("Step Vdeck by 1 reverse")
End If
End Sub
Private Sub cmdStop_Click()
'Stop the drive
Call SendCommand("Stop Vdeck")
End Sub
Private Sub cmdPause_Click()
'Put the deck in pause mode
Call SendCommand("Pause Vdeck")
End Sub
Private Sub cmdPlay_Click()
'Put the deck in play mode
Call SendCommand("Play Vdeck")
End Sub
Private Sub cmdForward_Click()
If fMarkIn And fMarkOut Then
'Play in 10X forward mode
Call SendCommand("Play Vdeck scan")
Else
'Single step forward
Call SendCommand("Step Vdeck by 1")
End If
End Sub
Private Sub cmdEject_Click()
'Stop the tape drive, if running
Call SendCommand("Stop Vdeck wait")
'Rewind to the beginning of the tape
Call SendCommand("Seek Vdeck to start wait")
'Eject the tape
Call SendCommand("Set Vdeck door open")
End Sub
If you have a video overlay card, you can use the MCI overlay device to save the content of the video buffer to a .bmp or .dib file, then use Visual Basic's LoadPicture function to transfer the data from the file to the Picture property of the Image
control. A more elegant (and usually less expensive) approach is to use Play, Inc.'s Snappy video capture device and the SNAPPY.VBX custom control for picon capture. Snappy, which has a street price of about $180, plugs into your PC's printer port and has
RCA connectors for composite analog video input from a VCR or camcorder and optional loop-through output to a TV monitor. Installation of the Snappy takes no more than five minutes and, unlike higher-priced video overlay cards, you don't need an open
expansion slot or a connection to your graphic card's feature connector. If you use SNAPPY.VBX, you're limited to 16-bit Visual Basic 4.0 and a Jet 2.5 (Access 2.0) .MDB file to store the clip data and images.
Jet tables that contain full-color (15-bit or 24-bit color depth) images become very large, especially if you add images of 320- by 240-pixel or larger size. As an example, a 320- by 240-pixel image with 24-bit color depth requires 320 * 240 * 3 or more than 230KB of storage space.
You set the Snappy control's SnapFileType property to 1 - DIB on Clipboard to place a copy of the captured device-independent bitmap on the Clipboard. Set the value of the SnapSize property to 1 - Still, SnapSource to 0 - Tape, SnapState to 0 - Off,
Stretch to True, VideoThru to False (unless you connect a TV monitor to the Snappy's video output connector), and Visible to False. The following code example from VidLog captures a 320- by 240-pixel image, pastes the image into the
data-bound 180- by 120-pixel imgPicon control (with the Stretch property set to True), then saves the data from imgPicon in a LongBinary field of the VidLog16.mdb database:
Private Sub cmdSnap_Click()
'Place the deck in pause mode for capture
Call SendCommand("Pause Vdeck wait")
'Copy a DIB to the Clipboard with the Snappy
snp320x240.SnapState = 2
'Paste the data to the Image control
imgPicon.Picture = Clipboard.GetData()
'Update the record in place
dtcLog.Recordset.Edit
dtcLog.Recordset.Update
End Sub
Don't try adding code to the PicSnapped event-handler to paste the DIB to the Image control; the PicSnapped event triggers when Snappy grabs the video data, long before the capture process is complete.
There are a variety of commercial applications for Play's Snappy in real-time video image acquisition. Examples include creating employee identification badges with low-cost color printers, capturing images for industrial quality control, automating
surveillance systems (including high-resolution blowups of surveillance tapes), and publishing semi-live still pictures on the Internet. For Internet applications, use Snappy's variable-quality JPEG format (SnapFileType = JPEG) and save the image to
the .jpg file that's linked to your home page. You can use MCI commands to start and stop the recording process (if your camcorder has a LANC connector) and take advantage of the camera-direct output to produce sharper images. Snappy's rendering of video
content, even from low-cost camcorders, offers substantially better quality than still pictures shot with today's $1000 consumer-grade electronic cameras. Images captured from a Sony DCR-VX1000 DV camcorder in Snappy's maximum-resolution (1500- by
1125-pixel) mode with the 4 - Highest value for the SnapQuality property rival pictures shot on 35-mm film.
The objective of VidLog is to create an ASCII batch capture file for Adobe Premiere from the Log table of VidLog16.mdb. Premiere's batch capture file import format requires a tab-separated text file with Reel Name (tape number), In, Out, and File Name,
with optional Comment and Settings fields (see Figure 9.15). Controls are bound to the Recordset object of the dtcLog Data control, so a Recordset clone is used to create the required text file (see Listing 9.7.) A drive letter prefix, determined by the
estimated accumulated size of the clip files, is added to the .AVI file name.
Listing 9.7. Code to create a formatted text file from a RecordsetClone object.
Private Sub cmdExport_Click()
'Export the log in Adobe Premiere batch format
Dim rsdLog As Recordset
Dim strLog As String
Dim strID As String
Screen.MousePointer = 11
'Create a recordset clone
Set rsdLog = dtcLog.Recordset.Clone
'Delete the existing file
On Error Resume Next
Kill "VidLog.txt"
On Error GoTo 0
'Open the batch file
Open "VidLog.txt" For Append As #1
rsdLog.MoveFirst
Do Until rsdLog.EOF
'Create padded four-character clip ID string
Select Case rsdLog(0)
Case Is > 999
strID = CStr(rsdLog(0))
Case Is > 99
strID = "0" & CStr(rsdLog(0))
Case Is > 9
strID = "00" & CStr(rsdLog(0))
Case Else
strID = "000" & CStr(rsdLog(0))
End Select
'Reel name
strLog = "Tape " & rsdLog(1) & Chr$(9)
'In point
strLog = strLog & rsdLog(2) & Chr$(9)
'Out point
strLog = strLog & rsdLog(3) & Chr$(9)
'.AVI file name (no extension)
strLog = strLog & "Clip" & strID & Chr$(9)
'Scene
strLog = strLog & rsdLog(7)
'Append the record to the batch file
Print #1, strLog
rsdLog.MoveNext
Loop
'Close everything
Close #1
rsdLog.Close
Set rsdLog = Nothing
Screen.MousePointer = 0
End Sub
This chapter described the use of the data-bound DBGrid for browse-mode display and editing of desktop database tables. Although the examples use Jet 3.0 .mdb files, the code included in this chapter is equally applicable to foreign file types, such as
dBASE, FoxPro, and Paradox table files. The use of DBCombo controls to create picklists and set foreign-key values also was covered. The chapter concluded with an example of the use of the native Image control to store bitmaps in the LongBinary fields of
Jet databases.
The remaining two chapters of Section III, "An Introduction to Database Front-End Design," show you how to graph summarized (rolled-up) data and how to print reports using VBA code and the 32-bit Crystal Reports OLE Control that's included
with the Professional and Enterprise versions of Visual Basic 4.0.
