Form validation rule based on total in a different table

[jherbert]

Hi All!



I am creating a form for people to enter allocations of a total that will go into one table, however, the total of the two allocations they enter must equal the total of another query. Below is a mock example of what I am trying to do:

The form will enter data into "Table 1." Allocation 1 and 2 are different columns in Table 1. Below is the basic layout of Table 1:

[Table 1]
|Allocation 1 |Allocation 2|Month |Dept |
|2 |3 |1/1/16 |100 |

Now "Query 2" does a total count of employee names based on "Table 2." The total count in Table 2 equals 5. Below is the basic layout of Query 2:

[Query 2]
|Total |Month |Dept |
|5 |1/1/16 |100 |

I want to put a validation rule into the form so that allocation 1 and 2 equal the total count in Query 2. Does anyone know any way to do this? Any help would be greatly appreciated!

[ranman256]

make a query, "qsAllocationSum", that sums ALL1+ALL2, month, dept

then when the user enters the value, check it with
in the text box after update event,

sub txtEnterVal_AfterUpdate
   vSum = Dlookup("[SumOfAllocaton]","qsAllocationSum", "[month]=" & txtMonth & " and "[Dept]='" & cboDept & "'")
  if vSum <> txtEnterVal then
      msgbox "values do not equal"
 endif

end sub

[jherbert]

Thank you for your help! I will give it a try.