Results 1 to 5 of 5
  1. #1
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715

    Modified Universal Search

    This is a modified version of a Universal Search originally written by Candace Tripp. The original expected a numeric PK in the first field. I have modified it to have the PK in any position, BUT it must be a single field Primary Key. You are presented with a form into which you enter comma separated strings. The search is made against all text fields of data type text in all nonSystem tables.

    see attached OpeningForm ModifiedUniversalSearch.jpg

    Enter some sample keywords, or parts,separated by commas see Sample search terms.jpg

    Click Search and see results Sample Output from Search.jpg

    Double click one of the search results to open a detail form with some fields (first 11 IIRC) from the record in the table
    see DoubleClick on a term opens Detail Form.jpg

    The database was obtained from UtterAccess and modified by me as mentioned above. My changes were made in 2010, and the database was saved in 2003 mdb format.

    I have put in a few validation lines, trapped a couple of errors, and added a basic timer. It came with some sample data, and I have linked a few of my own. Delete the linked tables and link to your own.

    Good luck !!!
    Attached Thumbnails Attached Thumbnails OpeningForm ModifiedUniversalSearch.jpg   Sample search terms.jpg   Sample Output from Search.jpg   DoubleClick on a term opens Detail Form.jpg  
    Attached Files Attached Files
    Last edited by orange; 01-03-2019 at 08:06 AM. Reason: spelling/grammar

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Thank you for the upload Orange. This is going to come in handy.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Thanks Steve. I modified the original to help someone on a different forum. I don't think it will handle volumes, but it's more than adequate when people are search for 5-6 terms across all table all fields.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    An update based on other threads:

    This routine does not work with attachment fields.

    I have attached a new zip (same name). This has been revised (28/Jun/2015) to remove the use of only text or memo fields.

    Note that the IDType refers to the datatype of the pk (N --numeric, T--text)

    You can adapt this to your data by
    -removing/deleting the linked tables
    -remove the original tables(these are useful for testing)
    -link all your relevant tables to the database.
    Attached Files Attached Files
    Last edited by orange; 05-10-2018 at 09:12 AM.

  5. #5
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    This is useful. Thanks! But my not all my tables have primary keys :/
    As such, I can not search anything

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

Similar Threads

  1. Last Modified Record
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 01-06-2014, 10:58 PM
  2. modified Date & Modified BY
    By bronson_mech in forum Queries
    Replies: 1
    Last Post: 12-06-2013, 10:45 AM
  3. Creating a modified count query
    By cardgage in forum Queries
    Replies: 3
    Last Post: 12-16-2012, 03:01 PM
  4. Modified Date
    By PatCollins in forum Import/Export Data
    Replies: 3
    Last Post: 04-24-2012, 01:13 PM
  5. Universal Search
    By Douglasrac in forum Access
    Replies: 3
    Last Post: 05-20-2011, 06:25 PM

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