Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    32

    Unanswered: multiple filters

    I have a form that I enter criteria into. This criteria will determine what records will show up in the next "display" form. The items I want to filter is "date" and "observer". The date part I have worked out. When I also filter the observer in the query, it works the first time but subsequent attempts does not display any records and the date box of the "display" form shows the #name? thing. Why when filtering more than one item will it not let you show records after the first time? Am I missing a requery somewhere?

    Thanks!

  2. #2
    Join Date
    Nov 2009
    Posts
    49
    So just to check you have both criteria in the SAME query, if not then it would be best to change it.

    Are you just hiding the "display" form? if you are then you are going to have to .requery the "display" form.

    If you get a #name? thing while filtering, it usually means that there is more than one record that fits the criteria and so it is confused on which one to display.

    If neither of these are applicable you could explain a bit more?

  3. #3
    Join Date
    Jan 2010
    Posts
    32
    Thanks for the reply.

    Both filters are in the same query.
    The form is not "hidden" as I need it visible to view the data. It works great the first time. But when I go back in and filter again choosing different items in the comboboxes, it returns no records and #name? in the date box. I just don't understand why it works great the first time but subsequent attempts it doesn't.

  4. #4
    Join Date
    Nov 2009
    Posts
    49
    I know this a silly question, but are removing the filters before you apply new one?

    For instance if you had all the numbers from A1-A100, then B1-B100 and so on.

    The first time you filter you say it has to be a "S" and Between "14-18".

    The second time is "D" "2-50"

    If you didn't remove the first filter then you are essentially looking for D2-D50 in a selection of S14, S15, S16, S17 and S18. There is no D value therefore returns 0 records.

  5. #5
    Join Date
    Jan 2010
    Posts
    32
    I hope I'm not making myself look stupid here.

    I don't think I want to remove the filters. The filters in the query represent the items in the combobox of the previous form. I have three diferrent combo boxes and I want to be able the select records based on any one or all three combo boxes (filters).

  6. #6
    Join Date
    Nov 2009
    Posts
    49
    are you requerying the actual form? Or objects in it? Just put this coding into all comboboxes on the OnChange event

    Forms![Name of Form].requery

    If you have this then maybe you can do a simple hide and unhide as I am sure this also automatically does a requery.

    Forms![Name of Form].visible = false
    Forms![Name of Form].visible = true

    Phil

  7. #7
    Join Date
    Jan 2010
    Posts
    32
    I appreciate you sticking with me here.

    The form with the comboboxes ( frmReports) is a form used PRIOR to the form displaying the records (frmReview). I have a "show records" button at the bottom of frmReports that opens frmReview and displays the the records selected and filtered by the comboboxes in frmReports. I don't know if requerying makes sense when it is a form you haven't opened yet?

    Thanks!

  8. #8
    Join Date
    Nov 2009
    Posts
    49
    Sorry forgot the form worked the first time, so if you read what i wrote before then ignore it.

    When you go back to change the criteria, have you left the results form open? If so a requery will be needed.

  9. #9
    Join Date
    Jan 2010
    Posts
    32
    When I say "go try it again"...I guess I mean that I close everything down and assume that I won't open the DB for a day or 2. Let's say the next day I want to pull some records again using diferrent criteria. It doesn't work when I go back in. But if I open the query it is based on in design view, the filters I added in "criteria" are still there.

  10. #10
    Join Date
    Nov 2009
    Posts
    49
    Ok... thats really weird.

    If you shut the whole database down and open it up again it doesn't work? If thats the case have you ever only run the query once and it's worked, and never worked since?

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by pb190 View Post
    If you shut the whole database down and open it up again it doesn't work?
    You really need to understand what is going on based upon fields upated and events firing. This is key to any design (along with the relational setup.)

    There are many reasons for your problem. I might suggest adding in a few Msgboxes in different places (ie. Msgbox "I'm here. Value X = " & x)

    type fo coding to help you and make things easier.

    You "May" have different things coded which are difficult for us to guess on what your desigining and how you want it to function designed or need a function. (I use functions all the time.)

    Each developer has their own style for doing this.
    Last edited by pkstormy; 01-20-10 at 23:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jan 2010
    Posts
    32
    Thanks! I've got it worked out.

Posting Permissions

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