Last edited date in a label

[Anonymous_354]

I'd like to have a label that updates everytime the database (forms, tables, queries, etc.) gets update. I'm guessing it'll take code, but I may just be overlooking a property. Any thoughts?

 

[agehoops]

Updated in what way? Design, data or literally everything?
I would've though you're going to need some sort of code that can check the properties of the database file itself, and monitor the data and time that it was last edited. When this changes, then use some code which will change the label to whatever you want it to be.

So for example, if you setup a macro called AutoExec, and tell it to load a form (create a new form) then this will run when the database is loaded.

In the form, you need to decide how often you want it to check the status of the file. Lets say every 10 seconds or so.
Set the form timer to 10000 for 10 seconds, and in the OnTimer even put something like the following:

Dim infoReader As System.IO.FileInfo
infoReader = My.Computer.FileSystem.GetFileInfo("C:\testfile.txt")
me.labelname.caption = "File was last modified on " & infoReader.LastWriteTime

Or something similar to that. Will have to fiddle around with it as i haven't tested the code. This code will check the file C:\testfile.txt (you need to change this to your db file) and find out the date it was last modified on. Then changes the caption of the label to whatever you tell it to.

Hope this helps

 

[Anonymous_354]

Well, I would like all three updates. What is the My.Computer.FileSystem for?

Ok. Just to get things straight (in my mind), C:\blah\blah\blah is the path to my database, labelname is the name of the label that will display the date, and you don't need the & if you aren't going to put in the "File was last modified on..." Did I get it right?

P.S.: Please forgive my ignorance.

 

[Anonymous_354]

How much more information does one of you need to figure out a problem like this?

 

[agehoops]

Yea that's all right. The C:\ is the location and filename of the backend database or whatever file it is that you want to get the modified details from.

labalename is the name of the label on that form that you want to put the information into for the user to see.

If you just want it to show the information and not have any text then you're right, you don't need the '&' there.

The My.Computer.Filesystem is (correct me if I'm wrong somebody) the way in which VB accesses that part of the windows filesystem. Because the database is just a normal file stored in windows, the code has to use the windows system to get the details of the file.

Hope this all helps

 

[Anonymous_354]

Well, I don't mean to rain on your parade, but we're dealing with VBA, not VB or VB.NET. If I put the code in a VB compiler (Visual Studio 2005 to be exact) it has no problem with it. I think a translation may be in order. How? I don't know...

Yes, your replies have helped some. Thanks

 

[agehoops]

Ah yea sorry my bad. Try looking for a function called GetFileTime() which i believe is also built into the access VB so it SHOULD work.

http://msdn2.microsoft.com/en-us/library/ms724320.aspx

Have a look on there, it gives you some more details on it, but you should be able to get that to return the information you need

 

[Anonymous_354]

I'm not seeing where it "should" be built in.

The code on the page is some form of C, not VBA.

 

[agehoops]

whoops sorry.

Have a look through here:

http://www.cpearson.com/excel/FileTimes.htm


Has loads of vba codes including that one

 

[Anonymous_354]

I can't seem to get the files to work... Either that or I'm not using them right or don't have the right one. Can one of you put together some code for me? The label I'm trying to update is Label139 (yeah, I know I should name them better, but this is database is only for me) and the file name is C:\Downloads\NWind.mdb. That's not the file name I need to update, but it'll tell me where to correctly put it in. Thanks.

 

[agehoops]

Hiya. Sorry about all this, appears to be a lot more simple than i first thought

Dim LResult As Date

        LResult = FileDateTime("C:\Downloads\NWind.mdb")
        Label139.Caption = LResult

That code is currently set to run when a command button is clicked but can be used anywhere you like. You can call the variable anything you like as well, I just used LResult.

Hope this helps

 

[Anonymous_354]

Excellent! Works great!

Now for the next task. Apparently there's something called %USERPROFILE% (short for C:\Documents and Settings\*username*\). In theory this is supposed to make it more flexible in that you can use it in a different environment at the same time (so each computer doesn't have to have the same user name to use the database). I need to be able to expand the name %USERPROFILE% into C:\Documents and Settings\*username*\. Any ideas on how to do that?

 

[agehoops]

Try using :

Environ("USERPROFILE")

This will return you the path where the My Documents folder is, so you'll need to add the /My Documents after it, but it will be dynamic for every user, and windows being windows will have ever documents folder stored as My Documents so you can just add that all the time

 

[Anonymous_354]

Where should it go?

Should it look like this:

Dim LResult As Date

        LResult = FileDateTime(environ(%USERPROFILE%) & "\My Documents\")
        Label139.Caption = LResult

Or something like this...:

Dim LResult As Date
Dim tempDestination as String

tempDestination = environ(%USERPROFILE%)

        LResult = FileDateTime(tempDestination & "\My Documents\")
        Label139.Caption = LResult

Or does it matter?

 

[agehoops]

Theoretically either, but to save on code and confusion use the shortest. I've just tested it out and it works fine. You'll still need to give it a file in order for it to work though.

        Dim LResult As Date

        LResult = FileDateTime(Environ("USERPROFILE") & "\My Documents\[filename and extension here]")
        Label139.Caption = LResult

Be careful though, don't use % around the USERPROFILE otherwise it will give you a syntax error.

 

[Anonymous_354]

Excellent again! Worked first try! That's all I need for this form. I'll hollar if I need more help. Thanks!

 

[agehoops]

Brilliant. No problem, glad I could help