Solved

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

Posted on 2013-05-20
27
2,830 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

740 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