469,284 Members | 2,505 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,284 developers. It's quick & easy.

How to update date when doing record updates

I created a Customer database. I have eight fields. Customer,address,city,state,zipcode,phone#directio ns,and date. When I add a new customer record, I have to problem inserting the current date. What I am having a problem with is after I update a record ( any field ), I need to set the date field to the current date. I tried on the date properties sheet on afterupdate I put =date(). Does not work. Do I need to do that for all of the fields? I am using access 2003
Thank you
Aug 10 '11 #1
11 7144
patjones
931 Expert 512MB
Hi John,

How are exactly are you doing the update? In other words is the table connected to a form where you edit some of the fields and then hit a button to update the record, for instance? I think we can get you on the right track with just a little more information. Thanks.

Pat
Aug 10 '11 #2
Pat, I have the table connected to the form. I select the form, CT CUSTOMERS and there is a table linked to the form. I search of the record I want to update, update the record ( maybe changing the phone number, and closing out the form.Lets say the old date is 8/8/2011, and after the update was complete, I need the date field to reflect the current date such as 8/10/2011
Thanks john
Maybe just an idea, but also add a field like (U) to appear on only records that were updated. Then I could search on all records with a (U). Might be a better way to go. No (U) on any record other than a record that was updated
Thanks again
Aug 10 '11 #3
patjones
931 Expert 512MB
It's no problem. Just one more thing...when you are done updating the record (perhaps modifying the phone number as you say) - you simply close the form? Or is there some button on the form that you hit? Thanks.

Pat
Aug 10 '11 #4
Pat, I normally would just close the form
john
Aug 10 '11 #5
patjones
931 Expert 512MB
I'm not sure if you have worked with VBA (Visual Basic for Applications) at all. But there's a real simple solution for this by using it. This will work provided that you're displaying that 'last updated' column on the form.

Suppose the text box that displays the date of the last update is called txtDateUpdated, for the sake of what I'm demonstrating (you're going to replace this with whatever the name of your control is). If you go into the properties sheet for the form, and under Events select On Dirty, then [Event Procedure], Access will take you to a VBA window which will have two lines that look like

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2.  
  3. End Sub

Between these two lines, insert the code

Expand|Select|Wrap|Line Numbers
  1. If Me.ActiveControl.Name <> "txtDateUpdated" Then Me.fldLastUpdate = Now

so that the entire thing looks like

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2.  
  3. If Me.ActiveControl.Name <> "txtDateUpdated" Then Me.fldLastUpdate = Now
  4.  
  5. End Sub

Then go to Debug > Compile, and close the VBA window. Back in the form, when you make a change to the active record, in any field, the last date updated box should automatically fill in.

The On Dirty event is triggered on the first keystroke that takes place in any field.

Pat
Aug 10 '11 #6
NeoPa
32,173 Expert Mod 16PB
This should be a lot simpler than that Pat I would suggest (unless I've missed something).

The BeforeUpdate event procedure for the form would simply set the vale of [Date] to Date() (Not Now() preferably - even though that would work, as it is somewhat misleading in code) :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Me.Date = Date()
  3. End Sub
I've assumed the [Date] field and the control on the form bound to that field are both called Date. In reality, both using the same name for field and control, as well as naming anything Date, are bad ideas and should be avoided.
Aug 10 '11 #7
patjones
931 Expert 512MB
AfterUpdate was my first choice also, but the problem that I encountered with it when I tested it in my sandbox database was two-fold.

First, after modifying a field and then attempting to move to another record, Access would update the date/time every time I hit the next record button, but would not actually move to the next record.

Second, when I tried to close the form, Access raised an error saying that it could not save the record. However, when I closed that error and proceeded with the form close anyway it turned out that the record was in fact saved - which I would expect considering that it's a bound form.

So I went to On Dirty, which eliminated these problems...
Aug 10 '11 #8
NeoPa
32,173 Expert Mod 16PB
I've just reread what I posted and I made a blooper there. I've updated it now to say BeforeUpdate instead of AfterUpdate (as the latter would make no sense anyway).

If you feel any of your reservations still count in this situation let me know. Maybe the only way to handle it is by working with the Dirty flag, but I'd be more than a little surprised.
Aug 10 '11 #9
patjones
931 Expert 512MB
Yes, BeforeUpdate works nicely.
Aug 10 '11 #10
entom2
1
Oops, I meant to say that OnDirty worked for me! Cheers
Mar 17 '19 #11
NeoPa
32,173 Expert Mod 16PB
Good to see people are still benefitting from threads from almost eight years ago.
Mar 28 '19 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by EManning | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.