Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Trouble with recalling all records based on value

    Hey all,

    I have spent all day on this and have found nothing on any forum and google said "you're screwed"... so I thought I'd ask here. - I apologize if this is the wrong section... just let me know and I'll move it.

    Here's the issue:

    I have an access database that has a table of departments, and a table of policies. I have a column in the policies table that is related to departments, such that a department entry into a policy must match a department in the department table.

    I created a form for managing policies, and I have a combobox with all the departments in them, and a listbox with all policies in it that brings up a policy in the form whenever you click on a policy in the list box. - I have it set up right now so that when I select a department from the combobox, it filters the listbox down to whatever department is selected. I did this by editing the row source of the listbox and adding the following code into the criteria for the department section:

    [Forms]![Policy]![Combo33] OR [Forms]![Policy]![Combo33] Is Null

    The OR section I use to show ALL the policies when the form is first started until a department is selected. That all works great.

    The last little thing that is KILLING me, is that I want the user to be able to go back to ALL policies after they've filtered from the combobox. I created a record in the Department table called "ALL", but I can NOT figure out how to make the listbox display All records when that one field is selected.

    ANY help would be Greatly appreciated!

    Thanks so much,

    Vince

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    My example is at work, but basically here's what I did:

    I made a table called "tbl_All" and put in a field that I name the same as the field I use for my selection...I'll call that "Selection," then one line of data in which I typed "All"

    I made a union query to link that to my selection query. Example:
    SELECT tbl_All.Selection, 1 as Rank
    FROM tbl_All
    UNION ALL SELECT tbl_TABLE.Selection, 2 as Rank
    FROM tbl_TABLE
    SORT BY 2,1

    Then I link the combo box to that query.

    For the criteria in the query I put something like
    Iif ([Combobox]="All",[FieldName],[ComboBox])

    I hope this helps...If it doesn't work and you don't have the answer by Monday, I'll give you an example I have at work.

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    Quote Originally Posted by chris07tibgs View Post
    Then I link the combo box to that query.

    For the criteria in the query I put something like
    Iif ([Combobox]="All",[FieldName],[ComboBox])

    I hope this helps...If it doesn't work and you don't have the answer by Monday, I'll give you an example I have at work.

    You, are amazing. I honestly didn't understand most of that (I'm very new to access), but I took your Iif statement, and was able to add another OR to my initial criteria such that:

    IIf([Forms]![Policy]![Combo33]="{All}",[Forms]![Policy]![Combo33],[Department])

    And.... IT Worked!! - You have no idea how glad I am that this worked... that's the majority of the hard part that I had left with this database. Thanks So much!

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    You're welcome. The reason why I make a union query is because the way you have it, "All" will come alphabetically in the list. The query, the way I set it up, sorts All first then everything else after.

    Mess around with that if you're ambitious and if you have any questions I'll try to help.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Here is a crude example of what I was referring to above...Hope this helps
    Attached Files Attached Files

Posting Permissions

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