dropdown to set multiple query filter criteria

[soulpiercing]

I am currently working on a criminal justice tracking program for the US Army. The structure of our legal office is set up in a way that we can all use a centralized database which will make reports and tracking at all levels easier but there is an issue I am trying to resolve.

Our office structure looks something like this:

MAIN legal office

1st Sub office

2nd sub office

3rd sub office


What I want to do, is on my main screen have a drop-down list of these offices (based on a table "offices"). Depending on the selection from this list, I want a filter to be applied to ALL queries so that if I work in the 2nd sub office - the cases from my office are all I see.

In every case, there is a selection of "unit" and in the unitdata table, the sub office is selected from "offices"

The main office would be set to view ALL data with no filters.

Issues:
this program will likely be used in other stations as well so the names of the sub offices will change as will the number of sub offices.

Ensure Key numbers are assigned regardless of which filter is in place. (shouldn't really be an issue).

Thanks for any help.

 

[WayneRyan]

Jason,

The queries for your forms and reports should
simply state in the criteria for the "office"
field:

=Forms![YourMainForm]![YourCombo] Or
=Forms![YourMainForm]![YourCombo] = "Main Office"

That way the branch offices will only see their
data and the main office will see all.

Since the data lives in one central place, the
restriction on what they view will not affect
your assignment of keys.

hth,
Wayne

 

[soulpiercing]

I understand and yes - that is what I want to do. What I am trying to get at is avoiding having multiple queries that are identical other than the 1 filtered item.

Another thought.....

could I do a mass query of all of my data fields with the one filter in place, then base my other queries off of that query?

query 1 selects the unit then all other queries work from query 1... When the query 1 drop down is changed, the source data would be changed for all other queries?

am I right or at least close?

 

[WayneRyan]

Jason,

Not multiple queries, the same query returns
different recordsets depending on the value
of the combo in your main form.

The only wrinkle is the second criteria to
allow for the Main office to return all
records.

Wayne

 

[soulpiercing]

I was just looking at that. In the query design view, properties of the Office field - change to combo box... etc Right? Then the main form uses this box to set the query?


Is there a way to set the box to a blank entry?


Also - now that I have this somewhat figured out.... Can I have records entered while being viewed through this query automatically be set to the specified office so that they will always come up in this query? Is this automatic for new records since the filter is set in the query?

Hope this makes sense....

 

[WayneRyan]

Jason,

No, the query uses the combo box on the main
form as its criteria for the Office column.

The query doesn't change, just the combo.

On the entry form, the default for office
would be:

=Forms![YourMainForm]![YourCombo]

I don't see where a blank combo box,
indicating no office, would help.

Wayne

 

[raskew]

Jason-
Here's an example of a Northwind query (sure I borrowed it from one the MS sample databases).
It demonstrates how to drill down to a specific group of records if the user enters criteria, or returns
all records if the criteria is left blank.

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShipCountry
FROM Orders
WHERE ((([Enter the country: Example: 'USA']) Is Null)) OR
(((Orders.ShipCountry)=[Enter the country: Example: 'USA']) AND 
(([Enter the country: Example: 'USA']) Is Not Null));