Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Date stamp (2000)

    Hi,
    I'm currently tweaking an old database that has been completed. It now requires an audit trail of who completed what work and when. The process is that a person enters a payment and is then made to enter their name to record that they authorised that payment. However there is no reference of a date in relation to the payment.
    My question is, does Access have an internal 'time/date' stamp that I could draw every time a specific field is altered?
    Reports are then produced (hopefully in date order!) that details, basically, who's working and who's not on a specific date.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Date stamp (2000)

    You already asked something similar in <post#=404420>post 404420</post#>. Were the replies and links provided there unsatisfactory?

    I once posted some example code for a simplistic audit trail - see <post#=320697>post 320697</post#>. You may be able to adapt it to your situation.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Date stamp (2000)

    Thanks Hans. My previous post on this subject left me in a bit of a quandry as to how to proceed.
    As WendellB suggested in <post#=408715>post 408715</post#> I seem to have a bit of a problem with security over a networked system. I'd therefore put this on the backburner to tackle at a later date.
    I have been perusing the web and have found this that has an example of creating a login form. Maybe this is the angle that I should approach from. As opposed to trying to set up unstable security through a networked system.
    My post was a query as to whether Access can automatically record a date that a record is altered presumably with something similar to <post#= 320697>post 320697</post#> which you have kindly posted. Essentially I was hoping to avoid having to create a user log in form as I supsect that I may get complaints from the users. All I'm literally after (now anyway) is for the user to record their name next to the change (which is already set up) and for that to be recorded behind the scenes. Or at least to be ordered by the date of change in the query.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Date stamp (2000)

    Access does not have any built in audit facilities. The only way to keep track of changes in records is to write your own code, such as the example I posted a link to. And although user-level security is very useful, you don't have to use it if you don't want to - you can store the name under which the user logged in to the computer.

    Microsoft SQL Server does have built-in facilities that enable tracking changes at the table level, so if you would switch to a SQL Server backend with an Access frontend, you could use that.

    In most of my databases, I don't need to keep track of all changes, but I routinely add a LastModified date/time field and a ModifiedBy text field to tables. In the form(s) used to enter/edit records, I use the Before Update event of the form to set LastModified to Now and ModifiedBy to either CurrentUser (for databases with user-level security) or the network login name of the user (for unprotected databases). That way, I can always see when a record was last modified and by whom. (End users never edit tables directly.)

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Date stamp (2000)

    Sorry, couple of questions:
    1)Presumably the Before Update event of the form will only update a record if data is actually changed, or will this also include data that is viewed.
    2) How do I retrieve the network login name of the user who is altering a record?

    Thanks for your patience.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Date stamp (2000)

    1) The Before Update and After Update events of a form only occur if the user has changed the record, not if (s)he has only viewed it. So the log table would only record the date/time and username after a record has been modified. If you also want to keep track of who views a record, you could use the On Current event of the form, but I fear that could get out of hand quickly - it would generate large numbers of log records.

    2) Near the bottom of the post I referred to earlier (<post#=320697>post 320697</post#>), there is code for a function to retrieve the network login name; the post also describes how to use it instead of CurrentUser in the audit code.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Date stamp (2000)

    Yet again you have solved my problems!!!! I have implemented your coding into my form and it does precisely what I need it to do. Effectively record the usage of the database without the user knowing it!
    Thank you for your patience and knowledge. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Date stamp (2000)

    Hmm, I've placed all the coding in the form and all works okay, accept when an entry is entered or amended it display this error message.
    Not too sure what this means?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Date stamp (2000)

    There is probably a typo in one of the field or table names in your code. Check carefully that you have replaced all names from the code I posted with the names you are using.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Date stamp (2000)

    Good morning Hans,
    I've thoroughly checked all the field names, table names etc but can find no errors.
    Username is the field (text) in TblPayments. I also have a field DateTime, formated as Date/Time with default value as Now().
    TblPayments is updated through a form FrmPayments , which is a sub form to FrmMain .



    This is the relevant coding that I have currently in the form. The coding does actually work and displays the user name in the relevant field but I just get this annoying pop-up error everytime the record that has been updated is exited.

    I do not have the ID (autonumber), Action or ObjectName fields in TblPayments (in regards to post <post#=320697>post 320697</post#>). TblPayments already has a unique ID for each record and I solely wish to retrieve the NetUser name. Is this correct?

    <font face="Georgia">Option Compare Database
    Option Explicit
    Dim fOKToClose As Boolean

    Private Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _
    (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

    Public Function NetUser() As String
    Dim strName As String, strUserName As String
    strName = vbNullString
    strUserName = Space(255)
    If WNetGetUser(strName, strUserName, Len(strUserName)) = 0 Then
    NetUser = left(strUserName, InStr(strUserName, vbNullChar) - 1)
    Else
    NetUser = "-unknown-"
    End If
    End Function

    Private Sub Form_AfterUpdate()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "TblPayments", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!ObjectName = Me.UserName
    'rst!Action = "M"
    rst!UserName = NetUser
    rst.Update

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Set Date & User

    DateTime = Now()
    Me.UserName = NetUser


    On Error Resume Next
    Me.Filter = "[Archive] = False"
    Me.FilterOn = True
    End Sub</font face=georgia>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Date stamp (2000)

    You wanted to create an audit trail. You need a separate log table for that, but you seem to use the same table that acts as record source of the form. That is not correct, for then you won't be able to trace every modification. Please read the post I referred to earlier carefully.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •