Message Box on Duplicate Entry

[bwesenberg via AccessMonster.com]

I have a form that my users enter their Audits in. The Primary key is the
RECID field.
They enter a policy number and an effective date in this form as well.
What they want to happen is after they enter the policy number tab and enter
the effective date if the Policy Number and the Effictive date is a duplicate
record they want an warning message. However they want to be able to continue
to enter this record.
They just want to be made aware that it is a duplicate.

Can this be done. I know it will be code but if it can be done can you help
me out with the code.

Thanks

 

[Beetle]

You can use something like the following in the Before Update event of
the Effective Date control. It would be a good idea to put the code in
the Before Update event of the form also.

***********************************************
Dim strMsg As String
strMsg = "This is a duplicate Policy Number and Effective Date." & vbCrLf
strMsg = strMsg & "Do you want to continue?"

If DLookup("RecID", "TheTable", "[Policy Number]=" & Me![Policy Number] &
" And [Effective Date]=#" & Me![EffectiveDate] & "#")>0 Then
If MsgBox (strMsg, vbYesNo + vbInformation, "Duplicate Values")=vbNo Then
Cancel = True
Me.Undo
End If
End If

************************************************

You'll have to correct for line wrap. Also, the above assumes that your
Policy Number field is an integer data type. If it is text, then adjust that
part
of the DLookup like;

"[Policy Number]=""" & Me![Policy Number] & """ And [EffectiveDate]=#" &
Me![Effective Date] & "#"