I tried this and that and finally, via Google, came up with this? Is there
a more intuitive, straightforward way to delete a record from a table and
simultaneously cause the form to go to the next record?

Public Function fncDelCurrentRec(ByRef frmSomeForm As Form) As Boolean

With frmSomeForm
If .NewRecord Then
.Undo
fncDelCurrentRec = True
GoTo Exit_DelCurrentRec
End If
End With

With frmSomeForm.RecordsetClone
.Bookmark = frmSomeForm.Bookmark
.Delete
' frmSomeForm.Requery
End With
fncDelCurrentRec = True

Exit_DelCurrentRec:
Exit Function

Err_DelCurrentRec:


MsgBox (Err.Description)

fncDelCurrentRec = False
Resume Exit_DelCurrentRec

End Function

On Tue, 17 Aug 2010 20:59:17 -0400, "LAS" <(E-Mail Removed)> wrote:

>I tried this and that and finally, via Google, came up with this? Is there
>a more intuitive, straightforward way to delete a record from a table and
>simultaneously cause the form to go to the next record?

Ummm... yes.

Click the Record Selector (the bar to the left, typically). Press the Delete
key and respond to the prompt.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

LAS wrote:

> I tried this and that and finally, via Google, came up with this? Is
> there a more intuitive, straightforward way to delete a record from a
> table and simultaneously cause the form to go to the next record?

DoCmd.RunCommand acCmdDeleteRecord

k.i.s.s.

"LAS" <(E-Mail Removed)> wrote in
news:i4fb97$r47$(E-Mail Removed):

> With frmSomeForm.RecordsetClone
> .Bookmark = frmSomeForm.Bookmark
> .Delete
> ' frmSomeForm.Requery
> End With
> fncDelCurrentRec = True

This code has multiple problems:

1. the bookmark is irrelevant, as it's not used.

2. bookmarks should never be stored, ever, as they are volatile --
any requery will invalidate them.

3. the Requery is commented out, but it's necessary to get rid of
the #deleted# record from the form's display buffer.

4. there is no reason whatsoever that this should be done with the
RecordsetClone. The RecordsetClone should be used for record
navigation and finding things in the underlying recordset without
affecting the form's edit or display buffers. It should never be
used for anything that alters the edit buffer.

The form's recordset should not be used, either. Editing should be
done through the form's default collection.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Rick Brandt <(E-Mail Removed)> wrote in
news:i4fbtq$u6s$(E-Mail Removed):

> LAS wrote:
>
>> I tried this and that and finally, via Google, came up with this?
>> Is there a more intuitive, straightforward way to delete a
>> record from a table and simultaneously cause the form to go to
>> the next record?
>
> DoCmd.RunCommand acCmdDeleteRecord
>
> k.i.s.s.

Well, to be fair, LAS is trying to write code that is generalized.

Your response doesn't work in the case of being on a new record, but
I'd handle that not within my deletion code, but within whatever
controls call it. That is, if I'm firing the deletion from a command
button, I'd use the OnCurrent event to enable that button only if
..NewRecord is false (e.g., Me!cmdDelete.Enabled = Not Me.NewRecord).

Also, while I have no objection to using RunCommand to delete a
record when the command is issued within the form where the record
is being deleted, it's potentially problematic if focus ends up on
the wrong form.

In general, I accomodate deletions in one of two ways:

1. do nothing, and let deletions be done using 100% Access defaults.

2. turn off AllowDeletes and have a command button that entirely
controls the deletion process.

In either case, I don't customize any of the form's delete events
because in the former case, I'm relying on Access's default
behaviors, and in the latter case, I don't need them because I'm
completley controlling the process in code.

The code that LAS posted indicates to me that he/she is not properly
controlling the user interface because if you did that, there'd be
no reason to account for the .NewRecord issue (because you wouldn't
allow the delete code to be called in the first place).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/