How to bypass the before update event when a record is deleted (1 Viewer)

[Hrithika]

I have a database which is slowly evolving. Users needed a feature to delete some records without a trail and some with a trail from the form. So I added an apply action field in the subform using which they can delete a record without a trail and if they wanted to keep a trail they could do that too. When user selects "Delete Violations as it was entered in error" the system deletes the record completely which is what everyone wanted. Everyone was happy.

After six months of use now I am asked to add an audit trail. With the help of forums and examples I managed to do that also. I also looked at Browne's method but my data structure does not match the requirements for that method. I used an alternate method. It works as intended except when a record is completely deleted using the code I mentioned above. Then it goes in the infinite loop. I somehow need to bypass the before update event so that the function to write the audit trail is not called.

Spent the entire week including my weekend for a solution. I call the experts to kindly take a look and suggest a solution. I have attached the database for your review. I am out of all ideas at this point.

 

[gemma-the-husky]

I don't understand. I didn't think a delete operation fired a beforeupdate event (whether instigated by pressing the delete key, or by running code of some sort)

 

[Hrithika]

Somehow the beforeupdate event is running, after I delete the record using the apply action. Error screenshot attached.

 

[gemma-the-husky]

I haven't checked your uploads - but I presume you are deleting something that turns out to be related to a record you are retaining - so when you save THAT record, the system throws an error because of the deletion.

Try and add some relational integrity - in the relationships window, right click a join and assert RI over the join., No doubt some of them will be rejected because you have orphan records.

eg, you have a customer with orders - and then you delete the customer - leading to a number of orphan orders.

 

[Hrithika]

I do have this relational scenario you mentioned. I have one to many relation established.

For uploading the database, I imported the data into this from a split database. The relationship didn't carry over. After reading your suggestion I re-established referential integrity and retried but still no luck.

From your example, I am deleting the order but never the customers. However, I have the function called both from the form and the Subform because I need to save the changes on both the forms.

The first error message shows "Record in 'HearingAuditViolations' was deleted by another user". When you click ok, that's when I get the elaborated error message that was attached in the earlier post.

 

[JHB]

Use the DoCmd.RunCommand acCmdDeleteRecord instead of deleting direct in the table.

Private Sub ACTIONCODE_Change()
On Error GoTo actioncode_change_err
If Me.ACTIONCODE.Value = "CS90" Then

    If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
    
    'Debug.Print Me.ID
    DoCmd.SetWarnings False
   [B][COLOR=Red] Me.AllowDeletions = True
    DoCmd.RunCommand acCmdDeleteRecord[/COLOR][/B]
    [B][COLOR=Red]Me.AllowDeletions = False[/COLOR][/B]
    Else
    Me.ACTIONCODE = ""
    
    End If

Me.Form.Requery
Pause (0.1)
Me.Form.Repaint

End If

actioncode_change_err:
Select Case Err.Number
      Case -2147352567                        ' Whatever number you anticipate.
          Resume Next                  ' Use this to just ignore the line.
      Case 3020
          'Resume Next                  ' Use this to give up on the proc.
          Exit Sub
      Case Else                        ' Any unexpected error.
          Call LogError(Err.Number, Err.Description, "SomeName()")
          'Resume Exit_SomeName
      End Select
Exit Sub

DoCmd.SetWarnings True
End Sub

 

[gemma-the-husky]

OK

first I don't think you actually need the allowdeletions statements - they just affect the user interface, not what command buttons do. I assume the delete statement works.

Out of interest, if the deleterecord failed, then it would cause a runtime error, which would pass control back to the active error handler, and the allowdeletions=false would never get executed.

Me.AllowDeletions = True
DoCmd.RunCommand acCmdDeleteRecord
Me.AllowDeletions = False

second, there is nothing in here that would fire an audit log, or raise an error. so it must be something in the process that carries on AFTER this actioncode event/sub.

I would put error handling into this proc, and put a breakpoint in to trace the flow. The log error message you are getting is not access - it is a custom error handler that is firing.

 

[Hrithika]

I made the change you suggested and commented out the on error goto statement to see where it takes. It generated run-time error 2046. The command or action deleterecord isn't available now.

The existing delete statement works as intended. Moreover, I do plan to disable the record selector on the subform.

