I have a form with a subform that Supervisors use to paste in phone
stats. When the Supervisor tries to paste in phone stats for an
agent and date that has already been entered, I want a message box to
appear and then to undo the paste for that day. So far I've been
able to get the code below to work somewhat. The code below works as
far as the message box displaying the correct duplicated agent/date.
A message box will appear for each duplicated record, which is
great!! However, when I add the me.undo I get Invalid use of Null
errors. Then when go to the table where the data is stored and change
the data types for the 2 cells referenced from number and date to text
and text... then the code starts to work somewhat (not all the proper
data appears/gets pasted). I get the message saying that the 1st date
is a duplicate, then I get a message asking me if I want to suppress
further error messages. when I say No, I don't get any more error
messages (when I should because I said no). Instead what I get is a
prompt asking if i'm sure I want to paste records. The problem is
that of the 3 records that should be pasted, only 1 actually shows up.

I am at a loss and keep going in circles. Can someone help me?

Here is the code I'm working with. I'm putting it on the before
update event on the subform. To find the duplicates I'm concatenating
2 text boxes on the data being pasted into the form and comparing that
with an index field of a query. The query looks at the same
PhoneStatEntry table where the index field concatenates the same 2
fields (extn and date).

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim PID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim ctl As Control


Set rsc = Me.RecordsetClone
PID = Me.Extn.Value & Me.Date.Value
stLinkCriteria = "[Extn]&[Date]=" & "'" & PID & "'"

If DCount("Index", "qryPhoneStatIndexed", _
stLinkCriteria) > 0 Then
'Message box warning of duplication
MsgBox "Warning: Phone Stats for " _
& Extn & " on " & Date & " have already been entered." _
& vbCr & vbCr & "", _
vbInformation, "Duplicate Information"
' Here is where I try to add Me.Undo
End If

Set rsc = Nothing

End Sub

I'v been working on this problem on an off for a few days. I decided
to look again through previous discussions about this related matter
and actually may have found something that will work. The discussion
is here:
http://groups.google.com/group/comp....5e6c8f5c5b3483

I'll try it in the morning and find out.

If anyone can help me with the code above, I would still appreciate
it.

On 04/01/2012 23:43:09, faraa wrote:
Try

DoCmd.CancelEvent

which should stop the update of the form

SendKeys SendKeys "{ESC}" On the BeforeUpdate of a Field will set that field
back to the original value SendKeys "{ESC}"

That worked like a charm Phil!! Thank you so much for the quick
response, you're awesome!