• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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?

  • 3
  • 2
1 Solution
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
mrchaos101Author Commented:
I think Im wanting the first example.

Could you give a bit more info on this process?
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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)

So this is noe table with:

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


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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now