ohgee
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you have multiple versions of Access running? If so ... this could be the problem:
http://allenbrowne.com/bug-17.html
http://allenbrowne.com/bug-17.html
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!NameOfYourS ubformCONT ROL.Form.O ccupationI D
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.
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!NameOfYourS
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.
ASKER
Thanks for this, appreciate it. Will check it out this weekend and let you know.
ASKER
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
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
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.Occupa tionID
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).
If you wish to simply CLEAR those values, use an UPDATE statement:
db.Execute "UPDATE OccLevel2 SET Assessment2 = 0 WHERE OccupationID=" & Forms!OccInput.Form.Occupa
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).
ASKER
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.
I will add your comments to my list of how-to's. Again many thanks for the help.
ASKER
Still get messg, object or class does not support the set of events. Am I on the right track here?
Open in new window