Use the DoCmd.RunCommand acCmdDeleteRecord instead of deleting direct in the table.

Private Sub ACTIONCODE_Change()
On Error GoTo actioncode_change_err
If Me.ACTIONCODE.Value = "CS90" Then

    If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
    
    'Debug.Print Me.ID
    DoCmd.SetWarnings False
   [B][COLOR=Red] Me.AllowDeletions = True
    DoCmd.RunCommand acCmdDeleteRecord[/COLOR][/B]
    [B][COLOR=Red]Me.AllowDeletions = False[/COLOR][/B]
    Else
    Me.ACTIONCODE = ""
    
    End If

Me.Form.Requery
Pause (0.1)
Me.Form.Repaint

End If

actioncode_change_err:
Select Case Err.Number
      Case -2147352567                        ' Whatever number you anticipate.
          Resume Next                  ' Use this to just ignore the line.
      Case 3020
          'Resume Next                  ' Use this to give up on the proc.
          Exit Sub
      Case Else                        ' Any unexpected error.
          Call LogError(Err.Number, Err.Description, "SomeName()")
          'Resume Exit_SomeName
      End Select
Exit Sub

DoCmd.SetWarnings True
End Sub

 

[Hrithika]

I commented out the goto error section, which was a work in progress anyways, to see where it leads. There is no error coming up in the ACTIONCODE_Change() event which runs when I apply the action code. In the code I have me.form.requery which kicks of the form's beforeupdate event.

I do need to get the form cleaned after the record is deleted. Can't have #deleted# displaying in the control, but requerying runs the before update event which is what I want to skip.

When the form's before update runs it tries to insert a record which is not there anymore.

OK

first I don't think you actually need the allowdeletions statements - they just affect the user interface, not what command buttons do. I assume the delete statement works.

Out of interest, if the deleterecord failed, then it would cause a runtime error, which would pass control back to the active error handler, and the allowdeletions=false would never get executed.

Me.AllowDeletions = True
DoCmd.RunCommand acCmdDeleteRecord
Me.AllowDeletions = False

second, there is nothing in here that would fire an audit log, or raise an error. so it must be something in the process that carries on AFTER this actioncode event/sub.

I would put error handling into this proc, and put a breakpoint in to trace the flow. The log error message you are getting is not access - it is a custom error handler that is firing.

 

[JHB]

OK

first I don't think you actually need the allowdeletions statements - they just affect the user interface, not what command buttons do. I assume the delete statement works.

No - if allowdeletions statements is set to false the error no. 2046 raise, "Command or action DeleteRecord isn't available now."

...
second, there is nothing in here that would fire an audit log, or raise an error. so it must be something in the process that carries on AFTER this actioncode event/sub.

The original code line was:

CurrentDb.Execute "Delete * From HearingAuditViolations Where ID = " & Me.ID

Follow by a "Me.Form.Requery" which fired the "Form_BeforeUpdate" event - and then a "journey" through procedures and functions started, but what throws the error is applying a value to a control which point to the deleted record.

 

[Hrithika]

@JHB,

I am ready to try anything and everything. What do you suggest?

 

[JHB]

The suggestion I posted in post #6 runs by me, (I just downloaded your database again, made the change and deleted without any problem). I'll attached the database, try it - report back if it also works by you or not.

 

[Hrithika]

Initially it gave me runtime error 2046. Once I commented out the select case in the actioncode_change_err: it worked.

However, there is a difference in the way it is working now. Earlier as soon as the action code was applied the record would get deleted. Currently, for the record to disappear, you will have to hit the save record button. Can we make the record disappear as soon as the delete action code is applied. If is not doable I may get a pass from the users on that.

However, they will kill me if I save the audit on a record they deleted using the first choice from the drop down which translates to 'CS90'. They want audit trail for everything else except 'CS90' I know it's counter-intuitive, but again I am dealing with lawyers who can make hair splitting arguments.

 

[Steve R.]

When user selects "Delete Violations as it was entered in error" the system deletes the record completely which is what everyone wanted. ... After six months of use now I am asked to add an audit trail.

Don't delete any records. "Null" all personal data and add fields which record the date that the personal data was nulled out, the reason why (drop down list), and perhaps the approving person (again a drop down list).

