Trying to limit the values that can be entered in a column based on the value of another column

Question

I’m building a database using MS access, the purpose is to keep track of the researchs and the funds given to each research. The funding amount depends on what kind of funding program the research is registered to,and the employee shouldn’t be able to enter a value that exceeds the specified amount

(for example if the research is, registered to “small research projects” funding program, the funding amount will be 25,000 Saudi riyals max and could be less)

How can I apply this rule using MS access?

Answer 1

Do the following.

1. Create a table for funding program,

2. Create a table for fund disbursements

3. Create a form for funding program

4. Create a form for fund disbursements

5. Go to vba and add codes under the following

A. Disbursedamount after update //This is to check if the amount been entered is more than the amount left to be spent on the program

B. Fundingprogram after update This will use a function called dsum to update a field in the funding program form with the total spent till date.

Ensure you set the locked properties of the totalspent field in form view to "yes", this is to prevent any user from changing the value on this field.

Also take note of the areas i drew a blue line in the table of the fund disbursement and replicate exact same thing

I have attached images that shows the steps.