Data Entry Validation

[kickergirl]

I am trying to ensure that the user is notified if they enter more disbursments than was allotted for a specific account. The twist is that each account will have an initial allotment and a renewal allotment. The disbursements are captured in a subform.

I entered the following code in the AfterUpdate event of the control capturing the individual disbursements, but I get Runtime error 2428 - You entered an invalid argument in a domain aggregate function.

If DSum(DisAmt, Disburse_Info, DisInd = "1" & Forms!Caa_Info_frm!Total_I) + DisAmt > Forms!Caa_Info_frm!OfferAmt Then
MsgBox "Initial Disbursements are greater than Initial CAA amount. Please verify data.", vbCritical, "Excess Disbursements"
End If

-DisAmt is the subform control where I am entering current disbursement
-Disburse_Info is the tablename that stores the data
-DisInd is the subform control where I indicate whether the current disbursement is for the Initial or Renewal Allotment
-Forms!Caa_Info_ frm!Total_I is the main form control that displays the subform subtotal for disbursements for the initial allotment
-Forms!Caa_Info_ frm!OfferAmt is the main form control that displays the initial allotment amount.

Help!

[FishVal]

Hi, there.

If you want to limit number of entries in subform, you may take a look at this thread
LIMIT the rows on a control

[kickergirl]

I am not actually trying to limit the number of data rows. I am trying to ensure that the sum of disbursements does not equal more than the amount allotted.

[tdw]

I entered the following code in the AfterUpdate event of the control capturing the individual disbursements, but I get Runtime error 2428 - You entered an invalid argument in a domain aggregate function.

If DSum(DisAmt, Disburse_Info, DisInd = "1" & Forms!Caa_Info_frm!Total_I) + DisAmt > Forms!Caa_Info_frm!OfferAmt Then
MsgBox "Initial Disbursements are greater than Initial CAA amount. Please verify data.", vbCritical, "Excess Disbursements"
End If

-DisAmt is the subform control where I am entering current disbursement
-Disburse_Info is the tablename that stores the data
-DisInd is the subform control where I indicate whether the current disbursement is for the Initial or Renewal Allotment
-Forms!Caa_Info_ frm!Total_I is the main form control that displays the subform subtotal for disbursements for the initial allotment
-Forms!Caa_Info_ frm!OfferAmt is the main form control that displays the initial allotment amount.

Help!

Someone correct me if I'm wrong (since I'm no expert), but this looks like you are trying to calculate something using a calculated field. I don't know if I'm using the correct terminology, but it is my understanding that you cannot use a calculated form field to perform another calculation. Is Forms!Caa_Info_ frm!Total_I a subtotal on a form that uses a calculation as it's control source? If so I think that instead of using that result, you have to repeat the same calculation again anywhere else you need it, rather than trying to use the field containing the results of that calculation. Again, please anyone correct me if I'm wrong, and also please anyone, if I'm right, rephrase what I'm trying to say in a more understandable way.

[FishVal]

If DSum(DisAmt, Disburse_Info, DisInd = "1" & Forms!Caa_Info_frm!Total_I) + DisAmt > Forms!Caa_Info_frm!OfferAmt Then
MsgBox "Initial Disbursements are greater than Initial CAA amount. Please verify data.", vbCritical, "Excess Disbursements"
End If

-DisAmt is the subform control where I am entering current disbursement
-Disburse_Info is the tablename that stores the data
-DisInd is the subform control where I indicate whether the current disbursement is for the Initial or Renewal Allotment
-Forms!Caa_Info_ frm!Total_I is the main form control that displays the subform subtotal for disbursements for the initial allotment
-Forms!Caa_Info_ frm!OfferAmt is the main form control that displays the initial allotment amount.

Help!

Hi, there.

DSum function deals with tables/queries, not with form controls.
And all 3 arguments has to be String type (refer to Access help for proper syntax). Strings constants has to be enclosed in double quotes.

So lets look close at your code

DSum(DisAmt, Disburse_Info, DisInd = "1" & Forms!Caa_Info_frm!Total_I)

1st argument:
has to be name of table field, so if table field name is the same as that of control name it should looks like "DisAmt"
2nd argument
just enclose it in double quotes to let VBA recognize it at String type constant
3rd argument
has to be a string expression of criteria to filter records, just the same as what you pass in WHERE clause of SQL statement; I can hardly guess what did you mean with the expression you've posted

kind regards,
Fish