Control Not Updating Table

[Skip Bisconer]

I have a form to record employees training progress to a Log table. I am in the midst of updating this entire process and at one time had this problem solved (I thought). The user selects and employee id from a combobox list then selects the Training policy number from a combobox list and enters a training date. Each policy has an Interval(year) assigned 0, 1 or 3 to it.

The retrain date is where I am having issues. I have Retrain date's control source set at =IIf(Nz([Intv],0)=0,Null,DateAdd('yyyy',[Intv],[DateTrained])). This is suppose to add the interval to the Train Date and post to the table. The problem is it doesn't post to the table. All the other data on the form posts except that item. I can say that at one time in the past it did as the current table has retrained dates entered.

Can some one give me a suggestion as to what I am not seeing.

 

[KenHigg]

The way you have this set up is that it never actually gets stored but is derived as needed from the other fileds, which is the way it should be, in other words you shouldn't store a calculated value.

So when you have the form up the correct date is being displayed but not stored in the table?

 

[Skip Bisconer]

Thanks for responding Ken

Yes! that is true, it does show in the form but it never gets to the table. Do you know what I should do to get it to the table?

 

[boblarson]

Skip:

A control source, in order to be saved to the table, needs to be a field in the table or query. Any calculated control source would need an alternate method. Now, I don't suggest actually storing calculated values, except when absolutely necessary and so if it is actually necessary you can add a control which is bound to a field in the table and then in the After Update event of the control with your IIF statement, you can put:

Me.YourBoundControlName = Me.YourCalculatedControlName

And that will save it to the table.

 

[Skip Bisconer]

Hi Bob,

Since my post I have been banging this around and came up with an AfterUpdate event on DateTrained using the NZ function. It seems to be working. Would you Look at this and see if I am creating my self a problem.

Private Sub DateTrained_AfterUpdate()
RetrainDate.Value = IIf(Nz([Intv], 0) = 0, Null, DateAdd("yyyy", [Intv], [DateTrained]))
End Sub