By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,262 Members | 2,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,262 IT Pros & Developers. It's quick & easy.

Strange Behavior - Allen Browne's Edit Tracking sometimes doesn't write to table

P: n/a
I use Allen Browne's Audit Trail code in everything I do and I love
it.

Recently, I've run into a problem I can't figure out.

I have a database with about 35 lookup tables. I am creating an
"Admin" screen to allow the head social worker to maintain items in
the list. (This is a volunteer project for a non-profit that is trying
to help elders "age successfully" in their own homes, and I'm trying
to help them do some tracking for analysis later.)

I have a table of Phone Types (Home, Office, etc). and a table of
Religions (none, Catholic, Protestant, Jewish, etc).

To test the form and the audit, I choose an item, add an "s", close
the form. Go back in and remove the "s" so the original item is
displayed.

I should see 4 records: Edit From, Edit To, Edit From, Edit To.

On the religion one, I see the first Edit From, Edit To, but not the
second set. If I do it over and over I don't even see the first set
all the time.

I have stepped through the code and don't see anything wrong (but I'm
not an advanced code-writer by any stretch). I have "break on all
errors" and there are no breaks (no errors, I presume).
BUT, my audit table keys are skipping: I go from 57 and 58 to 65 and
66. I know 59 and 60, 61 and 62, 63 and 64 SHOULD have been there - I
was adding and removing an "s" from the item name right along.

GENERALLY, when I add the "s" I see the audit records; when I remove
it I don't. But that hasn't always been the case. In other forms,
I've added and removed an "s" to an item 6-8 times, and all the edit
records are in the table. I created a new form, copying one that
worked and changing the field and table names - problem still exists.

I tried changing my recordset code to DCount - same thing happens.
(I'm trying to make sure the user doesn't change to something that
exists).

I figure it might be something in the table or audit table, but what?

Thanks for the help with this mystery -
Sara

Code:
http://allenbrowne.com/AppAudit.html (Audit Trail code) - inserted
right from Web

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Uses Allen Browne's Audit Trail code to track user changes

' Check to make sure there is no Active record with this description
first
'Dim intcount As Integer

Dim strSQL As String
strSQL = "Select Religion From tlkpReligion " & _
"Where ReligionKey <" & Me.txtReligionKey & " And "
& _
"Religion = '" & Me.txtReligion & "' And " & _
"ReligionRecordStatus = 'A'"

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

'intcount = DCount("Religion", "tlkpReligion", "Religion = '"
& Me.txtReligion & "' And " & _
"ReligionRecordStatus = 'A'")

If rst.RecordCount 0 Then
'If intcount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Update"
Err.Number = 0
Cancel = True
Exit Sub
End If
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tlkpReligion", "audTmpReligion",
"ReligionKey", _
Nz(Me.txtReligionKey, 0), bWasNewRecord)

End Sub
Private Sub Form_AfterUpdate()

Me.cboReligion.Requery
Me.cboReligion = Me.txtReligionKey

Call AuditEditEnd("tlkpReligion", "audTmpReligion", "audReligion",
_
"ReligionKey", Nz(Me.txtReligionKey, 0), bWasNewRecord)
End Sub

Jun 24 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Comment out the error handler. It should fail on the insert, and give you a
message.

For example, if your audit table has a unique index on one of the fields, it
won't allow a 2nd record to be written.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <sa*******@yahoo.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
>I use Allen Browne's Audit Trail code in everything I do and I love
it.

Recently, I've run into a problem I can't figure out.

I have a database with about 35 lookup tables. I am creating an
"Admin" screen to allow the head social worker to maintain items in
the list. (This is a volunteer project for a non-profit that is trying
to help elders "age successfully" in their own homes, and I'm trying
to help them do some tracking for analysis later.)

I have a table of Phone Types (Home, Office, etc). and a table of
Religions (none, Catholic, Protestant, Jewish, etc).

To test the form and the audit, I choose an item, add an "s", close
the form. Go back in and remove the "s" so the original item is
displayed.

I should see 4 records: Edit From, Edit To, Edit From, Edit To.

On the religion one, I see the first Edit From, Edit To, but not the
second set. If I do it over and over I don't even see the first set
all the time.

I have stepped through the code and don't see anything wrong (but I'm
not an advanced code-writer by any stretch). I have "break on all
errors" and there are no breaks (no errors, I presume).
BUT, my audit table keys are skipping: I go from 57 and 58 to 65 and
66. I know 59 and 60, 61 and 62, 63 and 64 SHOULD have been there - I
was adding and removing an "s" from the item name right along.

GENERALLY, when I add the "s" I see the audit records; when I remove
it I don't. But that hasn't always been the case. In other forms,
I've added and removed an "s" to an item 6-8 times, and all the edit
records are in the table. I created a new form, copying one that
worked and changing the field and table names - problem still exists.

I tried changing my recordset code to DCount - same thing happens.
(I'm trying to make sure the user doesn't change to something that
exists).

I figure it might be something in the table or audit table, but what?

Thanks for the help with this mystery -
Sara

Code:
http://allenbrowne.com/AppAudit.html (Audit Trail code) - inserted
right from Web

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Uses Allen Browne's Audit Trail code to track user changes

' Check to make sure there is no Active record with this description
first
'Dim intcount As Integer

Dim strSQL As String
strSQL = "Select Religion From tlkpReligion " & _
"Where ReligionKey <" & Me.txtReligionKey & " And "
& _
"Religion = '" & Me.txtReligion & "' And " & _
"ReligionRecordStatus = 'A'"

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

'intcount = DCount("Religion", "tlkpReligion", "Religion = '"
& Me.txtReligion & "' And " & _
"ReligionRecordStatus = 'A'")

If rst.RecordCount 0 Then
'If intcount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Update"
Err.Number = 0
Cancel = True
Exit Sub
End If
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tlkpReligion", "audTmpReligion",
"ReligionKey", _
Nz(Me.txtReligionKey, 0), bWasNewRecord)

End Sub
Private Sub Form_AfterUpdate()

Me.cboReligion.Requery
Me.cboReligion = Me.txtReligionKey

Call AuditEditEnd("tlkpReligion", "audTmpReligion", "audReligion",
_
"ReligionKey", Nz(Me.txtReligionKey, 0), bWasNewRecord)
End Sub
Jun 24 '07 #2

P: n/a
On Jun 24, 12:23 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Comment out the error handler. It should fail on the insert, and give you a
message.

For example, if your audit table has a unique index on one of the fields, it
won't allow a 2nd record to be written.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <saraqp...@yahoo.comwrote in message

news:11*********************@p77g2000hsh.googlegro ups.com...
I use Allen Browne's Audit Trail code in everything I do and I love
it.
Recently, I've run into a problem I can't figure out.
I have a database with about 35 lookup tables. I am creating an
"Admin" screen to allow the head social worker to maintain items in
the list. (This is a volunteer project for a non-profit that is trying
to help elders "age successfully" in their own homes, and I'm trying
to help them do some tracking for analysis later.)
I have a table of Phone Types (Home, Office, etc). and a table of
Religions (none, Catholic, Protestant, Jewish, etc).
To test the form and the audit, I choose an item, add an "s", close
the form. Go back in and remove the "s" so the original item is
displayed.
I should see 4 records: Edit From, Edit To, Edit From, Edit To.
On the religion one, I see the first Edit From, Edit To, but not the
second set. If I do it over and over I don't even see the first set
all the time.
I have stepped through the code and don't see anything wrong (but I'm
not an advanced code-writer by any stretch). I have "break on all
errors" and there are no breaks (no errors, I presume).
BUT, my audit table keys are skipping: I go from 57 and 58 to 65 and
66. I know 59 and 60, 61 and 62, 63 and 64 SHOULD have been there - I
was adding and removing an "s" from the item name right along.
GENERALLY, when I add the "s" I see the audit records; when I remove
it I don't. But that hasn't always been the case. In other forms,
I've added and removed an "s" to an item 6-8 times, and all the edit
records are in the table. I created a new form, copying one that
worked and changing the field and table names - problem still exists.
I tried changing my recordset code to DCount - same thing happens.
(I'm trying to make sure the user doesn't change to something that
exists).
I figure it might be something in the table or audit table, but what?
Thanks for the help with this mystery -
Sara
Code:
http://allenbrowne.com/AppAudit.html(Audit Trail code) - inserted
right from Web
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Uses Allen Browne's Audit Trail code to track user changes
' Check to make sure there is no Active record with this description
first
'Dim intcount As Integer
Dim strSQL As String
strSQL = "Select Religion From tlkpReligion " & _
"Where ReligionKey <" & Me.txtReligionKey & " And "
& _
"Religion = '" & Me.txtReligion & "' And " & _
"ReligionRecordStatus = 'A'"
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'intcount = DCount("Religion", "tlkpReligion", "Religion = '"
& Me.txtReligion & "' And " & _
"ReligionRecordStatus = 'A'")
If rst.RecordCount 0 Then
'If intcount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Update"
Err.Number = 0
Cancel = True
Exit Sub
End If
bWasNewRecord = Me.NewRecord
Call AuditEditBegin("tlkpReligion", "audTmpReligion",
"ReligionKey", _
Nz(Me.txtReligionKey, 0), bWasNewRecord)
End Sub
Private Sub Form_AfterUpdate()
Me.cboReligion.Requery
Me.cboReligion = Me.txtReligionKey
Call AuditEditEnd("tlkpReligion", "audTmpReligion", "audReligion",
_
"ReligionKey", Nz(Me.txtReligionKey, 0), bWasNewRecord)
End Sub- Hide quoted text -

- Show quoted text -

That's it! Thanks so much. I had (shame on me!) left the indexes in
the audit table. It all works now and I shall go check every audit
and audit temp table to make sure I didn't do the same on any other
tables.

By the way, I love the audit trail code - helps me resolve user issues
on occasion, as well as helps me find my errors. Thanks!

Sara

Jun 24 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.