How to create Search Form part 2 : MS Access

How to create Search Form part 2

 

It is an expansion of How to Create Search Form Part 1. I assume you know how the search form works from the part 1. The selected option for searching will be added to the part 2. In this example, I will use the Customer Type as an option to select before searching for customer name.

I have four types of customers from the Customer Type table; Individual, Government, Business and Non-Profit.

 

 

 

Step #1 Create four check boxes for all four customer types and two buttons

Name checkbox1 as ChkInd for Individual

Name checkbox2 as ChkBus for Business

Name checkbox3 as ChkGov for Government

Name checkbox4 as ChkNoe for Non-Profit

Name button1 as cmdSelect for Select All

Name button 2 as cmdDeSelect for De-Select All

 

 

 

 

 

 

 

 

 

Step #2  Set the Default value of all four checkboxes to True. All options will be checked when form opened.

Step #3 Enter the VBA code below under the On Click Event Procedure of “De-Select All” button

Private Sub cmdDeSelect_Click()
Me.chkInd = False
Me.chkBus = False
Me.chkGov = False
Me.ChkNon = False
End Sub

 Step #4 Enter the VBA code below under the On Click Event Procedure of “Select All” button

Private Sub cmdSelect_Click()
Me.chkInd = True
Me.chkBus = True
Me.chkGov = True
Me.ChkNon = True
End Sub

 Step #5 Enter/revise the VBA code below under the On Click Event Procedure of “Search” button

 

Private Sub Command163_Click()
Dim strsearch As String
Dim Task As String
Dim strProgram As String
Dim Individual As Integer
Dim Government As Integer
Dim business As Integer
Dim nonProfit As Integer
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
   MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
   '......................
   'DOWNLOAD THE SAMPLE FILE FOR FULL CODE
   '.....................
   Me.RecordSource = Task
Me.txtSearch.BackColor = vbWhite
End If
End Sub

‘********************************

 Example 1:

Search for “Ma” on only individual option of customer type. There is only one customer on the list that has name contains “Ma”.

 

 

 

 

Example 2:

Search for “Ma” on only Business option of customer type. There are two customers on the list that have name contain “Ma”.

 

 

 

 

Example 3:

Search for “Ma” on all options of customer type. There are four customers on the list that have name contain “Ma”.

 

 

 

 

 

---

 

Search for two keywords

The above example only search for a single word. If you want to search customer name by entering two words like “John Smith” you will need to revise the search code a little bit. You need to change the code where it says (CustomerName Like “”*” & strsearch & “*””).

 Start with

Dim strSpaceFix as String

Dim strLoad as String

 ‘Load Text Box contents to strLoad variable

strLoad = Me.txtSearch.Value

 ‘Replace spaces with addition search code

strSpaceFix = Replace(strLoad, ” “, “” & “*”” AND [CustomerName] Like “”*” & “”, 1, -1)

 Then add strSpaceFix to the SQL statement

([CustomerName] Like “”*” & strSpaceFix & “*””)

 

Result of entering on key word searching for “john”

There are two records found for this search shown below:

 

 

 

 

 

Result of entering two key words searching for “john sm”

There is only one record of customer found from this search.

 

 

 

 

---

 Related Video:

 

It should be stored cialis pill at room temperature. The doctor will access the condition of the patient cialis 5mg cheap before prescribing the medicine. The response of endothelial cells is important for the woman’s sexual partner to understand what she would respond to and where she likes to be stimulated prior cialis on line More Info to settling for the real thing. Thus, if you are suffering from sildenafil rx any of the above mentioned capsules to overcome the problem for ever. Related Topic:  

How to Create Search Form Par 1