Hi all,
I managed to create a form that is able to search keywords on Access 2007. However, the keyword search must be exact to what is contained in the table.
For example, I stored "red blue yellow" into the database, and I can only search "red blue yellow", "red blue" or "blue yellow" in order to call that data.
How do I make it such that I am able to search "blue red yellow", "yellow blue red" or "red yellow blue"?
These are my current codes:
I managed to create a form that is able to search keywords on Access 2007. However, the keyword search must be exact to what is contained in the table.
For example, I stored "red blue yellow" into the database, and I can only search "red blue yellow", "red blue" or "blue yellow" in order to call that data.
How do I make it such that I am able to search "blue red yellow", "yellow blue red" or "red yellow blue"?
These are my current codes:
Code:
Private Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long
'If Me.Dirty Then
'Me.Dirty = False
'End If
If IsNull(Me.txtSearch) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtSearch, " ")
If UBound(varKeywords) >= 99 Then
MsgBox "Too many words."
Else
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "Directory.FName Like ""*" & strWord & "*"" AND "
End If
Next
IngLen = Len(strWhere) - 4
If IngLen > 0 Then
Me.Filter = Left(strWhere, IngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
Code:
Private Sub Command2_Click()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim filepath As String
Dim strSearch As String
Dim rst As DAO.Recordset
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
Me.txtSearch.BackColor = vbYellow
Me.txtSearch.SetFocus
Else
strSearch = Me.txtSearch
Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))")
If rst.EOF Then
MsgBox "File does not exist."
Else
Me.txtSearch.BackColor = vbWhite
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
filepath = rst![Directory]
Set wrdDoc = wrdApp.Documents.Open(filepath)
End If
End If
End Sub