Solved Error 3622 - dbSeeChanges option when accessing a SQL server table (1 Viewer)

[Tea]

Hello,

I have a form (form_student) with student's information where I want to create a button with "on click" event and it should delete the record of this student from my table (that's called tb_student).

Snippet of my code

If MsgBox("Delete this student?", vbYesNo + vbCritical + vbDefaultButton2, "Warning") = vbYes Then
    If Me.Dirty Then
        Me.Undo
    End If
    CurrentDb.Execute _
    "DELETE FROM tb_student " & _
    "WHERE ID = " & Me.ID
    
    If Not IsNull(Me.form_class!IDclass) Then
        CurrentDb.Execute _
        "DELETE FROM tb_class " & _
        "WHERE IDclass = " & Me.form_class!IDclass
    End If
    MsgBox "Deleted!"
    DoCmd.Close acForm, "form_student", acSavePrompt
  End If

Form_class is a subform in my current form_student. This student is assigned to a class, so after deleting this student it should delete also from the tb_class.

The problem is when I've tried to delete a student, this error came up.

Can somebody explain what this error means and how to fix it?

Thank you

 

[isladogs]

You need to add a comma followed by dbSeeChanges at the end of each sql statement when referencing tables stored in SQL Server

 

[Tea]

You need to add a comma followed by dbSeeChanges at the end of each sql statement when referencing tables stored in SQL Server

CurrentDb.Execute , dbSeeChanges _

I don't know if I understood correctly, I've tried this but it's not working.

 

[isladogs]

If MsgBox("Delete this student?", vbYesNo + vbCritical + vbDefaultButton2, "Warning") = vbYes Then
    If Me.Dirty Then
        Me.Undo
    End If
    CurrentDb.Execute _
    "DELETE FROM tb_student " & _
    "WHERE ID = " & Me.ID, dbSeeChanges
   
    If Not IsNull(Me.form_class!IDclass) Then
        CurrentDb.Execute _
        "DELETE FROM tb_class " & _
        "WHERE IDclass = " & Me.form_class!IDclass, dbSeeChanges
    End If
    MsgBox "Deleted!"
    DoCmd.Close acForm, "form_student", acSavePrompt
  End If

 

[arnelgp]

CurrentDb.Execute "theSQLHere", dbSeeChanges

 

[Tea]

If MsgBox("Delete this student?", vbYesNo + vbCritical + vbDefaultButton2, "Warning") = vbYes Then
    If Me.Dirty Then
        Me.Undo
    End If
    CurrentDb.Execute _
    "DELETE FROM tb_student " & _
    "WHERE ID = " & Me.ID, dbSeeChanges
  
    If Not IsNull(Me.form_class!IDclass) Then
        CurrentDb.Execute _
        "DELETE FROM tb_class " & _
        "WHERE IDclass = " & Me.form_class!IDclass, dbSeeChanges
    End If
    MsgBox "Deleted!"
    DoCmd.Close acForm, "form_student", acSavePrompt
  End If

This helped! Only it deletes the record in the subform only and not also from the form_class. How can I delete it from both forms at one time, because it deletes only the class record and the student is still there, when i open the record again and delete it, it's deleted. But I want to delete the class the student is assigned to and also the student at the same time. How to do that?

Thank you

 

[isladogs]

Records are stored in tables not in forms. So you can't actually delete from forms.
If you want a record to be 'automatically' deleted from both tables, then you need to set up a relationship between the tables, apply referential integrity with cascade delete
If that doesn't mean anything to you, please read my extended article Relationships & Referential Integrity