Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004

    Unanswered: how to know who last modified this record

    I checked the forum and only found topics that how to know who are logging in the database. But for security reason, all I want is to know who last modified this record.
    For example: I have a "customer" form, and I want to put a text box name 'lastmodifiedby' in that form and set it's control source to 'lastmodifiedby' filed in the table "customer". on after update event of the form, I will put some code so that when someone modified data on the form, the 'lastmodifiedby' text box will display network username.
    Could anyone help me on this problem or give me the address of place where allowed to download this code freely
    many thanks.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    First off you cannot allow users to view data in a table or query, all data manipulation (adding, editing & deleting) must be done in a form

    you can retrieve the userid of the netork logon usign an API call, the same API library can also give you the PC ID and users name. See Dev Arish's API Calls on

    you need to insert some code into each forms afterupdate event.

    Hwoever if you really want to know what is going on the you eed an audit trail, where you write every change so that you know who did the dirty deed. After all if all you have is the last person who edited the record that may not be the person who made a critical change. Try searching on this site for a audit log

  3. #3
    Join Date
    May 2004
    many thanks

  4. #4
    Join Date
    Dec 2004
    Madison, WI


    If you're using SQL Server, you might find this helpful. It's an update Trigger which records into another table if some key fields are updated.

    CREATE TRIGGER [WECCMeasures1_Update] ON dbo.WECCMeasures
    After Update -- For Update
    declare @NewAC varchar(15), @OldAC varchar(15), @MP varchar(50), @BMT varchar(50), @MeasureID int, @MID int, @OPD datetime, @NPD datetime, @OCN int, @NCN int, @ORS varchar(50), @NRS varchar(50), @ORD datetime, @NRD datetime, @RA money
    If Update (AccountingCode) or update(CheckNumber) or update(DatePaid) or update(ReconcileStatus) or update(ReconcileDate)
    select @OldAC = AccountingCode from deleted
    select @NewAC = AccountingCode from inserted
    select @MeasureID = MeasureID from deleted
    select @OCN = CheckNumber from deleted
    select @NCN = CheckNumber from inserted
    select @OPD = DatePaid from Deleted
    select @NPD = DatePaid from inserted
    select @ORS = ReconcileStatus from deleted
    select @NRS = ReconcileStatus from inserted
    select @ORD = ReconcileDate from deleted
    select @NRD = ReconcileDate from inserted
    select @MP = MeasureProgram from deleted
    select @BMT = BillMeasureTo from deleted
    select @RA = RewardAmount from deleted

    if @OldAC <> "" or @OCN <> "" or @OPD <> "" or @ORS = "Cleared" or @ORS = "Void" or @ORD <> ""
    INSERT INTO WECCMeasuresChangeLog (MeasureID, MeasureProgram, BillMeasureTo, OldAccountingCode,NewAccountingCode, OldCheckNumber, NewCheckNumber, OldDatePaid, NewDatePaid, OldReconcileStatus, NewReconcileStatus, OldReconcileDate, NewReconcileDate, RewardAmount)
    Select @MeasureID, @MP, @BMT, @OldAC, @NewAC, @OCN, @NCN, @OPD, @NPD, @ORS, @NRS, @ORD, @NRD, @RA

    Otherwise, we utilize a lastmodifiedDate and lastModifiedBy on the forms. If something is changed on the form, we write to these fields (ie. the field: we set the field LastModifiedDate = date() and the LastModifiedBy to =getuser() where we have a function like (for the getuser):
    Function GetUser() As String
    Dim strSQL As String
    '** Procedure to Get the User's Name from the Windows Login
    Dim si As SystemInfo
    Set si = New SystemInfo
    Dim strOut As String
    'strGetUser = si.UserName
    'strOut = si.UserName & " is logged into " & si.ComputerName
    GetUser = si.UserName
    If GetUser = "" Then
    MsgBox ("There is a problem with your Network Login Name!! Please contact your Network Administrator.")
    End If
    End Function

    Since we use unbound forms, these are written in the Update function we have to update the records on what is in the form.

    Hope you find any of this helpful. Otherwise others might have some better ideas for you.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2004
    I'm has been learning sql server 2000 for just 2 weeks so I don't know much about this. At the moment I think I will use access. I will use sql server in the future.
    thanks anyway

Posting Permissions

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