Multiple Filtering Questions

[Datech]

I feel that these questions have somewhat simple solutions. However, in my haste to design this database I've skipped over some of the simpler tricks and tools Access has to offer. If the description isn't enough for you I can try and provide a sample DB. Fact is it's so big right now that it'd take a while to isolate what I'm doing right now. Sorry in advance for the lack of consistency with naming conventions.

Form: Resource Utilization


Actual Form Name: "Copy of Owners" (it started out as a test but I ended up liking it)
Purpose: Show contents of ResourceAllocation table
Current setup:

• Record Source: ResourceAllocation table
• Continuous Forms
• A text box for each field is linked to the proper control source in a row
• Labels in the form header correspond to what eventually displays in the "column" below
• In this way its a nice, well formatted form that simply displays all of the records without looking like a datasheet

What I want:

1. Display Condition 1: If User.usertype = 1, then display only records where Owner = User.username (an Owner logs in and should only see his owned resources)
2. Display Condition 2: If User.usertype = 2, then display a combobox of the Owners, and filter records based on selection of combobox (a manager logs in and has a choice to view all the Owners)
3. Display Condition 3: For both Display Condition 1 & 2, allow sorting in all of the fields for the records (sort by LastName, Project, etc)

Table: ResourceAllocation
Fields (important fields in bold):

• LastName - Lookup from EmployeeData table
• "First Name" - autopopulates based on LastName
• DeleteRecord - Yes/No for a delete query
• Project - Lookup from Projects table
• Funding Method - Value list
• Owner - Lookup from Owners table
• Start Date - date/time
• End Date -date/time
• Percent Utilization - number

So, with my current setup, how can I get it to filter and lock the Owner based on a usertype of 1? I have a With statement in my log in form that stores all the information needed to denote user type and look up the name or OwnerID. I simply don't know how to create the filter or the Where condition or whatever may be the best way to implement this. I also don't know how I can add sorting to these columns. I imagine I should change the form header labels to buttons, so that when you click it, a macro or VBA sorts the display for you?

Any and all help is much appreciated. If I have time I'll put together the sample tonight if no one understands the request.

[TG_W]

If you set the form to pull the data from ResourceAllocation, then set the Owner=OwnerID from whoever is logged-in in the criteria section of the query.

Like [Tables]![YourLogInTableNameHere]![OwnerID]