Deleting selected record on subform

  • Thread starter Thread starter Haydnw
  • Start date Start date
H

Haydnw

Please can someone help me with some VBA to delete the
*selected* record in a subform. I have a button on the
subform which is supposed to delete the selected record.
The problem is that whenever I press it, the first record
(ie at the top of the screen) is deleted.

I have tried several ways to delete the record, including:

1) Using the wizard for the command button and
choosing 'Delete record'

2) Running a SQL statement to delete the record which
matches the ID of the selected record (this deleted two
records at a time!!)

3) Using 'SendKeys' to emulate a user pressing the delete
key when the required record is selected

4) Using DoCmd.DeleteRecord (or whatever it is!)

I did find something in an old newsgroup post which
referred to using RecordsetClone but was trying something
else at the time and now can't find it again! Any ideas
would be very gratefully received!

Regards,
H
 
If the command button is in the subform (i.e. it is not in Datasheet view),
you should be able to use:
RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord

Executing a SQL statement for a single value and deleting 2 records is a bit
of a worry. Are you sure the code did not run twice?

Using the subform's RecordsetClone generates no warning, but the code would
be:
If Me.Dirty Then
Me.Undo
End If
If Me.NewRecord Then
Beep
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.Delete
End With
End If
 
Hi Allen

Thanks for the quick response! Both of those bits of code
still delete the top record. I had actually tried the
second piece before, pinched from a post by you which I
found using google!

Both the form and the subform have AllowEdits and
AllowDeletions set to false, and when the button is
clicked I set them to true first. Could that be what's
causing the problem? I may have just answered my own
question there! I'm guessing that when I set those, the
form flicks up to the top record!

Have just tried it and that is indeed the problem! Thank
you for causing me to stop long enough to realise my own
mistakes!

H
 
[snip] Thank you for causing me to stop long enough to realise my own
Click to expand...
mistakes!

Now, *that* is a pleasure. Helping you to solve your own issues it what
these groups are about.
 
Back
Top