Delete subform

[kristi1023]

I would like to delete the data in a subform on click of a delete button on the main form. The main form lists employee data and subform lists training data for that employee. The forms are linked using employyee id. Is there a simple way to complete this task?

Thanks

 

[WildHare]

The easiest way is to create a DELETE query, with the appropriate linkages, and a criteria set to the employee key field reference on the form - just make sure you are deleting only at the child "training" level, and not the employee him/herself.

Then run that query on the button click event.

It should be straightforward, if you have trouble get back to us.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[thornmastr]

It is better planning and far better design to do something within a form in that form.
You will make your life far less complicated putting your delete button in the subform if you are trying to delete the record shown by the subform. Put another way, if you have a delete button on your primary form it will be assumed you are deleting the record shown by the main form which you really do not want to do.

So, given that I have convinced you and you now have a delete button on your subform, on the on click event of that button, do the following.

Dim rs As Recordset
Dim varBM As Variant

varBM = Me.Bookmark
rs = Me.RecordsetClone
rs.Bookmark = varBM
rs.Delete
rs.Close
Set rs = Nothing

That will do it for you.

Whoops, one more thing, with that logic you will see either deleted or error in the subform. Assuming you have more than just that one record in the subforms record set, you can substitute the following code to get around that problem.


Dim rs As Recordset
Dim varBM As Variant

varBM = Me.Bookmark
rs = Me.RecordsetClone
rs.Bookmark = varBM
rs.Delete
rs.requery
if rs.recorcount > 0 then
rs.movefirst
varBM = rs.bookmark
me.bookmark = varBM
rs.Close
Set rs = Nothing


Robert Berman

 

[kristi1023]

Thank you both for your responses. Jim, I tried the delete query with no success b/c my relationships aren't set up as one-to-many. My lack of success with the query is also likely due to lack of knowledge. Robert, I've always had a delete button on the subform so that a user may individually delete courses. However, I want to force a delete of all courses on the subform on click of the delete button on the main form. Is this possible? Many thanks for the code on the workaround for avoiding "deleted" to be displayed on subform when a record is, in fact, deleted.

Kristi

 

[thornmastr]

You can definitely do it. I am assuming the underlying recordset of the subform contains the entire collection of child records associated with the parent form and for whatever reason you do not want to use SQL to do this.

For purposes of clarity I am calling your subform control childform.

Put the following code in the on click event of the delete button in your main form.

Dim rs as recordset

Set rs = me!childform.recordsetclone

With rs
.movefirst
while not .eof
.delete
.movenext
wend

‘at this point all records are deleted. You will probably now want to close the entire form.
‘ if not, skip the next line

docmd.close acform,me.name

rs.close

set rs = nothing


That will do it for you.

Robert Berman