audit trail

[jdraw]

LilyK,

Please post a copy of your database (zip format) and some details about what you were trying to do.
Some specifics of the record in question would be helpful.
We do not need your entire database necessarily --only enough records to show the issue.

 

[LilyK]

LilyK,

Please post a copy of your database (zip format) and some details about what you were trying to do.
Some specifics of the record in question would be helpful.
We do not need your entire database necessarily --only enough records to show the issue.

Here is the copy of my database ..

1. I can't delete records in my subform where the error occurred Run-time error "13": Type mismatch
I don't know what is the mistake...

2. Is there any possibilities I can save the data of the deleted information like this
tutorial:

.
I tried to follow this tutorial but nothing happens to my database.

Thank you in advance

 

[jdraw]

Your error 13 Type mismatch is because you have defined gRecID as Integer in modAudit

Option Compare Database
Option Explicit

Public gRecID As Integer
Public show As Boolean....

but in your table tbl_RecordsData Reference_number is defined as short text.

That is the cause of error 13.
You should also be aware that Date and Time are reserved words in Access and may lead to syntax issues.
Most developers would advise you to use autonumber Primary Keys in each/all of your tables.

**Also, that video is not one I have used. It may be based on the original Martin Green material and/or the revisions by others, but not me. I have not watched the entire video since it was not part of the materials discussed earlier in the thread with PSSMargaret. If you read the thread from the start, you'll notice that deletes and subforms were a major issue with the original Martin Green Audit routine.

Good luck with your project.

 

[LilyK]

Your error 13 Type mismatch is because you have defined gRecID as Integer in modAudit

Option Compare Database
Option Explicit

Public gRecID As Integer
Public show As Boolean....

but in your table tbl_RecordsData Reference_number is defined as short text.
View attachment 89327
That is the cause of error 13.
You should also be aware that Date and Time are reserved words in Access and may lead to syntax issues.
Most developers would advise you to use autonumber Primary Keys in each/all of your tables.

**Also, that video is not one I have used. It may be based on the original Martin Green material and/or the revisions by others, but not me. I have not watched the entire video since it was not part of the materials discussed earlier in the thread with PSSMargaret. If you read the thread from the start, you'll notice that deletes and subforms were a major issue with the original Martin Green Audit routine.

Good luck with your project.

Hi @jdraw thank you so much for your help! it works

I have one more question, referring to this jpg:

Am I doing the right way of deleting the record? It is because when I click yes, it did not appeared in tempAuditRec and it is gone permanently. However, if I click no, It appeared in tempAuditRec ...

 

[jdraw]

Glad it is working!
What did you change to make it work? How about posting that database(zip)?

 

[LilyK]

Glad it is working!
What did you change to make it work? How about posting that database(zip)?

Nothing much, just edit my primary key and corrected the mistakes that you just highlighted before!

do u have any idea about my question in #64 regarding the deletion of the record?

 

[jdraw]

LilyK,

I don't understand your set up in general. I'm allowed to enter data into the subform in the very first record displayed on form RecordsData. I'm not sure how/why Date Today isn't checked or validated, but I don't know your intent.

So please provide an overview of your intended logic. I want to follow the logic the add, edit and delete some records.

When I added new records in the subform I got an error as shown below.

You should have Option Explicit as the second line in each module. This will ensure every variable name is checked before processing. Highlights many spelling errors and omitted declarations.

I'll edit the variable error and continue, but I do need a description of your processing logic to offer more suggestions.

It appears from the audit log that records are being deleted okay when I tried to delete the 2 records that I added in the subform.
tblAuditTrail tblAuditTrail

