I am using a disconnected ADO recordset that I bind to my form in
Access using VBA. I am able to edit existing records, but deleting
them is causing a headache.
When you delete record(s) either by clicking Delete Record button or
highlighting the row and pressing Delete key, Access stored them in a
buffer, then asks user to confirm deletion ("You are about to delete 1
record(s)...")
If I click No, the record is still deleted from the recordset!
Is this just a bug with ADO?

I have verified in code that the record has been removed from the
recordset after I say "No" to the delete. Therefore it cannot be the
form misrepresenting the underlying recordset.

I cannot bind directly to the table - there is business logic in the
middle filtering the results so I need to use ADO. I know there are
workarounds - add another column for user to click on, then I can
handle all deletion logic myself, but this is not ideal.
Does anyone know how to fix this bug with Access?

You could cancel the form's delete event then supply your own code to do the
delete.

"billy_tech" <misterhodes (AT) googlemail (DOT) com> wrote

On Jul 13, 1:04*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
True, although that would remove the possibility of deleting several
rows at once, which is a useful feature.

"billy_tech" <misterhodes (AT) googlemail (DOT) com> wrote

On Jul 13, 1:04 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
Some code can solve that problem.

'------------------------------
' Track selection of records
' Note: no gaps allowed
'
' Add the following to mouse events
'OnMouseDown: =SelRecord([Form],"Down")
'OnMouseMove: =SelRecord([Form],"Move")
'OnMouseUp: =SelRecord([Form],"Up")
'
' Global vars
Dim glnMySelTop As Long ' Remember 1st selected record
Dim glnMySelHeight As Long ' Remember number of selected records
Dim ginfMouseDown As Integer ' Status of mouse button
'
Function SelRecord(F As Form, MouseEvent As String)
On Error Resume Next

Select Case MouseEvent
Case "Move"
' Store the form and the form's Sel property settings
' in the MySel variables ONLY if mouse down has not
' occurred.
If ginfMouseDown = True Then Exit Function
glnMySelTop = F.SelTop
glnMySelHeight = F.SelHeight

Case "Down"
' Set flag indicating the mouse button has been pushed.
ginfMouseDown = True
Case "Up"
' Reset the flag for the next time around.
ginfMouseDown = False
End Select
End Function

'------------------------------------------------
' Call this function to delete the selected records
'
Public Function DelSelected() As integer
Dim i As Long
Dim rsC As Recordset

Me.SelTop = glnMySelTop ' Show selected records
Me.SelHeight = glnMySelHeight

If glnMySelHeight = 0 Then
Exit Function
End If

Set rsC = Me.RecordsetClone

With rsC
.MoveFirst
.Move Me.SelTop - 1

For i = 1 To glnMySelHeight
.Delete
.MoveNext
Next i
End With
glnMySelTop = 0 ' Reset selection after
use
glnMySelHeight = 0
Set rsC = Nothing
End Function

Hi Ron
thanks for that code
Unfortunately, it wouldn't quite work.
Firstly, you're relying on mouse click to record the selection. I
often type Ctrl+A to highlite all records in datasheet view, then
press delete key, so the mouse events are not applicable. Also, at
what point are you proposing running the DelSelected() function? It
can't run at the BeforeDelConfirm stage, because by this point the
records are permanently deleted, even if I click "No" to warning. If I
do it in the onDelete, then it's back to original problem that I only
want to ask user once for confirmation, even if they select multiple
records. I'm wondering if, in the OnDelete event, I can check if the
record is the last one in the selection. If it is, then I can delete
all of them, otherwise cancel the deletion (but add it to the
collection of rows to delete).
I reckon something like

Dim PurchasesToDelete as Integer 'Global var

Private Sub Form_Delete(Cancel As Integer)

If PurchasesToDelete = Me.SelHeight Then
'call function to delete records, then reset PurchasesToDelete to
0
Else
PurchasesToDelete = PurchasesToDelete + 1
End If


End Sub

"billy_tech" <misterhodes (AT) googlemail (DOT) com> wrote

In OnDelete set "Cancel=True" then popup a message box to confirm the delete
of the selected records, then call the delete function.

billy_tech <misterhodes (AT) googlemail (DOT) com> wrote in
news:08c2b5d7-9efc-4530-b189-53dcf5defcb8 (AT) u26g2000yqu (DOT) googlegroups.co
m:

Why?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On Jul 13, 9:10*pm, "David W. Fenton" <XXXuse... (AT) dfenton (DOT) com.invalid>
wrote:
The user doesn't have a direct link to underlying table. They only see
certain records that are filtered using business logic in a stored
procedure. A view runs too slow, I've tried that already. As such I
need a sproc. I love datasheet view though. When it's updatable, it's
by far the quickets form of data entry for our operators, so I'm
trying my hardest to replicate the functionality for them.