How do I add a date to a form when a field on the form has been edited? | Member | | Join Date: Aug 2009
Posts: 46
| | |
I have a form with multiple controls on it. I would like to be able to have the date automatically recorded on the form when any changes have been made to show when the record was last updated. Is there a way to do this?
| |
best answer - posted by missinglinq |
I see nothing here demanding that there be a separate updated field for each control. To timestamp when any control is edited in the record, simply place the code in the Form_BeforeUpdate event:
- Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Me.Last_Updated_Date = Now
-
End Sub
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 244
| | | re: How do I add a date to a form when a field on the form has been edited?
Yes, write code on the "AfterUpdate" of each control you want to track, so that once it is updated it will update the Last Updated field to Now().
i.e. - Private Sub Control_AfterUpdate()
-
Me.Last_Updated_Date.Value = Now()
-
End Sub
-AJ
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 2,991
| | | re: How do I add a date to a form when a field on the form has been edited?
I see nothing here demanding that there be a separate updated field for each control. To timestamp when any control is edited in the record, simply place the code in the Form_BeforeUpdate event: - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Me.Last_Updated_Date = Now
-
End Sub
| | Member | | Join Date: Aug 2009
Posts: 46
| | | re: How do I add a date to a form when a field on the form has been edited?
Missinglinq,
I tried your code but it's not working. I have a text box that is called Last_Updated_Date that I would like to date displayed in. Am I missing something?
| | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 2,991
| | | re: How do I add a date to a form when a field on the form has been edited?
If you change anything in the record, it should place the date/time in the field Last_Updated_Date. What does happen? Do you get an error message? Does anything appear in the Last_Updated_Date field?
Linq ;0)> | | Member | | Join Date: Aug 2009
Posts: 46
| | | re: How do I add a date to a form when a field on the form has been edited?
It doesn't give an error or anything. I change a field in the record and it stays blank. I have the last update field as a text box, is that right or should it be something else?
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 244
| | | re: How do I add a date to a form when a field on the form has been edited?
Quote:
Originally Posted by stateemk  It doesn't give an error or anything. I change a field in the record and it stays blank. I have the last update field as a text box, is that right or should it be something else? Try adding a - Me.Last_Updated_Date.requery
to your code.
-AJ
| | Member | | Join Date: Aug 2009
Posts: 46
| | | re: How do I add a date to a form when a field on the form has been edited?
It still didn't do anything. I change a combo box selection in the record, then tab off of it and it won't do anything. I'm just curious, but why is the code in the before_update instead of the after_update? Here's the code that I have just in case I'm not seeing something or thinking right. - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
-
Me.txt_LastUpdatedDate = Now
-
Me.txt_LastUpdatedDate.Requery
-
-
End Sub
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 244
| | | re: How do I add a date to a form when a field on the form has been edited?
I don't know if it matters if it's beforeupdate or afterupdate, but can you confirm that the code is getting executed, put a breakpoint on the beforeupdate line and see if it trips.
-AJ
| | Member | | Join Date: Aug 2009
Posts: 46
| | | re: How do I add a date to a form when a field on the form has been edited?
Okay, I left my code the same and tried changing one of the fields in the record. I then clicked to the next record and the last update field put in today's date and time. However, my next problem is that it threw that date in there for all records instead of the one I just changed. How do I get it to only update the date/time for the record that I edit instead of changing all of them?
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 244
| | | re: How do I add a date to a form when a field on the form has been edited?
I tested this, and I was not able to reproduce your issue, but I did find that the beforeupdate and afterupdate on the form will give you issues..
beforeupdate on form: Requires you to tab to a new record before you can see the record updated.
afterupdate on form: Causes a infinite loop, because after you update the "Last_Updated_Date" field, that triggers the afterupdate again, so it won't end.
I would recommend in this case, to use the afterupdate on each individual field.
-AJ
| | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 2,991
| | | re: How do I add a date to a form when a field on the form has been edited?
Doing this for each field is simply overkill! If it changes the date/time for all records, your Last_Updated_Date textbox is unbound! The textbox has to be bound to a field in the underlying table for it to reflect the date of change for just that record.
| | Site Addict | | Join Date: Mar 2007 Location: Oakland, California, USA
Posts: 521
| | | re: How do I add a date to a form when a field on the form has been edited?
If all the records now show the date, it sounds to me as if the control is unbound.
Why not use the form's OnDirty event for this. OnDirty fires when any bound control changes, but once raised, it does not occur again for the record. The date would then be assigned, and the user would see it if any changes were pending, not just after the record is saved.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: How do I add a date to a form when a field on the form has been edited?
Quote:
Originally Posted by stateemk Okay, I left my code the same and tried changing one of the fields in the record. I then clicked to the next record and the last update field put in today's date and time. However, my next problem is that it threw that date in there for all records instead of the one I just changed. How do I get it to only update the date/time for the record that I edit instead of changing all of them? It sounds like this is required as a retrospective time-stamp. It doesn't really make much sense to expect to see a time-stamp before the record is saved away. Remember, it is always possible for all form changes to be cancelled until the record is actually saved. Stamping it before then would actually be misleading.
I would suggest that the Form_BeforeUpdate event procedure would be the most appropriate place for this.
| | Site Addict | | Join Date: Mar 2007 Location: Oakland, California, USA
Posts: 521
| | | re: How do I add a date to a form when a field on the form has been edited?
You are quite correct. I hadn't thought of that.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
