Trying to make a data-entry form with dynamically updated totals (1 Viewer)

[TSGAccounting]

Hey all!

I am working on a "manpower" database. My boss uses it to assign each employee's hours to various projects each month. The total hours assigned are limited by the number of hours an employee can work in a month, and the total budget of the project for that month (since each hour of work would have a dollar value assigned to it).

What he wants is to be able to see dynamically updated totals when he puts them in. So if he adds 5 hours to Dave on the Bobcat Project, he wants to see fields on that form which show Dave's total assigned hours for that month (and the difference from the maximum hours, to see if he hit the cap). He also needs to see the total assigned budget for the project that month - including the hours he just assigned to Dave - along with the difference from the monthly budget, again to see if he over-assigned something.

The problem is, if I base the form off a huge query that includes all this, it can't do data entry. But if I base the form off the Work-Hours table, I can't figure out how to reference other query info or write a formula to provide the numbers I need (especially if the data is spread across multiple tables).

How should I go about doing this? I feel like I'm missing something simple.

 

[Mark_]

Have you looked into DLookup? I don't know what tables you have or their layout, but I am under the impression you have ONE record that lists for that month how much can be spent on a given project and also ONE record that lists how many hours Dave can work.

DLookup would let you pull that record and return just the amount you are asking for (looks up the value of one field).

For how many hours they have used so far, I'd make a small function that opens a total query based on your criteria and returns the total hours Dave has worked.