Using Validation rules to restrict certain types of data entry.

[Long Tom Coffin]

Using the (wonderful!) help I have recieved here, I've created a very good database for use in multiple departments here at office. Le Boss has declared herself pleased, but there is one more thing she requires.

There are 3 tables I am dealing with, each connected with pk's an fk's to each other. The first table is for cash Allotments we get from the government. the second is a table for Obligations, where we obligate funds from that allotment to specific businesses. The third table is for disbursements, where cash disbursements go out to those businesses from the obligated amounts from the allotments. My issue is that I need to be able to prevent end users from entering in monetary amounts into either the ObligationsAmount field in the Obligations table or the DisbursementAmount field in the Disbursement table that exceed the total amount of the AllotmentAmount in the Allotment table. If overages like that were to be posted and sent out, it would cause a deficit which would then precipitate an office wide SHTF situation.

Would I use a Validation rule to construct something like that? And if so, how would I construct it?



Thanks!

[June7]

I don't think can use the ValidationRule property. Use VBA code in textbox AfterUpdate procedure. Code could use DSum function.

Problem with this kind of validation could arise if multiple users enter data simultaneously.

[Long Tom Coffin]

I don't think can use the ValidationRule property. Use VBA code in textbox AfterUpdate procedure. Code could use DSum function.

Problem with this kind of validation could arise if multiple users enter data simultaneously.

June7-

Thanks for the advice! Only problem is I have no experience with VBA whatsoever. I'm looking at some online tutorials and trying to understand how VBA works in the meantime. Would happen to have any advice/assistance for a new VBA user?


Thanks!

[June7]

Microsoft has a lot of info.
For starters
http://office.microsoft.com/en-us/ac...010341717.aspx

Regarding your validation issue, correction, BeforeUpdate event might be better approach. Review:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
In your case, the validation would be to retrieve data from tables and see if the additional data would exceed limit. This could use recordset or domain aggregate function (DLookup, DSum, DAvg, etc).