Subform Record Deletion via Button

[dmo]

I've found quite a few threads related to form/subform deletions but, haven't found anything that addresses this particular issue. And, thanks in advance for any help!

Details about how the form is set up are provided below but, here's the gist of the dilemma...I have an "Add Record" button in the main form that properly adds a new record to the subform (GoToControl, GoToRecord new, the SetValue for each of the fields in the record). I was hoping to similarly create a "Delete Record" button in the main form but, it's not working as smoothly. I tried several macro variations including the following commands (in order): GoToControl, FindRecord, SelectRecord, DeleteRecord. But, instead of deleting the single record from the subform, the entire "main" record (along with ALL subform entries) are deleted. I think it has to do with how I'm "finding" and then "selecting" the subform record but, am at a loss how to resolve it.

(Note: if I need to provide "code" for the form, I will need some direction on how to extract it from access - I generally work in design view and "code" via macros instead of VBA)

This is a form used for attendance tracking at a dog training facility. Basic form structure is as follows:
Main Form: ClassID, ClassName, ClassDate; plus 2 buttons (Add Record, Remove Record) - SingleForm View
Subform 1: DogNameBreed (combo box), OwnerName (txtbox), DogID (txtbox), ClientID (txtbox) - this subform is not linked to the main form - the selection from the combo box populates the text boxes in this subform and, when the user clicks the "Add Record" button, the DogID and Client ID from this subform (along with ClassID from Main Form) "feed" into Subform 2 - SingleForm View
Subform 2: ClassID, DogID, ClientID ("hidden" in the footer of the main form and linked to the main form via ClassID) - this subform is based on a query that is based on my Attendance Tracking table
Subform 3: DogNameBreed, OwnerName, ClientID (hidden), DogID (hidden) - since Subform 2 doesn't have dog/owner names, it's not useful to users for confirming who was in attendance - When "Add Record" button is clicked, Subform 3 DogID field is set based on the selection from Subform 1 - this form is in Continuous View and allows the user to see all the dogs added to attendance to this particular class being looked at in the SingleView Main Form

 

[bparkinson]

How abut just letting the user select a row in the subform, and use it's primary key to issue a SQL DELETE statement with code like this behind a cmdbutton:

dim i as integer
dim sSQL as string
i = subform_record_primarykey
sSQL = "DELETE FROM SubFormTableName WHERE ID = " & i
DoCmd.RunSQL sSQL

 

[dmo]

As noted in my original post, I don't really know how to write in code so, adjusting the "language" to fit my DB is not going to be easy. I tried the following but, the debugger is giving me error 3078 and telling me it can't find table SF5_AttendForF2. I'm quite certain I just don't know how to make the proper adjustments. Is there a way to do this without code (i.e. through macros or properties of the command button or form/subform)?

Private Sub Command27_Click()
Dim i As Integer
Dim sSQL As String
i = DogID
sSQL = "DELETE FROM SF5_AttendForF2 WHERE DogID = " & i
DoCmd.RunSQL sSQL
End Sub

Also, keep in mind that the subform where the actual "record" is located is the one that's "hidden" in the form footer (not displayed since it only contains ID numbers that are useful to the end user). The subform that is displayed doesn't have "editable" records since it pulls info from several places. I can't be sure since I don't know VB but, I am left with the impression that since the user can't manually select the record from the "hidden" subform, the suggested code may not work.

any other thoughts?

 

[John Big Booty]

Try;

Private Sub Command27_Click()
Dim i As Integer
Dim sSQL As String
i = [B][COLOR="Red"]Me.[/COLOR][/B]DogID
sSQL = "DELETE FROM SF5_AttendForF2 WHERE DogID = " & i
DoCmd.RunSQL sSQL
End Sub

This assumes that the Field DogID is a field on the same form as your Command button. i will now pick up the DogID of the current record.

If DogID is a field on one of your Sub Forms check this link for the correct syntax for referring to that field.

 

[dmo]

I looked at the link and your updates and now have this:

Private Sub Command27_Click()
Dim i As Integer
Dim sSQL As String
i = Me!SF2b_OwnersDogs.Form.DogID
sSQL = "DELETE FROM Me!SF5_AttendForF2.DogID WHERE DogID = " & i
DoCmd.RunSQL sSQL
End Sub

now I'm getting run-time Error 3131 (Syntax error in FROM clause). any thoughts?

(thanks for all the help!)

 

[John Big Booty]

Te highlighted portion of the code should only be the table name from which the record is to be deleted;

Private Sub Command27_Click()
Dim i As Integer
Dim sSQL As String
i = Me!SF2b_OwnersDogs.Form.DogID
sSQL = "DELETE FROM [B][COLOR="Red"]Me!SF5_AttendForF2.DogID[/COLOR][/B] WHERE DogID = " & i
DoCmd.RunSQL sSQL
End Sub