I am reacting to two words: "violation" and "audit trail". The word "violation" implies that you are doing something legal, such as processing parking tickets.

The fact that someone wants an "audit trail" means that you have to account for the data, it just can't disappear. Consequently, the database must be able to provide information on how a case was "closed" even if issued in error.

------------------------------------------------------------------------------
PS: Since the topic includes the creation of accidental records, there is an approach to minimize the creation of accidental records and the necessity to delete them. I use an unbound form. The form can't be saved until certain data is entered. One hypothetical example, the parking ticket number, the vehicle license plate number, etc.

That avoids certain situations, such as record creation when a bound form is opened and the person realizes it is lunch time and then closes the form without entering any data.

 

[JHB]

Initially it gave me runtime error 2046. Once I commented out the select case in the actioncode_change_err: it worked.

However, there is a difference in the way it is working now. Earlier as soon as the action code was applied the record would get deleted. Currently, for the record to disappear, you will have to hit the save record button. Can we make the record disappear as soon as the delete action code is applied. If is not doable I may get a pass from the users on that.

I'm not observing the same as you, as soon I click "Yes", (in your MsgBox), to delete the record it disappear, (also without any runtime error).
But I see you missing a "Exit Sub" in your code else you'll run into the error handling part.
Did you download the database? I don't know what the difference in your set up and in mine, because as mention above I'm not getting any error message.
Only for testing purpose, set your subform's property "Allow deletion" to "Yes" in design view, and the try to delete a record from the menuline or right click an chose "Delete Record", (mark the whole record).

...
  Me.Form.Requery
  Pause (0.1)
  Me.Form.Repaint
End If
[B][COLOR=Red]Exit Sub[/COLOR][/B]

actioncode_change_err:

However, they will kill me if I save the audit on a record they deleted using the first choice from the drop down which translates to 'CS90'. They want audit trail for everything else except 'CS90' I know it's counter-intuitive, but again I am dealing with lawyers who can make hair splitting arguments.

Hope you're still alive.
For avoiding writing to the logfile, make your if structure active again,(in the form's before update).

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo BeforeUpdate_Err
'WriteSubFormChanges Me
[B][COLOR=Red]If Me.ACTIONCODE.Value <> "CS90" Then
[/COLOR][/B]'If IsNull(Me.ACTIONCODE.Value) = False Then
  Call WriteChanges([Form])
[B][COLOR=Red]End If[/COLOR][/B]

 

[Hrithika]

Thank you JHB. I did figure this out yesterday evening but forgot to update the post. Just one change from the solution you suggested. The if statement had to be

If Me.ACTIONCODE.Value <> "CS90" Or IsNull(Me.ACTIONCODE.Value) = True Then

to accommodate null values in the apply action field.

I guess I will have to live with the change of behavior on the subform. The deleted value won't disappear until you hit save record. There is no fix for that, right?

 

[JHB]

to accommodate null values in the apply action field.

I guess I will have to live with the change of behavior on the subform. The deleted value won't disappear until you hit save record. There is no fix for that, right?

Until now you haven't write if you have tried the version I've uploaded, (if not please try it.)!
I've made a pdf file how it looks like when I delete a record.

 

[Hrithika]

Yes. I did download the version you attached. Perhaps the version attached does not contain all the fixes you made. I re-downloaded the version once again to be sure.

Attached is the zip with screenshots captured using windows problem step recorder. You will have to scroll down a little to see all the screenshots.

I also included what I changed in the code to get over the error 2046.

Could you please re-attach the working version you have.

Until now you haven't write if you have tried the version I've uploaded, (if not please try it.)!
I've made a pdf file how it looks like when I delete a record.

 

[JHB]

I think I got what is different - yours and mine database setup isn't the same, you have hidden most of the Ribbons, so the command isn't available.
I've made another solution, try it and report back if it works or not.

 

[Hrithika]

Thank you. It works perfect. Just the way I wanted.

I see you have gone back to the old delete statement as it was and added me.form.requery twice.

Me.Form.Requery
Me.Form.Requery

and added

Case 3020
Exit Sub

in the select case statement on actioncode_change_err:

Am I missing something?

Re-querying the form twice is what seems to have given the desired result. Please let me know if I learned it wrong.

Thank you very much. You are a savior.