Assume you have a subform named "log_form," whose records are derived from the "log_table." You want to delete the current record selected on "log_form" from within the parent form, and you want the command button to be located on the parent form, not the subform. The following syntax would work:
|
Dim stID As Integer, sSQL As String stID = Me.log_form.Form!autoid MsgBox "The ID is " & stID sSQL = "Delete * from log_table WHERE log_table.autoid like " & stID DoCmd.RunSQL sSQL |
The bold-faced line of code is to make sure that the proper record is being deleted. You can later remove it (or make it skip execution by making it a comment line via the ') once you feel comfortable that it's consistently deleting the proper records.
For more information, do a search under microsoft.public.access.formscoding for the topic "delete record." The thread "help to delete record a record on a subform", created April 11th, 2001, is especially helpful.
One of the key messages (and code) derived from a message in that thread:
|
Hi Michelle First the syntax. a subform is a control on the parent form and therefore must be refered to in this manner. i.e. forms!ParentForm!Subform To reference a control on the subform the syntax would be forms!ParentForm!Subform.form!Control You wish to delete a subform record. Its not really appropiate to promt the user for a record id as this is requiring the user to enter the same id number as displayed by the record, therefore user entry error may occur and you delete the wrong record. Instead prompt the user to confirm deletion, your code will auto pick up the id number from the selected record and delete. Much cleaner. Therefore........ example as A97 Dim strSQL$, response$, idNumber As Integer response = MsgBox("Are you sure you want to delete the current record? Y/N",vbYesNo + vbInformation + vbDefaultButton2,"Data Check") if response = vbYes then idNumber = Me.SubFormName.form!RecordID strSQL = "DELETE CustomerOrders.*, CustomerOrders.OrderRef " & _ "FROM CustomerOrders " & _ "WHERE CustomerOrders.OrderRef Like " & [IdNumber] DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True Me.SubformName.Requery DoCmd GoToRecord,,acFirst MsgBox "Record Has Been Deleted.",vbInformation,"Data Check" exit Sub Else MsgBox "Deletion Aborted",vbInformation,"Data Check" exit sub end if |