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

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

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.