From: billy_tech on
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?
From: paii, Ron on
You could cancel the form's delete event then supply your own code to do the
delete.

"billy_tech" <misterhodes(a)googlemail.com> wrote in message
news:08c2b5d7-9efc-4530-b189-53dcf5defcb8(a)u26g2000yqu.googlegroups.com...
> 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?


From: billy_tech on
On Jul 13, 1:04 pm, "paii, Ron" <n...(a)no.com> wrote:
> You could cancel the form's delete event then supply your own code to do the
> delete.
>
> "billy_tech" <misterho...(a)googlemail.com> wrote in message
>
> news:08c2b5d7-9efc-4530-b189-53dcf5defcb8(a)u26g2000yqu.googlegroups.com...
>

True, although that would remove the possibility of deleting several
rows at once, which is a useful feature.
From: paii, Ron on

"billy_tech" <misterhodes(a)googlemail.com> wrote in message
news:ac465466-744f-461a-ab9d-bfb5d50a437e(a)t10g2000yqg.googlegroups.com...
On Jul 13, 1:04 pm, "paii, Ron" <n...(a)no.com> wrote:
> You could cancel the form's delete event then supply your own code to do
the
> delete.
>
> "billy_tech" <misterho...(a)googlemail.com> wrote in message
>
> news:08c2b5d7-9efc-4530-b189-53dcf5defcb8(a)u26g2000yqu.googlegroups.com...
>

>True, although that would remove the possibility of deleting several
>rows at once, which is a useful feature.

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


From: billy_tech on
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