Unbound Form Audit Trail (Allen Browne)

[Zugz]

hello,
i have an unbound form and from my earlier thread realised i cannot use
allen brownes example for creating an audit trail on an unbound form. my
question is can i create an audit trail with an unbound form? is there some
way i can modify mr brwones example to work for an unbound form because i
cannot bind it and i need to do an audit trail. im still fresh in access and
any suggestions would be apprciated. im using an unbound form because its for
multiple users and will be split. thankyou

 

[Douglas J. Steele]

In an unbound form, you have to know when data has changed, and write the
changes to the table yourself.

When that occurs, also write the details of what changed to an archive
table.

 

[Zugz]

Hello Douglas
thankyou for the advice on unbound forms and audittrails. im really new at
access and im strugling to get this done here's what ive come up with so far.
the code is not exactly correct but i the logic is there

i have a main table called tblMain, and a main unbound form called frmMain,
where all the deletion editions and new records are added and its recordset
is tblMain. I created a new table called tblAudit with the same fields as
tblMainTable, as well as extra fields for AuditType (deletion, addition or
edit) AuditDate and Username of the user who made the changes. here is an
illustration...

tblMain
Field1 = MainID
Field2 = Issues
Field3= Status

tblAudit
Field1 = AudID
Field2 = MainID
Field3 = Issues
Field4 = Status
Field5 = AuditDate
Field6 = Username
Field 7= AuditType

frmMain
txtMainID
txtIssues
txtStatus
cmdAdd
cmdDelete
cmdEdit

So when a user Adds a new record a copy should be saved in tblMain and
tblAudit, like this......

private sub cmdAdd()
Dim db As Database
Dim rstMain As Recordset
Dim rstAudit As Recordset

Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
rstAudit.AddNew

rstAudit("Issues") = Me.txtIssues
rstAudit("Status") = Me.txtStatus
rstAudit("MainID") = Me.txtMainID
rstAudit.update

rstMain.AddNew
rstMain("Issues") = Me.txtIssues
rstMain("Status") = Me.txtStatus
rstMain("MainID") = Me.txtMainID
rstMain.update

end sub

Private sub cmdDelete()
Dim db As Database
Dim rstMain As Recordset
Dim rstAudit As Recordset

Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")
rstAudit.AddNew
rstAudit("AuditType") = "Deletion"
rstAudit.update
rstMain.Delete

end sub

Private sub Edit()
Dim db As Database
Dim rstMain As Recordset
Dim rstAudit As Recordset

Set rstMain = CurrentDb.OpenRecordset("SELECT * FROM tblMain")
Set rstAudit = CurrentDb.OpenRecordset("SELECT * FROM tblAudit")

rstAudit.AddNew
rstAudit("AuditType") = "Edit"
rstAudit.update

rstAudit.Edit
rstAudit("Issues") = Me.txtIssues
rstAudit("Status") = Me.txtCurrentStatus
rstAudit("MainID") =Me.txtMainID
rstAudit.Update

rstMain("Status") = Me.Status
rstMain("Issues") = Me.txtIssues
rstMain("MainID") = Me.txtMainID
rstMain.Update
End sub

Thats my plan. i tried it out and it didnt work and i know its because my
code isn't correct at all. i know when i edit or delete i need to save the
current image in the audit table before any modifications but i don't know
how to do that exactly.
i hope i managed to explain clearly what my problem is. i have learnt that
in future i will avoid unbound forms if i can get way with it. but in this
project(my first one) i had to use unbound forms. thankyou for the help i
know its a lot to ask for.

 

[Douglas J. Steele]

You're making it so hard on yourself using an unbound form!

Presumably you're populating the controls on the form somehow. You have to
keep track of what the original values are and compare them to the values
when cmdAdd is invoked and decide yourself what's changed.