Validating sum of Record Data with a txt Field

[redekopp]

Hello, so basically i have a form with a field called txtActualAcres - it contains the number of acres insured. it could be 160 lets say.

In a subform we have a datasheet view crop adjustment with an acres field that creates records for multiple counts.
I want it so that when they are moving down from count to count if they put 40 acres count 1, 40 acres count 2, 40 acres count 3 , then they can not put more then 40 acres on count 4 or it will give them a msgbox and a validation error stopping them. (because it cant go above the insured acres of 160)



if its easier the form is called frmCerealCounts and the subform is called Child1 with sfrmCerealCropDataSheet as the source.


Please help! Thank you!

[redekopp]

Is there anything else i could provide to make this clearer for anyone? Or is this just a particularly difficult task to complete?

[Micron]

Or is this just a particularly difficult task to complete?

For me, it's mainly a matter of not seeing your thread before. For the other 50 or so viewers, it could be that they don't know how to sum text, which is what your post title suggests you are trying to do. That is a bit more difficult. However, I'm going to assume "txt Field" means textbox, since many people use the words 'field' and 'control' interchangeably.

I suggest a textbox control, locked true, enabled false, txtInputAcres beside txtActualAcres. It would have an expression that subtracts the DSum of the subform records for the acreage field from txtInputAcres, showing the balance. In case the user ignores the hint on the remaining acreage, in the form BeforeUpdate event, subtract the same DSum of txtActualAcres from txtInputAcres and cancel the update if the number is negative.

If your acreage is in fact text, you'll need the IsNumeric function to ensure the entry resembles a number, then convert it to a number (CInt or CLng for example) then do the addition. Having to convert means your tables are not set up correctly, IMO.

[redekopp]

Thank you sir, that is exactly what i was looking for. Apologies for my explanation and misuse of txt and text. It is a number yes. Thanks!

[Micron]

No apologies required. I take it that you have or will be able to create what you need.
Good luck with your project!