At the heart of any windows application are the components. These are the building blocks of the graphical user interface and encompass the business rules that drive the program. The controls (graphical objects) that users interact with on the screen are built with OCX components. This article will show you how to maximize the use of OCX components. One of the best ways to do this is to encapsulate end-user business rules inside OCX components. Doing so provides the following advantages:
The VB 5.0 or 6.0 compiler allows developers to create OCX components of any level of complexity. OCX components can make your VB development faster and easier. However, the more business rules your OCX components encapsulate, the less chances that it can be re-used in another project. But encapsulating specific business rules in OCX components in your VB project gives you good experience for future projects that might require more complex OCX components.
In addition to the MS Visual Basic Component Tool Guide book, there are two other helpful books for developing with OCX components: ActiveX Controls with Visual Basic 5.0 by Jose Mojica (IDG Books, Foster City, CA) and How to Program Visual Basic 5.0--Control Creation Edition by Jeffrey P. McManus (Ziff-Davis Press, Emeryville, CA). Both books describe the entire development cycle of any complex OCX component.
Find window assignment
Our sample VB project came from a client with a large MS Access database, with a master table that contains 48 child reference tables. Each child table is related to the master with a one-to-many relationship. One of the main user requirements was a generic Find window that allowed users to get a master record by selecting one record in each reference table.
Our personal VB development experience has shown that the Find (Search) window is probably the most heavily used window in any application. A well-designed search window allows the user to access information quickly for making business decisions. Therefore, it's extremely important this window is programmed for speed and efficiency.
At the outset, before the user even clicks on the Find button, we need to populate 48 Combo Box controls with the reference table data in the load event of the Find window, and then let the user select from the values in each Combo Box. If a record is found, the master window should be populated with the found record. If no record is found, the current record in the master window should remain. To populate the Combo Box control in the unbound mode using ADO 2.0, we use the standard procedure shown in Listing A.
Listing A: Populating the Combo Box in the unbound mode using ADO 2.0
Private Sub PopulateComboBox(pcboComboBox As _
ComboBox, poADOConnectionObject As ADODB. _
Connection, ByVal pvSQL As String)
On ErrorGoTo PopulateComboBoxError
Dim cmdCommandObject As ADODB.Command
Dim rdRecordsetObject As ADODB.Recordset
Dim varArrayData() As Variant
Dim lRecordCount As Long
Dim iFieldCount As Integer
Dim lLoop As Long
Dim lCollectionnCount As Long
Set cmdCommandObject = New ADODB.Command
Set rdRecordsetObject = New ADODB.Recordset
pcboComboBox.Clear
lCollectionnCount = collCollection.Count
If lCollectionnCount > 0 Then
For lLoop = lCollectionnCount To 1 Step -1
collCollection.Remove lLoop
Next lLoop
End If
With cmdCommandObject
Set .ActiveConnection = poADOConnectionObject
.CommandText = pvSQL
.CommandType = adCmdTableDirect
rdRecordsetObject.Open
cmdCommandObject, ,
adOpenForwardOnly,
adLockReadOnly
End With
Set cmdCommandObject = Nothing
Call GetRowsADORecordset(rdRecordsetObject, _
varArrayData, lRecordCount, iFieldCount)
If lRecordCount > 0 Then
For lLoop = 0 To lRecordCount - 1
pcboComboBox.AddItem
varArrayData(1, lLoop)
collCollection.Add
Item:=varArrayData(1, lLoop),
Key:=varArrayData(0, lLoop)
Next lLoop
End If
pcboComboBox.ListIndex = 0
rdRecordsetObject.Close
Set rdRecordsetObject = Nothing
PopulateComboBoxExit:
Exit Sub
PopulateComboBoxError:
MsgBox "Unable to populate the Combo Box. " & _
Err.Description, vbCritical, _
"Combo Box Population Error"
pcboComboBox.Clear
Resume PopulateComboBoxExit
End Sub
The Connection object conComboBox was set using the following procedure:
Private Sub
OpenADOConnectionObject(ByVal _
psConnectionString As String)
With conComboBox
.ConnectionString = _
psConnectionString
.CursorLocation = adUseClient
.Open
End With
End Sub
We have two comments about the PopulateComboBox procedure. First, the pvSQL parameter could be a dynamic SQL, Access stored query, or stored procedure from any database server (in this case, the CommandType = adCmdStoredProc).
Second, the GetRowsADORecordset function, shown in Listing B, gets multiple records of a recordset object and puts it into a variant two-dimensional array. To get more information about GetRows method of the ADO Recordset object, you can open the VB Help file or read the article, "Using the ADO GetRows method for faster data retrieval," in the June 1999 issue of Inside Visual Basic.
Listing B: The GetRows ADO Recordset function
Private Sub GetRowsADORecordset(prdADO As ADODB. _
Recordset, pvarArrayData As Variant, _
plRecordCount As Long, piFieldCount _
As Integer)
On Error GoTo GetRowsRecordsetError
If prdADO.EOF Then
plRecordCount = 0
Else
pvarArrayData = prdADO.GetRows()
plRecordCount = UBound(pvarArrayData, 2) + 1
piFieldCount = prdADO.Fields.Count
End If
GetRowsRecordsetExit:
Exit Sub
GetRowsRecordsetError:
MsgBox "Unable to use GetRows for ADO Recordset
Object. " & _
Err.Description, vbInformation, "GetRows Method Error"
Resume GetRowsRecordsetExit
End Sub
At this point we asked ourselves: Do we want to call this procedure 48 times in the load event of the Find window? Probably not. Even if we encapsulate this procedure in a VB ActiveX DLL file, it's not the most efficient design. We decided to create a new ActiveX component based on a standard VB Combo Box control with the encapsulation of three main functions: Populate (shown in Listing A), Set (shown in Listing C) and Get (shown in Listing D) text value. By encapsulating these three functions in the new Combo Box component, we can re-use it in any database windows application.
Listing C: The Set Combo Box Text function\
Private Sub SetComboBoxText(pcboComboBox As _
ComboBox, pcollCollection As Collection, _
ByVal sTextString As String)
On Error GoTo SetComboBoxTextError
If Len(sTextString) Then
If pcboComboBox.ListCount > 0
Then
pcboComboBox.Text = _
pcollCollection.Item
(sTextString)
End If
End If
On Error GoTo 0
SetComboBoxTextExit:
Exit Sub
SetComboBoxTextError:
pcboComboBox.ListIndex = -1
MsgBox "Unable to set the ComboBox Text Value", _
vbCritical, "ComboBox Error"
Resume SetComboBoxTextExit
End Sub
Listing D: The Get Combo Box Key function
Private Function GetComboBoxKey(pcboComboBox As _
ComboBox, pvArrayData As Variant) As Variant
On Error GoTo GetComboBoxKeyError
If pcboComboBox.ListCount > 0 Then
If m_DIndexPosition >= 0 Then
GetComboBoxKey = pvArrayData(0, _
m_DIndexPosition)
End If
End If
GetComboBoxKeyExit:
Exit Function
GetComboBoxKeyError:
GetComboBoxKey = "
MsgBox "Unable to get the ComboBox Text Value", _
vbCritical, "ComboBox Error"
Resume GetComboBoxKeyExit
End Function
The Collection object was a good approach for storing the primary key (collection key) and description (collection item) fields of the reference table in a single object. It's easy to get the item member using the key member of the collection object, as shown in the SetComboBoxText function in Listing C.
The real problem is figuring out how to get the key member using the item member. We need to set the Text property of the Combo Box control with its corresponding item member using the key value that came from the main table. The ItemData property of the Combo Box control is an array of long integer values with the same number of items as a control's List property.
When the primary key field in the reference table is a character data type, the ItemData won't be able to store the values of this key field. This functionality isn't available in the Collection object either. In this particular case, the Collection object and the ItemData property won't be able to get the key member by using the item member.
We solved this problem by using the variant dynamic array returned from the GetRowsADORecordset function in Listing B. As shown in the GetComboBoxKey function in Listing D, passing the index position of the item to the array with the column number 0 gives us the key value for the selected item.
DComboBox component development
Our DComboBox component was created using the ActiveX Control Interface Wizard. The component was built based on one standard VB Combo Box control. The new properties are shown in Table A.
Table A: DComboBox component properties
| Property | Data type | Use |
|---|---|---|
| DIndexPosition | integer | set/get the index position using ListIndex property |
| DKeyValue | variant | set/get the text value, Listing C and D |
| DconnectionString | string | set the connection string to open an ADO Connection object |
| DSource | string | set the command text of the ADO Command object |
The new method is called DPopulate and executes the population of the DcomboBox, as shown in Listing A. The DComboBox was tested in Test.vbp project with the Test.mdb Access database file. It contains a table tblName with Name_Code and Name_Description fields. If we name our DComboBox as DComboBoxTest, the following lines of code show how to populate the Combo Box:
With DComboBoxTest
.DConnectionString="Provider= _
Microsoft.Jet.OLEDB.3.51; _
Data Source=" & _
App.Path & "\" & "TEST.MDB"
.DSource = "qryNameAll"
.DPopulate
End With
The "qryNameAll" is the Access stored query as "SELECT tblName.Name_Code, tblName.Name_Description FROM tblName". If you set DSource = "tblName", the code will work without any problems.
To set the description name with a code, all it takes is one line of code. For example, by setting
DComboBoxTest.DKeyValue() = "E"
the name Ernest will appear highlighted in the DComboBoxTest. If the name John is selected in the DComboBoxTest, we can get the code name with the following statement:
VCodeName=DComboBoxTest.DKeyValue() _
and vCodeName is equal to "J".
This example demonstrates how you can manipulate the entire functionality of your DComboBox component with only several lines of code. In our VB project with 48 Combo Box components, this approach significantly reduced the amount of code required and sped up development time considerably. It was great for Microsoft to give VB developers the opportunity to create custom OCX components.
One thing we would like to mention about this particular component is the ability to bring up a name by typing in the first few letters of it (this is known as auto-expand). This feature is standard for most dropdown Combo Box components and many end-users have come to expect it. To provide auto-expand, we encapsulate the following code in the change event of the DComboBox component:
Dim sFindLetter As String
Dim plValue As Long
sFindLetter = Combo1.Text
If sFindLetter = " Then
If Combo1.ListCount > 0 Then
Combo1.ListIndex = _
m_DIndexPosition
End If
Else
plValue = _
SendMessage(Combo1.hwnd, _
CB_FINDSTRING, True, ByVal
sFindLetter)
If plValue >= 0 Then
Combo1.ListIndex = plValue
m_DIndexPosition = plValue
End If
End If
The SendMessage API function and the constant CB_FINDSTRING must be declared as:
Private Const CB_FINDSTRING = &H14C
Private Declare Function SendMessage _
Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As _
Long, ByVal wParam As Long, lParam _
As Any) As Long
ActiveX component distribution
Recently, we've received a lot of questions about ActiveX component distribution. Apparently many VB developers are having trouble understanding the setup process. The challenge is determining how to replace the old version with the new using the least amount of time and distribution files. This is really a question of version compatibility. VB has three options for handling ActiveX component version compatibility: No Compatibility, Project Compatibility, and Binary Compatibility, as show in Figure A.
Figure A: This is the Component tab of the project
properties.
If you open the project with Windows Notepad, you'll see the following line of code for our DcomboBox component:
Object={33E743CD-B903-11D2-B67D- _
008048FBE9AB}#3.0#0; DComboBox.ocx
The 36-character code 33E743CD-B903-11D2-B67D-008048FBE9AB represents the 16-bit Globally Unique Identifier. This code is automatically generated by the VB compiler every time you compile the control, copy it to the Windows system registry, and increase its size. The number #3.0#0 is the Version Control Identifier and DComboBox.ocx is the OCX control name.
We suggest the following steps for the setup process. During development and testing of the OCX control, you should use the Project Compatibility option (the default). When the control is ready to distribute for the first time, you should select the No Compatibility option and back up the control in another directory or use Source Safe for version control.
When the time comes for the next version upgrade, we recommend using the Binary Compatibility option pointed at the OCX control you created for the first distribution with the No Compatibility option. This way, you keep the same Globally Unique Identifier originally used in the Window's system's registry. As long as you don't change the public interface, you can use the Binary Compatibility option for future control upgrade versions.
If for any reason you need to change the public interface and you want to keep the Binary Compatibility option, you can create a new class file with the new methods, properties, and events, and simply comment out the old public interface logic and leave the procedure definition as is. This approach makes client updates a lot easier.
Conclusion
With just several lines of code, we're able to manipulate the entire functionality and behavior of the Combo Box control. We've given the Combo Box control to many of our VB developer friends and they find it very useful and a real time-saver. Now they want more. See "Selecting and deselecting all items in a List Box control" in this issue.
Copyright © 1999, ZD
Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD
Inc. Reproduction in whole or in part in any form or medium without
express written permission of ZD Inc. is prohibited. All other product
names and logos are trademarks or registered trademarks of their
respective owners.