Deleting a record in a subform (1 Viewer)

[DeeDee77]

I have a Main form with an Add Record and Delete Record button on it. Then there is a subform on the Main form. When I press the Delete Record button nothing happens. Is there something that I need to do to tie the subform to the command button? Thanks for any help!!

 

[bdp]

If the data for the subform is in a separate table, you will also need to delete the data that has a relationship with the information on your main form. You can do two ways. The simpilest method is in the relationships window, set the referential integrity between the parent and child table to include Cascade Deletes. You need to use caution when using this method that you are not deleting information that you shouldn't be deleteing. The second method is to delete the information through code by creating a recordset and looping through it and deleting all records that are related to the data from your main form.

After this is said and done, be sure to requery your subform to view the update you just made (after pressing the delete button that is).

After re-reading your post, are you only trying to delete the record in the sub-form and not the main record? If that's the case, the method I provided above won't work. Let me know if that's what you are looking for.

 

[DeeDee77]

Yes, only in the subform. It is in datasheet view, and I tried actually putting the button on the subform, but it won't be there once you get out of design view.

 

[bdp]

The simpilest answer is to set the Record Selectors property of the subform to Yes. Then in Form View, click on the record you want to delete then right click on the record selector (it will be the square on the left of the record) and press the Delete button on your keyboard. You will be prompted if you want to delete the record, and after selecting Yes, it will delete the subrecord. If you don't like the default Access message, you can make your own by creating it in the On_Delete event of the subform.

 

[dgm]

You could run a delete query like the following.

Assuming the name of your sub form is 'subMain',
the name of your table is 'myTable', and
you have a unique identifier in your sub form
('myID' in this case. Does not have to be visible, just in underlying recordset),
this code should delete the selected record in your datasheet.

Private Sub cmdDelete_Click()
Dim intID as Integer

intID = Me.subMain.Form.myID

If Not IsNull(intID) Then

strSQL = "DELETE * FROM myTable WHERE myID = " & intID

DoCmd.RunSQL strSQL
Me.subMain.Requery

end if

End Sub