Tracking Record Activities into a table

[roeti]

Hi there,

I am trying to create a recordlog table that will track record activities in my database (adding, modifying, etc.. of records) but can't seem to work my head around it.

I have created a recordlog table that was meant to be populated whenever a record activity on my desired form which I cannot currently

Below is what I have so far from online guides which i searched

The form which I want to log record activities from

Option Compare Database
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    ModBy = Environ("Username")
    ModDate = Now()
 
End Sub
 
Private Sub Form_Current()
If Me.NewRecord Then
    CreatedBy = Environ("Username")
End If
End Sub

I am totally new to access so please kindly assist

Thanks in advance

[MikeTheBike]

Hi

Not sure what the problem you are having is, but I think this

If Me.NewRecord Then
11.     CreatedBy = Environ("Username")
12. End If

should be in the Form_BeforeUpda te event.

Does that help?

MTB

[NeoPa]

My first, and probably most important, piece of advice must be Require Variable Declaration.

After that, I would say that you're probably better off using SQL for adding new records to a separate table when you're logging updates. It can also be done using Recordsets (EG Access VBA DAO Recordset Loop Using Two Recordsets) but that's another question.

Notice in the code below how the SQL is formed first; How it's invoked; Where it's invoked from. All important to understand if you're to create a system that actually works. Also, you have nothing in your code that includes the table name of where you're hoping to write this data. That's a strong clue the code won't work. In my suggested code I'll use the name [tblLog].

Option Compare Database
Option explicit
 
Private Sub Form_AfterUpdate()
    Dim strSQL As String
    Dim dbVar As DAO.Database
 
    strSQL = "INSERT INTO [tblLog]([%F],[ModDate]) VALUES '%U',#%D#"
    strSQL = Replace(strSQL, "%F", IIf(Me.NewRecord, "CreatedBy", "ModBy"))
    strSQL = Replace(strSQL, "%U", Environ("USERNAME"))
    strSQL = Replace(strSQL, "%D", Format(Now(), "yyyy\-m\-d HH:nn:ss"))
    Set dbVar = CurrentDb()
    Call dbVar.Execute(strSQL, dbFailOnError)
End Sub

NB. While Environ("USERNA ME") will normally give you an accurate reading of who's logged on, it can easily be hacked so shouldn't be used where security or accurate logging is required. A far more reliable approach can be found at Function to Return UserName (NT Login) of Current User.

Also, see Literal DateTimes and Their Delimiters (#) for why simply using Now() in a SQL string is not a good idea.