How do I make a USER input search in Access-- Noob issues.

I made a DB in Access.  

It contains,  Part Number, Company name, Company Part number, Date, cost, quote, Notes  for fields.

I have made a form and can get data into the db just fine.

I want to do a Querie.   I wnat to make a new form with a Txt box and link it a field.  User enters a Search parameter then it pulls up ALL data  that matches...

Say   Date:  09/20/2004

They enter date and it pulls up all records with that date.  

OR say..

Part #  And it pulls up the card with that Part number.

Can sombody plz point in the right direction?

LVL 1
mrchaos101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stumpy1Commented:
Would I be correct in saying what you want is to create a form which queries the Table depending on what the user enters?
If yesm there are 2 ways to do it, you can use a parameterized query whereby the value for the parameters are provided by the fields on the form.
Otherwise you could dynamically build the SQL query through code dependant on what the user entered oin the form
0
mrchaos101Author Commented:
I think Im wanting the first example.

Could you give a bit more info on this process?
0
stumpy1Commented:
Say you have a new form with txtPartNumber, txtDate fields on it and you want to use these as parameters for your query.
Create your query as usual through the query designer. Then click in the criteria field and click the expression builder button (build) toolbar button. Choose Forms-your From name (eg. NewFormName) in 1st column, the field name (eg. txtPartNumber) in the 2nd and <value> in the 3rd and click ok.

Your SQl will look something like
Select * from Table where [Part Number] = [Forms]![NewFromName]![txtPartNumber]

This SQL takes the value from the txtPartNumber field on the NewFormName form and uses it as criteria in the query. The form must be open when you are running the query
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mrchaos101Author Commented:
Ok still confused.

SO I should make lets say a SEARCH form

I enter a TXT BOX that they can type in.
I enter a Button to preform the search.

Say I want to do a search for a part number...

I call the from PartSearch.


I can build the quto qurie and do the BUILD button but from there Im lost.
(by they way it is a  many to one relationship incase you need to know)

0
SimonLarsenCommented:
So this is noe table with:

Part Number, Company name, Company Part number, Date, cost, quote, Notes  for fields.

yes?

And you have made a nice little popup form with two controls on it, say a text box called SearchDate they can type a date in and maybe a combo box called SearchPart with the list of part numbers. (recordsource for the combo box use the lookup table for your parts, if you don't have that you could use Select distinct PartNumber from YOURTABLENAME order by PartNumber, that could be slow depending on various factors, if it is too slow use a text box).

and a button that says "go"

You have another form that has all the reords from the table. Say it is called Form1

Behind the go button put some code:

dim s as string
s = ""

if isdate (me.SearchDate) then s = "[Date] = #" &  me.SearchDate & "# "

if not isnull(me.SearchPart) then s = IIF (s = "","[Part Number] = '" &  me.SearchPart & "'", s & " and [Part Number] = '" &  me.SearchPart & "'")

DoCmd.SelectObject acForm, "form1"
DoCmd.ApplyFilter , s

Which has a nice little bonus of letting them search for either a date or a part or a part and a date. Watch the word wrap.
0
stumpy1Commented:
You have a search form called 'PartSearch' with a textbox called 'txtPartNumber' and a button on it. You also have a query that has [Forms]![PartSearch]![txtPartNumber] as the criteria on the Part Number field.

You have another form that displays your search results called say, 'ResultDisplay'. This form has its recordsource set to the name of the query above.

The button on the PartSearch form opens the ResultDisplay form when clicked. Since the criteria in the query is [Forms]![PartSearch]![txtPartNumber] it picks up whatever value is in the txtPartNumber textbox on the PartSearch form and dynamically uses that value in the query and then displays the result in the ResultsDisplay form.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.