Deleting a record on a subform from the main form

[John S. Ford, MD]

I'm using Access 97 and have a form with an embedded subform. The subform
appears in datasheet view so it has no visible controls. I'm trying to code
a button on the main form that will delete the current record of the subform
ie. the record (in datasheet view) that has the cursor on it. I apparently
can't use the DoCmd.RunCommand acCmdDeleteRecord statement from the main
form because it would refer to a record on the main form (not the desired
subform's current record. I've tried code like:

Private Sub cmdDeleteRecord_Click()
Me.sbfctlSubform.Form.DoCmd.RunCommand acCmdDeleteRecord
End Sub

This generates an error that I assume is due to the fact that "DoCmd" isn't
accessible to a subform from a main form.

So how can I do this? Can it be done using DAO recordsets? Any ideas
greatly appreciated!

John

 

[Allen Browne]

You can delete the record from the RecordsetClone of the subform like this:

Dim rs As DAO.Recordset

With Me.sbfctlSubform.Form
If .NewRecord Then
Beep
Else
Set rs = .RecordsetClone
rs.Bookmark = .Bookmark
rs.Delete
Set rs = Nothing
End If
End With

 

[John S. Ford, MD]

Allen,

Thank you, thank you, thank you!

John

 

[user]

Just as a matter of curiosity, does the subform necessarily have a
bookmark (i.e. what is bookmarked if the cursor is in a field of the main
form)? And if the cursor is in a field on a subform and the user clicks on
a button on the main form, does this take the focus off the subform (so that
there is no "current" record)?

Doug

 

[Allen Browne]

Each form has its own current record, regardless of whether it has focus.

In a continuous form or datasheet, if you show the record selector, it
contains an arrow (triangle) in the current row.

Each form also has its own bookmark and RecordsetClone, and again this has
nothing to do with having focus or being the active form. That is one reason
why searching in the RecordsetClone is a good way of finding the record,
i.e. it is not dependent on the correct form having focus. (Another reason
is that you get a NoMatch if no matching record was found.)

 

[user]

OK, thanks!

Doug