Cancel insertion/delete last record

[Rose B]

I have a form and after the user has entered a certain number of fields I
want to test whether there is already a record in the underlying table that
has the same key attributes. I have added some code in the after update event
of the last field (which is one I know will be updated due to the nature of
the process) to test whether the record already exists. I then have a vbYesNo
test to check whether the user wishes to continue. If not, then I need to
delete the record from the underlying table (or stop it from being entered if
this is possible) and close the form. I have done everything except for
deleting the record. How can I do this?

 

[Allen Browne]

Use the BeforeUpdate event of the form (not controls) to test whether it is
a duplicate. This works at the last possible moment before the record is
saved, and it works regardless of what order the user entered the data.

The example below does nothing if the form is at an existing record where
the Surname and Amount fields are unchanged. If it's a new record that won't
be the case; if it's an existing record that's been changed such that it is
now a duplicate, it fires the warning. In the example, I've used a Text
field (Surname) and a Number field (Amount) so you can see how to create the
string for each. If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

You can add as many fields as you need to the criteria string (strWhere.)
The code is crafted so you can see whether your strWhere string is right
with the Debug.Print.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If (Me.Surname = Me.Surname.OldValue) AND _
(Me.Amount = Me.Amount.OldValue) Then
'do nothing
Else
strWhere = "(Surname = """ & Me.Surname & """) AND (Amount = " _
& Nz(Me.Amount, 0) & ")"
'Debug.Print strWhere
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
strMsg = "Same surname and amount as ID " & varResult & _
vbCrLf & "CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Duplicate") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

 

[Rose B]

This works except that not all the fields have been entered at the point
where I want to issue the challenge - is there any way that I can force an
update so that the Before Update form event comes in as part of an After
Update control event and then, if the user elects to continue processing can
continue (which is entering more information in the form and making a subform
visible)?

 

[Allen Browne]

Sure. In the AfterUpdate event of the last control you wish to check you can
force the update with:
Me.Dirty = False

 

[Rose B]

Thanks Allen - I often wondered what that command actually did