Help with totals based off form selection

[thewake]

Hey all,



I could do with some help. ..I'm making a basic db to monitor annual leave.

I have a table of staff info (name, staff no etc) and a form which is the front end for it.

In that table I have included their annual leave entitlement as a numerical field.

In another table I am monitoring their leave entries and again i have a front end form for this and on this leave form I choose my staff member on a drop down and enter their dates of leave etc.

What I want is a text box on the second form (the leave entry one) that auto updates to pull through their entitlement from the staff entry table based on which staff member is selected in the drop down. This will enable the user to see at a glance what the currently selected staff member has as a total entitlement.

Eventually ill make it so the entitlement reduces as leave is recorded. ..but for now I'm more concerned with making the entitlement pull through to the leave entry form.

Hope that all makes sense.

Thewake

[SMC]

Is entitlement, the total amount of leave that the staff member is allowed?
And the ultimate goal is to have entitlement = (starting entitlement - leave taken)?

[thewake]

That's the ultimate yup. And the figure is a real time up to date figure viewable by the user when completing the leave entry form

[SMC]

I don't really have as much time as I thought I would to create a mock db for you. I might get to it later tonight.

I would have a query that pulls the difference between leave date and return date to create a column.

You can then sum that column in another query which will be based off of the name in the Leave Entry form. Select the name, type in leave date, type in return date. Once the return date is typed in have the query run and that will produce a column that will give you a total for the amount of leave taken by that employee.

The remaining entitlement can then be coded to pull the Annual Entitlement number in the "Staff" table and subtract from it the summed value that the query pulls.

[thewake]

Many thanks. ...I'll try you walk through as soon as a I can. I appreciate the help