Auto Update a Date

[PaulaCM]

I am trying to create a field in my Access 2007 form where, when you modify anything in the form it updates the date.

In other words, I have a "Date Update" field that I need to automatically update, only when something is modified on the form.

I'm a novice, so please explain in step by step terms!

Thank you!!!

[nico5038]

Best to add some code to the form.
1) Open the form in design mode and directly press the [Properties] button.
2) Goto the Events tab and double-click the "BeforeUpdate" text so the field is filled with the text "Eventprocedure".
3) Press the [..] button behind the text and some pre created VBA code for the event will open.
4) Type within the sub:

Me.[Date Updated] = Now()
 

5) close the code and save the form.

Access will now place the date and time in the field before the record is updated in the database.

BTW it's best not to use spaces in fieldnames...

Nic;o)

[PaulaCM]

Best to add some code to the form.
1) Open the form in design mode and directly press the [Properties] button.
2) Goto the Events tab and double-click the "BeforeUpdate" text so the field is filled with the text "Eventprocedure".
3) Press the [..] button behind the text and some pre created VBA code for the event will open.
4) Type within the sub:

Me.[Date Updated] = Now()
 

5) close the code and save the form.

Access will now place the date and time in the field before the record is updated in the database.

BTW it's best not to use spaces in fieldnames...

Nic;o)

Nic -

Hmmm... this doesn't seem to be working for me. Firstly, I'd like it to update the date only after I've updated something. Secondly, it just didn't work :(

thanks for taking the time to help (again)!

best,

[nico5038]

Strange, the BeforeUpdate is only triggered when a change has been made to the form, thus it's just the event you need.

Did you make sure that the fieldname (see under the other tab of the properties) of the date field is correct ?

Nic;o)

[PaulaCM]

Thanks Nic!

It's working now. But, it only updates if I change something in one of my text fields, not if I click a yes/no tab.

Not a big deal, but it'd be good to have it update upon either.

Does that make sense?

[nico5038]

Thanks Nic!

It's working now. But, it only updates if I change something in one of my text fields, not if I click a yes/no tab.

Not a big deal, but it'd be good to have it update upon either.

Does that make sense?

I guess that the YesNo field isn't "bound" to a tablefield in the recordsource of the form. Open the properties of the field and check under the Data tab or a fieldname is filled for the controlsource
Access will by default automatically trigger the BeforeUpdate when one of the "bound" fields is changed....

Nic;o)

[PaulaCM]

Nico -

I'm sorry, but for some reason, this isn't working again. I do have some that updated, but now they aren't doing it.

Also, if i'm on one of the yes/no fields, I don't have any options under the data tab.

Sorry to be such a pain!

[missinglinq]

By being in the Form_BeforeUpdate event (which is where it should be) it isn't going to be reflected until the record is saved, either by explicitly saving it or moving off of the record then back to the record.

The reason you see nothing under the Data Tab for the checkbox, I suspect, is that you've actually selected the box's label, not the checkbox itself, which is a very common error.

Also, to get only the date, use

Me.[Date Updated] = Date()

Welcome to bytes!

Linq ;0)>

[PaulaCM]

I tried that!

I've tried saving the form, saving the record, moving off the record, everything that I can think of and now the date isn't updating!

I even found the datasource on the checkbox for my yes/no fields (thanks for the tip) and the source is the correct box.

so, I really don't know what I'm doing wrong...

here's what my code looks like:


Private Sub Date_Updated_BeforeUpdate(Cancel As Integer)
Me.[Date Updated] = Date
End Sub


The only thing I did was insert that line of code you gave me, so maybe it's part of the auto generated code that is somehow wrong?

thanks for the help!

[nico5038]

I tried that!

I've tried saving the form, saving the record, moving off the record, everything that I can think of and now the date isn't updating!

I even found the datasource on the checkbox for my yes/no fields (thanks for the tip) and the source is the correct box.

so, I really don't know what I'm doing wrong...

here's what my code looks like:


Private Sub Date_Updated_BeforeUpdate(Cancel As Integer)
Me.[Date Updated] = Date
End Sub


The only thing I did was insert that line of code you gave me, so maybe it's part of the auto generated code that is somehow wrong?

thanks for the help!

As indicated by Linq, you need to use the Form's BeforeUpdate event.
Your code is in the Date_Updated BeforeUpdate event.
Remove that code and click in the small grey square top left (between the horizontal end vertical ruler). When it has a small black square inside, the form is selected and the Properties window will allow you to chose the right BeforeUpdate event.
The form is by default selected when you open the form for Design mode, you can check the "black square" to see that.

Nic;o)

[PaulaCM]

I know that I'm being obtuse, but .... still not working....

Here's the code that I've used:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Date Updated] = Date
End Sub


No updating of the date even when I save, exit, or move to another entry.

Thanks for your patience and your help (clearly, I'm a moron)

[nico5038]

Hmm, did you perhaps also use a formcontrol with the name [Date] ?
To be sure (and more precise), try to use Now() instead of Date in your assign statement.

When still not working, make a temp .mdb and add that as an attachment so we can have a look.

Nic;o)