Update/Add To recordset from form

[robmkimmons]

I have a form [frmEmployee] which has basic employee information. Within that form, a subform [sfTrain] which tracks various training courses the employee has participated in.

Within this subform is a field [Hours] which keeps track of how many hours are completed for that particular training event (instance). This field must be updated when the employee completes more hours.

For example, when the training event is first entered let's say the employee completed 5 hours worth. So, 5 is put in the [Hours] field. The following week, the employee completes another 5 hours for the SAME training event. I want to be able to add that 5 to the previous to update the field to 10. (did i spell it out well enough?)

I have tried several attempts such as an unbound text field on my form and a control button which runs an update query to add the amount to the recordset, but i don't know if i'm getting the code right or not. Is this the right direction i should be heading?

Can someone point me in the right direction?

Thanks in advance! ~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[SBendBuckeye]

You might want to consider having your database field be display only and let the user update an unbound field such as adjusted hours. You could then update your database field out of the after update event for your unbound field.

One gotcha to be aware of if you do something like the above is that the previous value in the control is ONLY updated when the recordset is updated, it will NOT be updated if you go into and out of it a couple times during the same record.

If you post an email address, I did something similar for a total pieces / total weight situation for a trucking company a while ago (it was not on a subform but it might get you started).

Good Luck!

 

[robmkimmons]

Thanks Buckeye,

Yes, i have the db field only visible but not enabled. The user imputs the new hours in an unbound field (they are not allowed to mess w/the db field) and then click a control button to add the unbound field to the db field.

one glitch i'm having is that when i update the value it adds that amount to ALL the records in the subform. i.e. ALL training instances are incremented by two hours if i'm only trying to add two hours to one particular instance. i'm sure i just have a syntax problem somewhere that isn't telling access to only update that particular record in the subform.

and yes, i'd love some examples to go by!
robmkimmons@yahoo.com

THANKS! ~Rob

If we expect the unexpected, does that make the unexpected... well, expected?