unable to undo delete on form that uses ADO recordset
in [Help]
|
From: billy_tech on 13 Jul 2010 06:15 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 13 Jul 2010 08:04 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 13 Jul 2010 08:33 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 13 Jul 2010 09:26 "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 13 Jul 2010 10:40
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 |