By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Tracking Record Activities into a table

P: 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.

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
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
Expert Mod 15k+
P: 31,418
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.