Only detail records deleted when using form

[Chris Pettingill]

I have a SQL Server 2K database with a Master and Detail table. I have set
up an explicit relationship with cascading deletes in SQL Server. I have a
query defined like:

SELECT * FROM Master LEFT JOIN Detail ON Master.MasterId = Detail.MasterId

This allows me to see all of the records in the master and detail. When I
delete one or more records from a query view, then all corresponding records
in the master and detail tables are deleted. I have a form that displays
this query in a Datasheet view. When I try to delete records in the same
way from the form, I see on the detail records are deleted (but I get no
errors). I need to use a form to display this info to the user so they can
double click on a row and display another form. I've tried using the
Delete, BeforeDeleteConfirm, and AfterDeleteConfirm events for the form to
try and manually delete the master records too, but I can't figure out a way
to get this to work. How can I code my form to ensure when some records are
selected for deletion, the corresponding master AND detail records are
removed. (Note: Adding/editing the data in this form works fine with no
coding). I know the Delete event adds the records to be deleted to a buffer
before they're actually deleted - is there any way I can have access to this
buffer in the BeforeDeleteConfirm event? Then I can manually delete the
appropriate records and then refresh the dataset.

Also, the tables were originally Access tables in the database, set up the
same way (with an explicit relationship defined) and I still had the same
problem.

Thanks,
Chris


PS: I guess my real end goal is to have a query that displays info from the
joined Master and detail, and supports full editing, deleting and adding of
data.

 

[Chris Pettingill]

I found something that seems to work, but if someone has a better solution,
I'd like to know. I added the following code (events for Delete and
AfterDelConfirm):



Option Compare Database
Option Explicit

Private rdel As String

Private Sub Form_AfterDelConfirm(Status As Integer)
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", "DELETE FROM Master WHERE MasterId
IN ( " & rdel & ")")
qdf.Execute (dbSeeChanges)
rdel = ""
End Sub

Private Sub Form_Delete(Cancel As Integer)
If rdel <> "" Then
rdel = rdel & ", "
End If
rdel = rdel & Form_MyForm!MasterId
End Sub