UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Log User When New Record Added To Table, Access 2013    
 
   
cwua
post Aug 9 2019, 05:23 PM
Post#1



Posts: 566
Joined: 12-November 12
From: Beaconsfield, England


Every now and then we have blank or corrupt records added to our tblClients and I don't know if it is user error or a glitch in Access or my database design.
For starters, I'd like to know which users are creating these. Well, all new records for that matter, not just the blanks.
Often the only data added is the ClientID, which is an Autonumber.
I do store the logged on user within the database so there's no problem identifying that, but I need help with the code to gather the ClientID and date/time when it was added.
Many thanks!
CW

--------------------
I always try to think things through and it is often rather trying
Go to the top of the page
 
theDBguy
post Aug 9 2019, 05:53 PM
Post#2


UA Moderator
Posts: 77,304
Joined: 19-June 07
From: SunnySandyEggo


Hi. Have you tried looking in the Code Archive for some examples of an "audit trail?"

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cwua
post Aug 9 2019, 06:04 PM
Post#3



Posts: 566
Joined: 12-November 12
From: Beaconsfield, England


Yes, I have, and I only found ways of tracking edits and deletions, not new records.
Thanks
CW

--------------------
I always try to think things through and it is often rather trying
Go to the top of the page
 
June7
post Aug 9 2019, 06:06 PM
Post#4



Posts: 1,153
Joined: 25-January 16
From: The Great Land


Review http://allenbrowne.com/AppAudit.html

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
cwua
post Aug 9 2019, 06:21 PM
Post#5



Posts: 566
Joined: 12-November 12
From: Beaconsfield, England


Thank you June7, that looks very useful - and given that it is from Allen Browne I have no doubts whatsoever that it will work.
Many thanks for pointing me in that direction
CW

--------------------
I always try to think things through and it is often rather trying
Go to the top of the page
 
dale.fye
post Aug 10 2019, 02:46 PM
Post#6



Posts: 162
Joined: 28-March 18
From: Virginia


If the only way to add a record to that table is to use a form, then use the Form_BeforeUpdate event.

I usually use that event to ensure that all required fields have values. If not, I cancel the event.
If so, then I check to see whether it is a new record or an old record, and I have fields for CreatedBy, CreatedDT, ModifiedBy and ModifiedDT. This lets me do something like:

CODE
Private Sub Form_BeforeUpdate(Cancel as Integer)

    Cancel = NOT PassesChecks   '<= this is the function I call to determine whether all required fields have values
    if Cancel then Exit Sub

    if me.NewRecord then
        me!CreatedBy = fOSUserName
        me!CreatedDT = Now()
    Else
        me!ModifiedBy = fOSUserName
        me!ModifiedDT = Now()
    End IF

End Sub

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
cwua
post Aug 12 2019, 03:31 AM
Post#7



Posts: 566
Joined: 12-November 12
From: Beaconsfield, England


Thank you Dale, I will give that a try (and yes the only way for the users to add a new record is via a form, so this should trap anyone who is starting to add one and then backs out for some reason).
Thanks
CW

--------------------
I always try to think things through and it is often rather trying
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 06:20 PM