Solved

Access vba to DELETE the first or last record in a table

Posted on 2013-05-20
27
2,590 Views
Last Modified: 2013-05-21
Hi,

I have a table "tblShopTransTmp" which stores 3 fields:
ProductID, CustomerID, Price

Sometimes their may be more than 1 row with the same data in:
i.e
ProductID, CustoemrID, Price
1                  20                 £5.00
1                  20                 £5.00
4                  20                  £10,00
6                  20                  £10.00

I need some vba to delete the first or last row where the productID field is the same.

I have tried to use the following but cannot get it to work:

CurrentDb.Execute "DELETE * FROM tblShopTransTmp WHERE SELECT FINDLAST(Product_ID) FROM tblShopTransTmp " & _
            "WHERE Product_ID =" & Me.lstPurchaseList.Column(0) & ""

ANy ideas please???

Many thanks
0
Comment
Question by:andrewpiconnect
  • 11
  • 7
  • 5
  • +1
27 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39182319
"I need some vba to delete the first or last row where the productID field is the same."
...Don't you really mean to delete the duplicate record?

Because the ID may be the same, but the other fields may be different.

Is this a one shot deal?

You can create a query that gets all the unique records.
Then use this to create a brand new table.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182323
dim dbs as dao.database
dim rst as dao.recordset
dim lngSaveID as long
set dbs = currentdb()
set rst = dbs.openrecordset("Select * from tblShopTransTmp Order By ProductID;",dbopendynaset)
lngSaveID = 0
do while not rst.eof
    if rst!ProductID = lngSaveID then
        '* Delete the record
        rst.delete
    endif
    lngSaveID = rst!ProductID
    rst.movenext
loop
set rst = nothing
set dbs = nothing

Open in new window



Also, you may want to consider putting a unique index on ProductID so that this doesn't happen again.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182331
Actually, the code above will delete the duplicate records, not "just the last" or first duplicate.

Perhaps that is what you were looking for anyway?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39182337
A query like this will get all the unique "Records"

SELECT ProductID, CustomerID, Price
FROM tblShopTransTmp
GROUP BY ProductID, CustomerID, Price

You can name this query something like: qryUniqueProducts

Then create a "Make Table" query from this query:
SELECT qryUniqueProducts.ProductID,qryUniqueProducts.CustomerID,qryUniqueProducts.Price INTO tblUniqueProducts
FROM qryUniqueProducts;

Name this query something like: qrymtUniqueProducts

Now when you run qrymtUniqueProducts, you will create a brand new table: tblUniqueProducts
...that will only contain the unique records


JeffCoachman
0
 

Author Comment

by:andrewpiconnect
ID: 39182366
Hi,
There cld be more than 2 duplicate re ords (it all depends on how many the user has selected).

What ineed is to be able to decrease the duplicate records one at a time

Thanks
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182389
this updated code will delete the duplicates one at a time. Each time this code is run, one out of each set of duplicates will be deleted.

dim dbs as dao.database
dim rst as dao.recordset
dim lngSaveID as long
set dbs = currentdb()
set rst = dbs.openrecordset("Select * from tblShopTransTmp Order By ProductID;",dbopendynaset)
lngSaveID = 0
do while not rst.eof
    if rst!ProductID = lngSaveID then
        '* Delete the record
        rst.delete
        lngSaveID = 0
    endif
    lngSaveID = rst!ProductID
    rst.movenext
loop
set rst = nothing
set dbs = nothing

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39182498
<There cld be more than 2 duplicate re ords (it all depends on how many the user has selected).
What ineed is to be able to decrease the duplicate records one at a time>
...Not sure what this means...

Is your ultimate goal to delete the duplicates?
That is what my posts illustrate.

If I am not understanding something, then please continue on with pdebaets.

JeffCoachman
0
 

Author Comment

by:andrewpiconnect
ID: 39182505
Hi pdebaets,

Your code did delete one record but it threw an error:

I get an error message "No Current record" on line 13
0
 

Author Comment

by:andrewpiconnect
ID: 39182516
Is there any way to stop the code running after line 10 once the record has been deleted?
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182522
Try this:

dim dbs as dao.database
dim rst as dao.recordset
dim lngSaveID as long
set dbs = currentdb()
set rst = dbs.openrecordset("Select * from tblShopTransTmp Order By ProductID;",dbopendynaset)
lngSaveID = 0
do while not rst.eof
    if rst!ProductID = lngSaveID then
        '* Delete the record
        rst.delete
        exit do
    else
        lngSaveID = rst!ProductID
    endif
    rst.movenext
loop
set rst = nothing
set dbs = nothing

Open in new window

0
 

Author Comment

by:andrewpiconnect
ID: 39182536
For those that never quite understood what i was trying to achieve.
I wanted to remove one record at a time. Each time a botton was clicked. Then refresh the listbox showing the amended product list.

