Checking Composite Key Duplicates

[AndyCash]

Mary... anyone... can someone help me?

I have used Mary's excellent sample code to check for the existence of a duplicate primary key and give the user an improved feedback (well, better than the Access default one!).

What I'd like to know, is that if the 'primary key' is in fact composite from three fields, e.g. appointment_date, appointment_time and gp_id, then how can we code a solution to detect a gp has been double-booked for example, and then return a custom message box?

Thank you so much in advance,

AndyCash

[nico5038]

For this we create in general a unique index consisting of these three fields. Thus Access will block dupes (and Null values). The raised error can be intercepted with an On ERROR and communicated to the user.
Another option is to check the combination after entering all fields with e.g. a DCOUNT() to check or 1 is returned..

Nic;o)

[NeoPa]

Andy, You're being unclear. Are you looking for a way to avoid duplicates, or a way to detect them?

The composite PK will ensure none is added. Detecting dodgy data within a dataset is another matter, and can be done using a SQL query. If you need further help then you'll need to be clearer about what details you're working with.

[AndyCash]

Hi NeoPa,

Sorry! What I am trying to do is this:

I am creating an appointments database for a local hairdressers. I have one table for 'Regular Customers' with CustID as PK. I have another table for 'Stylists' with StyID as PK. Both these PKs are foreign keys in an 'Appointments' table.

In the 'Appointments' table, to keep each appointment unique and prevent double bookings, I have ApptDate, ApptTime and StyID as composite keys.

This works fine in preventing a stylist from being booked in twice on the same day and time.

However, should the end user accidently book in a duplicate appointment, naturally Access returns its own error message. What I would like to learn, is what do I need to do within my database, to override this error message and return a custom error message that is more user friendly, e.g. "This stylist already has an appointment on this day and time. Please amend".

Thank you to anyone who can help give me the solution... I do appreciate it in advance.

AndyCash

[nico5038]

I would try to prevent his by showing e.g. a bar with the "booked" time(s) of the stylist.
Just mimic the Outlook agenda with the appointments visible per day/week...

Idea ?

Nic;o)

[NeoPa]

You could check out Custom Error Messages as one option.

Nico's idea wouldn't hurt too. It never hurts to give the operator more information, especially when this can help to avoid mishaps.

Lastly, you could check the dataset first (using DLookup()) in the Form_BeforeInsert() event procedure and throw up your own MsgBox() message if the composite key already exists.