Unanswered: Trouble with recalling all records based on value
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.
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.