469,138 Members | 1,297 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Tracking Record Activities into a table

3
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_BeforeUpdate(Cancel As Integer)
  4.     ModBy = Environ("Username")
  5.     ModDate = Now()
  6.  
  7. End Sub
  8.  
  9. Private Sub Form_Current()
  10. If Me.NewRecord Then
  11.     CreatedBy = Environ("Username")
  12. End If
  13. End Sub
I am totally new to access so please kindly assist

Thanks in advance
Oct 19 '17 #1

✓ answered by 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].
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option explicit
  3.  
  4. Private Sub Form_AfterUpdate()
  5.     Dim strSQL As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "INSERT INTO [tblLog]([%F],[ModDate]) VALUES '%U',#%D#"
  9.     strSQL = Replace(strSQL, "%F", IIf(Me.NewRecord, "CreatedBy", "ModBy"))
  10.     strSQL = Replace(strSQL, "%U", Environ("USERNAME"))
  11.     strSQL = Replace(strSQL, "%D", Format(Now(), "yyyy\-m\-d HH:nn:ss"))
  12.     Set dbVar = CurrentDb()
  13.     Call dbVar.Execute(strSQL, dbFailOnError)
  14. End Sub
NB. While Environ("USERNAME") 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.

2 1899
MikeTheBike
637 Expert 512MB
Hi

Not sure what the problem you are having is, but I think this
Expand|Select|Wrap|Line Numbers
  1. If Me.NewRecord Then
  2. 11.     CreatedBy = Environ("Username")
  3. 12. End If
should be in the Form_BeforeUpdate event.

Does that help?

MTB
Oct 20 '17 #2
NeoPa
32,164 Expert Mod 16PB
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].
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option explicit
  3.  
  4. Private Sub Form_AfterUpdate()
  5.     Dim strSQL As String
  6.     Dim dbVar As DAO.Database
  7.  
  8.     strSQL = "INSERT INTO [tblLog]([%F],[ModDate]) VALUES '%U',#%D#"
  9.     strSQL = Replace(strSQL, "%F", IIf(Me.NewRecord, "CreatedBy", "ModBy"))
  10.     strSQL = Replace(strSQL, "%U", Environ("USERNAME"))
  11.     strSQL = Replace(strSQL, "%D", Format(Now(), "yyyy\-m\-d HH:nn:ss"))
  12.     Set dbVar = CurrentDb()
  13.     Call dbVar.Execute(strSQL, dbFailOnError)
  14. End Sub
NB. While Environ("USERNAME") 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.
Oct 30 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Dan Greenblatt | last post: by
5 posts views Thread by byrocat | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.