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

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
        rst2Subform.MoveNext
    End If
    Loop

Open in new window

ohgeeAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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.
0
 
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

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

http://allenbrowne.com/bug-17.html

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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.

0
 
ohgeeAuthor Commented:
Thanks for this, appreciate it. Will check it out this weekend and let you know.
0
 
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
Me.Occupation.SetFocus

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

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

0
 
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).
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.