How to make a "Last Updated Date" Automatically? 1

[Cero21]

First off I'm a beginner Access user but I'll try to explain what I want to do as best as I can.

I am creating a inventory database where each sub unit (number based, 741 744 and 748) can update a main table with what inventory they have. This is done through a form where each unit is restricted to their own equipment by locking their unit number in the form.

Queries are used to view the equipment held by each unit by filtering by unit number.

Reports are then made from each query to get a report of each unit.

The main table alone is made into a report that displays all the equipment from all the units combined.

What I want to do is have access somehow timestamp the last update made by each sub unit. Then I want to place these timestamps into the full report so that I will know when the sub united updated their inventories. Is this possible? And if so, how do I do it?

Thanks for your time.

 

[GingerR]

What you can do is this: add a field to your table called LastUpdatedDate. Make it a date/time field. In your form, bring that field onto your form. In the form's design mode, view PROPERTIES. Find the AfterUpdate event. To the right of "After Update", pick "[Event Procedure]" and click the BUILD button (the little button to the right with the three dots on it). Write some code like this, using the NOW() function which is a built-in function that returns the Date/Time. You could also use the Date() function, which just returns the Date (no Time).

Me.LastUpdatedDate = Now()

Try that and let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

I'm sorry I'm not sure on how the relationships work in Access and need to know a small coding thing.

Here is an example of what I have setup.

TABLE: Comms Eqpt Holdings
QUERY: 741 Comms Eqpt Holdings (filtered by using unit# = 741)
FORM: 741 Comms Eqpt Holdings - Built using the wizard off of the query

I put a field into Comms Eqpt Holdings (TABLE) called 741LastUpdatedDate (date/time type)

Therefore, when coding it what exactly do I write?
Me.741LastUpdatedDate didn't work
Comms_Eqpt_Holdings.741LastUpdatedDate = Now()?

Thanks for your help

 

[GingerR]

Do you have a form where you do data entry?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

In the example above, the form 741 Comms Eqpt Holdings add/deletes/edits the Table Comms Eqpt Holdings. The form only deals with 741 entries as the unit number "741" is locked in the form.

 

[GingerR]

Oh, sorry, didn't see that.

Where did you put that code? It should go into the Before Update or After Update event of the form. You probably won't see the date change right on the form, because Before Update/After Update fire when you move to a different record or close the form. But if you look in the table, the date/time stamp should be there.

Also, another question unrelated to yours: why do you have a form that only shows one unit's data? Will you in the future be making multiple forms, one for each "unit"? There are easier ways. If you want to pursue, let us know.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

Putting the code into the form is fine because I don't need it to show the updated date in the form right away. It is more important when I create a report of the data so we can see when the data was last updated.

As to the other question. I had made multiple forms, one for unit using the structure I mentioned above. Even though I've already implemented the said structure I'm curious about some of the other "easier" ways you are referring to.

Thanks again.

 

[GingerR]

Will you be making copies of that form, one for each unit? Is this in the same db you are sending me? IF so I'll take a look.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

I have already made copies of the form for each unit. And yes, this is the same db. Did you receive the db yet? I sent it off yesterday but it is possible it could have been blocked by our mail server.

 

[GingerR]

nope not yet. try putting it in a zip file. did you compact it?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

GingerR - Since I haven't been able to send the db for a while now maybe you could provide some insight into this date thing in the meantime.

So here is what I have in the Form properties, after update code

Private Sub Form_AfterUpdate()
Me.741LastUpdatedDate = Now()
End Sub

This doesn't work and I know it is wrong but I don't know how to fix it. The table/form/query structure is as above. What code do I write here?

Thanks a lot for your help!

 

[GingerR]

Hi--my email account is valid, others have sent stuff, so I don't know if you just haven't sent anything or if you might be sending to the wrong address?

Anyhow, are you sure the date isn't really going into the table, but just not being displayed on the form?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

Oh I know your email is valid it's just that the firewall here at work is very very strict on attachments to outside email addresses.

By trying the code Me.741LastUpdatedDate=Now() gives me a compile error.

 

[GingerR]

what's the error?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

I get the error:
expected: =

Sorry, I couldn't send the db during the weekend. I was away from home.

 

[Cero21]

Further to by last.

I got that error and .741 is highlighted

 

[Cero21]

I think I found a solution to my problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;209637

 

[Cero21]

I guess I have one more thing I would like to do to this.

Is there anyway I could get the new date to appear on all the records, not just the one record that changed?

Any ideas? Thanks.

 

[GingerR]

But I thought you wanted the date to appear when a record was updated? Wouldn't it defeat the purpose if you put a "Last Updated" date on a record which wasn't updated?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Cero21]

GingerR you're right. I should leave it as is. However, on the reports I would like to show the newest date anything was changed by each sub unit. So if one unit, say, 741 changed one item in their communications equipment inventory the report would say Last Updated:[Newest Date]. How do I implement this?