473,320 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2219
MikeTheBike
639 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,556 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

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

Similar topics

12
by: Dan Greenblatt | last post by:
I am writing some software that, among other things, needs to track the state of database tables. This includes occasionally checking the table to see what records or added, modified, or deleted....
5
by: byrocat | last post by:
Sybase and DB2 both have the capability of tracking user activities at a number of levels: invalid access attempts to databases, table, etc.; creation/deletion/modification of database...
0
by: Mark | last post by:
All, Excuse the re-post but I have found something which works for the UserID but not for the Password (see previous post below). The problem is I don't understand how it works and therefore...
1
by: gaurkamal | last post by:
I want to delete record in table B when i delete record in Table A both table have a common column. I want to do it using trigger .Can any body give some idea. Table details are. Table A: ...
1
by: Becky99 | last post by:
In the database I'm working on (2003) I have a client table that includes a client status field, and I have a linked notes table that includes the note entry date, note type, and note details. Each...
5
by: saga git | last post by:
how to display nth record from table
3
by: debo sniffa | last post by:
I want to add a value from a record in Table A to a record in table B if the value is same as 2 other fields in the Table A. How could i get this done in access?? Pls help me... ;-)
0
by: Neven Huynh | last post by:
Hi Everyone, Here i my LINQ query to get record in Table Menu with condition are parentID == 0(get root menu) and ID != (parentID list) (which is parent ID list is are id of menu record that have...
0
by: akhilesh56 | last post by:
hello i want add 1000 above record in table lookup row source section but only 200 something record added. how increase row source limitation
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.