19/04/2016
How to Create a Search Form by Keyword using VBA
Search on a single field
Private Sub btnSearch_Click()
Dim strsearch As String
Dim Task As String
'Check if a keyword entered or not
If IsNull(Me.txtsearch) Or Me.txtsearch = "" Then
MsgBox "Please type in Employee Number.", vbOKOnly, "Employee Number Needed"
Me.txtsearch.BackColor = vbYellow
Me.txtsearch.SetFocus
Else
strsearch = Me.txtsearch.Value
Task = "SELECT * FROM EMPLOYEE WHERE ((employeenumber Like ""*" & strsearch & "*""))"
Me.RecordSource = Task
Me.txtsearch.BackColor = vbWhite
End If
End Sub
Search data from multiple fields using operator “OR”
The result of using OR operator between fields will be wider than single field because all data that matching the search keyword from multiple fields will show on the result. For instance, searching keyword for “Fresno” on three fields: “CustomerName, City or Address” the result will show as follow:
Private Sub Command66_Click()
Dim strsearch As String
Dim Task As String
'Check if a keyword entered or not
If IsNull(Me.txtSearchname) Or Me.txtSearchname = "" Then
MsgBox "Please type in Employee Name.", vbOKOnly, "Employee Name Needed"
Me.txtSearchname.BackColor = vbYellow
Me.txtSearchname.SetFocus
Else
strsearch = Me.txtSearchname.Value
Task = "SELECT * FROM EMPLOYEE WHERE ((firstname Like ""*" & strsearch & "*"") OR (lastname Like ""*" & strsearch & "*"") OR (Address Like ""*" & strsearch & "*""))"
Me.RecordSource = Task
End If
End Sub
