Recordset Delete Query (1 Viewer)

C

chrisjames25

Registered User.
Local time
Today, 11:26
Joined
Dec 1, 2014
Messages
401
Hi

I have an invoice form with a subform. When i press a cmd on the main form it runs the following code to cancel the new cancel and delete any entries that that been made to the recordset set contained in the subform.

Code: 
Private Sub Cmd_Close_Click()
'On Error GoTo cmd_close_Click_Err

On Error Resume Next
 
    If (Not Form.NewRecord) Then
        DeleteSubInvoice
        DoCmd.RunCommand acCmdDeleteRecord
      '  ResetData
        Me.Frm_ChildInvoiceForm.Requery
    End If
    
    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If
    
    If (Form.NewRecord And Form.Dirty) Then
       
        DeleteSubInvoice
        Me.Undo
     '   ResetData
        Me.Frm_ChildInvoiceForm.Requery
       
    End If

   
        ResetData
                
    
    Me.Cbo_Customer.SetFocus
    

End Sub

Private Sub DeleteSubInvoice()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_InvoiceDetails", dbOpenDynaset, dbSeeChanges)
    rs.MoveFirst
    Do Until rs.EOF
    
        If rs!Invoice_ID = Me.Txt_Invoice_ID Then
        
        rs.Delete
        
        End If
        
        rs.MoveNext
    Loop

rs.MoveFirst

End Sub

This works but was worried about this getting slow if i am forever having to loop through all invoices details from every invoice ever entered until find the invoice in question.

SO my idea was to have a qry that is based on the current invoice_ID and then delete the data from the qry. So i set the qry up and changed the following bit of code to reflect the qry:

Code: 
Private Sub Cmd_Close_Click()
'On Error GoTo cmd_close_Click_Err

On Error Resume Next
 
    If (Not Form.NewRecord) Then
        DeleteSubInvoice
        DoCmd.RunCommand acCmdDeleteRecord
      '  ResetData
        Me.Frm_ChildInvoiceForm.Requery
    End If
    
    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If
    
    If (Form.NewRecord And Form.Dirty) Then
       
        DeleteSubInvoice
        Me.Undo
     '   ResetData
        Me.Frm_ChildInvoiceForm.Requery
       
    End If

   
        ResetData
                
    
    Me.Cbo_Customer.SetFocus
    

End Sub

Private Sub DeleteSubInvoice()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_InvoiceDetails", dbOpenDynaset, dbSeeChanges)
    rs.MoveFirst
    Do Until rs.EOF
    
        If rs!Invoice_ID = Me.Txt_Invoice_ID Then
        
        rs.Delete
        
        End If
        
        rs.MoveNext
    Loop

rs.MoveFirst

End Sub

My problem is it doesnt seem to work. I set the recordset and then it jumps back up to the me.undo part of code.

Any ideas why. Cheers
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:26
Joined
Jan 14, 2017
Messages
18,216
You are correct in thinking the recordset will get very slow with large datasets.
The solution is to get rid of the recordset code completely and use a SQL DELETE statement.
Instead of all of the recordset code, just use

Code: 
CurrentDb.Execute "DELETE * FROM tbl_Invoice_Details WHERE Invoice_ID = " & Me.txtInvoiceID & ";", dbFailOnError

This assumes Invoice_ID is a number field. If it's text, add text delimiters

In other word, scrap the DeleteSubInvoice procedure and use this instead

Code: 
Private Sub Cmd_Close_Click()
'On Error GoTo cmd_close_Click_Err

On Error Resume Next
 
    If (Not Form.NewRecord) Then
       CurrentDb.Execute "DELETE * FROM tbl_Invoice_Details WHERE 
               Invoice_ID = " & Me.txtInvoiceID & ";", dbFailOnError
      '  ResetData
        Me.Frm_ChildInvoiceForm.Requery
    End If
    
    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If
    
    If (Form.NewRecord And Form.Dirty) Then
       
       CurrentDb.Execute "DELETE * FROM tbl_Invoice_Details WHERE 
             Invoice_ID = " & Me.txtInvoiceID & ";", dbFailOnError
        Me.Undo[COLOR="SeaGreen"] '<==is this needed?[/COLOR]
     '   ResetData
        Me.Frm_ChildInvoiceForm.Requery
       
    End If

   
        ResetData
                
    
    Me.Cbo_Customer.SetFocus
    

End Sub
 
Last edited:
C

chrisjames25

Registered User.
Local time
Today, 11:26
Joined
Dec 1, 2014
Messages
401
PErfect. Worked a treat. Far simpler and should keep speeds up. Many thanks.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:26
Joined
Jan 14, 2017
Messages
18,216
You're welcome

Golden rule #1
Never use recordsets where other methods will do the job just as well.
Queries or SQL equivalents are far faster and usually have less code
 
C

chrisjames25

Registered User.
Local time
Today, 11:26
Joined
Dec 1, 2014
Messages
401
Follow question. As you state golden rule is to use other method instead of recordset if possible should i use an apend query instead of a recordset to add invoice details to the subform?

Code: 
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_TempInvoiceDetail")

rs.AddNew
rs.Fields("Variety_ID") = Me.Lst_SearchResults.Column(6)
rs.Fields("Batch_ID") = Me.Lst_SearchResults.Column(0)
rs.Fields("TrayQty_ID") = Me.Lst_SearchResults.Column(7)
rs.Fields("NoOfTrays") = Me.Txt_HeadNoOfTrays
rs.Fields("AdditionalPlants") = Me.Txt_HeadAdditionalPlants
rs.Fields("Invoice_ID") = Me.Txt_Invoice_ID
rs.Fields("TotalPlants") = Me.Txt_HeadTotalPlants

rs.Update
rs.MoveFirst
rs.Close
Set rs = Nothing
db.Close


Me.Frm_ChildInvoiceForm.Requery
Me.Frm_ChildInvoiceForm.Form.Recordset.MoveLast
Me.Child70.Requery

Above is my current code but thinking now i should change to append query. The one bit not sure how to achieve after the append query is the bit that makes the record go to the last record in the data. Above i just used
Code: 
Me.Frm_ChildInvoiceForm.Requery
Me.Frm_ChildInvoiceForm.Form.Recordset.MoveLast
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:26
Joined
Jan 14, 2017
Messages
18,216
Recordset code does have many uses but it runs row by agonising row (RBAR) whereas action queries / SQL statements effectively do all changes at once. So if you many many records to append, you should notice a significant improvement in speed of execution using APPEND

To move to the last record use DoCmd.GoToRecord , , acLast OR DoCmd.GoToRecord , {ObjectName}, acLast

If you leave the object name argument blank it assumes the active object

You can also use the same syntax with acNext, acPrevious, acFirst, acGoTo (together with record value) and acNewRec

e.g. DoCmd.GoToRecord , , acGoTo, 10 moves to the 10th record
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Value changed after Function
Replies
9
Views
169
mloucel
mloucel
smtazulislam
Add field item data create left-side gap
Replies
13
Views
437
smtazulislam
smtazulislam
J
Using a variable in sql query
Replies
10
Views
319
ebs17
E
S
What's wrong with this code?
Replies
8
Views
397
jdraw
jdraw
M
Solved Cannot delete record from linked SQL Server table using VBA
Replies
1
Views
229
mikenyby
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom