Results 1 to 6 of 6
  1. #1
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Search Box Function

    Hi, I'm trying to create a form which has a search box and button. There is also a list of my table data displayed below. When the button is clicked, I'm trying to filter the resources accordingly. For example, if 'Jack' is entered, all records with the name 'Jack' appear. If the company 'Mason' is entered, all results with 'Mason' appear. I would like The search box to bring up results for any text in any fields and display the results in datasheet view. For example, four records would show all four records. At the moment, it shows one record at a time.

    I could be filtering them but I have little idea how to setup my form or how the form interacts with the data as I'm a novice. Not sure if I'm even filtering them correctly. I've attached a test file. Any help would be greatly appreciated.



    Thank you, Paddington
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the form has a property : DEFAULT VIEW , set = continuous forms.
    this will show all records as a list.
    you currently have 1 form to show a single record,

    the other form has a subform. You dont need the subform.
    make a new form with all fields in the detail band, and set to continouous form.

    in the search box, the code to filter is:

    Code:
    sub txtBox_afterupdate()
    if IsNull(txtBox) then
       me.filterOn = false
    else
       me.filter = "[Name] like '*" & me.name & "*'"
       me.filterOn = true
    endif
    end sub
    so you dont really need the button
    just typing the word in the box, and pressing Enter , triggers the search in the AFTERUPDATE event of the text box.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure exactly what you are asking

    "search box to bring up results for any text in any fields and display the results in datasheet view."

    You could see if this modified universal search is applicable.

    Good luck

  4. #4
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11

    Search Box Function

    Hi Ranman256 and Orange, Thank you very much for your responses and help. Ranman256, thank you for the tip about the continuous view. Orange, thank you for sharing that terrific database you created. Please forgive me but these are the questions I have:

    Ranman, I really liked your idea of hitting 'enter' and not having to hit the command button. However, when I applied the code, I couldn't get it working. I've tried to apply it in the VB Editor in the 'Employees2' form in the attached file. Is there something I've missed? I did try renaming 'txtbox' too.

    Orange, I was initially trying to create a datasheet view. While the 'employees' form does achieve the desired result, is there a way I can get the data to appear like a datasheet below the search? I did try 'Datasheet View' but had no luck.

    Thank you so much again, Paddington
    Attached Files Attached Files
    Last edited by paddington; 04-23-2018 at 07:23 AM.

  5. #5
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11
    Hi Orange, After trialling a few designs, I realised the design I was trying to accomplish was 'Tabular'. This set the data out like I was after. I'm sorry as I probably didn't explain it very well.

    Hi Ranman256, I was able to get your VBA code working. Thank you. The only question is about searching to 'Name' or 'Company' and to display all applicable results. I'll need a line like:

    Code:
    Me.Filter = "[Company] like '*" & Me.txtbox & "*'"
    as well as

    Code:
    Me.Filter = "[Name] like '*" & Me.txtbox & "*'"
    Not sure though how to do this as If, If, and Else Statement. I'm sorry as my knowledge is very basic.

    Thank you for your help. Paddington

  6. #6
    paddington is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2017
    Posts
    11
    Resolved this by using this Code:
    Code:
    & _ " Or [Company] Like '*" & Me.txtbox & "*'"
    Thank you everybody for your help.

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

Similar Threads

  1. Search function in acces
    By stevie23 in forum Access
    Replies: 6
    Last Post: 10-04-2016, 06:43 AM
  2. Search Function
    By sk88 in forum Access
    Replies: 2
    Last Post: 01-05-2012, 12:33 PM
  3. Problem with search function
    By sk88 in forum Access
    Replies: 2
    Last Post: 12-20-2011, 01:05 PM
  4. Search function in forms
    By vCallNSPF in forum Forms
    Replies: 1
    Last Post: 03-14-2010, 06:17 PM
  5. search function
    By Eric1066 in forum Access
    Replies: 0
    Last Post: 10-23-2009, 10:23 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