CODEWITHSUNDEEP
ms-accessvba
Luca Guarro
Luca Guarro

Reputation: 1168

This operation is not supported within transactions

I have two subforms in my microsoft access application that are very closely linked. These two forms are called Phases and Scenarios. Each form gets their records from the corresponding phases and scenarios tables. There are many scenarios for each phase in the database.

So on the form current event for the Phases form, I need to grab its unique PhaseID to filter the Scenarios Form. This all works well except when a Phase gets deleted. When a phase gets deleted, all associated scenarios get deleted as well. After the deletion, access will auto-select a new phase and when I try to apply the filter to the Scenarios form I get this error:

Run-time error '2074': This operation is not supported within transactions.

Here is my code:

Phase current event

Private Sub Form_Current()
   Dim filtStr As String
   Dim frm As Form
   Set frm = Forms!Main
   filtStr = "PhaseID=" & Me!PhaseID
   frm.Scenarios.Form.Filter = filtStr <--ERROR HERE
   frm.Scenarios.Form.FilterOn = True
End Sub

Phase deletion event

Private Sub Form_Delete(Cancel As Integer)
  Dim PhaseID As Long
  Dim Response As Long
  Dim style As Long
  PhaseID = Forms!Main!Phases!PhaseID

  style = vbYesNo + vbQuestion
  Response = MsgBox("Are you sure you wish to delete this phase and all related scenarios and nodes?", style)
  If Response = vbYes Then
     deleteScenariosAndNodes PhaseID
     Forms!Main!Scenarios.Requery
  Else
     Cancel = True
  End If
End Sub

Public Sub deleteScenariosAndNodes(PhaseID As Long)
  Dim ESDHeadNodeID As Long
  Dim scenID As Long
  Dim rst_Del As Recordset
  Set rst_Del = CurrentDb.OpenRecordset("SELECT * FROM Scenarios WHERE PhaseID = " & PhaseID)
  While Not rst_Del.EOF
     scenID = rst_Del!ScenarioID
     ESDHeadNodeID = DLookup("ESDNodeID", "ESDNodes", "((ESDNodes.ESDNodeType)=1) AND ((ESDNodes.ScenarioID) = " & scenID & ")")
     DeleteESDChildren ESDHeadNodeID, False
     rst_Del.Delete
     rst_Del.MoveNext
  Wend
End Sub

Upvotes: 0

Views: 950

Answers (1)

Gustav
Gustav

Reputation: 55856

Remove the OnCurrent code completely.

Then specify properties MasterLinkFields and ChildLinkFields of the subform control to: [PhaseID]

Also, if you applied Referential Integrity between the master table and the child table and set Cascade Delete to On, the remaining code could be reduced to:

Private Sub Form_Delete(Cancel As Integer)

  Dim Response As Long
  Dim style As Long

  style = vbYesNo + vbQuestion
  Response = MsgBox("Are you sure you wish to delete this phase and all related scenarios and nodes?", style)
  If Response <> vbYes Then
     Cancel = True
  End If

End Sub

Upvotes: 2

Related Questions