you can simply set a unique index on the field in the table: open the table
in Design view. look at the list of field properties in the bottom half of
the window. set the Indexed property to Yes (No Duplicates), from the
droplist of options. close and save the table.
a default message will pop up if the user attempts to save duplicate data in
a record, in the form. it is a pretty ugly message though; if you want to
substitute your own, you'll have to "trap" the error that calls the message.
to do that, open the form in Design view. in the Properties box, click on
the Event tab and scroll to the OnError event line. double click on the
line, a default value of [Event Procedure] will appear. click on the Build
button at the right (...) and the VBA Editor window will open, with the
cursor between the following two text lines, as
Private Sub Form_Error(DataErr As Integer, Response As Integer)
<your cursor will be here>
End Sub
*without moving the cursor*, paste in the following code, as
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "The record already exists. Please modify or delete your
entry."
End If
so the completed procedure will look like this, as
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "The record already exists. Please modify or delete your
entry."
End If
End Sub
close the Design window, then save and close the form, and try entering a
duplicate value. you should see a message box with the above text in it.
hth
Phil said:
HI,
I have a continious form that gets its data from a table with one field.
Duplicates not allowed. If the user enters new data that is a duplicate I
want a simple error message that states the record already exists. The user
can then modify or delete the entry made. The table is Item and the field is
Description. I did this in the past (long past) but can't remember what I
did.
Thanks in advance for any help.
