Limit listbox to records not in subform

[RECrerar]

Okay, I'm sure this is quite simple and I'm also sure that there must be a thread on it somewhere but for the life of me I can't find it.

For each project there are a certain number of tasks that need to be performed.

I have a form that displays the completed tasks for each project in a subform. On the main form there is a listbox that displays all the tasks that need completing.

What I would like is that when a tasks is added to the completed list (via a command button click) it is then not seen in the listbox. Each task has a unique task ID and so basically when populating the list box I want it to look at the task IDs in the subform and if one is found there not include it in the listbox.

Hope that makes sence and any help would be appreciated.

Thanks
Robyn

 

[RECrerar]

Just a quick update on what I have done so far.

I now have a second listbox on the form that just contains the TaskIDs from the sub form. I'm not sure if I stricktly need this but my thinking is that this may make it easier.

So what I now have is two listboxes and if an item is in one of them I don't want it in the other one, i.e. they are mutually exclusive.

I'm sure I'll figure out a way to do this eventually but if anyone can help me along a bit that would be great.

 

[WayneRyan]

Robyn,

Without seeing your tables, this is tough.

Assuming that you have tblProjectTasks which has all tasks for a project.
Assuming that you have tblProjectCompletedTasks which has all completed tasks for a project.

Query for the mainform Listbox:

Select A.TaskName
From   tblProjectTasks As A Left Join tblProjectCompletedTasks As B On
         A.TaskID = B.TaskID
Where  B.TaskID Is Null
Order By A.TaskName

That should give you a listbox on your main form with all uncompleted tasks.

hth,
Wayne

 

[RECrerar]

Hi,

Thanks for the reply. That does sound similar to my table layout.

I have the following two tables:

tblTaskList
TaskID
Task Description

and

tblCompletedTasks
ProjectID
TaskID

I'm not currently at work but I will try this in the morning. It sounds like that method should work but I will let you know either way.

Thanks

 

[RECrerar]

Hi,

Unfortunately your plan didn't work as my tables were set up differently to how you assumed with one table holding all the data on projects and task completed. However it did get me thinking of how to use a query for the list box, something I'm ashamed to admit I hadn't considered before.

Anyway I now have it working with the following query:

SELECT Task_ID, Task_Description
FROM tblTaskList
WHERE ((((DCount("[ProjectID]","tblCompletedTasks","taskID=" & [Task_ID] & " AND ProjectID=" & [Forms].[frmCompletedTasks].[PID])))=0))
ORDER BY Task_ID;

So this basically looks at every task and then and only adds them is a combination of that taskID and the current projectID is not found in the completed task table.

Thanks for the pointer. Robyn