Cascade Delete a record that is not 1 to many related

[Elana]

I need some coding advice here. My two tables are not one-to-many related so I can't cascade delete.

I have a table where my user enters file notes. The user has the option to create a suspense item for the note. When the user elects to create the suspense item, I have placed code to automatically add a record in the suspense item table. When this occurs, the suspense item ID gets placed in my notes table. So far, so good.

Now, if the user elects to delete a file note for which there has already been a suspense table entry, I need to be able to find the entry in the suspense table and delete it too.

I'm pretty sure I need to use the seek method to find the record, but once I find it, how do I delete it? I'm not very adept yet a find and seek, so any help is appreciated.

So here's my code so far...I've placed it on the "On Delete" event for my file notes form. I need to tell it to match me.SuspenseID with the SuspenseID in the suspense table and then delete the record in the suspense table.


Dim dbs As Database
Dim rst As Recordset


Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset "tblSuspense", dbOpenTable)

With rst
.Index = "SuspenseID"


.Close

End With

Help?

 

[Elana]

Well, I guess I figured it out myself. Here's what I did and it seems to work great:

Private Sub Form_Delete(Cancel As Integer)
Dim dbs As Database
Dim rst As Recordset


Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblSuspense", dbOpenTable)

rst.Index = "SuspenseID"
rst.Seek "=", Me.SuspID


If rst.NoMatch = True Then
Exit Sub
Else
rst.Delete
End If



End Sub

 

[Elana]

Hey Pat - Thanks for your input - sometimes I get so bogged down in the details I can't see the forest for the trees. I hadn't even considered your solution and it's so simple!

I guess the good news is that I learned how to use the seek method on a recordset, even though I don't need it for this particular task.

Thanks again.