access 16, audit trail table (1 Viewer)

akika · May 12, 2019

hi all,

i need help on attached audit trail.
ive created a form and added an audit trail module and audit table.

In the audit table its recording the new and deleted value in field action.
Howver, when editing the value, field action is blank.

Can u pls check where i went wrong in the code?

and how can i amend the code to include the emplCode field being amended in the audit trail table?

thxs

theDBguy · May 12, 2019

Hi. Here's the section of your code that updates the Audit Trail table when the user "edits" data:

Code:

With rst
.AddNew
![FormName] = Screen.ActiveForm.Name
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
![UserID] = strUserID
![DateTime] = datTimeCheck
.Update
End With

And here's the section of your code when the user "adds" a new record:

Code:

With rst
.AddNew
![DateTime] = datTimeCheck
![UserID] = strUserID
![FormName] = Screen.ActiveForm.Name
[COLOR=Red][B]![Action] = UserAction[/B][/COLOR]
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
.Update
End With

So, the reason why you don't see "EDIT" in the Audit Table is because you are missing the "bolded" line above in your "edit" code. Hope it helps...

akika · May 12, 2019

yeah that's right. didnt notice it..

It's working now

thanks a lot!!

Other than recordID, how can i amend the code to include the emplCode field being amended in the audit trail table?
or can i shall i concatenate the employeeCode with the record ID?

theDBguy · May 12, 2019

akika said:
yeah that's right. didnt notice it..
It's working now thanks a lot!!

Other than recordID, how can i amend the code to include the emplCode field being amended in the audit trail table?
or can i shall i concatenate the employeeCode with the record ID?

Hi. You’re welcome. Not in front of a computer now but I’ll take another look later and let you know.

theDBguy · May 12, 2019

akika said:
Other than recordID, how can i amend the code to include the emplCode field being amended in the audit trail table?
or can i shall i concatenate the employeeCode with the record ID?

Okay, looking back at your code, I'll have to ask, why would you need the emplCode too if you already have the recordID? Having the recordID alone should be enough to find out what is the emplCode, if you need it later on. Otherwise, you will have to add another field to your audit table to store the emplCode and then try something like this:

Code:

![EmplCode] = Screen.ActiveForm.Controls(EmplCode).Value

Hope it helps...

CJ_London · May 13, 2019

unless you have dimmed EmplCode as a string and assigned a value then

Screen.ActiveForm.Controls(EmplCode).Value

won't work. it needs to be

Screen.ActiveForm.Controls("EmplCode").Value

akika · May 13, 2019

Thanks a lot theDBguy & CJ_London for ur help

Its working wz:
Screen.ActiveForm.Controls("EmplCode").Value

theDBguy · May 13, 2019

akika said:
Thanks a lot theDBguy & CJ_London for ur help
Its working wz:
Screen.ActiveForm.Controls("EmplCode").Value

Excellent! Glad to hear you got it to work. Good luck with your project. Thanks CJ for the assist!

akika · May 13, 2019

One more help pls

after add the code, the delete audit not working as expected.
the record ID and Empl Code is BLANK

Action is set to DELETE,
ChangeID, DateTime, UserID, FormName are updated with values.

but not recordID and EmplCode
Any idea?

CJ_London · May 14, 2019

depends when you are running the code. Looking back through the thread, I don't see anything to indicate when you are.

With regards delete - usually better to have a deleted flag of some sort. I typically use a 'datedeleted' flag. Benefits are the data can be recovered if deleted in error and also perhaps prevent the same data being added again in error.

akika · May 14, 2019

I had added:
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("ID", "DELETE")

should code be changed for delete?

CJ_London · May 14, 2019

no idea - see this link on the afterdelconfirm event

https://docs.microsoft.com/en-us/office/vba/api/access.form.afterdelconfirm(even)

looks to me like the event is too late

however you are passing a string "ID" which does not make any sense to me

Suggest step through the code and see what you get

theDBguy · May 14, 2019

akika said:
One more help pls
after add the code, the delete audit not working as expected.
the record ID and Empl Code is BLANK

Action is set to DELETE,
ChangeID, DateTime, UserID, FormName are updated with values.

but not recordID and EmplCode
Any idea?

Hi. Can you post the updated version of your db, so we can take a look inside? Thanks.

akika · May 15, 2019

hi theDBguy,
Latest DB attached.
Pls advise.

theDBguy · May 15, 2019

akika said:
hi theDBguy,
Latest DB attached.
Pls advise.

Hi. Thanks. The reason why your fields are empty is because you're using the wrong event to log the record delete. Try using the On Delete event instead.

akika · May 15, 2019

DBGuy, yes ur right
it's ok with ondelete

THANKS a lot for your help

theDBguy · May 15, 2019

akika said:
DBGuy, yes ur right
it's ok with ondelete

THANKS a lot for your help

Hi. You're welcome. Glad we could assist. Good luck!

akika · May 20, 2019

hi,
the delete audit trail log is quite weird.
at times its populating the ID and code
action = delete
and at times it's only action = delete other remain blank
or even not adding any record in the log table

:banghead:

any idea

theDBguy · May 20, 2019

akika said:
hi,
the delete audit trail log is quite weird.
at times its populating the ID and code
action = delete
and at times it's only action = delete other remain blank
or even not adding any record in the log table

:banghead:

any idea

Do you notice any pattern?

akika · May 20, 2019

well i dont find a specific pattern.

If i comment AfterDelConfirm code, then no record for the delete action are log in the audit tbl

If i comment ONDelete code,
then action = delete is added in audit tbl , but other fields ID, code are blank in the audit tbl

If i uncomment both code,
then out of few test, the action, ID, code might be populated. Im not able to fully replicate that part :banghead:

Ive attach the DB...

can u pls help

access 16, audit trail table (1 Viewer)

Registered User.

Attachments

I’m here to help

Registered User.

I’m here to help

I’m here to help

Super Moderator

Registered User.

I’m here to help

Registered User.

Super Moderator

Registered User.

Super Moderator

I’m here to help

Registered User.

Attachments

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

Similar threads

Users who are viewing this thread