acCmdDeleteRecord ERROR 3200 (1 Viewer)

shafara7 · May 8, 2017

Hi there. I'm new to programming in VBA. I am currently doing my internship and I am responsible for the programming part in Access-VBA.
The programm is already made by the previos intern, so I just need to find the errors and fixed it.

I have a form named frmMessauftragsverwaltung and a subform named sfmMesstermine with sfmEndlosMesstermine as the Source Object.
Inside the subform is a Delete Button, assigned at each row of the record displayed.

When I try to delete a record, an Error 3200 appears, saying
The Data cannot be deleted or changed because the Table "___" in relationship with another table. Something like that.

The code is below:

Code:

Private Sub btnLoeschen_Click()
    InfoboxLaden
    If Not Me.NewRecord Then 
        Dim eingabe As String
        eingabe = InputBox("Please insert password.", "Delete Record?")
        If eingabe = modData.getPW Then
            DoCmd.SetWarnings False 
            DoCmd.RunCommand acCmdSelectRecord
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
        ElseIf eingabe <> "" Then MsgBox "Wrong password", , "Error"
        End If
    End If
End Sub

What could possibly be the problem? Before this it works out fine until I added some combobox filter. But that should not affect the delete button should it?

CJ_London · May 8, 2017

the message is telling you the problem - you are trying to delete a record which has related records. If you were to delete this record, the related records would not have a parent - i.e. be what is called an orphan record. The rule is set in relationships where the relationship is set to 'enforce referential integrity'.

To do what you want, there are a number of solutions - which is the right one depends on how the db is intended to work.

1. if having orphan records does not affect the db then you can untick the 'enforce' option.
2. leave the 'enforce' option ticked and also tick the 'cascade delete related records'. Then when you delete the parent record, all the child records will be deleted automatically. Note if the child record is parent to an record, then that relationship also will need to have the 'cascade delete' option ticked
3. leave the 'enforce' option ticked and in your code, before deleting the parent record, have more code to delete the child records

Note that normal practice is to enforce referential integrity but also to not delete records but instead have a 'marked deleted' field or similar which is populated instead. Then queries would have a criteria to filter these deleted records out. The benefit is that once a record is deleted, it is gone and cannot be recovered, which can be a problem if a record is deleted in error. An administrator can review these marked records and delete properly if required (e.g. record created in error).

shafara7 · May 8, 2017

CJ_London said:
the message is telling you the problem - you are trying to delete a record which has related records. If you were to delete this record, the related records would not have a parent - i.e. be what is called an orphan record. The rule is set in relationships where the relationship is set to 'enforce referential integrity'.

1. if having orphan records does not affect the db then you can untick the 'enforce' option.
2. leave the 'enforce' option ticked and also tick the 'cascade delete related records'. Then when you delete the parent record, all the child records will be deleted automatically. Note if the child record is parent to an record, then that relationship also will need to have the 'cascade delete' option ticked
3. leave the 'enforce' option ticked and in your code, before deleting the parent record, have more code to delete the child records

Thank you very much for the reply and explaining it very clearly!
Where can I find the 'enforce' option?
Is it at the relationship table?
I think i am going for option 1.

But do you know why this suddenly happened?
When I first got the database everything works out fine, until I added some combobox to filter the records for easy viewing.
Does it really affect the delete button?

CJ_London · May 8, 2017

Where can I find the 'enforce' option?

in the relationships option in the database tools tab on the ribbon - double click on the relationship line. You may need to drag the relevant tables onto the GUI is not already there.

I think i am going for option 1

before you do, make sure you fully understand how the db works. Having orphan records can cause problems

But do you know why this suddenly happened?

no - adding a combobox should not affect the delete button. But it may be that previously you were only deleting records which did not have child records

shafara7 · May 9, 2017

Okay understood.

I have checked the tables and relationship.
It seems that the record from the subform that I want to delete is the child record.
So I assume if deleting it should not cause a problem?

And I also found another Delete Button inside a subform of another Form.
It uses a different codes

Code:

Private Sub btnLoeschen_Click()
    Dim eingabe As String
    eingabe = InputBox("Are you sure you want to delete the record" & vbCrLf & "Enter Password", "Record deleted")
    If eingabe = "" Then
        Exit Sub
    ElseIf eingabe = modData.getPW Then
        CurrentDb.Execute "Delete * FROM tblMessauftrag WHERE txtTeilesachnummer = '" & [tblStammdaten.txtTeilesachnummer] & "' AND indFOLand = " & [tblStammdaten.indFOLand] & ";"
        CurrentDb.Execute "Delete * FROM tblBelegung    WHERE txtTeilesachnummer = '" & [tblStammdaten.txtTeilesachnummer] & "' AND indFOLand = " & [tblStammdaten.indFOLand] & ";"
        CurrentDb.Execute "Delete * FROM tblStammdaten  WHERE txtTeilesachnummer = '" & [tblStammdaten.txtTeilesachnummer] & "' AND indFOLand = " & [tblStammdaten.indFOLand] & ";"
        Requery
    End If
End Sub

What is the difference between this code and the code on my first post?
Do you have to use different code for different occasion?

shafara7 · May 9, 2017

Here I attacthed the screenshots of my file.
Hopefully you can understand how my data works.

Cronk · May 9, 2017

As to what's the difference in code:
Post #9 is using sql to delete the record (a better method to trap errors) whereas #1 is not.
Post #9 is deleting 3 records, whereas #1 is deleting one.
Both would appear to check the password but in different ways.

As to "So I assume if deleting it should not cause a problem?", you said in #1 that an error was occurring. The child record might itself have children records

shafara7 · May 9, 2017

Thank you for your reply.
I figured maybe I should just write CurrentDb.Execute once only. Not three times.

But then I encountered another problem.
I just want to delete the selected record on the subform. See photo at #6.

With the code below, when clock on the delete button, it deleted the whole records on my table.

Code:

CurrentDb.Execute "Delete * FROM tblMessauftrag WHERE lngMessauftragNr

What could be wrong in the code?
Should I write "Delete.." instead of "Delete * .."
Or I should be more specific at "WHERE lngMessauftragNr = ???" ?

I am thinking of putting 'SetFocus' so that it will just delete on the selected row but I didn't know how to write it.

shafara7 · May 16, 2017

It finally works!
I just use CurrentDb.Execute "Delete * FROM tblMessauftrag WHERE lngMessauftragNr one time only.
And I also found out that I messed up the table names with another.
Silly me.

acCmdDeleteRecord ERROR 3200 (1 Viewer)

shafara7

Registered User.

CJ_London

Super Moderator

shafara7

Registered User.

CJ_London

Super Moderator

shafara7

Registered User.

shafara7

Registered User.

Cronk

Registered User.

shafara7

Registered User.

shafara7

Registered User.

Similar threads

Users who are viewing this thread