How programmatically Delete all records in a subform

S

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.
 
A

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
 
R

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
 
S

Silvio

This works. Than you again Allen.

Allen Browne said:
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.

Silvio said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top