Delete Subform Record

Huey462

Board Regular
Joined
Jul 25, 2011
Messages
147
I have a form (frmPeople) with several subforms, how can I delete a single record from the subform?</SPAN>

For example, John Doe has two speeding tickets on file, but one of them is a duplicate.</SPAN>

I have tried to select a textbox in the subform then run a delete command, but it deletes the record from the mainform (the person in this case)
</SPAN>
Code:
</SPAN>
Me.frmTickets.Form.txtCR1.SetFocus</SPAN>
DoCmd.RunCommand acCmdDeleteRecord</SPAN>
</SPAN>

How can I delete the subform record not the “main” record?</SPAN></SPAN>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Hi
if the subform is editable then just click on the row and hit the delete button (either on the top menu or from the right click menus).

If you need to do this in code you have to address the subform, not the form. It sounds iffy though. If the records are duplicates you need to identify one of them to delete and one not to delete.

It is also possible that your "duplicates" are because of a mistake in the way the query or form is created - in which case there is really only one record in the table, not two.
 

Huey462

Board Regular
Joined
Jul 25, 2011
Messages
147
The duplicate in the example is an actual duplicate, same info entered twice. I had hoped to copy/paste the code for each subform, but it seems like doing a sql delete from the table using the autonumber record ID may be the easier way to go.</SPAN></SPAN>
 

Huey462

Board Regular
Joined
Jul 25, 2011
Messages
147
Thank you for the help. In the off chance someone else has this issue, below is the code I used to fix the problem

Code:
Dim Check As Integer
  Check = MsgBox("Are you sure that you want to permanently delete all of the information?", vbOKCancel, "Confirm deletion.")
    If Check = vbCancel Then
        Cancel = True
        MsgBox ("Deletion cancelled!"), vbOKOnly, "Nothing Deleted"
    ElseIf Check = vbOK Then
        'sets database and recordset
        DoCmd.SetWarnings False
        Dim dbs As dao.Database
        Dim rst As dao.Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblallControlRoster")
        rst.Delete 'Deletes current record
        DoCmd.SetWarnings True
        MsgBox ("Record deleted, the form will now reload."), vbOKOnly, "Deletion Successful"
    End If
Call PeopleRecordCount 'updates checkboxes
Me.Requery 'reloads the form to update count/query
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top