The Record Cannot Be Deleted or Changed

[LarryB]

Good morning all,

I have a form which allows me to delete some records from it but not all of them, with the latter producing an error 3200 The Record Cannot Be Deleted or Changed because table "tblMain' includes related records.

My database structure consists of 3 tables, with tblMain on the many side, and the other two tables on the one side.

I understand that I have to delete records from the Many side first, but that is what I am doing and deleting a record on the form

I have no problems accessing table tblMain directly and deleting the record.

What has me baffled is I can delete other records in tblMain via the form. But I cannot see a pattern on records I can delete and cannot

Note as well that the form I am deleting from is opened from another form where the ID's match

Parent Call Form

Private Sub Agent_Code_DblClick(Cancel As Integer)
Dim sID               As String
Dim sForm           As String

sForm = "frmDistributionAddEdit"

sID = "[ID] = " & [ID]
DoCmd.OpenForm sForm, , , sID, acFormEdit

End Sub

frmDistributionAddEdit

Private Sub cmdDelete_Click()

    Dim iResponse       As Integer
    
    iResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete Operation")

    If iResponse = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
        [Forms]![frmDistributionRegisterMain]![DistributionSUB].Form.Requery
    End If
    
    DoCmd.Close
        
End Sub

Any suggestions?

Many thanks

 

[arnelgp]

have you set cascade delete on the relation window?

 

[gemma-the-husky]

check your relationships.

if you can't delete the record in the 1 table - then you MUST have related records in at least one of the many table(s)

personally, I do not like cascading deletes. eg - in this case, you might find you delete records you didn't really want to delete.

 

[LarryB]

Hi all,

I am weary using the cascade delete and update options in a relationship, but I had tried it earlier and it made no difference.

I have come across this issue before if I deleted a record from a table on the "One" side, in that particular case I deleted the from the table on the "Many" side before the one.

The fact that I can delete from the Many table by accessing the table directly tells me this is not the problem

Rather then using

DoCmd.RunCommand acCmdDeleteRecord

I am going to try and delete the record by using the record ID displayed on the form

 

[LarryB]

OK I have this working but I cannot see why my previous delete method was not working.

I created a Delete query on tblMain with criteria under the ID field

[Forms]![frmDistributionAddEdit]![ID]

Behind a command button on my edit form, I called the query

DoCmd.OpenQuery "qryTblMainDelete"

This deleted my record for me, and there were no "orphaned" records left in my tblMain (Many) table.

On the same form, I can create the error using my previous solution, and immediately after, I can delete it using the solution above by referring directly to the record ID

Odd?

 

[gemma-the-husky]

if you go back to the previous example, just try pressing the delete key, rather than your dedicated button.

if it's a code issue, the delete key should work.