OnChange Update Field1

[wreded]

i have a small database with 3 date fields. If any of the date fields are changed i want another date field (Updated) to reflect the date the change took place. i've tried placing code to update the Updated field on each date field's OnChange() property. Doesn't work (that i can see). Any ideas?
Thanks,
Dave

 

[Remou]

The On Change event is not the best for this. Try the After Update event.

PS It is often a good idea to post some code, even if it does not work.

 

[wreded]

i tried After Update and Before Update also, to no apparent avail:

Private Sub Suicide_Prev_Date_AfterUpdate()
    Me.Updated = Now
End Sub

The Before Update works at the form level and i use it alot, in this case it won't work because i have 3 calculated fields that take the dates and projects dates a year out for retraining. i do this through a query and it works.

NextTng: [Prev_Date]+365

If i do the form level Before Update as You skip through the records it updates the Updated field with the date/time it was updated through the query. Doesn't adequately show when a "physical" change to the record was made. Therefore i'm stuck with trying to make the After Update of each of my 3 date fields function properly.
Thanks,
Dave

 

[PHV]

1) I'd replace this:
NextTng: [Prev_Date]+365
with this (for leap year):
NextTng: DateAdd("yyyy", 1, [Prev_Date])

2) If the form is bound, then in its BeforeUpdate event you may try something like this:
If Me![date 1 control].Value <> Me![date 1 control].OldValue _
Or Me![date 2 control].Value <> Me![date 2 control].OldValue _
Or Me![date 3 control].Value <> Me![date 3 control].OldValue Then
Me!Updated = Now
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

Dave,

What happens when using the AfterUpdate event? Code break? Nothing happens? Error message?

Have you tried the bang operator instead of the dot? ie. Me[red]![/red]Updated FWIW, it's usually a good idea to avoid naming fields and such with names that are also properties and methods in VBA. While .Updated is not a property of the form object (it is a property of certain types of controls), ya never know...

HTH,

Ken S.

 

[Remou]

You have lost me, I'm afraid. Are you saying that the updated field is updated by query as you move through the records but you want it be be updated again(?) if a manual change is made to any of the three date fields? Can you explain a little more? Is it that another manual updated field is needed?

 

[wreded]

Updated" is a field i use to store the date of last change to a record. If a name or other field is changed then i take today's date and store it in a field named "Updated." This is just a little flag to the user to show when things were updated and a "bad" or unusual date could be a sign of trouble or wrong data. i could change the field name to "LastUpdate" with little trouble.

As a general practice i always bind my forms to a query rather than the raw data. This helps me ensure that things are in the proper sequence and any behind the scene updates are taken care of.

As for the "." or "!", i've never gotten the hang of which to use when. i've read everything i can find about the difference but it's still "tomato/tomAto" to me. Maybe it's my old dBASE ways coming out in me.

Finally, in the After Update event, nothing happens. "Updated" doesn't change and no errors thrown.

Thanks to all for Your assistance. i'll be trying all this stuff tonight (where i won't get interrupted by the phone).
Dave

 

[MajP]

The first step in debugging event procedures is often to ensure that the event is being trapped by a simple message box.

Private Sub Suicide_Prev_Date_AfterUpdate()
msgbox "After Update Trapped"
Me.Updated = Now
End Sub

If this msgbox does not appear than several things could be going on. In the contrls property you do not have [Event Procedure]. Another thing to remember (which I think you said), you can not trap events that happen in code. If I change the value of the field through code, the after update event does not fire.

If the message box appears than something else is going on.
What Eupher was getting at is that Bang notation allows you to use reserved words and spaces in field names. For example a database of government forms with a field named "forms". This would probably cause an error
me.forms
Forms has a meaning in VBA. But this is fine
me!forms.
me![Name With Space]

but not
me.Name With Space

 

[wreded]

MajP,
i learned (the hard way) NOT to use spaces in any names in Access. My big problem now is that i have inherited several Access applications that were created by users that either didn't know or didn't pay attention to generally accepted practices. Drag and drop is all well and good as long as the original person is around to play upkeep; but i can't for the life of me figure out what "Combo 105" or "Button 768" mean, what they call, or where they fit. i gotta step in to do clean up before i can begin to understand what's going on here. Keeping things simple and straight forward is hard work if it wasn't done from the beginning.
Dave

 

[Remou]

There is a problem with Access in that an event does not always get attached if is is pasted or typed. Have the relevant controls (or the form) an event procedure on their property sheets?

 

[MajP]

Not suggesting that you ever use Reserved words or Spaces in your naming convention. Personally I never use Bang notation unless I absolutely have to, but that is a different subject. But as Eupher pointed out "Updated" could be a reserved word (although I do not think it is). My point was only that if it is a reserved word then
Me.reservedword
gives an error
Me![reservedword]
will work.

Sometimes your stuck with the names others have used.