By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

Using multiple check boxes to filter search on continous form

P: 5
I am using Ms Access 2013 and the resource for the search is taken from http://www.iaccessworld.com/how-to-create-search-form/

The supplier table(tblSupplier) with fields (SupplierID , Company , FirstName, LastName , BusinessPhone ,Address and City)

From this I have created a tabular view on a continuos form

The attachment shows the screen capture of the design.

The code is :

(1) DeSelectAll command button

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3.  
  4. Private Sub cmdDeselectAll_Click()
  5.  'Links this subroutine to the command button
  6.  'cmdDeSelectAll
  7.  'This command will uncheck all the following check boxes:
  8.   'ChkCompany
  9.   'ChkFirstName
  10.   'ChkLasName
  11.   'ChkBusinessPhone
  12.   'ChkAddress
  13.   'ChkCity
  14.  
  15.  Me.ChkCompany = False
  16.  Me.ChkFirstName = False
  17.  Me.ChkLastName = False
  18.  Me.ChkBusinessPhone = False
  19.  Me.ChkAddress = False
  20.  Me.ChkCity = False
  21.  
  22.  
  23. End Sub
  24.  

(2) Command button Search

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsearch_Click()
  2.  
  3. Dim strSearch As String
  4.  'Declare the variable strSearch as data type string
  5.  
  6. Dim Search  As String
  7.  'Declare the variable Search as data type string
  8.  
  9.  If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
  10.  
  11.    MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
  12.  
  13.    Me.txtSearch.SetFocus
  14.  
  15. Else
  16.  
  17. I don't know if the code is correct....  
  18.  
  19.  
  20.  
  21. strSearch = Me.txtSearch.Value
  22.  'The value from the text box txtSearh is assigned th
  23.  'the varuable strSearch
  24.  
  25. Search = "SELECT * from tblSupplier where ((FirstName Like ""*" & strSearch & "*""))"
  26.  
  27.  
  28. End If
  29.  
  30. End Sub
  31.  


(3)Command button SelectAll

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSelectAll_Click()
  2.  'Links this subroutine to the command button
  3.  'cmdSelectAll
  4.  'This command will check all the following check boxes:
  5.   'ChkCompany
  6.   'ChkFirstName
  7.   'ChkLasName
  8.   'ChkBusinessPhone
  9.   'ChkAddress
  10.   'ChkCity
  11.  
  12.  Me.ChkCompany = True
  13.  Me.ChkFirstName = True
  14.  Me.ChkLastName = True
  15.  Me.ChkBusinessPhone = True
  16.  Me.ChkAddress = True
  17.  Me.ChkCity = True
  18.  
  19.  
  20. End Sub
  21.  

(4) Command Button ShowAll

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowAll_Click()
  2.  'Links this subroutine to the command
  3.  'botton cmsShowAll
  4.  
  5. Dim strSearch As String
  6.  'Declare the variable strSearch as data
  7.  ' type string
  8.  
  9.  strSearch = "SELECT *FROM tblSupplier"
  10.   'The task value of task is obtained from
  11.   ' the table tblSupplier
  12.  
  13.  Me.RecordSource = strSearch
  14.   'The strSearch is assigned to the current
  15.   'record source
  16.  
  17. End Sub
  18.  

(5) On form Load

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. Dim Search As String
  4.  
  5. Search = "SELECT *FROM tblSupplier WHERE(SupplierID)is null"
  6.  
  7.         Me.RecordSource = Search
  8.  
  9.         Me.txtSearch.SetFocus
  10.  
  11. End Sub
  12.  
  13. Private Sub txtSearch_AfterUpdate()
  14.  'Links this subroutine to the event procedure
  15.  'After the update of the textbox txtSearch
  16.  
  17. Call cmdsearch_Click
  18.  'Call the command cmdSearch on click
  19.  
  20. End Sub
  21.  
I need help for the search query....
Attached Images
File Type: jpg Capture.jpg (34.1 KB, 113 views)
Jun 16 '14 #1
Share this Question
Share on Google+
2 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
Pranish01,

You have given us 5 snippets of code, but don't tell us which one works or does not work and any errors you get.

If you are referring to (5) above, why would you ever have a Supplier ID that was Null? That goes against jsut about any principle in database normalization.

Exactly what are you trying to do?
Jun 16 '14 #2

Expert 100+
P: 1,240
Pranish01,
It's important to remember that readers don't know what you know about what you've done. Nowhere do you explain what result you're getting or not getting. Be clear and complete with your questions.

I suspect the problem may be there in the command button for searching. In line 25 of the Command button search, try this instead:
Search = "SELECT * from tblSupplier where ((FirstName Like "'* & strSearch & "*'))"

Jim
Jun 16 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.