Looking for a solution (not code)

[hockeyman9474]

Hi. I am looking for a suggestion more than anything. I am building a simple "Time Off " database. I have several tables (employee info, Start Time, Used Time, Time Remaining and Time requested). All are related via an ID# that is auto-generated with each new entry in the employee table. On my MAIN form I have a Combo Box that it tied to the employee table and allows the user to select an employees name. A search button opens a new form that only shows records for that employee. On that form are sub-forms derived from the tables listed above, married by ID. What I would like to do is add a new date and hours in the timeReqOff sub-form, and have the others update appropriately. i.e., a user starts with 100 hours showing in the timeRemaining sub-form.8 hours is entered into timeReqOff sub-form. I would like for the timeRemaining to adjust to 92 hours. I have tried every which way til Sunday with Update queries, and append queries, but just can't seem to get it right. I feel like I am overthinking this. is there an aeasy solution?

[pbaldy]

I would not store used time, time remaining, etc. I would store the transactions and calculate those things from transactions. In my view it's not different that inventory, where you don't typically store quantity on hand, you calculate it from transactions.

If you want to store them, an update query should work, or the Edit method of a recordset. Then you'd requery the subform.

[hockeyman9474]

Thank you. Like I said, I was probably overthinking the solution. Where I ran into issues was with update queries, but having to sum totals and then updating.

[pbaldy]

If you're still having trouble, it may help to attach a sample db we can play with.