Filter Combo box query from a form field

[jimbodel]

If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful

Jim

 

[Guus2005]

What you want is way too complex or it is just the way you describe it. I can't make head nor tails from your question.

Please start over and/or provide a sample database.

 

[Jon K]

What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks.

You can use a query to find the employees who have been assigned within or overlapping the required date/time range of the task. Then, based on that query, use another query to list the employees who have not been assigned and therefore are available for the task.


I have attached a sample database. You can open the main form, go to a TaskID, and double-click on an employee in the "Employees available" list box to add the employee to the task. (I have used a list box for better visual, demo effect.)

The row source of the list box uses the query qryEmployeesAvailable, which is based on the query qryEmployeesAssigned. The tricky part is building the criteria in the latter query.


Edit:
The form is for adding employees to tasks. You need another form to create new tasks before adding employees.

You can add Or IsNull(Me.TaskID) in the code to prevent it from adding an employee when the Task table has moved past its last record.

Private Sub lstEmployeesAvailable_DblClick(Cancel As Integer)
   If Me.lstEmployeesAvailable.ItemsSelected.Count = 0 [b]_
       Or IsNull(Me.TaskID)[/b] Then
      Exit Sub
   End If
   ................
   ................

.

 

[jimbodel]

Jon

Brilliant, certainly put me on the right track. Was trying to do it with SQL, VBA is def' the way forward.

Many Thanks

Jim