Search a string and display all items containing that string

[Richard Alvarez]

i put a search on my program and i use SQL. I want my search function to search even just the part of the string in my access database. how would i do that?

Private Sub Form_Load()
    
    Dim rsData As ADODB.Recordset
    
    ' load the database
    Set DBConn = LoadDatabase(App.Path & "\dbase\dbmain.mdb")
    
    ListEmployees
    Me.Timer2.Enabled = False
End Sub

Private Sub Form_Resize()
    Me.proOne.Width = Me.ScaleWidth - 9100
    Me.txtSearch.Width = Me.ScaleWidth - (Me.txtSearch.Left * 20)
    Me.lstMaster.Width = Me.ScaleWidth - (Me.lstMaster.Left * 2)
    Me.lstMaster.Height = Me.ScaleHeight - 1600
End Sub



Private Sub lstMaster_DblClick()
    If Not (Me.lstMaster.SelectedItem Is Nothing) Then
        frmNew.txtID.Text = Me.lstMaster.SelectedItem.SubItems(0)
        frmNew.txtFName.Text = Me.lstMaster.SelectedItem.SubItems(1)
        frmNew.cboPosition.Text = Me.lstMaster.SelectedItem.SubItems(2)
        frmNew.txtStatus.Text = Me.lstMaster.SelectedItem.SubItems(3)
        frmNew.txtRate.Text = Me.lstMaster.SelectedItem.SubItems(4)
        frmNew.dtpStart.value = Me.lstMaster.SelectedItem.SubItems(5)
        frmNew.dtpEnd.value = Me.lstMaster.SelectedItem.SubItems(6)
        frmNew.txtAge.Text = Me.lstMaster.SelectedItem.SubItems(7)
        frmNew.txtAddress.Text = Me.lstMaster.SelectedItem.SubItems(8)
        
        frmNew.cmdUpdate.Caption = "&Update"
        
        frmNew.Show
        Unload Me
    End If
End Sub

Private Sub mnuExit_Click()
    frmMain.Show
    Unload Me
End Sub

Private Sub mnuManageUsers_Click()
    frmManageUsers.Show
    Unload Me
End Sub

Private Sub mnuNew_Click()
    frmNew.Show
    Unload Me
End Sub

Private Sub Timer1_Timer()
If Me.proOne.value = "100" Then
    Call SearchAll
    Me.Timer1.Enabled = False
    Me.Timer2.Enabled = True
Else
    Me.proOne.value = Me.proOne.value + Val(1)
End If
End Sub

Private Sub Timer2_Timer()
    If Me.Label1.Visible = True Then
        Me.Label1.Visible = False
    ElseIf Me.Label1.Visible = False Then
        Me.Label1.Visible = True
    End If
End Sub

Private Sub txtSearch_Click()
    Me.txtSearch.Text = ""
    Me.Timer2.Enabled = False
End Sub

Private Sub txtSearch_GotFocus()
    Me.txtSearch.Text = ""
End Sub

Private Sub ListEmployees()
Set rsData = DBConn.Execute("SELECT EmpID, EmpName, EmpPosition, EmpStatus, EmpRate, DateHired, Endo, EmpAge, EmpAddress FROM tblMasterData")
    
    Me.lstMaster.ListItems.Clear
    If rsData.RecordCount > 0 Then
        rsData.MoveFirst
        
        Do Until rsData.EOF Or rsData.BOF
            With Me.lstMaster.ListItems.Add(, , rsData("EmpID").value & "")
                .SubItems(1) = rsData("EmpName").value & ""
                .SubItems(2) = rsData("EmpPosition").value & ""
                .SubItems(3) = rsData("EmpStatus").value & ""
                .SubItems(4) = rsData("EmpRate").value & ""
                .SubItems(5) = rsData("DateHired").value & ""
                .SubItems(6) = rsData("Endo").value & ""
                .SubItems(7) = rsData("EmpAge").value & ""
                .SubItems(8) = rsData("EmpAddress").value & ""
            End With
            rsData.MoveNext
        Loop
    End If
End Sub

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        If Me.txtSearch.Text <> "" Then
            Me.proOne.Visible = True
            Me.proOne.value = 0
            Me.Timer1.Enabled = True
            Me.Timer2.Interval = 400
        Else
            Me.Label1.Caption = "Type in a name to search."
            Me.Label1.ForeColor = vbRed
            Me.txtSearch.SetFocus
        End If
    End If
End Sub

Private Sub SearchAll()
Set rsData = DBConn.Execute("SELECT * FROM tblMasterData WHERE EmpName = '" & Me.txtSearch.Text & "'")

Me.lstMaster.ListItems.Clear
    If rsData.RecordCount > 0 Then
        rsData.MoveFirst
        
        Do Until rsData.EOF Or rsData.BOF
            With Me.lstMaster.ListItems.Add(, , rsData("EmpID").value & "")
                .SubItems(1) = rsData("EmpName").value & ""
                .SubItems(2) = rsData("EmpPosition").value & ""
                .SubItems(3) = rsData("EmpStatus").value & ""
                .SubItems(4) = rsData("EmpRate").value & ""
                .SubItems(5) = rsData("DateHired").value & ""
                .SubItems(6) = rsData("Endo").value & ""
                .SubItems(7) = rsData("EmpAge").value & ""
                .SubItems(8) = rsData("EmpAddress").value & ""
            End With
            Me.Label1.Caption = "Found Existing Record(s)."
            rsData.MoveNext
        Loop
    Else
        Me.Label1.Caption = "No Record(s) Found!"
    End If
End Sub

the problem is that it searches for the whole match of the string. i want it to be like this
the field contains john Castro, Robert So, Rick Williams ....
and if i type "john" it will display all records with the word "john".

please help me with.
much appreciated.

[Rabbit]

Instead of the = operator, use the like operator with wildcards.

[Richard Alvarez]

That "LIKE" thing, is that something like this;

Set rsData = DBConn.Execute("SELECT * FROM tblMasterData WHERE EmpName LIKE '" & Me.txtSearch.Text & "'")

Sir i'm sorry to admit that i'm new with these SQL lines.
Culd you give me some example please?

[Rabbit]

The exact syntax and wildcards depend on the DBMS that you're using.

SELECT *
FROM SomeTable
WHERE SomeField LIKE '%hello%'

[Richard Alvarez]

In my program, the user will input his/her query in a txtbox, so how would I going to replace that '%hello%' with my txtbox like '"%me.txtFName. txt%"'?

[Richard Alvarez]

Sir here is what I did but still don't know if it's correct.

Set rsData = DBConn.Execute("SELECT * FROM tblMasterData WHERE EmpName LIKE" & "*" & Val(Me.txtSearch.Text) & "*")

[Rabbit]

You do it the same way you have it in your original code. Except now you're using Like and wildcards as part of the string.