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

[sara]

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_BeforeUpda te(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.txtReligionK ey & " And "
& _
"Religion = '" & Me.txtReligion & "' And " & _
"ReligionRecord Status = 'A'"

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

'intcount = DCount("Religio n", "tlkpReligi on", "Religion = '"
& Me.txtReligion & "' And " & _
"ReligionRecord Status = '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( "tlkpReligi on", "audTmpReligion ",
"ReligionKe y", _
Nz(Me.txtReligi onKey, 0), bWasNewRecord)

End Sub
Private Sub Form_AfterUpdat e()

Me.cboReligion. Requery
Me.cboReligion = Me.txtReligionK ey

Call AuditEditEnd("t lkpReligion", "audTmpReligion ", "audReligio n",
_
"ReligionKe y", Nz(Me.txtReligi onKey, 0), bWasNewRecord)
End Sub

[Allen Browne]

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*******@yaho o.comwrote in message
news:11******** *************@p 77g2000hsh.goog legroups.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_BeforeUpda te(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.txtReligionK ey & " And "
& _
"Religion = '" & Me.txtReligion & "' And " & _
"ReligionRecord Status = 'A'"

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

'intcount = DCount("Religio n", "tlkpReligi on", "Religion = '"
& Me.txtReligion & "' And " & _
"ReligionRecord Status = '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( "tlkpReligi on", "audTmpReligion ",
"ReligionKe y", _
Nz(Me.txtReligi onKey, 0), bWasNewRecord)

End Sub
Private Sub Form_AfterUpdat e()

Me.cboReligion. Requery
Me.cboReligion = Me.txtReligionK ey

Call AuditEditEnd("t lkpReligion", "audTmpReligion ", "audReligio n",
_
"ReligionKe y", Nz(Me.txtReligi onKey, 0), bWasNewRecord)
End Sub

[sara]

On Jun 24, 12:23 pm, "Allen Browne" <AllenBro...@Se eSig.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...@yaho o.comwrote in message

news:11******** *************@p 77g2000hsh.goog legroups.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_BeforeUpda te(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.txtReligionK ey & " And "
& _
"Religion = '" & Me.txtReligion & "' And " & _
"ReligionRecord Status = 'A'"

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

'intcount = DCount("Religio n", "tlkpReligi on", "Religion = '"
& Me.txtReligion & "' And " & _
"ReligionRecord Status = '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( "tlkpReligi on", "audTmpReligion ",
"ReligionKe y", _
Nz(Me.txtReligi onKey, 0), bWasNewRecord)

End Sub

Private Sub Form_AfterUpdat e()

Me.cboReligion. Requery
Me.cboReligion = Me.txtReligionK ey

Call AuditEditEnd("t lkpReligion", "audTmpReligion ", "audReligio n",
_
"ReligionKe y", Nz(Me.txtReligi onKey, 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