1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Creating a Multi-Criteria Search Form, Access 2007

Discussion in 'Business Applications' started by Dobber92, Jul 21, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    I'm fairly new to Access, and I'm looking for a way to make a search form that has both text boxes and combo boxes associated with fields in my table, titled Tbl_DCR. I want to be able to select certain field values and have either a sub-form or the datasheet section of a split-form display any records that have that field value in them. So if I selected values from two different combo boxes, it would only display records that have both field values in them, and so on. I'm running Access 2007 on XP. Please help!:(
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Try setting up a basic select query and get that working the way you want with the criteria you want. This will help with how you will build your form and display the underlying data.
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,634
    You can either do as Rockn suggests and use a Query with Criteria to provide the data for your form/subform or you can use VBA code to set the Form's Filter Property.
    To see a very good example database have a look at the attached database originally created & posted by Allen Browne in 2006.
     

    Attached Files:

  4. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    That example database exactly what I'm looking for. Allen did a very good job at describing the code logic. However, the filter did not work when I tried it. I'll try to use this on my database anyways, but you may want to take a look at the example yourself and see if there's anything wrong with it.

    Thanks!
     
  5. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    Nevermind! The office security had disabled the filtering for some unknown reason. The filter works perfectly, and I'm confident it will work for my database. Thankyou!

    Dobber92
     
  6. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    I noticed in that example database that value lists were used to create the options in the filter combo boxes. Seeing as I have over 60 choices, is there a way to mess with the row course or something like that to directly link the combo box to the table field without screwing up the filtering capabilities?

    Thanks!
     
  7. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Yes, that is the preferred way to do it from a table. You just may need to change the criteria that sets the content of the second combo.
     
  8. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    Ok I got the combo boxes to work. My problem now is that the subform is displaying the ID numbers of my combo box field values instead of their text values. Any ideas on how to fix that?
     
  9. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is it a subform or a listbox? The subform should pull in whatever you have listed in the query that builds the form. If you are only bringing in the ID field that is all you will see.
     
  10. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    The query that my sub-form is based upon is created from a request table that used the lookup wizard to create a combo box for the individual from another table titled Individual table. When I open the query it displays the names like it's supposed to, but the sub form displays the IDs
     
  11. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    And you have the fileds (controls like text boxes) like names listed in the subform as well??
     
  12. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    I'm not sure what you're asking. The fields in my sub-form are the same as my query. When I did the lookup wizard, it made the feild format Number instead of text box. Does that make a difference?
     
  13. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Can you post the database again?
     
  14. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    I can't upload the database because there is sensitive info on there
     
  15. Dobber92

    Dobber92 Thread Starter

    Joined:
    Jul 21, 2011
    Messages:
    29
    I fixed it. I had get rid of the relationships associating the IDs with the other table and create new relationships linking the text value fields. They're unique, so I shouldn't have to worry about duplicate records
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1008457

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice