Pls Help, Double booking problem.

[Martin Griffiths]

could someone please help me, i am having a problem trying to get rid of double bookings in the attached database.

This database is for an assignment for my students, and unfortunately i had got the point now that i have got incredbily stupid and cannot see a solution.

I want to make sure that a client cannot double book an instructor and a car on a specific time or date...

Obviously i would also want to make sure that if one client has booked a specific car or instructor no other client would be able to book it, in the same time and date slot.. All lessons are 1 hour long

for some reason i have to make sure that a client can book a specific car...
This unit is being taught alongside another unit by another teacher, who does not see the complexities of the problem, and insists that this complexity is in the database, however they do not know the solution either.

I have tried creating different types of primary key combinations, but that does not work..

Any ideas or suggestions would be greatly appreciated

 

[pbaldy]

The database didn't make it Martin. Make sure you compact/repair before zipping, and that the zip file is under the limit.

 

[Martin Griffiths]

Here is the database

must have been a network issue, i was sure i had uploaded it,
i did zip, and compact and repair the database

 

[RuralGuy]

While we are studying your db, here's a link to read: The Evils of Lookup Fields in Tables

 

[Martin Griffiths]

hadn't realised that lookups were such a problem

hadn't realised that lookups were such a problem.
Not all the fields are lookup, just instructor_id and car_id. The time lookup is just a glorified value list.

i would also like to make sure that a client could not double book themselves with another instructor or car... no doubt some muppet would try.

The plan is that in a future unit the students would gain the experience of converting it to website/sql server.

For now though just sorting this double booking problem is enough if possible.

 

[Martin Griffiths]

been fiddling with it

After reading around this and other forums i have come up with an idea.

' checking for set of double bookings in turn using elseif
' checking instructor for double bookings
If DCount("*", "[tbl_bookings]", "[instructor_id] = " & Me.[instructor_id] & "[Date] = " & Me.[Date] & " [Time] = " & Me.[Time]) > 0 Then
  MsgBox "Double Booked"
' checking car for double bookings
ElseIf DCount("*", "[tbl_bookings]", "[Car_id] = " & Me.[Car_id] & "[Date] = " & Me.[Date] & " [Time] = " & Me.[Time]) > 0 Then
  MsgBox "Double Booked"
' checking client for double bookings
ElseIf DCount("*", "[tbl_bookings]", "[Client_id] = " & Me.[Client_id] & "[Date] = " & Me.[Date] & " [Time] = " & Me.[Time]) > 0 Then
  MsgBox "Double Booked"
End If

am i going down the right road

and i thought i would attach this as an event on the form.
however i get the following error message.

run time error 3075
syntax error (missing operator) in query expression '[instructor_id]=0[Date]=[Time]=0'

what am i doing wrong?

 

[RuralGuy]

Let's start here:

If DCount("*", "[tbl_bookings]", _
"[instructor_id] = " & Me.[instructor_id] & _
"[COLOR="Red"] AND[/COLOR] [Date] = " & Me.[Date] & _
"[COLOR="Red"] AND[/COLOR] [Time] = " & Me.[Time]) > 0 Then

Next you are using reserved words for your field names (Date, Time).
Problem names and reserved words in Access

 

[pbaldy]

Sorry, looks like both of us lost track of this thread. In addition to RG's comments, here's a good reference on the syntax for DCounts:

http://www.mvps.org/access/general/gen0018.htm

Pay attention to what the different data types need to be surrounded by (' or # or nothing), as that's what trips up most people.

 

[Martin Griffiths]

Date and time

good point about the date and time fields i'll change and see what differences that makes,

could it be as simple as that?

if i am being honest i quickly knocked up this database in a few spare minutes to see how simple it would be to make...and to prove a point about a problematic ERD that another teacher wanted to use to teach a parallel unit. i have spent alot of time since then staring and fiddling with fields to try and solve this double booking issue that i hadn't even noticed the DATE and TIME problem being a problem.
Now if i had been doing this assignment all those moons ago when i was at uni i would not have done such a stupid thing.

 

[RuralGuy]

Paul's reference for the delimiters on DateTime values "#" is more than likely your problem. Thanks for the catch Paul.

 

[pbaldy]

I got your back, my friend. I hope that storm that hammered us last week didn't hit you too hard.

 

[Martin Griffiths]

sorry guys(and girls?) but you have totally lost me,

i have renamed time to timebkn, and date is now datebkn.
but it still does not work.
this stuff about the delimters has totally confused me.

the more i think about this the more my brain forgets what i know about vba and access.

can someone give me a "dummies guide" to how to fix it?

 

[pbaldy]

Try this (change the control names if they've changed too):

If DCount("*", "[tbl_bookings]", _
"[instructor_id] = " & Me.[instructor_id] & _
" AND [datebkn] = #" & Me.[Date] & _
"# AND [timebkn] = " & Me.[Time]) > 0 Then

If that doesn't work, what's the error?

 

[Martin Griffiths]

well, i have inserted that code without the "_" because that would not work,
and i have inserted the code into the pickuppoint field, by putting it here its assumes that the user has inputted information into all the relevant fields, therefore meaning it can actually error check, if the code tries to check for any errors whilst any fields are empty i get a syntax error.

I think i can add some error checking on this later on to make sure the fields do have something in them.... which [event] is best to use , so that i can attach the code to the right place.

however.. the code thinks that every record is a double booking?

anyone who downloads the new attached file will see i have commented out a nested if, this is in-situ because i want to be able to check for a variety of double bookings not just the instructor.
What am i doing wrong? what obvious thing am i missing here?

 

[pbaldy]

I tested with Jeremy,10:00,11/11/07 (a known duplicate) and got the message. I switched to 11/11/08 and did not get the message, as I would expect. Are you sure you didn't test with a combination that another client had? If you still experience the problem, what data did you enter that you get the message and you think you shouldn't?

 

[unclejoe]

I think you’re confused about the IfThen statement. In your code, the IFThen is always True. Thus, the message always appears.

First thing you need to do is the controls naming convention. You had renamed the Field/Column your Table, that’s a start. But you have not rename your controls.

I have renamed the controls From “Date” to “txtDate” and “Time” to “txtTime”. Every thing is now working fine but you made a mistake about this “DCount”

If DCount("*", "[tbl_bookings]", "[instructor_id] = " & Me.[instructor_id] & " AND [datebkn] = #" & Me.[datebkn] & "# AND [timebkn] = " & Me.[Timebkn]) > 0 Then

The code above will always be True. You need to removed the “ > 0”.

According to your code, you want to count the number of bookings that matches your filter. Here’s what I have edit that’s working.

Private Sub PickupPoint_Enter()
Dim cnt As Integer
cnt = DCount("Booking_id", "tbl_bookings", "instructor_id = " & Me.instructor_id & " AND Datebkn = #" & Me.txtDate & "# AND Timebkn = " & Me.txtTime)
Debug.Print cnt
If DCount("[Booking_id]", "[tbl_bookings]", "[instructor_id] = " & Me.instructor_id & " AND [datebkn] = #" & Me.txtDate & "# AND [timebkn] = " & Me.txtTime) > 1 Then
MsgBox "*Warning* Instructor is double booked"
'ElseIf DCount("*", "[tbl_bookings]", "[car_id] = " & Me.[Car_id] & " AND [datebkn] = #" & Me.[datebkn] & "# AND [timebkn] = " & Me.[Timebkn]) > 0 Then
'MsgBox "*Warning* This Car is double booked"
'ElseIf DCount("*", "[tbl_bookings]", "[client_id] = " & Me.[Client_id] & " AND [datebkn] = #" & Me.[datebkn] & "# AND [timebkn] = " & Me.[Timebkn]) > 0 Then
'MsgBox "*Warning* The Client has double booked"
End If
End Sub

Note that the “Dcount” is always 1 or more than 1. I have modified it to check for more than 1 count. So that the message will only appear when there is more than 1.

However, I must warn you that the code check the current table records only, not the newly inserted record in the subform because the record is yet to be written into the table. And another thing, if the user click on the new row in the field “PickupPoint”, an error “3075” will appear; you might want to trap the error.

If you want something else, please post again on what you’re attempting so that others might be able to help out.

well, i have inserted that code without the "_" because that would not work,
and i have inserted the code into the pickuppoint field, by putting it here its assumes that the user has inputted information into all the relevant fields, therefore meaning it can actually error check, if the code tries to check for any errors whilst any fields are empty i get a syntax error.

I think i can add some error checking on this later on to make sure the fields do have something in them.... which [event] is best to use , so that i can attach the code to the right place.

however.. the code thinks that every record is a double booking?

anyone who downloads the new attached file will see i have commented out a nested if, this is in-situ because i want to be able to check for a variety of double bookings not just the instructor.
What am i doing wrong? what obvious thing am i missing here?

 

[RuralGuy]

UncleJoe:
Why do you believe the 1st DCount() will always be > 0?

 

[Martin Griffiths]

thanks guys (and any girls)

need to respond to a few points first,

my first error message was created because like an idiot, i was testing it on a record that had already existed, thus dcount would =1

when creating a new record, this works fine. ish
i have put in some code which i think makes sure that all appropiate fields contain relevant data.

thus..

Private Sub PickupPoint_Enter()
'checks to see if any fields contain empty data,
'if so it exits the procedure, or else it checks for double bookings
If Me.[instructor_id] = 0 Or Me.[datebkn] = 0 Or Me.[timebkn] = "" Or Me.[Client_id] = 0 Or Me.[Car_id] = 0 Then
MsgBox "Please make sure that all fields contain data before progressing"
Exit Sub
End If

'checks to see if instructor is double booked, and then if so, undoes the changes
If DCount("*", "[tbl_bookings]", "[instructor_id] = " & Me.[instructor_id] & " AND [datebkn] = #" & Me.[datebkn] & "# AND [timebkn] = " & Me.[timebkn]) > 0 Then
Me.instructor_id.Undo
MsgBox "*Warning* Instructor is either double booked or you are editing an existing booking"
End If

'checks to see if car is double booked, and then if so, undoes the changes
If DCount("*", "[tbl_bookings]", "[car_id] = " & Me.[Car_id] & " AND [datebkn] = #" & Me.[datebkn] & "# AND [timebkn] = " & Me.[timebkn]) > 0 Then
Me.Car_id.Undo
MsgBox "*Warning* This Car is double booked or you are editing an existing booking"
End If

'checks to see if client is double booked, and then if so, undoes the changes
If DCount("*", "[tbl_bookings]", "[client_id] = " & Me.[Client_id] & " AND [datebkn] = #" & Me.[datebkn] & "# AND [timebkn] = " & Me.[timebkn]) > 0 Then
Me.Client_id.Undo
MsgBox "*Warning* The Client has double booked or you are editing an existing booking"
End If

End Sub

how would i make this run regardless of where in the record the user is.
i can forsee problems attaching it to only the one text box.

in my code i am experimenting with the concept of undoing the entry, have i done it right.

why would i want dcount to be >1 not >0 what difference would that make?

would this go somewhere in solving the additional problem that now, a record cannot be edited without stating that a double booking exists, or checking properly that a double booking has taken place.

somewhere in my head that paragraph does make sense.

again i would like thank everyone who is helping me out in blowing away these cobwebs. Going through all this stuff is like having an epiphany.

 

[pbaldy]

I would try the before update event of the form/subform where the data is being entered. Add:

Cancel = True

if a record fails your test, which should stop the update from occurring. I think both RG and I disagree with unclejoe about the count > 1 thing.

 

[Martin Griffiths]

being thick again

i think i am being thick again.

my subform is in datasheet view, so all bookings are shown for that client.
so where would the before update be attached to?

I am not sure what the difference between the >0 and >1 will be though.

i'll do it and give feedback.