G
Guest
I have a data-entry main form with several embedded subforms for entering
contact info.
My main form has information on the Entity (usually a company). It is joined
to the subforms by Entity ID.
Because of the way my Contact table is structured, each piece of contact
info -- such as name, address, email, phone, cell phone, etc. -- is contained
in a separate record. I have separate subforms for entering each type of
info. The Contact subforms are bound to a join table called
Entity_Contact_Join and joined to the main form by Entity ID.
If someone presses the Delete key from within one of the subforms, I want
Access to delete the corresponding record in the Entity_Contact_Join table.
I attempted the following code on the Form_Delete event for one of the
subforms. It executes without error, but after it's done, all heck breaks
loose. I get a series of about 10 cascading error messages: "operation not
supported in transactions."
Is there a better way to go about this?
Private Sub Form_Delete(Cancel As Integer)
'November 2006 - currently in testing
Dim conEntity_Contact As Connection
Dim rstEntity_Contact As Recordset
Dim intEntityID As Integer
Dim intContactID As Integer
intEntityID = Me.[Entity ID]
intContactID = Me.[Entity_Contact_Join.Contact ID]
Set conEntity_Contact = CurrentProject.Connection
Set rstEntity_Contact = New Recordset
With rstEntity_Contact
.Source = "Select * from Entity_Contact_Join WHERE ([Entity ID] = " &
intEntityID & _
") AND ([Contact ID] = " & intContactID & ")"
.ActiveConnection = conEntity_Contact
.LockType = adLockOptimistic
.Open
.Delete
.Close
End With
Set rstEntity_Contact = Nothing
Set conEntity_Contact = Nothing
End Sub
contact info.
My main form has information on the Entity (usually a company). It is joined
to the subforms by Entity ID.
Because of the way my Contact table is structured, each piece of contact
info -- such as name, address, email, phone, cell phone, etc. -- is contained
in a separate record. I have separate subforms for entering each type of
info. The Contact subforms are bound to a join table called
Entity_Contact_Join and joined to the main form by Entity ID.
If someone presses the Delete key from within one of the subforms, I want
Access to delete the corresponding record in the Entity_Contact_Join table.
I attempted the following code on the Form_Delete event for one of the
subforms. It executes without error, but after it's done, all heck breaks
loose. I get a series of about 10 cascading error messages: "operation not
supported in transactions."
Is there a better way to go about this?
Private Sub Form_Delete(Cancel As Integer)
'November 2006 - currently in testing
Dim conEntity_Contact As Connection
Dim rstEntity_Contact As Recordset
Dim intEntityID As Integer
Dim intContactID As Integer
intEntityID = Me.[Entity ID]
intContactID = Me.[Entity_Contact_Join.Contact ID]
Set conEntity_Contact = CurrentProject.Connection
Set rstEntity_Contact = New Recordset
With rstEntity_Contact
.Source = "Select * from Entity_Contact_Join WHERE ([Entity ID] = " &
intEntityID & _
") AND ([Contact ID] = " & intContactID & ")"
.ActiveConnection = conEntity_Contact
.LockType = adLockOptimistic
.Open
.Delete
.Close
End With
Set rstEntity_Contact = Nothing
Set conEntity_Contact = Nothing
End Sub