Query or Code to prevent double booking

[NitroN1ck]

I am making a Database for a Driving School and I have a table that stores all of the bookings (Booking ID (PK), Driver ID, Instructor ID, Time of Meeting) and I have then made a Form that enables my client to easily enter the data.

Would I be able to set up a query, an If Statement, a lookup, something that would check to see if there isnt a double booking. For example, If Driver ID 1 is booked in with Instructor ID 1 at 10.45 and my client then tries to book in Driver ID 2 in with Instructor ID 1 at 10.45. I want Access to be able to prevent that just to make my clients life easier.

Hope that is clear, if not, please ask!

Thanks

 

[Mihail]

At table level, by using INDEXES window, create an index UNIQUE on fields DriverID and Time of Meeting.

Chenge the Time of Meeting name to TimeOfMeeting
The spaces in names will create troubles.

 

[pr2-eugin]

Yes you can do this. You can use a DCount function. I am not sure what your field names are, but something along the lines of..

If DCount("*", "yourTableName", "driverID = " & Me.driverID & " AND bookingTime = #" & Me.TimeOfBooking & "#") > 0 Then
    MsgBox "Cannot book, booking already exists", vbCritical
    Exit Sub
End If

 

[NitroN1ck]

At table level, by using INDEXES window, create an index UNIQUE on fields DriverID and Time of Meeting.

Chenge the Time of Meeting name to TimeOfMeeting
The spaces in names will create troubles.

Would setting the Driver ID as Unique mean that I wont be able to add another booking, on a different meeting for a driver?

But thanks for your response, Mihail, I will give it a go!

 

[NitroN1ck]

Yes you can do this. You can use a DCount function. I am not sure what your field names are, but something along the lines of..

If DCount("*", "yourTableName", "driverID = " & Me.driverID & " AND bookingTime = #" & Me.TimeOfBooking & "#") > 0 Then
    MsgBox "Cannot book, booking already exists", vbCritical
    Exit Sub
End If

Thank you for this! I will try that later on today. Incidentally my table is Booking_Tbl and the field names are "Booking ID" - AutoNumber, "Driver ID" - Number (Related to Driver ID in Drivers table, "Instructor ID" - Number (Related to Instructor ID in Instructors Table), "Meeting ID" - Number (Related to Meeting ID in Meeting Table) and "Time of Meeting" - Text

I assum I would just change the particular parts to suit my Database?

Many Thanks

 

[NitroN1ck]

Yes you can do this. You can use a DCount function. I am not sure what your field names are, but something along the lines of..

If DCount("*", "yourTableName", "driverID = " & Me.driverID & " AND bookingTime = #" & Me.TimeOfBooking & "#") > 0 Then
    MsgBox "Cannot book, booking already exists", vbCritical
    Exit Sub
End If

Sorry, me again!
I have tried to re work that code above but I get this error message. "The expression you entered contains invalid syntax. You may have entered an operand without an operator."

Access then proceeds to highlighting "DCount"

 

[pr2-eugin]

Could you please show the code you are using?

 

[NitroN1ck]

Could you please show the code you are using?

If DCount("*", "Booking_Tbl", "Driver ID" & Me.Driver ID & " AND Time of Meeting = #" & Me.Time Of Meeting & "#") > 0 Then
MsgBox "Cannot book, booking already exists", vbCritical
Exit Sub
End If

Perhaps I have manipulated that code wrongly...

 

[pr2-eugin]

Well you are missing the = after Driver ID, also if your column has spaces enclose them in Square brackets. Or even more, if you can change the field names, adhere to a proper naming convention..

If DCount("*", "Booking_Tbl", "[Driver ID] = " & Me.[Driver ID] & " AND [Time of Meeting] = #" & Me.[Time Of Meeting] & "#") > 0 Then
    MsgBox "Cannot book, booking already exists", vbCritical
    Exit Sub
End If

 

[NitroN1ck]

Well you are missing the = after Driver ID, also if your column has spaces enclose them in Square brackets. Or even more, if you can change the field names, adhere to a proper naming convention..

If DCount("*", "Booking_Tbl", "[Driver ID] = " & Me.[Driver ID] & " AND [Time of Meeting] = #" & Me.[Time Of Meeting] & "#") > 0 Then
    MsgBox "Cannot book, booking already exists", vbCritical
    Exit Sub
End If

Would I write that in the "Field" Row in Query Design View or somewhere else?

 

[pr2-eugin]

It could go in the Before Update event of the Form, or the Click of the button to add record to the table. If you are using before update use Cancel = True instead of

If this is your First VBA.. Check out.. http://www.baldyweb.com/FirstVBA.htm

 

[NitroN1ck]

It could go in the {Link Removed} event of the Form, or the Click of the button to add record to the table. If you are using before update use Cancel = True instead of

If this is your First VBA.. Check out.. {Link Removed}


I am making this in a query at the moment. Is this wrong?

 

[pr2-eugin]

It's not wrong, although setting up as suggested by Mihail would not allow you to enter any other records. So it would be better to validate the information on click of a button, then if the code passes the Test run the underlying Query..

 

[Mihail]

Would setting the Driver ID as Unique mean that I wont be able to add another booking, on a different meeting for a driver?

But thanks for your response, Mihail, I will give it a go!

I said to establish an index on BOTH field as once.
A SINGLE index that use BOTH fields.
Not an index for EACH field.