managing accidental rollbacks

[Guest]

We have an Access data entry form which is bound to a sql table called
PAYMENTS. There is an exit button in the data entry form that has code that
writes the data to another table called CHECK_LOG. I've copied and pasted
this code below and omitted specific logic relating to the data fields for
the sql string and business rules logic (e.g. if client X, then set the data
for field Y in a specific way). There have been 2 instances in the past
several months where data appeared in the CHECK_LOG table, but not in the
PAYMENTS table. We have sql trace files that revealed that the entry in the
PAYMENTS table was rolled back about 1 minute after the initial time of the
transaction.

I think there is some user action that causes this, but I have no idea what
that action would be, so this I want to find a way to know right when it
happens so we can check with the user immediately and/or prevent it from
happening.

Any feedback to get me started in solving this problem is much appreciated


Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click
Dim strSQL As String

If (Me![txtAdjBal] - Me![PMT_AMT]) < 0 Then
If MsgBox("This will create a credit balance. Do you still want to
post this payment?", vbYesNo) = vbYes Then
cancelSwitch = False
Else
SendKeys "{ESC}", 10000
GoTo bypass
End If
End If

If IsNull(Me![PMT_AMT]) = True Or Me![PMT_AMT] = "" Then
SendKeys "{ESC}", 10000
GoTo bypass
End If

strSQL = "INSERT INTO [CHECK_LOG] (fields_here) VALUES (values_here);"

DoCmd.RunSQL strSQL

bypass:
DoCmd.Close acForm, "PAYMENTS ENTRY SCREEN"

Exit_btnExit_Click:
Exit Sub

Err_btnExit_Click:
MsgBox Err.Description
Resume Exit_btnExit_Click

End Sub

 

[Allen Browne]

If this is a bound form, use the AfterUpdate event procedure of the *form*
to fire the code.

That will avoid the cases where:

a) The record failed to save for some reason (e.g. validation rule not met,
write conflict, ...), and so the log is written even though the record was
not saved, and

b) The record was saved in the form by a means other than clicking the
button (e.g. applying a filter, pressing Shift+Enter, closing the form,
sorting differently, ...), and so the log did not get written or was written
for the wrong record.

You might also like to use:
dbEngine(0)(0).Execute strSql, dbFailOnError
instead of:
DoCmd.RunSQL strSQL
so you are notifed if the log record was not written.
Details:
http://allenbrowne.com/ser-60.html

 

[Guest]

This makes sense in terms of hardening my code that writes to the CHECK_LOG
table, however the problem is in writing to the PAYMENTS table which is the
table that the form is bound to. In this case I think the problem has to do
with making sure the implicit transaction occurs, rather than the explicit
sql transaction written in code.

Would I have to unbind the form and make all of the data entry explicit in
order to ensure against this type of error?

If this is a bound form, use the AfterUpdate event procedure of the *form*
to fire the code.

That will avoid the cases where:

a) The record failed to save for some reason (e.g. validation rule not met,
write conflict, ...), and so the log is written even though the record was
not saved, and

b) The record was saved in the form by a means other than clicking the
button (e.g. applying a filter, pressing Shift+Enter, closing the form,
sorting differently, ...), and so the log did not get written or was written
for the wrong record.

You might also like to use:
dbEngine(0)(0).Execute strSql, dbFailOnError
instead of:
DoCmd.RunSQL strSQL
so you are notifed if the log record was not written.
Details:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

We have an Access data entry form which is bound to a sql table called
PAYMENTS. There is an exit button in the data entry form that has code
that
writes the data to another table called CHECK_LOG. I've copied and pasted
this code below and omitted specific logic relating to the data fields for
the sql string and business rules logic (e.g. if client X, then set the
data
for field Y in a specific way). There have been 2 instances in the past
several months where data appeared in the CHECK_LOG table, but not in the
PAYMENTS table. We have sql trace files that revealed that the entry in
the
PAYMENTS table was rolled back about 1 minute after the initial time of
the
transaction.

