Results 1 to 7 of 7
  1. #1
    sleake is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    51

    Create a search feature on a form that searches the entire record and goes to that record on a form

    I've built an address book in which I'd like to be able to search in all fields and have the form open to the record it found. For example, I want to be able to search for part of a name, a contact type like dentist, a city like Brooklyn, and find records with those values.



    I tried putting a text field, txtSearchContacts, on my form then creating a macro, shown below. This doesn't work. I received a message to change the search as formatted to yes, but that made no difference. Can someone point me to a resource to create this type of search?

    Click image for larger version. 

Name:	SearchMacro.jpg 
Views:	25 
Size:	20.7 KB 
ID:	19456

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Options:

    1. intrinsic tools from the ribbon and right click shortcut

    2. http://www.datapigtechnologies.com/f...tomfilter.html

    3. http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sleake is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    51
    I have used both approaches 1 (perfect for another database where only one field held the values) and 2. Allen Browne's great for searching in more than one field. There are four fields whose values I might want to search, but being rather lazy, I only want to enter the search value or part of value in a single ontrol. I would only be searching for a single value from one of the four fields, for example, "Murr" for Murray in the last name field, or "Lands" for Landscape in the Category field.

    Maybe I could concatenate those fields in the query underlying Allen's approach. I'd be happy clicking Find, if only I didn't have to select Current Document and Any Part of Field every time I do a new search.

    I tried the following but got a compile error that = was expected.

    DoCmd.FindRecord([Me!txtSearchContacts],acEntire, ,acSearchAll,SearchAsFormatted,acAll,FindFirst) Me!SearchContacts is an unbound field on my main data entry form.

    I'll try concatenating the fields using Allen Browne's approach until I hear back from you.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can use option 2 or option 3.

    Option 2 would have the LIKE and wildcard criteria under each of the fields but on different rows (like stair steps) so the OR operator is invoked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sleake is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    51
    I've tried modifying method 2, combo box. It will find the "last name" I typed in the cbo list, but when I add Me!Requery, the list disappears and it does not go to the record on the form. It only finds data in the first column (Last Name), not in the other three.
    The criteria string is:
    Like [Forms]![frmContactsDataEntry]. [cboSearchContact] & "*"
    And here's how I set up the data source query. Obviously I'm doing something really wrong. Maybe I should just do 3 combo boxes.
    Attached Thumbnails Attached Thumbnails Conacts Search Query.JPG  
    Last edited by sleake; 02-01-2015 at 02:44 PM. Reason: typo

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Don't know if this applies or not, but if you are looking to search multiple fields for one or more search terms, you can see my modified search here.

  7. #7
    sleake is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    51
    That is what I'm looking for. I'll take a look. Thanks. I'll post back.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-25-2015, 12:09 PM
  2. Replies: 1
    Last Post: 06-26-2014, 06:50 AM
  3. Replies: 21
    Last Post: 08-05-2013, 06:23 AM
  4. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  5. Replies: 1
    Last Post: 06-09-2012, 05:44 PM

Tags for this Thread

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