Validation

[Guest]

Hi All,

I have a form with a subform. The main form is connected to a table and
contains information on the company, the subform is connected to a different
table and contains data on the staff for the companies. Obviously when i
display the subform, only the staff for that company are shown. On my main
form there is a field [TotalSalaries], on the subform there is a field
[Salary] i wish to be able to validate so that all the [Salary] for those
staff that work for each company, is less than or equal to value of
[TotalSalaries] for each company.


For example if for CompanyA [TotalSalaries] is £150,000 and there are two
employees [Salary] = £80,000 and [Salary] = £20,000, this is fine because the
the two [Salary] add up to less than [TotalSalaries]. If for CompanyB [Total
Salaries] is £150,000 and there are three employees [Salary] = £80,000,
[Salary] = £20,000, [Salary] = £60,000, i need it to fail validation on the
entry of the third employee as the total of the [Salary] is greater than
[TotalSalaries] even though each individual [Salary] is less than the [Total
Salaries].

I don't think i can validate on the table itself because it contains all the
data for all the employees for all companies......

Hope someone can help guide me in the right direction, please if you can
help, could you be quite specific, i.e. "try this function in this property
on this field/table".

Thank you

 

[Guest]

Without being able to test it, I think you should be able to do this.

Create a text box on the main form. It can be invisible. Make the control
source the sum of the salary field in the sub form's recordset.

=DSum("[Salary]", SubformName.Form.Recordset.Name)

Then the test is:
If Me.txtCombinedSalaries > Me.TotalSalaries Then
MsgBox "Combined Salaries Are Greater Than Total Salaries"
End If

Note that [Salary] should be the name of the field in the recordset, not the
name of the control on the subform.