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?

CodePudding user response：

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.

CodePudding user response：

You can use constraints

CREATE TABLE tab (
        project char(25),
        categ char(10),
        budget INT ,
        CONSTRAINT CH_catagorie CHECK (
                budget <= CASE categ
                        WHEN 'cat 1' THEN 1000
                        WHEN 'cat 2' THEN 2000
                        ELSE 0
                        END
                )
        );
INSERT INTO tab VALUES('Project 1','cat 1',500);
INSERT INTO tab VALUES('Project 2','cat 2',1500);

Until there all ok. Now try a value which is too high.

INSERT INTO tab VALUES('Project 3','cat 1',1500);

And we get en error. We then check the contents of the table:

SELECT * FROM tab;

And the forbidden entry is not there.

 ----------- ------- -------- 
| project   | categ | budget |
 ----------- ------- -------- 
| Project 1 | cat 1 |    500 |
| Project 2 | cat 2 |   1500 |
 ----------- ------- -------- 

This works on mySQL. It may need modifying for MS Access.