Limit data input based on another fields criteria

[ECEK]

I have two tables linked by ID (Table1,Table2)

The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15

Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10
1,Eddie,5

What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.

Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)

Hope this is clear.

The two table are in datasheet format linked on a form.

 

[plog]

You are going to need to use an unbound form to assign people to tasks. This means, that the form isn't tied to a table/query, it updates Table2 via code.

That form would have 3 controls, one for each field of Table 2. It would also have a 'Submit' button. When the user enters data and clicks the button, it would use code to determine that the Task_done value was equal to or under the Task_Tms value for that task. If so, it compiles an INSERT statement and puts the data into the database, if not it shows a error message saying that the value submitted is greater than the Task_Tms value.

This code is going to use a DSum (http://www.techonthenet.com/excel/formulas/dsum.php), DLookup (http://www.techonthenet.com/access/functions/domain/dlookup.php) and DoCmd.RunSQL (http://msdn.microsoft.com/en-us/library/office/ff194626(v=office.15).aspx).