A calculation in a text box?

[burrelly]

I have created a database and started working on the forms, I have a form that contains the employee information on the top half of the form and at the bottom there are tabs which have subforms to different tables with information related to that employee absence / infractions / timekeeping etc etc

It is set up so the subforms only contain the data linked to the main employee table.

In one of the tabs is holiday data what it has is:

Start Date
End date
No of Days
Comments
Approved / Denied

What I need is a text box within the tap just above the subform that adds up the number of days from holidays that have been marked as Approved and also between the dates of the year we are in. This should only be calculated for the data in the subtable.

Then what i need is another text box that takes the value of the above box and subtracts it from 20.

I have never done anything like this so any help would be great.

 

[wazz]

i think i would just create a separate query or two that calculates that info and set the control source(s) of two unbound textboxes to your new queries. much easier than calculating from the subforms and you could use the queries in other places as well.

 

[burrelly]

How would i do that? I am not sure how i would link the data so it only shows the record that is showing. I asked something simalaer last week in the quary forum and was told would be better off doing it as a text box on a form.

 

[wazz]

you have your form that you don't want to change.

let's say you've got an employee table (tblEmp) and holiday table (tblHol).

- create a new query with those two tables.
- add all the fields you want to see (similar to your form/subform) and check the query to make sure you're getting the info you need.

- add criteria to the query. the criteria will come from your open form.
- for the employee, use the empID field and add criteria like: Forms!frmYourEmpForm!EmpID. that will you give you info about the current emp on the form.

- open your form (so the query can use it) and test your query again (to make sure it's "reading" the form). the query should now only give you info about the emp you see on the form.
- start removing all the fields you don't need (which should be most of them) until you get down to NumDays (holiday days).
- then you can (hopefully directly) use a Sum function to add the days.

- once that is working you can use this query directly on the form (on an unbound textbox) to actually read the form/subform info "independently".

try that for starters. look up 'Totals' in access help for some additional help. post back with an update.

 

[burrelly]

I am sorry but I really dont know how to do what you suggested, Can you help?