How to Undo an Event?

[prabhafriend]

Is it possible to Undo an Event? say Afterinsert.
Actually I am checking for Duplicate Records in afterinsert of every form.


Function Check_Duplicates() As Boolean
Dim tempset As Recordset
Dim resultset As Recordset
Dim filterstring As String


Set tempset = CodeContextObject.Recordset

tempset.MoveLast

For Each Field In tempset.Fields
If Field.OrdinalPosition = 0 Then 'Primary Key
filterstring = Field.Name & "<>" & tempset(Field.OrdinalPosition).Value
Else
Select Case VarType(tempset(Field.OrdinalPosition))
Case 1, 9, 8209 '8209 is OLE
'Skip
Case 3, 6, 11
'As it is
If filterstring = "" Then filterstring = Field.Name & "=" & _
tempset(Field.OrdinalPosition).Value _
Else filterstring = filterstring & " AND " & Field.Name & "=" & _
tempset(Field.OrdinalPosition).Value
Case 7
'Add #
If filterstring = "" Then filterstring = Field.Name & "=#" & _
tempset(Field.OrdinalPosition).Value & "#" _
Else filterstring = filterstring & " AND " & Field.Name & "=#" & _
tempset(Field.OrdinalPosition).Value & "#"
Case 8
'Add quotes
If filterstring = "" Then filterstring = Field.Name & "='" & _
tempset(Field.OrdinalPosition).Value & "'" _
Else filterstring = filterstring & " AND " & Field.Name & "='" & _
tempset(Field.OrdinalPosition).Value & "'"
End Select
End If
Next Field

tempset.Filter = filterstring

Set resultset = tempset.OpenRecordset()

If Not (resultset.BOF And resultset.EOF) Then
'Duplicacy Occurs
'Here I wanna Undo that Event which triggered this code. How?
End If
End Function


How to Retain that record in the post 'Beforeinsert' Stage?

Edited 21-Jul-10 by geekgirlau: Reason: insert vba tags

[geekgirlau]

In the Before Update event for your form, check the value of the function:


Sub Form_BeforeUpdate(Cancel As Integer)
If Check_Duplicates() = True Then
Cancel = True
Else
' ....
End If
End Sub


Function Check_Duplicates() As Boolean
Dim tempset As Recordset
Dim resultset As Recordset
Dim filterstring As String


Set tempset = CodeContextObject.Recordset

tempset.MoveLast

For Each Field In tempset.Fields
If Field.OrdinalPosition = 0 Then 'Primary Key
filterstring = Field.Name & "<>" & tempset(Field.OrdinalPosition).Value
Else
Select Case VarType(tempset(Field.OrdinalPosition))
Case 1, 9, 8209 '8209 is OLE
'Skip
Case 3, 6, 11
'As it is
If filterstring = "" Then
filterstring = Field.Name & "=" & tempset(Field.OrdinalPosition).Value
Else
filterstring = filterstring & " AND " & Field.Name & "=" _
& tempset(Field.OrdinalPosition).Value
End If
Case 7
'Add #
If filterstring = "" Then
filterstring = Field.Name & "=#" & tempset(Field.OrdinalPosition).Value & "#"
Else
filterstring = filterstring & " AND " & Field.Name & "=#" _
& tempset(Field.OrdinalPosition).Value & "#"
End If
Case 8
'Add quotes
If filterstring = "" Then
filterstring = Field.Name & "='" & tempset(Field.OrdinalPosition).Value & "'"
Else
filterstring = filterstring & " AND " & Field.Name & "='" _
& tempset(Field.OrdinalPosition).Value & "'"
End If
End Select
End If
Next Field

tempset.Filter = filterstring

Set resultset = tempset.OpenRecordset()

If Not (resultset.BOF And resultset.EOF) Then
Check_Duplicates = True
End If
End Function

[prabhafriend]

Hey Geek Girl. Sorry yaar. I thought the AfterUpdate event will be triggered when the user updates each and every field but its not. This is acutally what i want. The fact is Both AfterInsert and AfterUpdate are record based not field based. Am I right?

One Question: If you rate my function How much star it gets out of 5. I am just curious about state of skill :)

[prabhafriend]

GeekGirl,
As you suggested, I called the procedure on BeforeUpdate instead of afterInsert. Currently I am getting the following error on this line of Code 'tempset.MoveLast'.

Run-time Error '3426': This action was cancelled by an associated object.

I know we should not check Last record in the recordset if we check in BeforeUpdate because the record has not been updated in the table and hence not in the recordset. After a brief analysis I believe we have to two options here.
1. Checking the Duplicates Before Update/Insert
2.Checking the Duplicates After Update/Insert
Checking the Duplicates Before Update/Insert:
Since the record has not been Inserted/Updated in the table it's obvious it will not be in the recordset. So we have to compare the values in the controls to check Duplicates. If we start checking the values in controls, then it's really going to be a tough task i believe. First we have to find the controls which are bound and it goes so on.
Checking the Duplicates After Update/Insert:
I believe it is the easiest way If there is a way to just reverse this event. Since the record is updated in the recordset, We just have to check the values of all the fields in the recordset for Duplicates. If suppose there is a way to reverse this event, then the work is done. Since Unfortunately we don't have a way to undo the afterUpdate event, I need to find next easiest and perfect workaround for this. If we change the type of the recordset before deleting the record from the table still we will it reflect in the form. Simply said, I want to delete the record from the table if it's a duplicate but want to retain the values in the form's controls. Possible?
Simply said, Is there any way to make a bound form temporarily unbound (deleting the record from the table in the mean time) and bind the values of that old record (deleted in table) again in a newrecord state. Possible?

[geekgirlau]

Personally I would prefer to check for a duplicate before you save the record. However you are not really checking for a duplicate, as checking for a record that matches the values on your form.

You could do this fairly simply with a query. I don't know which fields you need to evaluate in order to determine a match, but basically you add each relevant field from the table, and the criteria is Forms!MyForm!Mycontrol (being the control containing the value for that field).

To check for a potential duplicate, you open a recordset based on your query - if it contains any records, what you have on your form must therefore be a duplicate to a record that already exists in your table.

[geekgirlau]

Oh, and with the rating I don't know what the general view is, but personally I rate solutions - if someone presents an answer that I wouldn't have thought of or that I think I might be able to use in future, I give it a rating (it's all about me you know!).

One suggestion - I'm not a big fan of having "If" in a single line. I find it's harder to read the logic that way, and most members on the board HATE having to scroll to the right.