No duplicate names

[Greg Ripper]

I have a list of contacts that I have made arranged by student id. If there
is a name in the list, I want to update the one currently there and not add
another.

I set the student ID as the primary key, but when I try to add the same name
twice, I do not get an error message. I want the user to be told they are
attemting to write the same id number twice. How do I do that?

RIP

 

[Michel Walsh]

Hi,


I assume the ID and the name are two different fields, and that you have
a primary key on the ID. Add a unique index on the field name so that the
name won't accept duplicated value too.



Hoping it may help,
Vanderghast, Access.

 

[George Nicholson]

Create an index on the Name field and set it its 'Unique' property to Yes.
One way to do this is in Design view, set the 'Indexed' property of the
field to "Yes, no duplicates"

 

[Jeff Boyce]

Greg

Are you sure you want to do that? Is there no chance that you'll have two
students with the same name (e.g., John Smith)?

Are you using a combo box to list the students (along with their ID#)? This
would help your users "pick" the right one, or see that there already is one
with the name in question. And you could add code to the combo box's
NotInList event to handle adding a new person.

Good luck

Jeff Boyce
<Access MVP>

 

[Greg Ripper]

I am actually using each student's unique ID# and this is what I am using
as the primary key. I don't want duplicates of this number, but when I do
try to add a duplicate it does not give me an error.

There are 2 forms one to add a new record and one to edit records already
created in tblcontacts.

The add form is where the problem is. It has a combobox that lists all the
students in the school from tblStudentData. Once that is selected, the user
can then enter data for the new record.

When they are finished typing they hit a button that closes the form. This
is where I would like them to get the error message about having a duplicate
primary key number, but it does not give me an error and closes the form
without adding the record.

What is going on here?


RIP

 

[Jeff Boyce]

Greg

If, as you indicated, you don't get an error when you "try to add a
duplicate" ID#, then your table doesn't have a "Unique, No Duplicates" index
set on that field. First, decide if your table will use that field as a
primary key and so set it (this will make it Unique, no Duplicates). If you
have another primary key you wish to keep, create an index on that field and
set its property to Unique, No Duplicates. Once you've done one of these,
you WILL get an error if you try to duplicate a student ID#.

The add form is where the problem is. It has a combobox that lists all the
students in the school from tblStudentData. Once that is selected, the user
can then enter data for the new record.

I don't understand. If the effort is to prevent duplication, why are you

When they are finished typing they hit a button that closes the form. This
is where I would like them to get the error message about having a duplicate
primary key number, but it does not give me an error and closes the form
without adding the record.

If the form isn't saving a record when you close, it IS having an error, but
perhaps not displaying it. Is there a chance you have code somewhere,
sometime, that turned the "warnings" off? Try this -- create a new macro,
with the SetWarnings - On. Run it. Now try your form again. Still no
error message when it fails to save?

Good luck

Jeff Boyce
<Access MVP>

 

[Greg Ripper]

I get an error if I try to enter the data directly into the table, but not
when I enter the data using the form.

Could the problem be that I am using a button that closes the form?

That has to be the problem, I made a new button and used the add new record
command for a button and then I get the error message.

So, how do I update the record and then close the form. I need the error
message.

Rip