473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 7563
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,556 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,556 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,556 Expert Mod 16PB
Good to see people are still benefitting from threads from almost eight years ago.
Mar 28 '19 #12

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

Similar topics

1
by: Chicken Kebab Abdullah | last post by:
I am making a database of my movie collection and I have a form which Shows a DVD Code in a combo, and a list of the divx movies on the dvd on the right in a list box. The combo boxes bound...
1
by: Lumpierbritches | last post by:
Thank you in advance for any and all assistance, it is GREATLY appreciated. I was wondering if there is a way to tell Access 97 to compare the first line with other textboxes using the...
3
by: jdph40 | last post by:
In Access 2002, I designed a simple database for our Safety department to enter results of a survey. There are 41 true/false statements. I have a main form called frmSurvey with a subform called...
2
by: jv | last post by:
Hello, I have a Service Cancellation form that contain a CancelDate text box. The text box has an AfterUpdate event that perform a few calculations. This works fine. However, next to the text...
3
by: gazelle04 | last post by:
Which happens first AfterUpdate event of a form or Clicked event of a button on a form. I have these codes on an AfterUpdate of a form: If UpdateDone = True Then ...
5
by: EManning | last post by:
I'm developing an unbound form with a series of questions on it. Each question is either a text box or an option group. I have coding in the AfterUpdate event of each control to highlight the...
1
imrosie
by: imrosie | last post by:
Please help with this one,,,,,I've been trying everything in my arsenal to fix this one. I'm stumped.... I"ve got a unbound combo box (customername) that has two events (on click); AfterUpdate and...
4
by: bcallnan | last post by:
Hello All- I am trying to reference a subform's afterupdate event that is 3 deep and am having some trouble getting it to work. The control is a combo box and i cannot seem to trigger the...
12
by: Gerhard | last post by:
This is bizarre... Im having problems with the combobox AfterUpdate event: Im running Access 2003. I created an unbound combobox with 3 columns on a form. The Row Source is from a table....
3
mshmyob
by: mshmyob | last post by:
I am scratching my head over this. I have a combo box control where the afterupdate event or even the onchange event keeps runnning twice. Below is some simple code to emulate the problem I am...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.