Capturing "Modified Date"

[mg1559e]

I have a form which simply just serves as a user interface for our admins to enter records into a table. They also have the ability to search the records and modify them as needed. I'd like to have a field on this form that captures the date the record was last modified. Is there an automatic way to do this?

I used the "=Today()" function to capture the date the record was created and was hoping there was similar logic to capture date modified.

thanks

 

[Rabbie]

Use the Before Update event to set the modifieddate. There have been several threads about this in last few days so it would worthwhile doing a search

 

[mg1559e]

thanks, I'll search for one of the other responses that perhaps detail that method. pardon my redundancy, i'm new to these post-sites.

 

[mg1559e]

my apologies. I'm not finding the proper response that i need to fix my table/form. Any help with setting up a field in a table that captures modified date would be fantastic.

 

[Brianwarnock]

LINK

I thought Today() was an EXCEL worksheet function

Brian

 

[mg1559e]

it works in Access as well.

 

[missinglinq]

But it only works if you have Microsoft Excel XX.X Object Library installed!

As to your problem:
Add a field to you underlying table; call it ModDate with datatype Date/Time.
If your form is based on a query, go into Query Design and add the new field to the query.
Place the new field on your form. You can set Visible = No if you don't want it visible.

Place this code in the code editor for you form

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.ModDate = Date
End Sub

If you'd like the time as well, replace Date with Now.

Linq

 

[mg1559e]

this is great! Thank you for the details steps. 2 questions....

1 - the form is pulling right from the table so will the solution you proposed still work?

2 - what exactly do you mean yb code editor? I have the form properties open and am looking for the correct field in which to put the code you provided.

thank you for your help!!!!

 

[missinglinq]

I was answering this post 2 hours ago and we lost power here! Just came back up! If you form is based directly on a table just omit these directions:

If your form is based on a query, go into Query Design and add the new field to the query.

No, forget about the property box. From Design View, from the menu click on View then on Code. This will take you into the code editor. If there's anything in it besides the line "Option Compare Database" (there may or may not be, depending on whether you've used the wizards to do things like make buttons, etc) scroll down to the end of any code, then just paste this in:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.ModDate = Date
End Sub

If you named the field ModDate like the instructions above said you're all set!

Hit <Alt> + <F11> and you're back in Design View and ready to run your form. Now, anytime you make changes to a record the date (or date/time if you use Now() instead of Date) will be placed in the field ModDate.

Linq