How to Re-apply a filter on delete?

[gershwyn]

I have a form (frmEditCategories) containing a sub-form (subCategories) that allows the user to add, delete, and change information that categorizes inventory items. The main form is not bound, and there is no parent/child relationship between the main form and the subform. I did this because every other form the users deal with have a form/subform set-up, and they are comfortable editing, deleting, and navigating within that setup.

I have code in the onDelete event of the subform that checks to make sure the record isn't referenced anywhere else before allowing it to be deleted. If a reference exists, then the user is prompted if they would like to mark it as inactive instead of deleting it. If they click yes, the field [Inactive] is set to True. By default, the sub-form is filtered so that inactive categories are not displayed.

What I would like to have happen is, when one or more records are made inactive (from the code in the sub-form's onDelete event) the filter should be reapplied so that record is no longer visible. The code is below:

Private Sub Form_Delete(Cancel As Integer)
  If [Reserved] Then
    MsgBox "This category is reserved by the system and can not be deleted.", vbOKOnly
    Cancel = True
  ElseIf DCount(1, "tblSalesSection", "SectionCategory = " & [CategoryID]) > 0 Then
    rVal = MsgBox("The category '" & [ShortName] & "' is in use and cannot be deleted. Would you like to make it inactive?", vbYesNo)
    If rVal = vbYes Then
      [Inactive] = True
    End If
    Me.Requery
    Cancel = True
  End If
  Cancel = True
End Sub

The Me.Requery line is throwing an error: "Error 3246: Operation not supported in transactions." I think I understand what it means about transactions, being in the middle of a delete operation at the time. But I can't think of how to get around it. Any advice would be appreciated.

[NeoPa]

I think you'll find that the event procedure you need for the .Requery call is Form_AfterDelConfirm().

I'll warn you, as I feel bound to warn all those unfortunate enough to think managing data using unbound forms a good idea, that proceeding along those lines is guaranteed to give you headaches sooner or later. Sorry if this sounds patronising. It's not meant to. I express myself this way as I do have a fair bit of experience and can be expected to know what I'm talking about in this arena.

That said, I can only offer advice. You are of course fully at liberty to ignore it if you choose to, but at least other people reading this thread will see the advice anyway, so my responsibility is covered.

[gershwyn]

Thank you for the response, NeoPa.

My understanding is that the AfterDelConfirm event will not fire if the delete event was canceled, which is the case here. I only allow the record to be deleted if it is truly not being used anywhere else. I put in a .Requery statement with a break on it and execution never reached that point.

As for your advice, I should point out the unbound form is merely a container for the subform and a handful of command buttons - no data. All the data is managed by the subform, which is bound to the underlying table.

[NeoPa]

Ah. The advice was specifically for unbound forms that manage data (re Para #2). If that is not your situation then ignore the advice entirely safely.

As for the event procedure, what you say is perfectly true. I rather assumed you wanted it after a deletion was successful, as typically there would be no cause to requery at all if the deletion didn't go ahead.

I'm afraid if the deletion is canceled then there is no event there to trigger any code, not only as far as Access goes, but also logically as far as I can see. Why would there be an event for when nothing happens.

[TheSmileyCoder]

I do remember a similar problem. At a time I was looking for an After-Delete action. What I did, was this:

Private bDeleting as Boolean
 
Private sub btn_Delete()
'Code for deletion would go here
 
'Code for delayed event
  bDeleting=true
  'Engage delayed event
  Me.TimerInterval=100
End Sub
 
Private Sub Form_Timer()
  if bDeleting Then
    'Reset timer (turn it off)
    Me.TimerInterval=0
    Me.Requery
  End if
End Sub
 

The reason for the bDeleting is that I have more then 1 delayed event. It is not strictly necessary.