Audit Trail function won't record new records

B

Bob H

When I add a new record to a form, then save it before selecting another
record or using find to find another record, I get an error at these
lines, one after the other when I rem the first one out etc.

rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!RecordID = Screen.ActiveForm.CurrentRecord

How can I get the code to accept a new record, let me save it then carry
out another operation without breaking down

Function TrackChanges()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCtl As String
'Dim strReason As String

'strReason = InputBox("Reason For Changes")
strCtl = Screen.ActiveControl.Name
strSQL = "SELECT tblAudit.* FROM tblAudit;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs.RecordCount > 0 Then rs.MoveLast

With rs
.AddNew
rs!FormName = Screen.ActiveForm.Name
rs!ControlName = strCtl
rs!DateChanged = Date
rs!TimeChanged = Time()
rs!PriorInfo = Screen.ActiveControl.OldValue
rs!NewInfo = Screen.ActiveControl.Value
rs!RecordID = Screen.ActiveForm.CurrentRecord
rs!CurrentUser = fOSUserName
'rs!Reason = strReason
.Update
End With

Set db = Nothing
Set rs = Nothing
End Function
 
A

Allen Browne

What event are you using to do this?
If Form_AfterUpdate, the OldValue is not available at that time. You'll need
to store the OldValue in Form_BeforeUpdate, so you can get at it in
Form_AfterUpdate.

(Or is the code failing on some other line?
 
B

Bob H

The Function TrackChanges is in the BeforeUpdate event on the main form.
How can I store the OldValue in the same event?

The code fails first on OldValue, so I rem that out, the it fails on the
next line, so I rem that out. Then it fails on the next line, so I rem
that out. The code then runs fine without error, but of course no
information is written to the tblAudit.

Thanks
 
A

Allen Browne

You could save the OldValues in to an array, a collection, or into a
temporary table.

From your last comment, it sounds like you have multiple issues you need to
debug here.

Presumably you've already looked at this way of storing audit trail info and
it doesn't suit your needs:
http://allenbrowne.com/AppAudit.html

Another option would be to update to Access 2010, and use data macros to
write your audit trail.
 
B

Bob H

At the moment I am trying your Audit trail functions, and I am getting
an error in this one:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWasNewRecord As String


bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tblTools", "audTmptblTools", "ToolID",
Nz(Me.ToolID, 0), bWasNewRecord)
End Sub

The error is Method or Data member not found, highligthing toolID from
here >(Me.ToolID)

ToolID is set at AutoNumber in the tblTools, but not as PK.

Thanks
 
B

Bob H

Ok, I changed (Me.ToolID)to (Me!ToolID), and now the error is at
bWasNewRecord in the second line.
Compie Error:
ByRef argument type mismatch

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWasNewRecord As String
Dim ToolID As Integer

bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tblTools", "audTmptblTools", "ToolID",
Nz(Me!ToolID, 0), bWasNewRecord)
End Sub
 
B

Bob H

I made another change:
Dim bWasNewRecord As String to
bWasNewRecord As Boolean

which gets past the type mismatch error, but now I get Runtime error 2465.
Tool Inventories can't find the field ToolID referred to in your
expression here:

Call AuditEditBegin("tblTools", "audTmptblTools", "ToolID",
Nz(Me!ToolID, 0), bWasNewRecord) <<< All this line is highlighted


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Object As Control
Dim bWasNewRecord As Boolean
Dim ToolID As Integer

bWasNewRecord = Me.NewRecord
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Object As Control
Dim bWasNewRecord As Boolean

bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tblTools", "audTmptblTools", "ToolID",
Nz(Me!ToolID, 0), bWasNewRecord)
End Sub

End Sub
 
A

Allen Browne

Okay, so again you have several errors here to debug, Bob.

Did you follow the steps at the bottom of this page exactly:
http://allenbrowne.com/AppAudit.html
#1 talks about how (and more importantly *where*) to dim the boolean. If you
put it in your code like that, it won't work.

As the article states, this is not a simple process, and you will need to
follow the steps exactly, even down to the order of the fields in the temp
and final audit tables, and watching out for the unique indexes etc.
 
B

Bob H

Allen, the only error I get now is at this line:

Private Sub Form_Delete(Cancel As Integer)
Call AuditDelBegin("tblTools", "audTmptblTools", "ToolID",
Nz(Me!ToolID, 0))
End Sub

The error message says it can't find ToolID reffered to in the expression.

ToolID is a Number in the tblTools, so is it been looked for in tblTools
or audTmptblTools. Whichever case it is in both tables, so wht can't it
be found.

Thanks
 
B

Bob H

Just a slight clarification here.
ToolID in th tblTools is an Autonumber and is a PK
ToolID in AudTmptblTools is a Number, and not a PK
ToolID in audTools is a Number and not a PK


Also I have noticed in your coding on the site there are 2 different
ways of doing so for: (Me!ToolID) and (Me.ToolID)
One is a dot and the other is a bang. Does it matter which.

Thanks
 
B

Bob H

Ok, not what I wanted to hear, but thanks.

Now from what I can find out, the dot is used in a system control
situation, ie .control, whereas the bang is used in a name I made up
myself ie !MyForm.

So, from that the correct coding should be Me!ToolID, and not Me.TooID.

I hope I am right here.
 
B

Bob H

Ok, thanks for that John, but it doesn't get me past the dreaded runtime
error 2465, Can't find the field ToolID in the expression.
Aaaargh, why not!

Just outof interest, should that field be also on the form, because as
it is just an autonumber on the tblTools, I didn't include it on the form.

Thanks
 
J

John W. Vinson

Ok, thanks for that John, but it doesn't get me past the dreaded runtime
error 2465, Can't find the field ToolID in the expression.
Aaaargh, why not!

Just outof interest, should that field be also on the form, because as
it is just an autonumber on the tblTools, I didn't include it on the form.
Click to expand...

If it's not on the form your expression won't find it, because that's where it
is looking.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another audit Trail module 8
populate listbox form database table 0
ACCESS 2002 enters some kind of endless loop and crashes 1
Error 87 When Creating Large Email Distribution List 1
Runtime Error 3001 Arguments Are Of The Wrong Type, Are Out Of TheAcceptable Range, or are in confli 4
Need to create a dynamic recordset using generic field names 8
Copy field value.............................FMR 1
Syntax error (missing operator) 4

Top