Just DLookup() the table to see if the value is there.
Private Sub Registration_AfterUpdate()
Dim strWhere As String
With Me.Registration
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration = """ & .Value & """"
If Not IsNull(DLookup("Registration", "MyTable", strWhere)) Then
MsgBox "Dupe!"
End If
End If
End With
End Sub
If you need more help with DLookup() see:
http://members.iinet.net.au/~allenbrowne/casu-07.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Peter" <peter@_nospam_exeter23.freeserve.co.uk> wrote in message
news:10igsru22jilaad@corp.supernews.com...[color=blue]
> My database has a text field "Registration" ~ it is the only Primary Key
> and
> no duplicates are possible. After you have entered all the fields for a
> new
> record you often find out that it is a duplicate when you try to save it.
> Has anyone got a code I can copy and use on the data entry form to produce
> a
> message box to warn that you are beginning to create a duplicate record.
> Maybe "After Update" on the "Registration Field".[/color]
