Simple error message for duplicate records

G

Guest

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.
 
T

tina

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
 
G

Guest

Thanks Tina,

That DataErr code is just what I was looking for.

Thanks again,
--
Phil


tina said:
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.
 
T

tina

you're welcome :)


Phil said:
Thanks Tina,

That DataErr code is just what I was looking for.

Thanks again,
--
Phil


tina said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top