How to update a field

[djclntn]

I'm uncertain how & if this can be done. In one of my dbs within a table (Queries/Forms) I have a Date/Time fld name 'LastUpdated' in which I put in the date of the last time I updated the Record. However, could there be a way in which when I update/make changes in a particular Record the 'LastUpdate' field automatically changes to the current date vs. me having to manually update.



Thanks in advance & I hope to have made my question clear...

[alansidman]

This may be helpful

http://www.datawright.com.au/access_..._in_access.htm

[djclntn]

Thanks for the link, however it wasn't very useful for me because the articles talked about multi-user database. Whereas, My databases are only use by me. Access is funny in the sense that it can do so much; so many things in Access can be done automated, but I suppose not everything. With my capability (or capacity) in learing it will forever leave my only know the tip of the iceberg with access/database, yet, I find it so intriquing to say the least. I always have these ideas to come to mind & so I come here & asks the experts.

Thank you kindly for your time...

[orange]

Hi djcIntn,

How do you do the updates/make changes to the records? Do you use a query?
If your updated records have an ID (unique number), you could use a query along these lines

Update YourTableName
Set LastUpdated = Now
Where RecordID In ( 2,4,6,45)

--- in this sample YourTableName represents the table invovled
------------------ RecordId is the numeric ID of the record
------------------ 2,4,6,45 are the actual ID's of the records you have just changed

[djclntn]

Hey Orange,

Nice to hear from you. Please, no disrespect to others; I think both June & you are really cool with me. I'm using Forms to update my Records -- not that it matters because my Forms are essentiaally based on Queries.. Here's the skinny, in my mock used car dealership db, whenever I add a nw vehicles/Record, sell a vehicle or make any changes I also have a "LastUpdate' fld in which I manually have to put in/change it to the current date. It's not that I'mm lazy, but if there's a way in Access where whenever you make any changes to a given Record you can keep track of when you last updated the Record. But, I want the "LastUpdated fld within the same Query/Form & not in some seperate query. I'm sorry if i talk to much.

My 'Vehcles' Table/Query does have a "VchlID" which is an auto number so I guess that would be used as my unique number. I'm sorry to say that I don't quite understand your suggestion; do I need to create a seperate query or...? Please excuse my ignorance if you will.

In any event thank for replying.

[alansidman]

I'm guessing that you didn't read beyond the first couple of lines in the tutorial that I gave you. It can be set up for one person. You do not have to have a multi-user db to employ this techinque. Follow it step by step and you will get what you need.

Alan

[orange]

djcIntn,

The site that Alan gave you - the first section - Simple Tracking - should do what you want.
As Alan points out, it DOES NOT APPLY ONLY TO MULTI-USER databases. It will work for your database.

[djclntn]

Alan,

I thank you for your follow-up response, however after reading the article several times I fail to unstand how to set u & accomplish what Im aiming for. Please, no disrepect to anyone here, but to me, in order to fully conceive & understanding the concepts of Access i see it as being very similar to being a computer programer. Truth be told, I don't have the mind when it comes to "programer" which is probably why i greatly struggle with the fundermentals of Access & am always coming here with questions. Those are just my thought & the facts.

Unless you might be willing to understand & walk me through the steps -- step-by-step, then me continuing to manually update the Recordss is just as easy.

Again, thaank you thus far...

[alansidman]

I'm not sure what you would want someone to do. This tutorial is a step by step exercise. What specific questions do you have that need to be answered? Post them and we will answer, but short of that, you need to jump in here and do the work. Print out the tutorial and follow it step by step. It is very simple.

[June7]

djclntn,

The referenced tutorial offers two approaches.

1. Simple tracking: merely updates the record to show the last date it was edited, also by whom if you want.

2. Audit trail: maintains a history of all edits to records in the table of concern. This means records are written to a 'log' table as well as updating the primary record.

If you want to review another user's experience with approach 2 - https://www.accessforums.net/showthr...ical-dates-for
That thread has a link to another article about maintaining edit history and has a sample db to download.

[djclntn]

Guys,

In the simple tracking example it asks who it was created & mod by, I don't have that info since i'm the sole user.

Private Sub Form_BeforeUpdate(Cancel As Integer)
ModBy = Environ("username")
ModDate = Now()
End Sub

Private Sub Form_Current()
If Me.NewRecord Then
CreateBy = Environ("username")
End If
End Sub

I'm sorry, but I don't understand what steps I ought to be taking. Do I need to create a "Createdby', CreateDate', Modby', & 'ModDate in my Table? If so,then I rather update my fields manually.

Here's what I had in mind or was hoping/looking for: in my 'LastUpdate' field fld -in a a query- be able to write some type of statement (maybe an Iif statement) saying something along the lines like, when Record is change update "LastUpdate' to Date(). If I need to go in & add new flds to my Table it's just not worthit.

Please, Alan June, & Orange I do thank you all for you input & time; I'm sorry, but I'm just not seeing/understand this article at all. I tried downloading the sample db June offered, but it wouldn't open forme. I think that I ought to chalk this up as a lost & just vcontinue updating the Records manually. You guys have given me all of that resouces & help you can, but you unfortunately can't make my brain comprehend what I need to do.

To all, much appreciation!

[orange]

No. Think about it. All you want is "when you Last Updated a particular record".
So add a LastUpdatedDate Date/Time field to your table layout.

In the query, in whatever method you use to update a record, you need to have some code or an expression to store the current Date in the LastUpdatedDate field.

How exactly do you update a record?
If your database is accdb, I can not read it. So be as detailed as you can as far as how you currently update your records.

[djclntn]

Orange, my man!

It's no one's fault but of mine own in lack of communication. Yes, all that I want is, "when I last updated a particular Record" -- exactly!!! In my Table I have a fld entitled'LastUpdated' which is a Date/Time. Data Type. You know, some folks might be reading my thread & saying to themselves, "Just do it manually you lazy A**' but hey, if it can be done automated isn't that what Access is all about?R

Right now when I updete a Record I go into the 'LastUpdate' field & punch in the current date (or Control & I'm sure that you know that trick. I believe to have address your questions at best. Can you take me further in this mission?

If needed I could send you the db in mdb format

[June7]

The code uses the BeforeUpdate and OnCurrent events of the form to modify the date field. These occur when the form closes and when it opens, respectively, and also when moving to another record. So the revised date value will not be displayed to the user until next time the record is viewed.

Also, the code does not ask you to provide username, it will pull that from Windows, even if this is a single user computer not on a network, there is still a name.