If you trust the users, you could just let them increase the value of the
Served field from say 6 to 7 when the person works an extra hour.
I am assuming that the people serve their hours over several sessions. If
so, it makes sense to have a related table showing:
PersonID relates back to the primary key of your Person table.
DateServed the date the person served this time
HoursServed the number of hours served on this occasion.
You could then create a main form with the person's details and the hours
they are to serve, and a subform showing the hours they have served, one or
row. In the Form_Footer of the subform, add a text box with these
properties:
Control Source: =Sum([HoursServed])
Format: General Number
Name: txtTotalHoursServed
You can now see each timeslot they have served, with the total at the
bottom.
If you want to see the hours remaining, you could then add another text box
to the main form, with Control Source:
=[Given] - [NameOfYourSubformControlHere].[Form]![txtTotalHoursServed]
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
ptlm65 said:
Thanks that worked great! I have one more delemma:
How do I create a form that allows me to enter new hours served that
would add to the hours served field?
I thought of creating a combo box with the person's name
Then create a text field to enter the new hours served
Then a command button to execute the amount in the text box to be added
to the existing hours remaining field which would in turn automatically
update the total hours remaining in the query. What would the code be
for that?
Allen said:
Remove the Remaining field from your table. It is giving you
maintenance
problems you don't need.
Instead, create a query that shows you this calculated field. You never
have
to update it, and never have to worry about whether it is right. And
you can
use the query anywhere you could have used your table, e.g. for a
form,
report, ...
1. Create a query into this table.
2. In query design view, type this expression into a fresh column in
the
Field row:
Remaining: [Given] - [Served]
More info in this article:
Calculated fields
at:
http://allenbrowne.com/casu-14.html
ptlm65 said:
OK,
I know this is simple but I am having a brain freeze today.
What I need to do is perform a simple subtraction of fields with the
sum automatically entered into a another field.
I have three tables:
tblHours Given
tblHours Served
tblHours Remaining
I need to have a form code that when I change the amount of "hours
served" it will subtract from the "hours given" and the new updated
total automatically entered into the "hours remaining" Any help?
Thank you
