Access 2010 Form, record deletions from query on JOINs both sides

[Guest Ledik]

I don't understand how it works. I have a form with datasource as query with LEFT JOIN (N:1), the table on the left are data and table on the right is the list. But when I delete a record from left side the unique record from right side is deleted too. This happen only in the form, when I did it in the query, this works as I want. There is no relationship between both tables. I set the form recordset to Dynaset (Inconsistent Updates). There is the datasource:

SELECT tblKalkulacePolozky.PK_Polozka, tblKalkulacePolozky.FK_KodKalkulace, tblKalkulacePolozky.FK_KodKalkPol, tblKalkulacePolozky.Mn, tblKalkPol.Cena, tblKalkPol.FK_Skupina, tblKalkPol.Popis

FROM tblKalkulacePolozky LEFT JOIN tblKalkPol ON tblKalkulacePolozky.FK_KodKalkPol = tblKalkPol.PK_KodKalkPol

WHERE (((tblKalkulacePolozky.[FK_KodKalkulace])=8));

 

When I delete selected record in the form from tblKalkulacePolozky, the record from tblKalkPol with the same key is delete too. I don't understand it. Could you help me?

I created own Form_Delete eventprocedure

Private Sub Form_Delete(Cancel As Integer)

Dim strSQL As String

Cancel = True

If MsgBox("Prejete si odstranit oznacene zaznamy (" & Me.SelHeight & ")?", vbYesNo, "Odstraneni zaznamu") = vbYes Then

strSQL = "DELETE FROM tblKalkulacePolozky" & vbNewLine _

& "WHERE PK_Polozka=" & Nz(Me!PK_Polozka, 0)

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If

End Sub

but there I have a problem requery the form after records deletion, because I am inside delete transaction. And I can't use Form_AfterDelConfirm because I used Cancel=True in Form_Delete

thank you

L

 

Continue reading...