Delete subform records without deleting main form records

A

Anita

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top