Duplicate Data

[student90210]

I have a form in Access which has event date and then also an event time slot such as "Morning, Afternoon, Evening" . I need to make it so that you cannot have a duplicate time booking, i assume i will need to code some VBA but if anyone could tell me what it would be much appreciated. Thanks

[CaptainD]

Look into using "Not In" in your where clause.

Select somedata from sometable where somefield NOT IN(Select DataYouDontWant)

[student90210]

thanks, care to elaborate? not exactly a VB expert....

[CaptainD]

Not really VB or VBA but in your SQL query that will pull the information from your database. If you post how your table are layed out we can assist you with how to create the select statement to get the information you are looking for.

Using "Not In(Select statement)" is a way to filter the information you want. In your case, you don't want information that matches what you are setting up or open dates in a select time span. so in your case, it would be "Select Dates and times from YourTable where Date and time not in (select ThisDate, ThisTime)" or something along those lines.

[student90210]

thanks, i have two tables. THe first of which is just contact information and is named CustomerDetails, it is autoincrementing and that number is linked into the BookingDetails Table. THis contains the fields Event Date, and Event Time Slot which only allows the values of Morning, Afternoon and Evening.

The form contains a date input field and a event slot which is a drop down box, there is then a save record button to add this data to the tables.

thanks alot

[cweiss]

This may be oversimplifying it, but in your BookingDetails, why not just make EventDate and EventTime the primary key? Then they'll just get a warning if they try to add a time slot that's already taken.

Otherwise, you could use validation or SQL like CaptainD suggested. Validation would just give them a warning message if they entered a value that wasn't permissible (i.e. Time slot is taken, please select another time); or you could restrict the values they can choose from by putting a SQL statement in the Data Source property.