Looking for advice on Find with a subform

[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.

 

[Mike Labosh]

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

 

[Guest]

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

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