Audit Trail

[SpirituaLee]

So sorry to be impatient, but I'd be so very grateful of some help on this.

 

[boblarson]

You need to provide the correct function call. For a subform you would need to use

Call AuditTrail(Me.SubformControlNameHere.Form, "Unique Field NameHere in Quotes", Me.SubformControlNameHere.Form![RecordIDFieldNameHere])

where SubformControlNameHere refers to the control on the parent form which HOUSES the subform. You need to use its name instead of the subform name unless the subform control name and the subform name are exactly the same.

 

[boblarson]

I was wrong - it would be in the subform's BEFORE UPDATE event and since it would be on that form, you should be able to just use

Call AuditTrail(Me, "FieldNameHereInQuotes", Me!FieldnameHere)

I was all turned around with my other answer and I realized it as I read it again.

 

[SpirituaLee]

You need to provide the correct function call. For a subform you would need to use

Call AuditTrail(Me.SubformControlNameHere.Form, "Unique Field NameHere in Quotes", Me.SubformControlNameHere.Form![RecordIDFieldNameHere])

where SubformControlNameHere refers to the control on the parent form which HOUSES the subform. You need to use its name instead of the subform name unless the subform control name and the subform name are exactly the same.

Thanks Bob,

My lack of intelligence means I might need a little clarification!

The subform, is called 'change_frm_change_to_sam_link_subform', which is housed by a tab control called 'change_tabs', on a tab called 'Linked SAMS'.

The unique record fieldname is called 'change_id'.

I'm a bit confused, as the tab control doesn't seem to have a BeforeUpdate Event, and neither does the tab itself.

The subform does though.

What am I missing?

 

[SpirituaLee]

I was wrong - it would be in the subform's BEFORE UPDATE event and since it would be on that form, you should be able to just use

Call AuditTrail(Me, "FieldNameHereInQuotes", Me!FieldnameHere)

I was all turned around with my other answer and I realized it as I read it again.

Aha, thanks Bob, did't see your correction above! I'll get on it now, thanks so much.

 

[SpirituaLee]

Aha, thanks Bob, did't see your correction above! I'll get on it now, thanks so much.

Hmmm, ok, I don't seem to be able to make any record changes, or record additions with the BeforeEvent set, but can when it's removed.

Well, it lets me make a change, but won't let me move away from the record (new or edited), until I press escape to undo

 

[boblarson]

Post the entire code you used in the FORM'S BEFORE UPDATE event.

 

[SpirituaLee]

Post the entire code you used in the FORM'S BEFORE UPDATE event.

OK, so with the line:

Call AuditTrail(Me, "change_id", Me!change_id)

... in the BeforeUpdate event field, I'm getting an error when adding / updating records in the subform...

"Change Tracking Database can't find the object '
Call AuditTrail(Me, "change_id", Me!change_id).' If '
Call AuditTrail(Me, "change_id", Me!change_id)
'is a new macro or macro group, make sure that you have saved it and typed it's named correctly."

Mucho stuck!

 

[boblarson]

You put that code in the VBA Window and NOT in the event property?

See here for the correct place to put that code:
http://www.btabdevelopment.com/ts/eventcode

 

[SpirituaLee]

You put that code in the VBA Window and NOT in the event property?

See here for the correct place to put that code:
http://www.btabdevelopment.com/ts/eventcode

I've tried both the Event Property (which results in the message above)and the VBA Window, under BeforeEvent..

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, "change_id", Me!change_id)
End Sub

The latter, results in me not being able to create a record, or edit a record in the subform, meaning I have to press escape to revert to how the record was.

 

[boblarson]

I've tried both the Event Property (which results in the message above)and the VBA Window, under BeforeEvent..

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, "change_id", Me!change_id)
End Sub

The latter, results in me not being able to create a record, or edit a record in the subform, meaning I have to press escape to revert to how the record was.

The second one is correct but if you can't edit, then is change_id really the field on the subform that you should be trying to capture? What is the primary key of the data for the SUBFORM, not the main form. If it is, then is change_id really the way it is spelled or is there a space in the field name?

 

[SpirituaLee]

The primary key field on the subform is 'sam_id_full' (which I've tried in the call).

Whether it makes any difference, the subform displays (in datasheet view) all linked projects, to the project shown on it's parent form.

The subform has a record source as follows:

SELECT [change_qry_change_to_sam_link].[linked_sam_id], [change_qry_change_to_sam_link].[sam_id_full], [change_qry_change_to_sam_link].[sam_title], [change_qry_change_to_sam_link].[sam_status], [change_qry_change_to_sam_link].[sam_submitter_name], [change_qry_change_to_sam_link].[sam_installation_date], [change_qry_change_to_sam_link].[sam_expr4], [change_qry_change_to_sam_link].[sam_ccompliance_date], [change_qry_change_to_sam_link].[change_id] FROM [change_qry_change_to_sam_link]

The only editable field is the linked_sam_id field, which, when a SAM ID is linked to the main project is added to a table used solely for the purpose of tracking these links.

Main form and sub form are linked by 'change_id', which I've tried referencing in the AuditTrail call, and I've also tried 'linked_sam_id' and 'sam_id_full.

I've muddled my way through to this point, and the database is working as required, but slotting the audit trail functionality in, is proving difficult (for the subform anyway)!

 

[boblarson]

Can you upload a copy of your database? I am having trouble figuring out why it isn't working (I've done it before with subforms and haven't had any of that trouble).

 

[SpirituaLee]

Can you upload a copy of your database? I am having trouble figuring out why it isn't working (I've done it before with subforms and haven't had any of that trouble).

Hey Bob, I think doing that would help a lot, but it relies on several other databases, and the data and many fields would need to be cleared down / renamed (due to the place I work), so unfortunately it's not an option for me

 

[boblarson]

Well, I'm at a loss to explain it. I see no logical reason, from any of your descriptions, as to why you can't add/edit a record in the subform when you have the code there.

 

[aji]

sTable = frm.RecordSource
sPCName = Environ("COMPUTERNAME")
sPCUser = Environ("Username")
sDBUser = "Me" 'Get Username from the database login
sDateTime = Now()
how to changee "me" in tblAudit log Dbuser field to be another user login from my login table?

 

[boblarson]

sTable = frm.RecordSource
sPCName = Environ("COMPUTERNAME")
sPCUser = Environ("Username")
sDBUser = "Me" 'Get Username from the database login
sDateTime = Now()
how to changee "me" in tblAudit log Dbuser field to be another user login from my login table?

If you are using Access User Level Security you can use

sDBUser = CurrentUser()

 

[aji]

If you are using Access User Level Security you can use

sDBUser = CurrentUser()

how to set up Access User Level Security in version 2007/2010 ?.

 

[boblarson]

how to set up Access User Level Security in version 2007/2010 ?.

If using the ACCDB file format, you can't. If using the MDB file format, you can, but I don't have a copy with me right now to tell you where to go. I think it is on the Database Tools tab, but it would only be there if you have an MDB file.

If you have never used Access User Level Security, I would advise against going down that path as it has been removed from Access's newer file format(s).

 

[BLOCKHEAD]

This is great. What modification would be need to have this write to a separate table and/or a separate table in a separate DB? This way the application Db can remain small / compact / efficient and gain the security of keeping the change log in a separate secured DB. Please advise. Thank you.