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

Posted on 2004-11-09
Last Modified: 2010-04-17
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?

Question by:mrchaos101
    LVL 5

    Expert Comment

    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
    LVL 1

    Author Comment

    I think Im wanting the first example.

    Could you give a bit more info on this process?
    LVL 5

    Expert Comment

    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
    LVL 1

    Author Comment

    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)

    LVL 8

    Expert Comment

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

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    A short article about a problem I had getting the GPS LocationListener working.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now