Prevent Duplicates

[The Last Day]

I'm making a form in Access which allows someone to set up a doctor's appointment. They can enter a date, time, their first and last name, and the doctor's last name. I need to prevent duplicate appointments (i.e., same time on the same date for the same doctor). How would I do this with VBA?

Thanks for any assistance.

 

[jsv2002]

You need a select query or Dcount to check your table first but would need to know more about your table structures etc.. before offering specific advise

 

[RuralGuy]

Here's a link that might be helpful.

 

[The Last Day]

You need a select query or Dcount to check your table first but would need to know more about your table structures etc.. before offering specific advise

There isn't a way to check using a module with the BeforeUpdate event?

 

[jsv2002]

The select query or Dcount would be in your before update event..

 

[The Last Day]

Woops. I'm a newbie.

Can you tell me more specifically what you need to see?

 

[evanscamman]

You could also setup a multi-field index in your table and disallow duplicates.

Evan

 

[The Last Day]

How do you do that?...

 

[evanscamman]

Open your table in design view and choose View:Indexes from the menu. Add all three fields but only give the 1st field and index name.
This creates an index based on all 3 fields. Then you can set the properties for the index to - Allow duplicates: No

The Access Help is also very helpful if you get stuck.

Evan
​

 

[The Last Day]

Open your table in design view and choose View:Indexes from the menu. Add all three fields but only give the 1st field and index name.
This creates an index based on all 3 fields. Then you can set the properties for the index to - Allow duplicates: No

The Access Help is also very helpful if you get stuck.

Evan
​

Ok, I see what you're talking about but I don't want each one to be No Duplicates separately. I only want it to reject duplicates of date and time and doctor if all three are duplicates... Can I still do that your way or do I need a module?

 

[The Last Day]

Ok, my fields are ApptID (Autonum), ApptDate, ApptTime, DoctorID, PatientID. I need it so it won't allow duplicates in date, time, and doctorID collectively. I don't know if this helps, but just trying to clarify.

 

[The Last Day]

Nevermind, looks like I figured it out on my own. Thanks!

 

[evanscamman]

If you list all three fields in your index, but remove the index name from the second and third field, they will become part of a multi-field index.

Evan

 

[LPurvis]

Thought it may seem more intimidating to some, a tiny SQL DDL statement can be easier than interpreting how yo use the index dialog to achieve the same (unless Bob Larson has a pictorial example of course... ah yes thought as much, here)

The query alternative to create your index is to just execute
CREATE UNIQUE INDEX AnyIndexName ON tblTableName (Field1, Field2, Field3)
your specific instance being something like:
CREATE UNIQUE INDEX IdxUniqueAppt ON tblAppointments (ApptDate, ApptTime, DoctorID)

Of course, this may not be enough for you. You might want to check that there is no other appointment within 5 minutes time (or whatever interval).
Though coding it would allow for more flexibility in this way - the index will perform better. Especially when your number of records grows substantial - by comparison the index is instant against a lengthy lookup.

Cheers.