Duplicate Problem

[Guest]

Hi,

I tried to prevent this problem several times but seem to get nowhere. I'm
no good at VB but duplicates are becomming a real problem for us. We
basically have a form to update staff's training records.

Users need to choose the staff member from a combo box, and then select the
course from the course name lookup field before then entering date attended
etc. Obviously no staff member should attend the same course twice.

Basically we would like a message box to pop up if the new record is a
probable duplicate informing the user and offering to go to the existing
record. I've been trying to sort this problem on and off for over a month.

Please help

Ian

PS My VB skills are like Father Christmas

 

[Douglas J Steele]

What do your tables look like? This is a classic many-to-many situation: you
should have one table for staff members, one table for course details, and
an intersection table that allows you to link staff members to courses. That
intersection table should have a primary key on it, representing the staff
id and the course id. With that set up, you can't store the same course
twice.

 

[Guest]

Hi,

Staff Members are stored in the Staff Table and Course Details in the Course
Table. The link is the Training table and the primary key for that table is
an autonumber field called attened number. Is there another way to prevent
duplicates only changing the primary key now would be murder.

Ian

 

[Douglas J Steele]

Leave the Autonumber field (although there's really no need for it), but
create a Unique Index on the combination of Staff Id and Course Id.

 

[Guest]

How would I go about this with records in the table?
Will I still be able to include a useful pop up message?

 

[Douglas J Steele]

Yes, you'll have problems if you've currently got duplicates in the table.
You'll have to clean up your data first.

Trying to insert a duplicate record will raise an error. If you don't like
the default error message, you should be able to trap the error that's
raised and substitute your own.

 

[Guest]

I can clean the table up thats no prblem I mean how would I actually go about
creating this field?

Sorry how/where would I trap the message? Would this be the validation text
accompanying the new field?

I do apologise I really am a novice with Access

Ian

 

[Douglas J Steele]

You need to go into the table design mode for your Training table.

When you're there, choose Indexes from the View menu to open up the Indexes
dialog.

Pick a name (any name: it's not critical) for the Index Name, and type it on
the first empty line. In the Field Name beside that, select the name of the
Staff Id field from the combo box. You can leave the Sort Order to the
default Ascending. Now, on the row below that, leave the Index Name blank,
and select the name of the Course Id field. (again, you can leave the Sort
Order as the default Ascending).

In the boxes at the bottom of the dialog, leave Primary No (since you
already have a Primary Key), but set Unique to Yes. Leave Ignore Nulls as
the default No.

That's it. Save your table.

Now, try to enter a duplicate. See whether that meets your needs.

 

[Guest]

Awesome thanks very much mate I've been trying to solve this probolem for
ages. It was nice and easy as well the other methods involved VB. However
there is one thing I was wondering if it was possible to change the message
displayed to users, in order to make it more helpful?

Ian