AuditTrailID	DateTime	UserName	FormName	Action	RecordID	FieldName	OldValue	NewValue
1​	18-Feb-21 6:31:22 PM​	wanii	RecordsData_subform	DELETE	ACE324	Reference_Number	ACE324
2​	18-Feb-21 6:31:22 PM​	wanii	RecordsData_subform	DELETE	ACE324	Title	letter 2
3​	18-Feb-21 6:38:36 PM​	wanii	RecordsData_subform	NEW	ACEGAYW	Reference_Number		ACEGAYW
4​	18-Feb-21 6:38:36 PM​	wanii	RecordsData_subform	NEW	ACEGAYW	Title
6​	18-Feb-21 6:38:47 PM​	wanii	RecordsData_subform	EDIT	ACE123456	Reference_Number	ACEGAYW	ACE123456
8​	18-Feb-21 6:38:54 PM​	wanii	RecordsData_subform	EDIT	ACE123456	Title		Letter 3
10​	18-Feb-21 1:23:25 PM​	Jack	RecordsData_subform	NEW	JDRAW1 test	Reference_Number		JDRAW1 test
11​	18-Feb-21 1:23:25 PM​	Jack	RecordsData_subform	NEW	JDRAW1 test	Title		test record
13​	18-Feb-21 1:25:49 PM​	Jack	RecordsData_subform	NEW	JDRAW1 test number 2.0	Reference_Number		JDRAW1 test number 2.0
14​	18-Feb-21 1:25:49 PM​	Jack	RecordsData_subform	NEW	JDRAW1 test number 2.0	Title		another teest record
16​	18-Feb-21 1:26:19 PM​	Jack	RecordsData_subform	EDIT	JDRAW1 test number 2.0	Title	another teest record	another test record
18​	18-Feb-21 1:46:14 PM​	Jack	RecordsData	NEW	18-Feb-21	DateToday		18-Feb-21
20​	18-Feb-21 6:31:57 PM​	wanii	RecordsData_subform	DELETE	ACE1234	Reference_Number	ACE1234
21​	18-Feb-21 6:31:57 PM​	wanii	RecordsData_subform	DELETE	ACE1234	Title	claim 1
22​	18-Feb-21 9:44:52 PM​	wanii	RecordsData_subform	DELETE	ACE123456	Reference_Number	ACE123456
23​	18-Feb-21 9:44:52 PM​	wanii	RecordsData_subform	DELETE	ACE123456	Title	Letter 3
24​	18-Feb-21 1:48:28 PM​	Jack	RecordsData_subform	DELETE	JDRAW1 test	Reference_Number	JDRAW1 test
25​	18-Feb-21 1:48:28 PM​	Jack	RecordsData_subform	DELETE	JDRAW1 test	Title	test record
26​	18-Feb-21 1:48:33 PM​	Jack	RecordsData_subform	DELETE	JDRAW1 test number 2.0	Reference_Number	JDRAW1 test number 2.0
27​	18-Feb-21 1:48:33 PM​	Jack	RecordsData_subform	DELETE	JDRAW1 test number 2.0	Title	another test record

 

[jdraw]

LilyK,

Seems there is an issue with formatting when I tried to edit my post?? I know there have been issues with the forum's Search feature recently, but this formatting seems bizarre.

I don't understand what the audit table is reporting when I look at the entire log.
For thoroughness you should be recording the RecordID of the record being add/edit/delete in the audit trail.

I don't understand your form/subform linkage

The log seems to reflect my test actions

tblAuditTrail tblAuditTrail

AuditTrailID	DateTime	UserName	FormName	Action	RecordID	FieldName	OldValue	NewValue
28​	18-Feb-21 2:20:22 PM​	Jack	RecordsData_subform	NEW	abc jed TEST	Reference_Number		abc jed TEST
29​	18-Feb-21 2:20:22 PM​	Jack	RecordsData_subform	NEW	abc jed TEST	Title		another test 2:20
31​	18-Feb-21 2:21:23 PM​	Jack	RecordsData_subform	NEW		Reference_Number
32​	18-Feb-21 2:21:23 PM​	Jack	RecordsData_subform	NEW		Title		another test a little later
34​	18-Feb-21 2:21:35 PM​	Jack	RecordsData_subform	NEW		Reference_Number
35​	18-Feb-21 2:21:35 PM​	Jack	RecordsData_subform	NEW		Title		another test a little later
37​	18-Feb-21 2:21:51 PM​	Jack	RecordsData_subform	NEW		Reference_Number
38​	18-Feb-21 2:21:51 PM​	Jack	RecordsData_subform	NEW		Title		another test a little later
40​	18-Feb-21 2:26:29 PM​	Jack	RecordsData_subform	NEW	xyz jed testing 2:25	Reference_Number		xyz jed testing 2:25
41​	18-Feb-21 2:26:29 PM​	Jack	RecordsData_subform	NEW	xyz jed testing 2:25	Title		test after removing Tag label
43​	18-Feb-21 2:27:11 PM​	Jack	RecordsData_subform	EDIT	xyz jed testing 2:25	Title	test after removing Tag label	test after removing Tag from label
45​	18-Feb-21 2:29:03 PM​	Jack	RecordsData_subform	DELETE	abc jed TEST	Reference_Number	abc jed TEST
46​	18-Feb-21 2:29:03 PM​	Jack	RecordsData_subform	DELETE	abc jed TEST	Title	another test 2:20

I just realized your buttons are tied to macros, not the vba code that I had written??
I'm somewhat confused since your video isn't one I have used and I don't use macros --perhaps were working in entirely different scenarios???

 

[jdraw]

Re your question in #64
This is a comment from within the audit module code
'**************************************************************************************************
'** -- N O T E -- 13-Apr-2017 --Temp audit record--
'** I have changed the DELETE to record each field's value to a temporary audit record
'** If the delete is confirmed it writes the data in temp audit record to the "real" audit log
'** If the delete is cancelled, nothing is written.
'**
'**************************************************************************************************

 

[LilyK]

LilyK,

Seems there is an issue with formatting when I tried to edit my post?? I know there have been issues with the forum's Search feature recently, but this formatting seems bizarre.

I don't understand what the audit table is reporting when I look at the entire log.
For thoroughness you should be recording the RecordID of the record being add/edit/delete in the audit trail.

I don't understand your form/subform linkage
View attachment 89340

The log seems to reflect my test actions

tblAuditTrail tblAuditTrail

AuditTrailID	DateTime	UserName	FormName	Action	RecordID	FieldName	OldValue	NewValue
28​	18-Feb-21 2:20:22 PM​	Jack	RecordsData_subform	NEW	abc jed TEST	Reference_Number		abc jed TEST
29​	18-Feb-21 2:20:22 PM​	Jack	RecordsData_subform	NEW	abc jed TEST	Title		another test 2:20
31​	18-Feb-21 2:21:23 PM​	Jack	RecordsData_subform	NEW		Reference_Number
32​	18-Feb-21 2:21:23 PM​	Jack	RecordsData_subform	NEW		Title		another test a little later
34​	18-Feb-21 2:21:35 PM​	Jack	RecordsData_subform	NEW		Reference_Number
35​	18-Feb-21 2:21:35 PM​	Jack	RecordsData_subform	NEW		Title		another test a little later
37​	18-Feb-21 2:21:51 PM​	Jack	RecordsData_subform	NEW		Reference_Number
38​	18-Feb-21 2:21:51 PM​	Jack	RecordsData_subform	NEW		Title		another test a little later
40​	18-Feb-21 2:26:29 PM​	Jack	RecordsData_subform	NEW	xyz jed testing 2:25	Reference_Number		xyz jed testing 2:25
41​	18-Feb-21 2:26:29 PM​	Jack	RecordsData_subform	NEW	xyz jed testing 2:25	Title		test after removing Tag label
43​	18-Feb-21 2:27:11 PM​	Jack	RecordsData_subform	EDIT	xyz jed testing 2:25	Title	test after removing Tag label	test after removing Tag from label
45​	18-Feb-21 2:29:03 PM​	Jack	RecordsData_subform	DELETE	abc jed TEST	Reference_Number	abc jed TEST
46​	18-Feb-21 2:29:03 PM​	Jack	RecordsData_subform	DELETE	abc jed TEST	Title	another test 2:20

I just realized your buttons are tied to macros, not the vba code that I had written??
I'm somewhat confused since your video isn't one I have used and I don't use macros --perhaps were working in entirely different scenarios???

Oh sorry, actually it's DateID. Actually, I want my main form representing data for my subform (DateToday)

It is like, want the list to be appeared according to DateToday.

Referring to the buttons, I just took them from the design tab. I thought it didn't affect much.........

and sorry for the inconvenience, I am not really familiar with access and VBA, still new

 

[jdraw]

Lily,

It appears that you have gathered code fragments from the internet without understanding the details/implications of each. For example you have macros behind the buttons on your form. The video you identified earlier does not represent the code I wrote in response to PSSMargaret.
I also noted that many of the labels on your form had their Tag set to "Audit". Tags set to "Audit" is used to identify the controls to be reflected in the AuditTrail/Log. The controls' values reflect the changes/values in the input data an are what is recorded in the log. Labels do not change, so they should not have a Tag = "Audit".

Since you are still new to database and Access and vba, I recommend you work through some tutorials and watch some of the Steve Bishop videos(or others) in the Database Planning and Design link in my signature.

It would be helpful to you and readers if you could tell us about your business. What is the purpose of your database and how does it support your business processes? Some sample transactions would also be helpful.

 

[LilyK]

Lily,

It appears that you have gathered code fragments from the internet without understanding the details/implications of each. For example you have macros behind the buttons on your form. The video you identified earlier does not represent the code I wrote in response to PSSMargaret.
I also noted that many of the labels on your form had their Tag set to "Audit". Tags set to "Audit" is used to identify the controls to be reflected in the AuditTrail/Log. The controls' values reflect the changes/values in the input data an are what is recorded in the log. Labels do not change, so they should not have a Tag = "Audit".

Since you are still new to database and Access and vba, I recommend you work through some tutorials and watch some of the Steve Bishop videos(or others) in the Database Planning and Design link in my signature.

It would be helpful to you and readers if you could tell us about your business. What is the purpose of your database and how does it support your business processes? Some sample transactions would also be helpful.

Noted, thank you for the explanation. Much appreciated.
I will work on it.
My purposes are :
1. To store all the business transaction in-out records in the database.
2. I want it to be easy for tracking records in the fastest way.

For example:
On 3/1/2021:
Mr. A has received an Invoice from his client, company ABC. In order to record that transaction between Mr. A and his client, that is the reason why the database was made. Easier for tracking on 3/1/2021 what the transaction has been made during that day in case if I want to look at it in the future.

Sorry if my question might be weird. Where is the correct place that I should put the tag "Audit". I can't find the word control in the property sheet...

 

[jdraw]

Thanks for the info. But where do Client and Invoice and Company etc fit in your database?
These "things/entities" do not appear in your relationships window. Nor are they identified in your tables.

I have modified the code in the database you attached in #70.
I added a sub to review and remove any "Audit" tags assigned to _Labels.
I also modified the code in your subform events to place the ReferenceID in the Audit table.
And the code in RecordsData form events to use DateID instead of DateToday.
These changes identify which records were added/modified /deleted.

Here is a revised database.

 

[LilyK]

Thanks for the info. But where do Client and Invoice and Company etc fit in your database?
These "things/entities" do not appear in your relationships window. Nor are they identified in your tables.

I have modified the code in the database you attached in #70.
I added a sub to review and remove any "Audit" tags assigned to _Labels.
I also modified the code in your subform events to place the ReferenceID in the Audit table.
And the code in RecordsData form events to use DateID instead of DateToday.
These changes identify which records were added/modified /deleted.

Here is a revised database.

The entities are with me, I do it already and still work on it.
I already try the revised version and it works well

Thank you so much @jdraw for your time and big help! You're so cool.
I will try my best on learning more about Ms Access.

Thank you and Have a nice day!

 

[jdraw]

Good stuff!
I updated the zip file (database and documentation) in this link (post #45 in this thread) today.
I recommend you review the documentation to learn the details of the audit routines that evolved through the discussion/learning with PSSMargaret early this thread.