On Thu, 23 Oct 2008 12:27:01 -0700, Gus wrote:

Yes. For user friendliness, you might want to use the BeforeUpdate event of
the textbox bound to FS_Agr_Num on the form; use DLookUp to see if the user's
entry already exists and cancel the addition with a warning message if so.

Great I will do and see if this works. Any suggestions as to the DLookup
code structure?

Something like this perhaps; the details may differ with your application and
your needs:

Private Sub FS_Agr_Num_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[FS_Agr_Num]", "YourTableName")) Then
MsgBox "This AGR Number has been used, please pick another", vbOKOnly
Cancel = True ' cancel the addition
Me!FS_Agr_Num.Undo ' erase the user's attempt
End If
End Sub

You could have code to jump to the record for the value entered, if you wish.
--

John W. Vinson [MVP]