How to build a CmdButton in the main form to delete records in the subform?

[Guest]

Several people here have posted the silmilar questions, but it seems there is still no solution to this

I have a main form with a continuous form subform in it. The main form and the subform are are bound to two different tables. The relationship between the main form and the subform is one to many. I'd like to build a CmdButton in the main form to enable me to delete record or records in the subform. I have tried several ways like using the button building wizard or DoCmd.RunCommand acCmdDeleteRecord, but when I press the delete button the record in the main form with all the related records in the subform are deleted. I know I can do this easily by selecting the record or records in the subform and press the delete key on the keyboard, but I prefer using a delete button on the form. Can anyone help me with this? Thanks.

 

[Naresh Nichani MVP]

Hi:

If you set focus to your subform via code and then call the DoCmd.RunCommand
acCmdDeleteRecord would that help?

Me.SubFormName.SetFocus
DoCmd.RunCommand acCmdDeleteRecord

Regards,

Naresh Nichani
Microsoft Access MVP

Several people here have posted the silmilar questions, but it seems there is still no solution to this.

I have a main form with a continuous form subform in it. The main form and

the subform are are bound to two different tables. The relationship between
the main form and the subform is one to many. I'd like to build a CmdButton
in the main form to enable me to delete record or records in the subform. I
have tried several ways like using the button building wizard or
DoCmd.RunCommand acCmdDeleteRecord, but when I press the delete button the
record in the main form with all the related records in the subform are
deleted. I know I can do this easily by selecting the record or records in
the subform and press the delete key on the keyboard, but I prefer using a
delete button on the form. Can anyone help me with this? Thanks.

 

[Guest]

Hi Naresh, thank you for your quick response. My subform is a continuous form with record selector on the left. Usually I would select the record or records I want to delete before I press the delete button. If I set focus to the subform, all the records in the subform will be deleted. I do not know how to code for the selection of a record or records in the subform. I am appreciated if you can post again on this. Thanks.

 

[Guest]

We do this all the time.

We place a unique number IDfield in the subform's record set.
We get the number contained in the field with the following
Forms!MainformName!SubFormName.Form![IDFieldName]
Then we access the recordset of the subform using its clone or other method
Find the record that contains the correct unique number in the IDfield
Then delete that record
Then requery the subform with a new sequence.

Hope this helps

 

[Guest]

Hi Rob, Thank you for your help. I do have a unique number IDfield in my subform's record set. I don't have time to test the method you provided at the moment. Should I type in the IDfield number for the record I want to delete? Or I just click on the record selector on the left of my subform? How come if I want to delete several records at a time? I am appreciated if you can provide me more codes on this. Thanks

 

[Guest]

The current record is returned with the form reference I provided. I just realized that the form reference I provided is slightly wrong it should be "Forms!MainformName!SubFormContainerName.Form![IDFieldName]

I have experimented with multiple records in the past but cant recall exactly how to do it. You can access the top row of the selected group with "Forms!MainformName!SubFormContainerName.Form.Seltop" and the bottom row with "Forms!MainformName!SubFormContainerName.Form.SelHeight"

 

[Guest]

Hi Rob, Thanks a lot. I test the code “Forms!MainformName!SubFormContainerName.Form![IDFieldName]†you provide but it is in red. I use “.†instead of “!†before the IDFieldName, the red color disappeared but it still doesn’t work and the error is 2046. Perhaps I still miss something. The following is the code I put in my delete button
Private Su
Forms!MainformName!SubFormContainerName.Form.[IDFieldName
DoCmd.RunCommand acCmdDeleteRecor
End Su

 

[Guest]

Hi Naresh, I am sorry I test the code you provide again and find that it works. Thanks a lot. This method can only delete one record at a time. I am still working to see if I can find some way to delete several records at a time

 

[John Spencer (MVP)]

If you want to delete all the records in the subform and IF they are bound to
the main form by the main form primary key then you could use a simple delete
query. The SQL for that would look something like:

DELETE subFormTableName.*
FROM subFormTableName
WHERE SubFormTableName.RelatingField = Forms!MainForm!PrimaryKey

The other option would be to write VBA to step through all the records in the
subform and delete them one at a time.

 

[Guest]

Thank you, John. I need to selectively delete "some" of the records in my subform most of the time.