Easy. How to put date/time stamp if form is changed

[dewsbury]

Hi,

This should be easy but is not working for me.

Simple table with 2 fields; NAME_1 and DATE_OF_UPDATE.

I have set up a form for the maintenance of these 2 fields.

If I change the NAME_1 field I want the DATE_OF_UPDATE to be set to the current date/time.

I thought that I could do the above by setting the properties of the NAME_1 field , "On change" =[DATE_OF_UPDATE]=now()

However, the above has no impact at all. Help!

 

[RuralGuy]

Tables have no triggers in Access. You need to set the TimeStamp in the BeforeUpdate event of your form.

 

[Pat Hartman]

The On Change event runs each time a key is pressed and so is not an appropriate event for this purpose. In fact, the ONLY time you would ever use the On Change event is if you actually wanted to trap keystrokes and prevent them at the time. I personally have never had occassion to use that particular event.

So as RuralGuy has already mentioned, use the BeforeUpdate event of the form. This event ONLY runs if the form has been dirtied and it is the last event to run before the record is actually saved.

 

[dewsbury]

Thanks but no joy yet!

Thanks guys.

I tried your suggestion
However, I must be missing something.

See attached word document which shows what I have done.

Reminder: I am trying to put a date/time stamp field on a record if I change the "NAME" field.

Perhaps I should be using a different approach?

 

[Brianwarnock]

You are not doing it correctly.
Click on the BeforeUpdate event in properties and you will see ... on the right click on those and then on code builder in the pop up then enter
Me.[DATE_OF_UPDATE]=now()
into the code.

Personally i would do it in the Name_1 Before update event not the Form's , but both previous posters are infinitely more experienced and knowledgeable than I, but if you do it in the form update you will update the date/time for any form change not just the Name_1 field.

Brian

 

[dewsbury]

Brian,

Thanks, I seem to be making some progress.

I put the the code below where you suggested.

Now when I change a record the date/time is displayed in the correct position on the screen.
However, the date/time is displayed on ALL records !!
Also, the date/time is NOT save onto the database table at all!!

Any thoughts?


Private Sub NAME_1_BeforeUpdate(Cancel As Integer)
[LAST_UPDATE] = Now()
End Sub

 

[Brianwarnock]

Where is the Me. ??


Brian

Edit I thought that your field was DATE_OF_UPDATE

 

[dewsbury]

Thanks again Brian.

I made a typo ... the Me. is in the actual code.
(What does Me. mean???)
The field name is actually LAST_UPDATE (I may have stated a different one earlier in the thread).

I feel we are very close to a solution.
Let me give a step by step of what I have done.

1. Open the relevant form.
2. Left click the name field followed by right click to get to properties.
3. Click on the "event" tab.
4. Click on the "..." symbol beside "Before Update".
5. This opens a form which now shows;
Private Sub NAME_1_BeforeUpdate(Cancel As Integer)
Me.[LAST_UPDATE] = Now()
End Sub


The above is not working.
The date/time is displayed for all records and not actually save for any!!!

Any other thoughts?

Thanks again.

 

[Brianwarnock]

Regarding the Me.

Pat's explanation in this thread is the best.
http://www.access-programmers.co.uk/forums/showthread.php?t=141894&highlight=bang&page=2
Post 25

I don't know why it is not working if the Form is bound to the table.

Brian

 

[RuralGuy]

Maybe posting a sample of your db that shows the issue would be helpful.

 

[dewsbury]

Maybe posting a sample of your db that shows the issue would be helpful.

Good suggestion.

I will create a new database with one single table that has only two fields !

What is the best way to "post a sample" onto the thread?

I note that mbd files are not allowed as attachments.
Should I zip it ??

 

[RuralGuy]

Zip it and it needs to be < 394KB in size. If you respond in Advanced mode then scrolling down will reveal the Manage Attachments button.

 

[Brianwarnock]

Can you not use the current DB? This ensures we get the exact problem.
If you can compact it before zipping.

If not create the new db by Importing the table and form from your existing DB to the new one remember to select options definition only during the import , then add data.

Brian

 

[dewsbury]

Resolved !

Thanks for all your help.

I think I had protected the field from update which obviously caused a problem.

 

[RuralGuy]

Excellent! It sounds like you have discovered the problem. That's great!