Results 1 to 15 of 15
  1. #1
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145

    Split Forms with ApplyFilter Search Box VBA

    Hi Forum,

    I am trying to use a create a search box for my split form. I'm using an unbounded text box with a command button. I'm trying to figure out a VBA code for the command button to search related (wild) words in all fields in my query (5 fields). Only thing I've figured out is probably DoCmD.Apply Filter,

    Below is a picture. I want to be able to "key" words in "Personnel", "Specifications", "Inspected Damages", "Comments".



    Click image for larger version. 

Name:	2018-08-14 20_32_01-Access - FrontEnd User 8-29-17 _ Database- C__Users_QEI_Desktop_FrontEnd Use.png 
Views:	15 
Size:	23.0 KB 
ID:	35114

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    It's unclear whether you want a separate search form for each of the fields or a single search form to all the fields, like this:
    Adjust names as required
    Code:
    Public Sub cmdSearch_Click()
        Dim strSearch As String
        Dim strFilter As String
        strSearch = "'*" & Forms![YourFormName].txtCommentSearch & "*'"
        Debug.Print strSearch
        strFilter = _
            "[Personnel] Like " & strSearch & _
            " Or [Specifications] Like " & strSearch & _
            " Or [InspectedDamages] Like " & strSearch & _
            " Or [Comments] Like " & strSearch & _
            " Or [SupplierVendor] Like " & strSearch
    
    
        Debug.Print strFilter
        Me.Filter = strFilter
        Me.FilterOn = True
    End Sub

  3. #3
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by davegri View Post
    It's unclear whether you want a separate search form for each of the fields or a single search form to all the fields, like this:
    Adjust names as required
    Code:
    Public Sub cmdSearch_Click()
        Dim strSearch As String
        Dim strFilter As String
        strSearch = "'*" & Forms![YourFormName].txtCommentSearch & "*'"
        Debug.Print strSearch
        strFilter = _
            "[Personnel] Like " & strSearch & _
            " Or [Specifications] Like " & strSearch & _
            " Or [InspectedDamages] Like " & strSearch & _
            " Or [Comments] Like " & strSearch & _
            " Or [SupplierVendor] Like " & strSearch
    
    
        Debug.Print strFilter
        Me.Filter = strFilter
        Me.FilterOn = True
    End Sub
    I'm having problems with this code.

    Please look at the images. Thanks!

    Click image for larger version. 

Name:	Problem 1.png 
Views:	13 
Size:	51.5 KB 
ID:	35119Click image for larger version. 

Name:	Problem 2.png 
Views:	13 
Size:	73.1 KB 
ID:	35120

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    The names in the square brackets must be the names of the controls on your form.
    The debug.prints are to help you diagnose problems.

  5. #5
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Yes they are.
    Attached Thumbnails Attached Thumbnails Problem 3.png  

  6. #6
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    is there something wrong with the Me.Filter = strFilter?

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Simplify the search by commenting out all of the terms except one. Then when that works, add another and repeat.
    There might be a problem searching the comboboxes. Depending how they are set up, you might have to search them using terms like
    " Or [Specifications].Column(1) Like " & strSearch & _
    Last edited by davegri; 08-15-2018 at 10:00 AM. Reason: clarification - only the comboboxes

  8. #8
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    I'm not working with comboboxes; this is a textbox [CommentSearch]

  9. #9
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Can I use DoCmd.ApplyFilter somehow??
    Attached Thumbnails Attached Thumbnails Problem 4.png  

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Personnel, Specifications and SupplierVendor are the comboboxes.
    Are these fields defined as numeric in the MRR table? Are they table lookup fields?
    If so, I don't know how you can filter on them.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    raychow22

    Can you post a copy of your database with just enough records/data to show the issue?

  12. #12
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by orange View Post
    raychow22

    Can you post a copy of your database with just enough records/data to show the issue?
    Hmm I cant seem to upload it.

  13. #13
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by davegri View Post
    Personnel, Specifications and SupplierVendor are the comboboxes.
    Are these fields defined as numeric in the MRR table? Are they table lookup fields?
    If so, I don't know how you can filter on them.
    They are not lookup fields. They are fields from a table which i made into a query. No resolution yet

  14. #14
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    If I used Macro .. This Formula does work... I want to use VBA because Macro is limited to 255 characters.. Is there a translation from the Macro to VBA?
    Attached Thumbnails Attached Thumbnails Problem 5.jpg  

  15. #15
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Yes, COMMENTS is not the problem. The problem is searching the comboboxes.
    If you limit the VBA search to Comments, it will work too.
    Last edited by davegri; 08-15-2018 at 02:59 PM. Reason: clarif

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-05-2017, 01:15 AM
  2. Search on Split-Form
    By Ekhart in forum Programming
    Replies: 8
    Last Post: 09-07-2016, 10:55 AM
  3. Replies: 2
    Last Post: 11-05-2014, 09:16 AM
  4. Split forms
    By GCS in forum Database Design
    Replies: 3
    Last Post: 11-30-2011, 08:30 PM
  5. Split Forms
    By bmclachlan in forum Forms
    Replies: 1
    Last Post: 08-17-2009, 02:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums