access 16, audit trail table (1 Viewer)

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
Any suggestions pls?
or is there another alternative to log the delete action, id and code in the table or by a macro??
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
akika,

I'm not sure if you are basing your code on the Martin Green (fontstuff) example, but you may find this thread -especially the interaction with PSSMargaret - useful.

As for alternative audit approach, you may find this useful.

Good luck.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:09
Joined
Oct 29, 2018
Messages
21,357
Any suggestions pls?
or is there another alternative to log the delete action, id and code in the table or by a macro??
If I get a chance, I'll take another look this weekend. Otherwise, it could be hard to find the problem if it doesn't happen all the time and without a pattern.
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
akika,

I'm not sure if you are basing your code on the Martin Green (fontstuff) example, but you may find this thread -especially the interaction with PSSMargaret - useful.

As for alternative audit approach, you may find this useful.

Good luck.


yes.. ive been using FontStuff example to do the audit trail..
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
@the DBguy,
the pattern is tht it's deleting the next record in the DB instead of the actual record.
so the null recordid and emplcode for action delete was there is empty record at last.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
akika,

The problem with DELETE was identified in the dialog with PSSMargaret mentioned previously.
The latest info I have, is the zip file in this post containing a sample database with an audit log and a word document describing the activity and set up I concocted.

I had trouble with ActiveScreen and ActiveControl, so devised an alternate method.
In the dialog with PSSMargaret, we noticed that the wrong record is logged for Deletes.

This database and logging approach was in answer to the issues posed by PSSMargaret. It may not solve the general case and has not been tested in other environments. However, feel free to use it in any development/design as you see fit. If you use it, and/or improve it or extend it, please let the forum know with a post in that original thread.
Good luck with your project.
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
ive tried the code mentioned in the previous attached post..
and the audit trail (edit new or delete not working

dont knw what i have missed out :(
Can anyone help pls ?
 

Attachments

  • Audit.zip
    593.4 KB · Views: 95

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Please provide a description of how to get into and use your database.
Include any particular Employee or activity(ies) to recreate the issue you are having.

Have you tried setting Show to True and reviewing any messages?
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
extract and shift click to have access to db.
it's a single form customer with search option
and 3 button add new, save and delete button

previous code was wrking for add and save in audit table but delete was deleting the next record and recording wrong Id.
I've changed it to shared post code
but no actions are being track.
seems I've miss a step
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Akika,

I turned Show to True then using Customer Form, I added and deleted a record.
I had info in immediate window (result of Show), but nothing on the auditTrail. I could see the record changes on the Split form. But I didn't use split form in original so changed Customer to Single form.
Your buttons are using embedded macros --which I do not use.

I tried stepping through the Open event code to check the ctl names and values

Code:
Private Sub Form_Open(Cancel As Integer)
show = True    'for debugging jdraw may 28 19
Dim ctl As Control
On Error Resume Next
For Each ctl In Me.Controls
Debug.Print ctl.Name & "  >" & ctl.Value & "<"
Next
End Sub


and got these:
Code:
[COLOR="Blue"]Text214  ><
Combo224  ><
ID  >908<
Form Code  >1234<
Form Name  >AKIKA<
Process  ><
User BU  ><
Author  ><
[/COLOR]
which seem a little strange to me.

I am confused as to what I'm actually looking at as far as the application is concerned.

I did add some records and then deleted 1 and got this in the immediate window (via the Show flag). I was trying to Delete 922.

customer Form_Delete(Cancel As Integer) gRecID is 922
Calling Audit to prepare to DELETE ID =922
Form_AfterDelConfirm(Status As Integer)
Record physically deleted ID 922


922 was deleted from table
Code:
ID	EmplCode	EmplName	Comments	HomePhone	Address
908	1234	AKIKA			
909	A 2345	test			
916	987	sam			
921		Polly  Dactyl	"Test of audit, ""audit"""	35 234 235	12 Lakeside Road
923	abear	Kodiak Bear	jed test2	123Goldilox	1 Dark Forrest Path
924		Bob Robertsville	Hi I'm Bob
but nothing in the auditTrail. Still looking.

UPDATE: After supper did some testing and noticed I did get an audittrail record earlier at 4:46???

Code:
ChangeID	DateTime	UserID	FormName	FieldName	OldValue	NewValue	Action	RecordID	EmplCode
70	28-May-19 4:46:14 PM		customer	Comments	jed test2	jed test2 editing 923 Kodiak Bear	EDIT	923	abear

I am getting error 438 and 3265 repeatedly.
 
Last edited:

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
thxs for ur help and for chking
is it better to use embedded macros or the procedures for the buttons?
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Can you describe for readers the names you have assigned to the controls on your form Customers?

Code:
[COLOR="Blue"]FormName -- ControlName{controlType} 

customer | Text214(TextBox)
customer | Label215(Label)
customer | Command222(CommandButton)
customer | Command429(CommandButton)
customer | Combo224(TextBox)
customer | Label225(Label)
customer | Command210(CommandButton)
customer | ID(TextBox)
customer | ID_Label(Label)
customer | Form Code(TextBox)
customer | Form Code_Label(Label)
customer | Form Name(TextBox)
customer | Form Name_Label(Label)
customer | Process(TextBox)
customer | Process_Label(Label)
customer | User BU(TextBox)
customer | User BU_Label(Label)
customer | Command168(CommandButton)
customer | Author(TextBox)
customer | Author_Label(Label)
customer | Command441(CommandButton)
[/COLOR]

I am taking another look to see if I can find the source of the 438 and 3265 errors. Because these errors relate to "things" not in a collection, I am suspicious of the control names--just a guess.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Akika,

I am returning a modified version of your database in zip format. I think I have resolved the 438 and 3265 errors. I have added and modified and deleted records and have successfully recorded the details in tblAuditTrail.
As I said I did change your Navigation form to a Single form, but you can extend/revise as you wish.
Please see the frmComments_JDraw in the database. There are a couple of routines in module ModJ also that I used when debugging.

Good luck with your project. Please keep us updated on your progress.
 

Attachments

  • AuditForAkika.zip
    72.7 KB · Views: 96
Last edited:

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
thxs Jack.
I will chk comments and details will keep u posted
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
hi,
ive added a drop-down field "Status" in the form and getting below error wen it adding / saving record. Please advise.


ActiveForm is customer
Label43 Error 438 in form_customer being ignored -6/2/2019 2:44:31 PM
Error 2427 in line 450 (You entered an expression that has no value.) in procedure AuditChanges of Module ModAudit
New Employee: 1234 6/2/2019 2:44:45 PM
ActiveForm is customer
Label43 Error 438 in form_customer being ignored -6/2/2019 2:44:45 PM
Error 2427 in line 450 (You entered an expression that has no value.) in procedure AuditChanges of Module ModAudit

customer Form_Delete(Cancel As Integer) gRecID is 936
Calling Audit to prepare to DELETE ID =936
Label43 Error 438 in form_customer being ignored -6/2/2019 3:11:49 PM
Error 2427 in line 640 (You entered an expression that has no value.) in procedure AuditChanges of Module ModAudit
Form_AfterDelConfirm(Status As Integer)
tmpAuditRec was deleted 6/2/2019 3:11:56 PM
Record physically deleted ID 0
ActiveForm is customer
Label43 Error 438 in form_customer being ignored -6/2/2019 2:44:31 PM
Error 2427 in line 450 (You entered an expression that has no value.) in procedure AuditChanges of Module ModAudit
New Employee: 1234 6/2/2019 3:44:45 PM
ActiveForm is customer
Label43 Error 438 in form_customer being ignored -6/2/2019 2:44:45 PM
Error 2427 in line 450 (You entered an expression that has no value.) in procedure AuditChanges of Module ModAudit
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Please post your latest database so I can see/review the issue.
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
pls find attached the Db.
Seems the error is linked with the label text below comments.
 

Attachments

  • Audit.FromAkikaV2.zip
    121.9 KB · Views: 102
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Original message deleted ---have resolved issue and can see tables.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:09
Joined
Jan 23, 2006
Messages
15,362
Akika,

You have "Audit" in the Tag property of Label43. Labels do NOT have values for audit purposes. Label do not have "OldValues" etc.
Remove the Tag value from this Label and all should be OK.
Let me know.

From ModJ:
FormName -- ControlName{controlType}

customer | ID(TextBox) --Audit
customer | Form Code(TextBox) --Audit
customer | Form Name(TextBox) --Audit
customer | User BU(TextBox) --Audit
customer | Process(TextBox) --Audit
customer | Author(TextBox) --Audit
customer | Status(ComboBox) --Audit
customer | Label43(Label) --Audit <---Error
 

akika

Registered User.
Local time
Today, 07:09
Joined
Aug 7, 2018
Messages
102
yeah thts right.. it ok.
thanks a lot Jack for ur help :)
 

Users who are viewing this thread

Top Bottom