How do i delete specific records in a table

[bryanchow1]

Hello, i'm pretty new to access and vba and i cant seem to get my code to work. The code is meant to delete a specific (r) number of rows from a table. Rows are deleted based on a criteria if the item id entered in a text box matches the item id in a field in the table. (there are many records with the same item id in the table)

Private Sub button2_Click()
   Dim rs As dao.Recordset, i As Integer, r As Integer
    On Error GoTo HandleError
    
    r = Me.txtbox2 - 1
    
    Set rs = CurrentDb.OpenRecordset("table 2", dbOpenDynaset)
    With rs
        For i = 1 To r
            .Edit
            If [Product ID] = Me.txtbox1 Then entirerow.Delete
            .Update
        Next i
    End With
    rs.Close
    Set rs = Nothing
    
ExitHere:
    Exit Sub
    
HandleError:
    MsgBox Err.Description
    Resume ExitHere
    
End Sub

txtbox1 = the text box where the item id is entered.
txtbox2 = the tex tbox where r is entered.

Any help would be much appreciated.

 

[RainLover]

Rename textbox to something more descriptive. If you continue along these lines you will have no idea what each Text Box or Label etc is for.

If r = 10 and there are 150 records with matching ID which 10 do you wish to delete.

 

[herbertioz]

Just use a sqlquery, something like:

sqlquery = "DELETE * FROM table where etc etc"
CurrentDb.Execute sqlquery, dbFailOnError

 

[bryanchow1]

Rename textbox to something more descriptive. If you continue along these lines you will have no idea what each Text Box or Label etc is for.

If r = 10 and there are 150 records with matching ID which 10 do you wish to delete.

any 10 records with the matching ID can be deleted as they're all the same, exact copies of each other.

 

[RainLover]

Create a Query that filters the records to the ID.

Then run your code to delete "r" records.

Do you need the code to do this or do you need help.

Question

Why is "r = Me.txtbox2 - 1"
I don't think you need the -1

Finally how do you get duplicate records in the same table. Should you not address this problem instead.

 

[bryanchow1]

Create a Query that filters the records to the ID.

Then run your code to delete "r" records.

Do you need the code to do this or do you need help.

Question

Why is "r = Me.txtbox2 - 1"
I don't think you need the -1

Finally how do you get duplicate records in the same table. Should you not address this problem instead.

Having the code would be much appreciated. I'm mot entirely sure how to write the code. And as for the "r = Me.txtbox2 - 1", one extra record is produced when i used this code to add records so i added a -1 to fix it. No idea why it does that.

I use a count of the id's in the table to see how many of each id there is.

Thanks in advance.

 

[RainLover]

If there are 20 records with the ID in question, how many do you want to delete,

 

[bryanchow1]

Depends on how many the user wants by changing the value of r; lets say 10?

 

[RainLover]

Have a look at this link. It has the information that you need.

http://msdn.microsoft.com/en-us/library/bb243799(office.12).aspx

There is something wrong with what you are doing. An ID should be unique so you should not have duplictes in the first place. I believe you should look at what you are doing. Eventually this will come back to bite you.

 

[bryanchow1]

Will take another look at my system. Thanks for the help anyways.

 

[RainLover]

Did the link help with your code.

 

[bryanchow1]

I tried to write a code but it doesn't seem to be working. Here's it.

Private Sub ButtonDelete_Click()
    Dim strSQL As String
        strSQL = "DELETE TOP " & Me.regNumber & " FROM data WHERE [Product ID] = Me.ProductID"
    Exit Sub
    DoCmd.RunSQL "strSQL"
End Sub

 

[bryanchow1]

Oh nevermind, i got a friend of mine to take a look at the code and he managed to fix it. Thanks for the help anyways, it was much appreciated.