How programmatically Delete all records in a subform

[Silvio]

Hello, folks.

How do I delete all the records in a subform from using a command button on
the main form? (I don't want to delete all the records from the table but
only the records in the open form/subform) The records default view on the
subform is Continuous Forms.

I am using the code below, but it deletes only one record not all. The
subform has a child-parent relationship to the main form.

Me.frmHolds.Form.Recordset.Delete


Thank you.

 

[Allen Browne]

Untested aircode example:

Dim db As DAO.Database
Dim strSql As string
Dim strMsg As String

If Me.NewRecord Then
strMsg = "No subform records"
Else
strSql = "DELETE FROM Table2 WHERE MyFK = " & Me.ID & ";"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.[Sub1].Form.Requery
strMsg = db.RecordsAffected & " subform record(s) removed."
Else
strMsg = "No relaed records to delete"
End If
Set db = Nothing
End If
If strMsg <> vbNullString then
MsgBox strMsg
End If

 

[roger]

I'd just write a delete query that that pulls whatever important data there
is from the main form.

then its just one line of code:
docmd.openquery

 

[Silvio]

This works. Than you again Allen.

Untested aircode example:

Dim db As DAO.Database
Dim strSql As string
Dim strMsg As String

If Me.NewRecord Then
strMsg = "No subform records"
Else
strSql = "DELETE FROM Table2 WHERE MyFK = " & Me.ID & ";"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.[Sub1].Form.Requery
strMsg = db.RecordsAffected & " subform record(s) removed."
Else
strMsg = "No relaed records to delete"
End If
Set db = Nothing
End If
If strMsg <> vbNullString then
MsgBox strMsg
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, folks.

How do I delete all the records in a subform from using a command button
on
the main form? (I don't want to delete all the records from the table but
only the records in the open form/subform) The records default view on the
subform is Continuous Forms.

I am using the code below, but it deletes only one record not all. The
subform has a child-parent relationship to the main form.

Me.frmHolds.Form.Recordset.Delete


Thank you.

 

[thebaul]

I created a button on the mainform and after making the appropriate changes to your code, when I click the button it gives me the following error -

Run time error 3061
Too few parameters, expected 1

ANd highlights the following line in code-

db.Execute strSql, dbFailOnError

This is the code I used after making neccessary modifications-

Private Sub Command844_Click()
Dim db As DAO.Database
Dim strSql As String
Dim strMsg As String

If Me.NewRecord Then
strMsg = "No subform records"
Else
strSql = "DELETE FROM ProductionDetailsTable WHERE ProDPrimaryKey = " & Me.ProPrimaryKey & ";"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.[ProductionDetailsTablesubform].Form.Requery
strMsg = db.RecordsAffected & " subform record(s) removed."
Else
strMsg = "No relaed records to delete"
End If
Set db = Nothing
End If
If strMsg <> vbNullString Then
MsgBox strMsg
End If
End Sub