I think there is some user action that causes this, but I have no idea
what
that action would be, so this I want to find a way to know right when it
happens so we can check with the user immediately and/or prevent it from
happening.

Any feedback to get me started in solving this problem is much appreciated



Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click
Dim strSQL As String

If (Me![txtAdjBal] - Me![PMT_AMT]) < 0 Then
If MsgBox("This will create a credit balance. Do you still want to
post this payment?", vbYesNo) = vbYes Then
cancelSwitch = False
Else
SendKeys "{ESC}", 10000
GoTo bypass
End If
End If

If IsNull(Me![PMT_AMT]) = True Or Me![PMT_AMT] = "" Then
SendKeys "{ESC}", 10000
GoTo bypass
End If

strSQL = "INSERT INTO [CHECK_LOG] (fields_here) VALUES (values_here);"

DoCmd.RunSQL strSQL

bypass:
DoCmd.Close acForm, "PAYMENTS ENTRY SCREEN"

Exit_btnExit_Click:
Exit Sub

Err_btnExit_Click:
MsgBox Err.Description
Resume Exit_btnExit_Click

End Sub

 

[Allen Browne]

Just move your code into the BeforeUpdate event procedure of the *form*.
Access fires this every time, just before saving the record. 100% reliable.
Instead of Sendkeys, just cancel the event if the data is not acceptable:
Cancel = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This makes sense in terms of hardening my code that writes to the
CHECK_LOG
table, however the problem is in writing to the PAYMENTS table which is
the
table that the form is bound to. In this case I think the problem has to
do
with making sure the implicit transaction occurs, rather than the explicit
sql transaction written in code.

Would I have to unbind the form and make all of the data entry explicit in
order to ensure against this type of error?

If this is a bound form, use the AfterUpdate event procedure of the
*form*
to fire the code.

That will avoid the cases where:

a) The record failed to save for some reason (e.g. validation rule not
met,
write conflict, ...), and so the log is written even though the record
was
not saved, and

b) The record was saved in the form by a means other than clicking the
button (e.g. applying a filter, pressing Shift+Enter, closing the form,
sorting differently, ...), and so the log did not get written or was
written
for the wrong record.

You might also like to use:
dbEngine(0)(0).Execute strSql, dbFailOnError
instead of:
DoCmd.RunSQL strSQL
so you are notifed if the log record was not written.
Details:
http://allenbrowne.com/ser-60.html

We have an Access data entry form which is bound to a sql table called
PAYMENTS. There is an exit button in the data entry form that has code
that
writes the data to another table called CHECK_LOG. I've copied and
pasted
this code below and omitted specific logic relating to the data fields
for
the sql string and business rules logic (e.g. if client X, then set
the
data
for field Y in a specific way). There have been 2 instances in the past
several months where data appeared in the CHECK_LOG table, but not in
the
PAYMENTS table. We have sql trace files that revealed that the entry in
the
PAYMENTS table was rolled back about 1 minute after the initial time of
the
transaction.

I think there is some user action that causes this, but I have no idea
what
that action would be, so this I want to find a way to know right when
it
happens so we can check with the user immediately and/or prevent it
from
happening.

Any feedback to get me started in solving this problem is much
appreciated



Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click
Dim strSQL As String

If (Me![txtAdjBal] - Me![PMT_AMT]) < 0 Then
If MsgBox("This will create a credit balance. Do you still want
to
post this payment?", vbYesNo) = vbYes Then
cancelSwitch = False
Else
SendKeys "{ESC}", 10000
GoTo bypass
End If
End If

If IsNull(Me![PMT_AMT]) = True Or Me![PMT_AMT] = "" Then
SendKeys "{ESC}", 10000
GoTo bypass
End If

strSQL = "INSERT INTO [CHECK_LOG] (fields_here) VALUES
(values_here);"

DoCmd.RunSQL strSQL

bypass:
DoCmd.Close acForm, "PAYMENTS ENTRY SCREEN"

Exit_btnExit_Click:
Exit Sub

Err_btnExit_Click:
MsgBox Err.Description
Resume Exit_btnExit_Click

End Sub