filter a subform using two combo box for the same field

[Camel]

Hi all! thanks for the help!
The database is a projects/tasks listing.


The tables (like 'Projects') are linked from the main database in the server.
In the local station I have a form with a subform which is filtered through combo boxes.
I need to filter twice the same field in the subform.
For example comboboxUSER1 filter gives me all tasks and projects of one user.
ComboboxSTATUS1 gives me all tasks (when is null) or I can choose which status I want to see (Active, Completed, In progress, Pending, etc.).

What I need is to EXCLUDE from the result of the subform the tasks with a particular Status: for example I want to see ALL except COMPLETED.

I was able to do this through a checkbox using Filters, but the database becomes too slow (I think using Filters combined with Link Tables on the Server may slow down everything). That is the form is slow in opening, it is slow in applying the Filter and in de-Filtering.

The other Combobox work well and fast.
So I tried to insert a ComboboxSTATUS2 to exclude from the results the field with Status 'Completed' but it was interfering with ComboboxSTATUS1 which was no more working. Both ComboboxSTATUS1 and ComboboxSTATUS2 operate on the same field 'Status'.
How can I do this?

The subform has the following recordsource which refers also to ComboboxSTATUS1:

SELECT Projects.*, Projects.ID, Projects.Number, Projects.[End Date], Projects.Status FROM Projects WHERE (((Projects.Status) Like IIf(IsNull(forms!Startup!ComboboxSTATUS1),"*",forms!Startup!ComboboxSTATUS1)) And ((Projects.Owner) Like IIf(IsNull(forms!Startup!ComboboxOwner),"*",forms!Startup!ComboboxOwner)) And ((Projects.Priority) Like IIf(IsNull(forms!Startup!ComboboxPriority),"*",forms!Startup!ComboboxPriority))) ORDER BY Projects.[End Date];

[orange]

Please describe exactly what this means

Both ComboboxSTATUS1 and ComboboxSTATUS2 operate on the same field 'Status'.

[Camel]

: Please describe exactly what this means
: Both ComboboxSTATUS1 and ComboboxSTATUS2 operate on the same field 'Status'.




Orange,
it means that both combo box point to the same field 'Status' of the table 'Projects'. They do not write to the field, they just filter the results of the query.
For example if ComboboxSTATUS1 = Active, the subform will give me only the projects which have in the field 'Status' the value 'Active'. If ComboboxSTATUS1 is empty (Null) it will give all the records.
I would like that while ComboboxSTATUS1 is Null I could choose ComboboxSTATUS2 < > 'Completed' (or < > 'Active', etc.), so that it can filter the result of the subform. But when I try to add (in the recordsource of the subform) ComboboxSTATUS2< > 'Completed', ComboboxSTATUS1 is not working anymore.
What am I doing wrong here?
Thanks!

[orange]

Watch the free video tutorials here on cascading combo boxes and see if it applies to you.
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

[Camel]

Thanks Orange,
but the video links that you posted do not apply. In fact it is clear how to do cascading Combo Box, but they point to different fields.

Instead what I am trying to do is to point two Combo Boxes of the same form to the same field, in other words a filter within a filter of the same subform, so to say.
Maybe I should try another approach, it's not clear how I can make it work.

[orange]

Instead of filtering the results of a query, perhaps you could use a combo box to create the where clause (or part of it) for a query or record source.

In English what is the "query/filter" you are dealing with? Can you give a sample using some of your data, including how the filter would work?

[Camel]

I would like that ComboboxSTATUS2 would filter the 'completed' status, while giving all the rest of the Status as a result ('Active', 'Deferred', 'Waiting on someone else', 'Pending', etc.).
To this effect, how can I modify the RecordSource of the subform ChildMembers?

*****
ADDING THE SAMPLE DATABASE Rup Sample Projects.accdb (in zip format) Rup Sample Projects.zip

*****

Here I am posting some code

In the ComboboxSTATUS1 afterupdate property:

Private Sub Status_AfterUpdate()
Me.ChildMembers.Requery
End Sub

In the ComboboxSTATUS1 doubleclick property:

Private Sub Status_DblClick(Cancel As Integer)
Me.ComboboxStatus1 = Null
Me.ChildMembers.Requery
End Sub

In the Recordsource of the subform ChildMembers:

