Delete a record from a table

[Allie]

Records added to a table have to ID numbers, the first on
is the autonumber/primary key, the second one is the ID
number of the transaction .

How can I use the after update event of a control of a
form to: check if there is a duplicate transaction ID and
delete the one with the smallest Autonumber.

Example

ID TransID Value
1 56 158
5 56 1580

In this case I would need to delete the first record.

Thanks for your help!!

 

[John Vinson]

Records added to a table have to ID numbers, the first on
is the autonumber/primary key, the second one is the ID
number of the transaction .

How can I use the after update event of a control of a
form to: check if there is a duplicate transaction ID and
delete the one with the smallest Autonumber.

Example

ID TransID Value
1 56 158
5 56 1580

In this case I would need to delete the first record.

Thanks for your help!!

Why not set a unique Index on the TransID so you don't add a duplicate
in the first place?

If you do want to do this, you could run a query

DELETE * FROM yourtable
WHERE [TransID] = Me![TransID]
AND ID < Me![ID];

This will delete all records with a lower ID value.

 

[Al Borges]

Hi Allie:

Hmmm... let's see if we can tackle this one-

Option Compare Database
Dim dbs As Database
Dim rst As Recordset
Dim ii As Long, iii As Long
Dim SQLStmt As String
Option Explicit


Private Sub CheckIndex()

On Error GoTo CheckIndex_Error
' first you gotta get the values of the new record that you'll work with
later on
If Not IsNull(Me!TransID) And Not IsNull(Me!Value) Then
ii = Me!TransID
iii = Me!Value
DoCmd.RunCommand acCmdSaveRecord
End If

CheckIndex_Error:
' here we trap the error that there exist 2 similar records (I take it that
the [ID] field is a key field!)
If Err.Number = 3022 Then
MsgBox "Duplicate record. We'll delete the lower valued record." ' a
white lie....
Me.Undo
Set dbs = CurrentDb()
SQLStmt = "SELECT ExampleTable.* FROM ExampleTable WHERE
ExampleTable.[TransID]= ii;"
Set rst = dbs.OpenRecordset(SQLStmt, dbOpenDynaset)
With rst
If !Value < iii Then
!Value = iii
End If
.Close
End With
Else
MsgBox Err.Number & " " & Err.Description, , _
"Private Sub CheckIndex()"
End If
Exit Sub
End Sub

----------------------------------

This should work (theoretically) ... let me know if it gets stuck in real
use. I almost feel like I'm cheating here, since I'm not **really** deleting
the lower numbered record at all times, just undoing the event and checking
the leftover similar key field record to see if it would have been the one
with the higher [Value] field, and if not, simply replacing the actual
number. Oh well.. the enduser will never know!

Regards,
Al

 

[Al Borges]

Hi John:

I posted without knowing that you posted first your much simpler
solution (as always it seems...). I'm definitely going to add that one to my
"tips" file!

The only problem would be if the table's ID field would be a key field,
it would yield an error which needs to be trapped. He would never thus be
able to make a global delete since the record would not be posted, I
think... Anyhow, the suggestion that I posted was gleamed form a post
several years ago by an "Elise M"... trapping the error very neatly.

BTW, if anyone wishes to pervue my "tips" collection (8 years worth of
what I think are the best and greatest suggestions on Access), go to
http://f1.pg.briefcase.yahoo.com/bc/alborgmd. I have to warn those that do
go, there a few records there that I've posted for other groups... call it
gold, or call it junk... for the tips, download the file called "My
miscellaneous Access Notes".

Kudos,
Al

Records added to a table have to ID numbers, the first on
is the autonumber/primary key, the second one is the ID
number of the transaction .

How can I use the after update event of a control of a
form to: check if there is a duplicate transaction ID and
delete the one with the smallest Autonumber.

Example

ID TransID Value
1 56 158
5 56 1580

In this case I would need to delete the first record.

Thanks for your help!!

Why not set a unique Index on the TransID so you don't add a duplicate
in the first place?

If you do want to do this, you could run a query

DELETE * FROM yourtable
WHERE [TransID] = Me![TransID]
AND ID < Me![ID];

This will delete all records with a lower ID value.