Textbox to Control Source as well as Datediff function

[cknroman]

Hello all,

I am a self taught Access guy. I have a form that has a textbox called 'Date Since RREP Approved' that I want to control a table field as well as preform a datediff function.

datediff("D",now(),[RREP Approved Date])

If I put that function in the control source I get the number of days since the RREP Approval Date. However I cant get it to update my table. Can someone help me with this?



Furthermore, I have a drop down box named 'Status'. When I select 'Completed' I'd like the 'Date Since RREP Approved' textbox to stop the datediff function. Is this possible?

Thanks in advance!

[Welshgasman]

I would set your field in the BeforeUpdate event of the form if you do it that way, else use the table field and set the control on a new record?
On the AfterUpdate event of Status, check if Completed and if it is do whatever your are talking about to 'Date Since RREP Approved' textbox to stop the datediff function' ?

It is possible if you use the second option to update the the Approval date, then that might take care of itself?

[cknroman]

Thanks! I was able to get the Date Since RREP Approved to update the source with the Datediff.

I am not understanding what you mean about the afterupdate... check if completed. I dont know how to afterupdate if [Status]=completed then stop [date since rrep approved],beforeupdate.

Not sure if that makes sense...

[Welshgasman]

I think the easiest option would be to set that control from your datediff formula in the Forms Current event IF the status is not Completed?
Also if you are not interested in the time element, I would use Date, otherwise you could come into problems if you start looking for records 5, 10 or 15 days old etc?