Preventing Duplicate Records Being Entered (Double Bookings)

[Hysteria86]

I have a form which allows the user to book rooms.

On this form, there are the following fields:

BookingID: (Autonumber)
RoomID: Text box
Time:Text Box
Date: Text Box
Class: Text Box
Teacher: Text Box

The form adds this information to the Booking table.

What I'm looking to do is prevent the user from double booking a room,like being able to check if the Room is already booked at that time and date, before the new information is added to the table and the room becomes double booked.

Basically this would be checking the RoomID, Time and Date fields, as everything else is irrelevant. What would be the best way to do this?

 

[grnzbra]

Assuming all booking periods are the same length you would want a select statement that looked like

strSQL = "SELECT * FROM Table WHERE RoomID = " & Me!RoomID & " AND Time = " & Me!Time & " AND Date = #" & Me!Date & "#"

Use that to open a recordset, rs.

Then you would have an If statement something like

If rs.EOF Then
rs.addnew
rs!RoomID = Me!RoomID
rs!Date = Me!Date
rs!Time = Me!Time
rs!Class = Me!Class
rs!Teacher = Me!Teacher
rs.Update
Else
MsgBox("Warning, Will Robinson, that room is already booked at that time. Try again")
End if

You may have to add single quotes around Me!RoomID if it is an alpha. Not sure what you'd have to do with Me!Time.

 

[Rickster57]

deja vu all over again

a result of a double post?

 

[Hysteria86]

a result of a double post?

I started another thread that might explain the problem a bit clearer (intended to delete this thread afterwards but couldn't find a function for it, if an admin wishes to then feel free), but thanks to everyone who has replied for the help