Date and time stamping

[mattpearcey]

Is it possible to introduce an automatic date and time stamp by the records each time the database has been used or amended? I am using Access 97 so i am not sure if this can be done? And how easy it is, Bearing in mind i am not an advanced programmer?

Help gratefully recieved and many thanks to any of you that do?!

Kind regards

Matt

 

[ghubbell]

Hi Matt,
Make a table and call it "tblLastUse". 1 field: Date/time.
Make an Update query: "qryLastUse". This query should contain your new table, and you'll have to drag or double-click the field you made to the "QBE" grid. If you have the query set to Update (from the toolbar-query types button), you'll see another row that says "Update to". Type in:
Now()

Your Db must open on to a form of some sort (a switchboard or "main" form. Open this form in design view and double-click on the black square top left corner of the view. This should popup the forms properties dialog. Find the events tab, and find the event called "On Open". Click in its "field" and select "Event Procedure". Notice the ellipse "..." button to the right. Click on this and Visual Basic will open and automatically label up a "sub" for this event. Within the sub add something like:

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryLastUse"
DoCmd.SetWarnings True

Close and save.
If we did this right, every time your "main form" is opened, we will run an update query (without warning), and this query should update your table with the current time (Now()). This is a table that will only store 1 record but it should always be the last time. Enjoy!
Gord
ghubbell@total.net

 

[mattpearcey]

thanks gord

Im working on it now. is it easy for me then to run reports on this to show frequency of update, who has updated (that is when i figure out how to set up the users, etc), what they have updated, and so on?

your views?

Thanks again Gord

Is there anything you dont know about access?

 

[MichaelRed]

MattPearcey,

this may be overkill for what you want, but look at the FAQ181-291 It will show a way to record much more than when the db was used.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[mattpearcey]

Michael

Ive taken a look at it doe sound great, but at the moment, i want to walk before i can run. But something that i will have a go at doing once i have got more to grips with things. Thanks.

Gord

I have tried your code, and as far as i can see i am doing everything correct. But...i am opening the DB and then playing around, then checking to see if this has been logged in the table, and nothing is there. Am i being stupid or have i donesomething wrong? As for the table, do i need to set it as date/time type? as a primary key? or add an id number as a primary key? why wont it work?

Cheers

Matt

 

[Ninabunny]

Try setting a Time/Date stamp on each record and User box first then in each form put this

Private Sub Form_BeforeInsert(Cancel As Integer)

On Error GoTo Err_Form_BeforeInsert
' registers current timestamp
Me.TimeStamp = Now()
Me.User = CurrentUser()
Exit_Form_BeforeInsert:
Exit Sub

Err_Form_BeforeInsert:
MsgBox Err.Description
Resume Exit_Form_BeforeInsert
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

'registers current timestamp and user
Me.TimeStamp = Now()
Me.User = CurrentUser()
Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate
End Sub

That shows the time/date stamp and User that is using the Database if you want to track when it changes who/when etc. you need to set up an append query to a table like this:

Private Sub Form_AfterUpdate()

On Error GoTo Err_Form_AfterUpdate

'if global variables equal a space then it is a new
'record and exits this procedure
If gvaddress = "" And _
gvcity = "" And _
gvstate = "" And _
gvcountry = "" And _
gvzip = "" Then
Exit Sub
'If variables equal each other, or hold the same value
'the procedure ends but if any of these values is not equal
'to the other it will call the Function Appendme()

ElseIf Me.PId = gvPId And _
Me.Address = gvaddress And _
Me.City = gvcity And _
Me.State = gvstate And _
Me.Country = gvcountry And _
Me.Zip = gvzip Then

Else
'function that is in the Function module

Call Appendme
End If
If gvaddress = "" And _
gvcity = "" And _
gvstate = "" And _
gvcountry = "" And _
gvzip = "" Then
MsgBox "Please enter appropriate values to this record "
Me.SSN.SetFocus
End If
Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate



End Sub
'Basically the above fields, whatever you called them have to match and if they don't it will call Append me which is the append query. This query needs to set to find functions because it cannot just be instructed through code.
I am pasting a part of it so you can see how it works.

Option Compare Database
Public gvaddress As String
Public gvcity As String
Public gvstate As String
Public gvcountry As String
Public gvzip As String
Public gvPId As Integer
Public gvInvAdd As String
Public gvFName As String
Public gvLname As String

Public Function Appendme() As Boolean
'Appendme = False
'docmd.setwarnings false
DoCmd.OpenQuery "qryAppend"
DoCmd.SetWarnings True
'Appendme = True
End Function


Public Function fungvaddress()
fungvaddress = gvaddress
End Function

 

[ghubbell]

Matt: Comment ( ' ) the setwarnings lines and open your form. See if the query is actually running, and that it is actually updating. You may need to type in the first record manually to get it going. Gord
ghubbell@total.net

 

[mattpearcey]

gord

got it going - all it needed was a little kickstart! Thanks! anytime you can, if you could put the previous comments made above, on how to make it more indepth to look at eahc form/record and when that has been modified, in the same language as before - then that would be great. although my coding is ok now, i still dont think that i could produce the good when it comes to entering what Nina and Micael have given me. thanks again

Matt