Delete all related records in subform, dependent on value on main form

Greetings Experts
Need help please

Have a form with a datasheet sub form. I need to delete all the sub form records if a value on the main form is X. So, for example, if the main-form text box value  = 0,  I need to delete all the related records in the sub form.

My posted code, set in the main form's current  event, and the main form control's change event, does not work; get messg “object or class does not support the set of events”. Have tried other code too, got it working but only one record in the sub form deleted. Need it to delete all related records for the 0 value in the main form.

Hope you can point me in the right direction.

Private Sub Form_Current()

Dim db As DAO.Database
Dim rst2Subform As DAO.Recordset

Set db = CurrentDb
Set rst2Subform = Forms![Occinput]![OccLevel2 Subform].Form.Recordset

Some code

Do While Not rst2Subform.EOF
    If Me.Assessment.Value = 0 Then
        Form![OccLevel2 Subform]!Assessment2.Value = Null
    End If

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The simplest way to delete records is through an SQL statement:

CurrentProject.Connection.Execute "DELETE * FROM SomeTableOrQuery WHERE Criteria=X"

You'd obviously have to change the table/field/criteria information to work with your structure.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ohgeeAuthor Commented:
Ok thanks, have tried this.
Still get messg, object or class does not support the set of events. Am I on the right track here?
Private Sub Form_Current()

Dim db As DAO.Database
Set db = CurrentDb

If Me.Assessment.Value = "+0" Then
db.Execute "DELETE * FROM OccLevel2 WHERE Form!OccInput!OccupationID = Form!OccInput![OccLevel2 Subform]!OccupationID"
End If

Open in new window

Do you have multiple versions of Access running?  If so ... this could be the problem:

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't refer to the Form when specifying the field you wish to use as a filter:

db.Execute "DELETE * FROM OccLevel2 WHERE SOME_FIELD_HERE =" &  Form!OccInput![OccLevel2 Subform]!OccupationID

Also, you must refer to the Forms collection (not the "Form" collection), and if OccupationID is on a Subform, you must refer to it correctly:

db.Execute "DELETE * FROM OccLevel2 WHERE SOME_FIELD_HERE =" &  Forms!OccInput!NameOfYourSubformCONTROL.Form.OccupationID

Note the NameOfYourSubformCONTROL. That is the name of the CONTROL on your main form, and may or may not be the same as the form you're using as a SourceObject. This is easy to munge up, so check this carefully.

ohgeeAuthor Commented:
Thanks for this, appreciate it. Will check it out this weekend and let you know.
ohgeeAuthor Commented:
I do have 2003 and 2007 installed but not both running.

Very happily have almost got it working with following code, using sql.
There is still one problem; the db.execute line attempts to delete only the values from the Assessment2 field. But it deletes all the related values from all the fields in the OccLevel2 table. How do I delete only the Assessment2 values?

Thanks for the help

If Me.HasSubOccs = 3 Then

If Not IsNull(Forms!OccInput![OccLevel2 Subform].Form.Assessment2) Then
    db.Execute "DELETE Assessment2 FROM OccLevel2 WHERE OccupationID=" & Forms!OccInput.Form.OccupationID
    End If

Me.Assessment.Value = Null
Me.Assessment.Enabled = False
Form![OccLevel2 Subform]!Assessment2.Enabled = False
Me.OccLevel2_Subform!OccLevel3.Enabled = True
Form![OccLevel2 Subform].Enabled = True
End If
End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't follow what you mean. A Delete statement should be used to REMOVE an ENTIRE record, not to simple "clear" a value.

If you wish to simply CLEAR those values, use an UPDATE statement:

db.Execute "UPDATE OccLevel2 SET Assessment2 = 0 WHERE OccupationID=" & Forms!OccInput.Form.OccupationID

Obviously you'd have to change the "= 0" part to set it to whatever is appropriate - for example, if this is a Text field, you'd set it to ='' (a Zero Length String).
ohgeeAuthor Commented:
OK, thanks very much. I had forgotten about the distinction between delete and update. Some of my code needs to delete all values, and other code needs to delete only select field values.
I will add your comments to my list of how-to's. Again many thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.