Can this be done 4 composite - Warn But Allow Duplicates in Microsoft Access


	Back to Warn But Allow Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Can this be done 4 composite

Kerry Smyth

3 years ago

Hi, I have a db with a join table for a many-to-many relationship. I don't want users entering duplicate records on the join table. I have already created a composite key as I've learned from one of your old videos and that works to prevent the user from doing that, however I like the custom message box and instructions I can put it it to help my users.

Some context: I have list of parts that go on equipment, so the PartT & EquipmentT have a many-to-many relationship. I made a composite key on the PartXEquipmentT called PartByEquipment that includes PartID and EquipmentID to prevent users from entering the same part twice on the same equipment. I really do not like the message box that Access puts out there. Any tips to do something similar for a composite duplication would be great! Thanks!

Scott Axton @Reply

3 years ago

Yes it can be done, though it takes a fair amount of programming ahead of time.

First you have to intercept the error before Access does, then you provide your own message instead.
Richard introduces this toward the end of Access Developer 11 with his Universal Dialog Box, continuing on in Access Developer 12.

I'm not sure how far along you are in the Expert series but keep on going. You will get there - just a bit more than your skill set right now based on the classes I see.

	Scott Axton @Reply 3 years ago
Just to add to the above, the lowly MsgBox is actually quite powerful when used as a function. Be sure to watch the MsgBox video to see how you can customize the message that is presented.

Kerry Smyth OP @Reply

3 years ago

Thanks Scott! Yeah I have finished Expert and eager to move up and get to the Developer classes very soon. I'm OK with using a msgbox instead of going all the way with a dialog box, but the trick will be to catch the error before Access. I mean, if you stop and think about it, if I can catch it before Access, I could just eliminate the need for a composite key at the table level.

Correct me if I'm wrong, but at the form level, I could do a before update event that would capture the error before the table does, introduce a msgbox to explain to the user what they did wrong, then "cancel" to escape from the record. My skill allows me to do that much, but where is gets beyond my expertise is checking two different boxes against all the data in the junction table to trigger the msgbox.

	Scott Axton @Reply 3 years ago
Kerry your though process is pretty spot on. Just keep doing little bits and you will have it down in no time.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Warn But Allow.

Next Unseen

New Feature: Comment Live View