SELECT Projects.*, Projects.ID, Projects.Number, Projects.[End Date], Projects.Status FROM Projects WHERE (((Projects.Status) Like IIf(IsNull(forms!Startup!ComboboxSTATUS1),"*",forms!Startup!ComboboxSTATUS1)) ORDER BY Projects.[End Date];

Data

Owner	Team Members	Category	Priority	End Date	Start Date	Status
Johnny
2	try project # 2
	Joseph, Judy	Archive, Appointments	1 High	17 July 2012	17 July 2012	Completed
	try task	1 High	20 July 2012	Completed	Johnny
	try task # 2	2 Normal	25 July 2012	In progress	Joseph
	try task # 3	3 Low	28 July 2012	Active	Judy
Joseph
1	first projects to check
	Joseph	Administration	1 High	30 June 2012	26 June 2012	Active
	requery seek and filter on add new project	Completed
	main and child database in server split acc2003	1 High	27 June 2012	Completed	Joseph
	permissions, all can manipulate?	2 Normal	30 June 2012	Active	Joseph
	alarm? Daily reminders? Send email to owner	2 Normal	14 July 2012	Not started	Joseph
	Insert print button	2 Normal	17 July 2012	Not started	Joseph
	pastel colours makeup	3 Low	08 July 2012	Pending	Joseph
	insert delete record task and project buttons	3 Low	14 July 2012	Completed	Joseph
	index task subform by enddate? Or priority?	3 Low	21 July 2012	Waiting on someone	Judy
	splash screen?	3 Low	27 July 2012	Not started	Joseph
	change colour when overdue?	3 Low	31 July 2012	Not started	Joseph
	mask to choose owner reports?	3 Low	31 July 2012	Not started	Judy

[orange]

see if this related post is helpful
https://www.accessforums.net/forms/m...form-2012.html

[orange]

You may also be able to set up Form - SubForm approach as shown in this video tutorial

http://www.599cd.com/VideoPlayer/?FN...mv&W=645&H=430

I think you have People (ProjectPerson) and Projects (and Project have different Statuses)

So working with People is assigned 1 or more Projects, the Form/subForm might be an approach.

In your sample data, I don't see your tables and relationships - that may help in understanding what you're dealing with..

[Camel]

Orange,
thanks for the kind help and replies. I appreciate!
Read lots of material, saw many videos, could not find anything similar. Seems this is a rare or unique problem, which makes me think I am totally wrong or I am a genius (I think the first one is more likely ).

However, the problem is still not solved. I try to rephrase, for clarity's sake:

ComboSTATUS1 and ComboSTATUS2 are two combo boxes in the main form

In the query of the subform in the field Projects.Status:
[code]Like IIf(IsNull(forms!Startup!ComboboxSTATUS1),"*",form s!Startup!ComboboxSTATUS1)[\code]

This works great and fast: it gives me all records with the field Projects.Status = Active or Completed or Pending, etc...
It means when ComboSTATUS1 is Null then it gives me all records with ANY value in field Projects.Status
If ComboSTATUS1 is NOT Null then it give me all records in field Projects.Status = to the selected value in ComboSTATUS1

BUT...
I need to get this:
I want ALL values from the field Projects.Status EXCEPT the ones where Projects.Status = Completed

I tried option N. 1 (preferred)
A combo box ComboSTATUS2 but when I change the subform query accordingly it obviously does not work
with ComboSTATUS1 = Is Null (which tells field Projects.Status to return all records) less ComboSTATUS <> "2" (2 is the value which correspond to 'Completed' in my table / which tells field Projects.Status to filter the records which are 'Completed').

I tried option N. 2
This also does not work.
I tried this in various versions but it gives me an error or the form is blank or even Access hangs and crashes!

in the subform query in the field Projects.Status:
Like IIf(IsNull(forms!Startup!ComboboxSTATUS1),(Project s.Status)<>"2",forms!Startup!ComboboxSTATUS1)
I think this can not work because this clause expect a value not another filter.

Option N. 3 not viable
I could use Filters but they make the form VERY slow, so this option is out.

What am I doing wrong here?
Thanks for the patience!

[orange]

I don't have much time to work on this today.
But this with

ComboSTATUS1 = Is Null

will not work.
Nothing is = to Null. NULL is undefined.
You can say if(IsNull(somefield)) then do something

However, I think you'll have to tell us what the application is, and in plain English explain how the status works.

You have told us HOW you have done something, not WHAT you want to happen. You have said you think it may be design, and that may be.

[Camel]

Sorry for taking the time... apologies for the long thread.

I understand that ComboSTATUS1 = is Null will not work. In fact I use it like this, as it can be seen from the previous post:
IsNull(forms!Startup!ComboboxSTATUS1)

But that said, I will try here to explain in plain English.
1. The application is a database to register projects. Practically a to-do list.
Projects includes Tasks which are things to do related to a single project.

2. Status is the status of the project which can be 'active', or 'completed', or 'waiting on someone else', or 'pending', and so on, as much status as you would like to insert. This list is in a separate table linked with Projects.

3. I have a main form which contains a subform.
The subform is filtered by comboboxes, by priority, owner, and STATUS (comboSTATUS1)

4. When you open the form, the subform gives you the list of the records of all projects.

5. I need to easily control how to EXCLUDE (or include as well) from this list the records which have the status = Completed. In doing this I would like not to use filters but another Combobox.
As I wrote:
"I need to get this:
I want ALL values from the field Projects.Status EXCEPT the ones where Projects.Status = Completed"

Thanks

[orange]

Sounds like status may be the status of a task associated with a project.

a Project --has- 1 or many ->>Tasks
A Task can have 1 status

[Camel]

Sounds like status may be the status of a task associated with a project.

a Project --has- 1 or many ->>Tasks
A Task can have 1 status

YES - Project has 1 or many Tasks
Every Task has a status also, but it is not relevant here because it complicates thing unnecessarily.

The Status I am referring to is the Status of the Project and NOT the Status of a Task

2. Status is the status of the project

[orange]

How do you define "status"?- In your application that is?

I think, and it is a guess, that Projects are --potential,started, underway, completed and these values are all dependent on
the status of the Tasks within that Project.