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@googlemail.com> wrote in message
news:08c2b5d7-9efc-4530-b189-53dcf5defcb8@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?

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

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

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

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@googlemail.com> wrote in message
news:71de36c0-6ffe-4ba8-80c0-426ffe795bdb@t10g2000yqg.googlegroups.com...
> 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

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@googlemail.com> wrote in
news:08c2b5d7-9efc-4530-b189-53dcf5defcb8@u26g2000yqu.googlegroups.co
m:

> I am using a disconnected ADO recordset that I bind to my form in
> Access using VBA.

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...@dfenton.com.invalid>
wrote:
> billy_tech <misterho...@googlemail.com> wrote innews:08c2b5d7-9efc-4530-b189-53dcf5defcb8@u26g2000yqu.googlegroups.co
> m:
>
> > I am using a disconnected ADO recordset that I bind to my form in
> > Access using VBA.
>
> Why?
>
> --
> David W. Fenton                  http://www.dfenton.com/
> usenet at dfenton dot com    http://www.dfenton.com/DFA/

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.