pdebaets code has worked well although it threw an erro on line 13 (presumably becaues the recordset had just been deleted.

I have added a line of code that seems to have resolved this:

==============================

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim lngSaveID As Long
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Select * from tblShopTransTmp Order By Product_ID;", dbOpenDynaset)
lngSaveID = 0
Do While Not rst.EOF
    If rst!Product_ID = lngSaveID Then
        '* Delete the record
        rst.Delete
        Exit Do ' EXTRA LINE OF CODE HERE
        lngSaveID = 0
    End If
    lngSaveID = rst!Product_ID
    rst.MoveNext
Loop
Set rst = Nothing
Set dbs = Nothing
==============================
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39182549
Thanks for the explanation.

glad you got it working

Jeff
0
 

Author Comment

by:andrewpiconnect
ID: 39182557
I spoke too soon. its not always deleteing the right Product_ID fo rsome reason
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39182573
I'll bow out know to avoid confusion.
I am sure  pdebaets can get you sorted.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182595
What is the "right" ProductID? Did it delete one of the duplicates, or some other non-duplicate record?
0
 

Author Comment

by:andrewpiconnect
ID: 39182613
Hwhen first run its deleted the correct ID. but if run for a second time it deleted a different ID than the one selected in the list.

Im not sure that because i have exited the loop i have left the recordset open and perjaps this causing the issue
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182659
I'm not sure what you mean by "correct ID". Do you mean the first duplicate encountered? The code is designed to find the first duplicate ProductID, then delete it. Is that what is happening, or not?

I also don't know what you mean by "... the one selected in the list". What list?
0
 

Author Comment

by:andrewpiconnect
ID: 39182829
Aha. perhaps this is the issue then. your code is deleting the first duplicate it finds. there cld be more than one duplicate at a given time.
Perhaps if i change lngSaveID. = Me.lstPurchaseList.Column(0) this will delete the ID that is selected in the listbox.

I shall tey tmrw and let you know
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39182926
<comment removed>
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 39183342
"Hwhen first run its deleted the correct ID. but if run for a second time it deleted a different ID than the one selected in the list."

PMFJI ... Since you are deleting duplicates based on a user selection from a list box, then I think I would use a slightly different approach.

Public Sub someButton_Click()
    
    Dim lngProductId As Long
    lngProductId = CLng("0" & Me.lstPurchaseList)
    
    Dim strSQL As String
    strSQL = "SELECT * FROM tblShopTransTmp WHERE Product_ID = " & lngProductId
    
    With CurrentDb.OpenRecordset(strSQL)
        
        If Not .EOF Then .MoveLast
            
        Do Until .RecordCount = 1
            .Delete
            .MoveLast
        Loop
    
    End With

    Me.lstPurchaseList = Null
    Me.lstPurchaseList.Requery 
    
End Sub

Open in new window


By doing it this way, you only pull into memory what you need to (records with the selected product Id, plus you delete all the duplicates of that ProductId.  If there are no duplicates for the ProductId selected, nothing is deleted.

Hope that helps!
0
 

Author Closing Comment

by:andrewpiconnect
ID: 39183743
Excellent.....worked a treat first time.

Many thanks
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 39184600
Great!

Good to learn of your success!

---

PS> With your configuration, the code should work fine, but to further "bullet proof" the code, you may want to rearrange it just a bit to ensure you don't get in an endless loop (which would be encountered if some clicks the button with no ProcessId selected) ...

Public Sub someButton_Click()
    
    Dim lngProductId As Long
    lngProductId = CLng("0" & Me.lstPurchaseList)
    
    Dim strSQL As String
    strSQL = "SELECT * FROM tblShopTransTmp WHERE Product_ID = " & lngProductId
    
    With CurrentDb.OpenRecordset(strSQL)
        
        If Not .EOF Then
             .MoveLast
            
            Do Until .RecordCount = 1
                .Delete
                .MoveLast
            Loop
        End If
    
    End With

    Me.lstPurchaseList = Null
    Me.lstPurchaseList.Requery 
    
End Sub 

Open in new window


Sorry I did not see that sooner!  Good luck in your project!
0
 

Author Comment

by:andrewpiconnect
ID: 39184778
Hi, thanks for that.

I have just noticed however that if there is more than 1 duplicate (3 or 4 for instance) then when i run your code it deletes all duplicates except for one.
How can i get it to reduce the duplicates one at a time. In othe words, the use has to click the button for each removal individually?

Cheers
0
 

Author Comment

by:andrewpiconnect
ID: 39184869
Done....

If Not .EOF Then
                .MoveLast
                                                   'Do Until .RecordCount = 1
                .Delete
                                                     '.MoveLast
               
                                                       'Looop
End If
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 39184892
"I have just noticed however that if there is more than 1 duplicate (3 or 4 for instance) then when i run your code it deletes all duplicates except for one."

I thought is what you wanted :-s ...

How can i get it to reduce the duplicates one at a time. In othe words, the use has to click the button for each removal individually?

Public Sub someButton_Click()
    
    Dim lngProductId As Long
    lngProductId = CLng("0" & Me.lstPurchaseList)
    
    Dim strSQL As String
    strSQL = "SELECT * FROM tblShopTransTmp WHERE Product_ID = " & lngProductId
    
    With CurrentDb.OpenRecordset(strSQL)
        
        If Not .EOF Then
             .MoveLast
             .Delete
        End If
    
    End With

    Me.lstPurchaseList = Null
    Me.lstPurchaseList.Requery 
    
End Sub  

Open in new window


Also ... just to be sure, the records are truly a duplicate right? --- Every field has the same value ... so it does not matter WHICH record that matches the ProductId selected is deleted correct?

If that is not the case, then we will have to find the match on more than just the ProductId.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 39184947
Also, note that with the above code, the User COULD delete records that are NOT duplicated...

If you want to ensure that ONLY duplicated records can be deleted (thus ensuring the ProductId selected remains in the set displayed in your ListBox, then this code should do the trick ..

Public Sub someButton_Click()
    
    Dim lngProductId As Long
    lngProductId = CLng("0" & Me.lstPurchaseList)
    
    Dim strSQL As String
    strSQL = "SELECT * FROM tblShopTransTmp WHERE Product_ID = " & lngProductId
    
    With CurrentDb.OpenRecordset(strSQL)
        
        If Not .EOF Then
            .MoveLast
            If .RecordCount >= 2 Then
                .Delete
            End If
        End If
    
    End With

    Me.lstPurchaseList = Null
    Me.lstPurchaseList.Requery 
    
End Sub   

Open in new window

0
 

Author Comment

by:andrewpiconnect
ID: 39185020
Many thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now