Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Looking for advice on Find with a subform

Discussion in 'Information Technology' started by default105, Jul 28, 2006.

  1. default105

    default105
    Expand Collapse
    Guest

    I have looked through many posts and have not seemed to find the solution
    that is similiar to mine (not saying there isn't one, I just haven't seen it
    yet).

    I would like to the user to be able to search a subform for either part# or
    part description. However, they will probably only know a partial part# or
    use a generic description to search.

    I am having trouble figuring out the best course of action to take. I need
    a way to search any part of the field.

    Can this be done using RecordsetClone?
    Should I create a search form seperate and have a onClick to goto the
    desired record?
    How do you go about searching with VBA without matching the whole field?

    I am not looking for someone to devote all of their time to right the
    example code (the would be rude on my part) but instead looking for some good
    resources on how to get started so I can learn how to go about this. I hope
    someone answers this post and many thanks for your time.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh Girl Looking for Advice (at The Guardian) Questions and Answers Sep 11, 2011
    New to Sikhism, Looking for Advice New to Sikhism Apr 9, 2007
    Looking For A Device To Create Digital Paintings Or Art Information Technology Feb 10, 2016
    General Help! Looking for a good clairvoyant/card reader......any suggestions? Please Blogs Jan 25, 2016
    We are through the looking glass now... Blogs Oct 17, 2015

  3. Mike Labosh

    Mike Labosh
    Expand Collapse
    Guest

    > I would like to the user to be able to search a subform for either part#
    > or
    > part description. However, they will probably only know a partial part#
    > or
    > use a generic description to search.


    > I am having trouble figuring out the best course of action to take. I
    > need
    > a way to search any part of the field.


    #include <firefox.h>
    printf("You must think in Russion, Mr. Ganz. You cannot think in English
    and then transform over.");

    #include <yoda.h>
    printf("Luke, you must *unlearn* what you have learned.);

    The crux and architecture of your question is not one of code, property
    settings or events. You are attempting to model a real-world system wherein
    items can be looked up by their names, descriptions or x-referenced
    keywords.

    The finest reference you will ever find for this solution below is to
    examine what they call the "Normal Forms". I will not insult you, but since
    I don't know the level of your thinking, I thought I would include this as a
    helpful tip.

    Access + the RushMore technology they stole from FoxPro is pretty sweet if
    you design your table structure to 3rd normal form. That's not enough for
    me. If you do 5NF or better, you will do yourself (and those who come
    after) a giant favor:


    So dig this (please forgive my SQL Server background)

    CREATE TABLE Item (
    ItemKey INT NOT NULL IDENTITY (1, 1), -- Primary Key Autonumber Long
    Integer
    Description NVARCHAR(50) NOT NULL, -- Natural Key Text Indexed Unique
    )
    GO

    CREATE TABLE ItemKeyword (
    ItemKeywordKey INT NOT NULL IDENTITY (1, 1), -- Primary Key
    Autonumber Long Integer
    ItemKey INT NOT NULL, -- Foreign key to table Item, NOT unique
    Keyword NVARCHAR(50) NOT NULL (you could index this)
    )

    What you have here is a simple mechanism that you can use to have multiple
    key search words to add for each item, and then the end-user application can
    query the ItemKeyword table like this:

    SELECT Item.ItemKey, Item.Description
    FROM Item INNER JOIN ItemKeyword
    ON ItemKeyword.ItemKey = Item.ItemKey
    WHERE ItemKeyword.Keyword IN ( [whole bunch of stuff] )

    or perhaps:

    WHERE ItemKeyword.Keyword = SomeParameter

    Apply 5NF or better, and you will become a Powerful Jedi.

    That's how we do it out there in the big world with databases that are 167
    GB (TB?) each with several hundred Billion (B, not a M) Billion records.
    And combined with a smart design like this and a well designed indexing
    scheme, it's pretty badass.
    --


    Peace & happy computing,

    Mike Labosh, MCSD MCT
    Owner, vbSensei.Com

    "Escriba coda ergo sum." -- vbSensei
     
  4. default105

    default105
    Expand Collapse
    Guest

    Thanks for the start, I am researching more on sql at the moment


    "Mike Labosh" wrote:

    > > I would like to the user to be able to search a subform for either part#
    > > or
    > > part description. However, they will probably only know a partial part#
    > > or
    > > use a generic description to search.

    >
    > > I am having trouble figuring out the best course of action to take. I
    > > need
    > > a way to search any part of the field.

    >
    > #include <firefox.h>
    > printf("You must think in Russion, Mr. Ganz. You cannot think in English
    > and then transform over.");
    >
    > #include <yoda.h>
    > printf("Luke, you must *unlearn* what you have learned.);
    >
    > The crux and architecture of your question is not one of code, property
    > settings or events. You are attempting to model a real-world system wherein
    > items can be looked up by their names, descriptions or x-referenced
    > keywords.
    >
    > The finest reference you will ever find for this solution below is to
    > examine what they call the "Normal Forms". I will not insult you, but since
    > I don't know the level of your thinking, I thought I would include this as a
    > helpful tip.
    >
    > Access + the RushMore technology they stole from FoxPro is pretty sweet if
    > you design your table structure to 3rd normal form. That's not enough for
    > me. If you do 5NF or better, you will do yourself (and those who come
    > after) a giant favor:
    >
    >
    > So dig this (please forgive my SQL Server background)
    >
    > CREATE TABLE Item (
    > ItemKey INT NOT NULL IDENTITY (1, 1), -- Primary Key Autonumber Long
    > Integer
    > Description NVARCHAR(50) NOT NULL, -- Natural Key Text Indexed Unique
    > )
    > GO
    >
    > CREATE TABLE ItemKeyword (
    > ItemKeywordKey INT NOT NULL IDENTITY (1, 1), -- Primary Key
    > Autonumber Long Integer
    > ItemKey INT NOT NULL, -- Foreign key to table Item, NOT unique
    > Keyword NVARCHAR(50) NOT NULL (you could index this)
    > )
    >
    > What you have here is a simple mechanism that you can use to have multiple
    > key search words to add for each item, and then the end-user application can
    > query the ItemKeyword table like this:
    >
    > SELECT Item.ItemKey, Item.Description
    > FROM Item INNER JOIN ItemKeyword
    > ON ItemKeyword.ItemKey = Item.ItemKey
    > WHERE ItemKeyword.Keyword IN ( [whole bunch of stuff] )
    >
    > or perhaps:
    >
    > WHERE ItemKeyword.Keyword = SomeParameter
    >
    > Apply 5NF or better, and you will become a Powerful Jedi.
    >
    > That's how we do it out there in the big world with databases that are 167
    > GB (TB?) each with several hundred Billion (B, not a M) Billion records.
    > And combined with a smart design like this and a well designed indexing
    > scheme, it's pretty badass.
    > --
    >
    >
    > Peace & happy computing,
    >
    > Mike Labosh, MCSD MCT
    > Owner, vbSensei.Com
    >
    > "Escriba coda ergo sum." -- vbSensei
    >
    >
    >
     

Share This Page