Tutorials > Tutorial 22 - Performing a List Search Using the Find Method |
In this tutorial you will utilize the Find method in True DBList. The Find method allows you to perform custom searches within the control.
Start a new project.
Based on the following illustration, place one list control (TDBList1), 3 combo boxes (TDBCombo1, 2 and 3), a text box (Text2), a data control (Data1), a command button (Command1) and four labels on the form as shown in the illustration below.
Set the RowSource of TDBList1 to Data1.
Set the DataMode property of the three TDBCombo controls to 4 – Storage.
Define X1, X2 and X3 as New XarrayDB corresponding to the three TDBCombo Boxes. These variables will hold the values for the TDBCombo Boxes.
Example Title |
Copy Code
|
---|---|
Option Explicit
Dim X1 As New XArrayDB 'corresponding to 3 TDBComboboxes
Dim X2 As New XArrayDB
Dim X3 As New XArrayDB
|
To connect Data1 to the datasource and fill the three TDBCombo Boxes with values at runtime, add the following code:
Example Title |
Copy Code
|
---|---|
Private Sub Form_Load() 'put the form in the middle Me.Left = (Screen.Width - Me.Width) \ 2 Me.Top = (Screen.Height - Me.Height) \ 2 Data1.Visible = False Dim path As String On Error Resume Next path = App.path & "\..\..\TDBLDemo.mdb" 'database path 'use run time connection With Data1 .DatabaseName = path .RecordSource = "Customers" .Refresh End With 'fill combo1 X1.ReDim 0, 3, 0, 0 X1(0, 0) = "Partial Include" X1(1, 0) = "Equal" X1(2, 0) = "Less Than" X1(3, 0) = "Greater Than" With TDBCombo1 .ColumnHeaders = False .Array = X1 .BoundText = "Partial Include" End With 'fill combo2 X2.ReDim 0, 1, 0, 0 X2(0, 0) = "Start From Beginning" X2(1, 0) = "Start After Current Row" With TDBCombo2 .ColumnHeaders = False .Array = X2 .BoundText = "Start From Beginning" End With 'fill combo3 X3.ReDim 0, 6, 0, 0 X3(0, 0) = "UserCode" X3(1, 0) = "LastName" X3(2, 0) = "FirstName" X3(3, 0) = "Company" X3(4, 0) = "Contacted" X3(5, 0) = "Phone" X3(6, 0) = "CustType" With TDBCombo3 .ColumnHeaders = False .Array = X3 .BoundText = "UserCode" End With Text2.Text = "" End Sub |
To handle the Command1 click event, add the following code:
Example Title |
Copy Code
|
---|---|
Private Sub Command1_Click() On Error GoTo Err_Handle Dim matchCompare As Integer Dim searchPos As Integer Dim bk As Variant If Text2.Text = "" Then Exit Sub End If Select Case TDBCombo1.BoundText Case "Partial Include" matchCompare = dblSeekPartialEQ Case "Equal" matchCompare = dblSeekEQ Case "Less Than" matchCompare = dblSeekLT Case "Greater Than" matchCompare = dblSeekGT End Select Select Case TDBCombo2.BoundText Case "Start From Beginning" searchPos = 1 Case "Start After Current Row" searchPos = 2 End Select If searchPos = 1 Then bk = TDBList1.Columns(CStr(TDBCombo3.BoundText)).Find(Text2.Text, matchCompare, True) Else If IsNull(TDBList1.Bookmark) Then MsgBox "There is no current row" Exit Sub End If bk = TDBList1.Columns(CStr(TDBCombo3.BoundText)).Find(Text2.Text, matchCompare, False, TDBList1.Bookmark) End If If Not IsNull(bk) Then TDBList1.Bookmark = bk Else MsgBox "Can not find this value!" End If Exit Sub Err_Handle: MsgBox Err.Description End Sub |
Finally, add the code below to clear the memory:
Example Title |
Copy Code
|
---|---|
Private Sub Form_Unload(Cancel As Integer) Set X1 = Nothing Set X2 = Nothing Set X3 = Nothing End Sub |
Run the program and observe the following:
You should have a form that looks like this:
Set the Column Combo Box to LastName and the Compare Mode Combo Box to Partial Include. Then place a letter in the Search String(number) text box and press the Find button. Notice how the first item in the LastName column beginning with the letter you entered is found.
Next, set the Search Pos Combo Box to Start After Current Row and press the Find button. Notice how the next item in the LastName column beginning with the letter you entered is found.
The Find method will also let you search numeric strings. Set the Column Combo Box to Contacted and the Compare Mode Combo Box to Partial Include. Then place a date from the Contacted column in the Search String(Number) text box and press the Find button. Notice how the first item in the Contacted column with the date you entered is found.
When using ICursor, the Find method can include wildcards (*) to allow for searches containing certain letters or numbers (such as *86 to search for any date ending in the year 1986). OleDB does not allow for wildcards so the OleDB Tutorial 22 includes a Inside Include option in Compare Mode which will search for values inside strings. |