Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    6

    Exclamation Unanswered: Queries, Forms and Data

    Ok so what i have is a database that i am making for an assessment and basically its an asset registry. So someone can modify and view the data in the database through a form, but now comes the tricky part:

    I need to be able to somehow create a way of searching the database by the user selecting a field (all the fields are listed in a drop down list box thing) and then typing a key search word or phrase to search by...

    Does anyone know how to do this without making a million different queries?? Is there an easy way even if i have to use visual basic for applications or something...

    Thanks in advanced,
    darcey

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Query by Form would seem to fit the bill here. Have a look at the Microsoft Knowledge base.

    http://search.support.microsoft.com/...7&mode=a&adv=1

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    This is fairly easy and you'll only need 1 extra table, and 1 query.

    Create a new table, call it "tbl_search" or something similar.
    Create a text field in this table, we'll call it [criteria]

    Close & Save.

    Create a new query, add the table with the asset registry in it, and add our new table "tbl_search" too.

    Drop in all fields and create a link between [criteria] and [fieldyouwanttosearchon] by dragging one to the other.

    Close & save

    Create a new form, bind it to "tbl_search" and drop in the field [criteria], disable the navigations buttons also. Add in a button to open the query/report/dataentry form with filter/form with list box, to display the results.

    close & save.

    Open the form, type seach criteria, and click your button.

    You can also add in code to display a msg, should no results be returned, and if you want it to be over multiple fields, repeat the query building process for each field you wish to use, and just add in extra buttons on the forms to display the results, EG: Search Part Number, Search Part Name etc.

    Hope this helps;

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  4. #4
    Join Date
    May 2006
    Posts
    6
    Thanks danlindley that is almost exactly what i need.

    However just one problem the user needs to be able to search any of the fields in the asset registry (which there are 4; type, model, make and serial number) and by creating the relationship your way i can only search the one that it is linked to "criteria". Or will it work?

    i tried it up until the point of making the relationship then thought of what i mentioned above.

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I'd be inclined to do this in VBA using:

    Code:
    DoCmd.ApplyFilter , "[" & Me!myFieldCombo & "]='" & Me!myStringBox & "'"
    This will filter the data according to your chosen field and text string.

    You can put this line in an after update event or create seperate button. I'd go for the button as it's more obvious to the user what is going on.

    Note the use of single and double quotes in the above as it's important!

    hth
    Chris

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking

    Quote Originally Posted by darcey123
    Thanks danlindley that is almost exactly what i need.

    However just one problem the user needs to be able to search any of the fields in the asset registry (which there are 4; type, model, make and serial number) and by creating the relationship your way i can only search the one that it is linked to "criteria". Or will it work?

    i tried it up until the point of making the relationship then thought of what i mentioned above.
    It can and will work my friend. you just need a query (and results form) per field you wish to search. Take a look at the attached example!

    There's multiple ways to do this, but i find this way the easiest and most customisable.

    Dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  7. #7
    Join Date
    May 2006
    Posts
    6

    Still confused

    Here we go, almost finished

    last bit i need how did u get it so that it read wat was in your text box then searched with that? i can get the serach happeneing just no reading the textbox

  8. #8
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    I;m not sure what you mean by your question, it is a bit veigue.

    All i can say, is make sure that you have used all tables/queries and forms that are in the searching sample i sent and that they are strcutured in the same manner.

    Check relationships, check tables, check queries and thats all i can suggest.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  9. #9
    Join Date
    May 2006
    Posts
    6
    Hey dan,

    disregard that last post, it was my stuff up i hadn't linked the form to the table or whatever but now that works, however it will only work when i type my criteria, press enter then close and reopen my form.

    I think it has something to do with how it is linked to the criteria field, because i don't think it is automatically updating the feild until after i have closed and reopened my form. (when it should be doing it immediately like yours does)

    You are probably sitting there thinking what is this guy on about so i thought id pass it on so you can have a look for yourself

    You have been a huge help...thankyou

    Darcey
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Greeets,

    You just about had it, you need a macro to close the form you had open then display your results, behind each button.

    I done it for you...

    Dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •