How to show the last modified time of a table?

[huangt]

Hi all,
I am a newbie of Access. Now My boss asked me to show the last modified time of a table in a swithboard.

I use a text box (text5) to show the time, the table name is "Astra HB3 Part_DMT", then I try the followed code:

Private Sub Text5_BeforeUpdate(Cancel As Integer)

Me.Text5 = table![Astra HB3 Part_DMT].LastChangeDate

End Sub

But it dones't work, Is "LastChangDate" the right property name for the table ? Do we need to open the table at the same time in order to show the time? Or we need to use the New() funciton?

Thanks

 

[ghudson]

Try this as the Control Source for a text box...

="Last Modified: " & DLookUp("[DateUpdate]","MSysObjects","[Name] = 'YourTableNameHere'")

OR

MsgBox "Table was last modified on " & DLookUp("[DateUpdate]","MSysObjects","[Name] = 'YourTableNameHere'")

Be warned that the date will change when a design change has been made and also when the db has been compacted. The date is not updated when a record change has been made to the table.

If you are worried about the modifications made to a record then you need to add an audit trail to your db. Check out my audittrail.zip sample if you are interested... Audit Trail

 

[smercer]

If you want a more perminant version, I have a field so that the date and time is changed when the user changes the current record.

see attachment

I use This to enter the current user name of windows (won't work for windows 98 but will work for windows XP) for when the user is entering a new record.

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.NewRecord Then
    Me!Entered_By_User = Environ("UserName")  'This will get the users windows username
    Me!last_modified_By_User = Environ("UserName")  'This will get the users windows username
End If
end sub

Please note there is no dates set. this is because I have set the dates fields default value to Now()

Then to make it log when the user edits a record, you have an on keypress event that will call a function

Here is what I have in mine:

Function Backup_Log_Stamp()

If Previous_Time_Saved = False Then
Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Previous_modification_time = Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Date_last_modified
Previous_Time_Saved = True
End If


Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!last_modified_By_User = Environ("UserName")  'This will get the users windows username
Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Date_last_modified = Date & " " & Time
End Function

the "Previous_Time_Saved" is a global variable for Making sure when you keep editing a record the Previous_modification_time does not change until you exit the record.

And then whenever the user has changed a record (test this with an if statement or just call the function from a Key press event, like this

Call Backup_Log_Stamp

Finaly if you only want to have the date last modified for the whole table just make a query with a Max equation in it.

Does this help? Please respond

 

[eggy168]

Sorry smercer, i didn't know this is today's thread. i sent you an email regarding this thread.

Anyhow, the Backup_Log_Stamp() is not quite working when I test it in my database. The Previous_Modification_Time and the Date_Last_Modified are not changing although I edited the record and exit the whole database.
Any Idea

Also, did you declare the Previous_Time_Saved as an Integer or any other data type?

FYI, I put down CurrentUser() instead of Environ("UserName") and it works perfect, too

Your generous replied is such a great help

 

[eggy168]

I am sorry, it is me again.

I have to take the word back for the currentuser(). It looks like the Backup_Log_Stamp is not working totally. The Last_Modified_By_User and the Date_Last_Modified are not working. So, I don't know what's happening.

However, when I put the Function and the Call in AfterUpdate(), it worked when I run the query but the form itself is kinda frozen itself. So, I just don't know what happened.

 

[smercer]

Put an onCurrent event in and then set the "Previous_Time_Saved" to false

Sorry I just left that bit out.

If It does not work you'll have to wait till I get home tonight

 

[smercer]

Also, did you declare the Previous_Time_Saved as an Integer or any other data type?

Public Previous_Time_Saved As Boolean

 

[huangt]

it works, thanks

thanks ghudson and others, the Dlookup funciton resolve the problem very simple but effective.

 

[eggy168]

hmm..I don't know how to set the Previous_Saved_Time as false in the onCurrent event type.

Private Sub Form_Current()
set Previous_Saved_Time as False
End Sub

This gives me an error message.

 

[smercer]

hmm..I don't know how to set the Previous_Saved_Time as false in the onCurrent event type.

Private Sub Form_Current()
set Previous_Saved_Time as False
End Sub

This gives me an error message.

Try this:
Private Sub Form_Current()

Previous_Saved_Time = False

End Sub

Make sure that it is a public or global variable otherwise it won't work like it should.

 

[eggy168]

I think I am so stupid Even I follow the steps, it is still not working on my db. I have no idea what's wrong. The time doesn't changed. No idea


Option Compare Database
Public Previous_Time_Saved As Boolean


Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me!Entered_By_User = CurrentUser
Me!Last_Modified_By_User = CurrentUser
End If

End Sub

Private Sub Form_Current()
Previous_Time_Saved = False
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
Call Backup_Log_Stamp
Function Backup_Log_Stamp()
If Previous_Time_Saved = False Then
Forms!frmBill!Previous_Modification_time = Forms!frmBill!Date_Last_Modified
Previous_Time_Saved = True
End If

Forms!frmBill!Last_Modified_By_User = CurrentUser
Forms!frmBill!Date_Last_Modified = Date & " " & Time
End Function

 

[smercer]

See attachment

 

[smercer]

Private Sub Form_KeyPress(KeyAscii As Integer)
Call Backup_Log_Stamp
Function Backup_Log_Stamp()
If Previous_Time_Saved = False Then
Forms!frmBill!Previous_Modification_time = Forms!frmBill!Date_Last_Modified
Previous_Time_Saved = True
End If

Forms!frmBill!Last_Modified_By_User = CurrentUser
Forms!frmBill!Date_Last_Modified = Date & " " & Time
End Function

Two other things you are doing wrong: the first is you are missing a "end sub" after you call the Backup_Log_Stamp line.

The other thing you are doing wrong is that the function must be in a module, which can be in the same module as the public variable "Previous_Time_Saved", but if you have it like this make sure the variable is before the function.

 

[eggy168]

I really don't know what's wrong of me. I tried everything you said and corrected the mistaked. ~~I moved the variable, Previous_Saved_Time and declared it as Public exactly like the gif you attached into a Module. Then I moved the function to the same module underneath the variable. I don't have the "End Sub" missing in this module because it is not "sub", so all I need is the "End Function" to conclude in this module. I double check the form itself, I don't see anything wrong. However, it is still not working. I really have no clue. My question now is it because my db is using Office 2000 in Win Pro 2000 which I don't think it is a matter, right?

In fact, I found another way to have the similar result by adding a command button. The disappointed comment of this is if user(s) doesn't click the button, there is no way to save the information.
If you are interesting, I am sharing the coding, here it is.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Previous_Modification_Time = Me.Date_Last_Modified
Me!Entered_By_User = CurrentUser
Me!Last_Modified_By_User = CurrentUser
Me.Date_Last_Modified = Now()
End If
End Sub

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command18_Click:
Exit Sub

Err_Command18_Click:
MsgBox Err.Description
Resume Exit_Command18_Click

End Sub

I don't know am I too stubborn or not, but I really want to try other's coding since I am still thinking your code is the best in my case. Plus, I really want to learn something new
Thanks

 

[smercer]

I really don't know what's wrong of me. I tried everything you said and corrected the mistaked. ~~

The other thing I have not told you (and I did not think it was nesercery) is that I have for each text box or combo box a keypress event call a function which in turn would call the Backup_Log_stamp function.

At the time I thought that having a keypress on the form would do all combo boxes and text boxes.

Well now I think it would be better if you just had a keypress for every text box or combo box or what ever other event (onClick for option buttons and tick boxes) that changes your record to have the line

call Backup_Log_stamp

Hope this works, if it does not I can email you. PM me your email address.

 

[Rich]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then


you don't need the If Me.Dirty statement, the Before Update event will only fire when the record's dirty anyway