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

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

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

While we are studying your db, here's a link to read: The Evils of Lookup Fields in Tables (http://www.mvps.org/access/lookupfields.htm)

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.

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?

Let's start here:
If DCount("*", "[tbl_bookings]", _
"[instructor_id] = " & Me.[instructor_id] & _
" AND [Date] = " & Me.[Date] & _
" AND [Time] = " & Me.[Time]) > 0 ThenNext you are using reserved words for your field names (Date, Time).
Problem names and reserved words in Access (http://www.allenbrowne.com/AppIssueBadWord.html)

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.

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.

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

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

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?

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?

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?

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?

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?

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

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.

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.

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.

The before update event of that subform, since that's where you're adding new records.

Let's ignore the >0 or >1 thing for now. You have it correct with >0.

Because the Dcount output value is always "-1". Meaning, the IfThen will always be True.

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

Because the Dcount output value is always "-1". Meaning, the IfThen will always be True.

Maybe we're not understanding what you're trying to say, but a DCount will count the records the match the criteria. If there are none, it will return 0. Otherwise it will return the number of records found. I don't think it can ever return -1. The OP is using it correctly, testing for >0. If it returns >0, then there's a duplicate to what's being tested; if not, then there is no duplicate.

Yes, Paul, you are right.

Accord to the code "Dcount(.....) > 0", where the OP's intentions of searching for a empty record.

But the IfThen statement reads the "Dcount" as "-1". This is where the confusion lies.

The more appropriate code would be:

Dim Cnt as Long

Cnt = DCount( "*", "Table", "WhereCondition")

If Cnt = 0 Then
'do nothing
Else
MsgBox "Dulplicate Record"
End If

Maybe we're not understanding what you're trying to say, but a DCount will count the records the match the criteria. If there are none, it will return 0. Otherwise it will return the number of records found. I don't think it can ever return -1. The OP is using it correctly, testing for >0. If it returns >0, then there's a duplicate to what's being tested; if not, then there is no duplicate.

I'm going to respectfully disagree. I've used tests like:

If DCount(...) > 0 Then

many times, and it works flawlessly. The DCount will not be evaluated as "-1"; it will be evaluated for whatever count it returns, and that value will be combined with the > 0 part, and THAT will be evaluated as True (-1) or False (0) for the If/Then test. In your example, there's no functional difference between

Cnt = DCount( "*", "Table", "WhereCondition")
If Cnt = 0 Then

and

If DCount( "*", "Table", "WhereCondition") = 0 Then

The results of both will be the same.

Yes, you are right again.

With this code "If DCount( "*", "Table", "WhereCondition") = 0 Then". You may get away with it.

But I'll never take any chances. I'll also test on my end. The "=0" did not work on my end because I have added a same record "Jeremy" with date and time matching. It skip the IfThen line.

I'm going to respectfully disagree. I've used tests like:

If DCount(...) > 0 Then

many times, and it works flawlessly. The DCount will not be evaluated as "-1"; it will be evaluated for whatever count it returns, and that value will be combined with the > 0 part, and THAT will be evaluated as True (-1) or False (0) for the If/Then test. In your example, there's no functional difference between

Cnt = DCount( "*", "Table", "WhereCondition")
If Cnt = 0 Then

and

If DCount( "*", "Table", "WhereCondition") = 0 Then

The results of both will be the same.

well it looks like i have opened a can of worms here, but i happy to say that it now works perfectly.

my two pennenth worth on the >0 >1 issue now that i have it working in practice is quite simple.

>0 will work if i am creating a new record, but not if i am amended a record,
however the >1 will let me amend, but not let me create a duplicate..

well i say not let me.. i mean inform me that a double booking has taken place.

therefore >1 wins :) ( on this occasion)

i thank you all again for your help

Glad you got it working Martin. Thanks for posting back with your success.

I was hoping that none of veterans would reply on this and that you’ll give me the input regarding the “>0” on why did you need this. I was wrong on both counts! The purpose was to check whether you understood this.

Since the outcome of the DCount is rated “Low” to “Serious”, I’ll try to explain it anyway for the benefit of others whom might encounter this.

Base on the assumption of the OP’s event for checking an existing record.

1. With a DCount(…..) > 0, the value is “1”. Therefore, the IfThen equation is “True”. A wrong message shows up. But not if there is no duplicate.

Something like this would be more appropriate.

If DCount(…) > 0 Then
msgbox “show duplicate”
Else
‘do nothing here because no duplicate
End If


2. With a DCount(….) = 0, although the DCount value is “1”, the IfThen is now “False”. Where the OP message supposed the show a message duplicate. However, if there is no duplicate, the message will not show up. Still not what the OP intended. Although the DCount value is “0” is correct if there was no duplicate.

Something like this would be more appropriate.

If DCount(…) = 0 Then
‘Do nothing here because no duplicate
Else
MsgBox “show duplicate”
End If

Although Paul was right, he’s getting away with it. (still confused yet?)

To clear things up, the following formula will be use to demonstrate that the Dcount became a mathematical equation.

Given: (duplicate record)
X = dcount(..) = 0
X = 1 = 0
X = False

VBA
X = If Dcount() = 0 Then
X = if 1=0 then
X = if false then

Given: (no duplicate record)
X = dcount() = 0
X = 0 = 0
X = True

VBA
X = if dcount = 0 then
X = if 0 = 0 then
X = if True Then

Because DCount is an undeclared variable in the IfThen statement, VBA evaluate it as a mathematical equation first before the IfThen.

Where as a declared variable will be evaluate as a “True” or “False” in the IfThen statement.

Example.

Dim cnt as Integer

cnt = Dcount(“*”, “Table”. “WhereCondition”)

If cnt = 0 Then
Msgbox “no duplicate exist”
else
msgbox “duplicate exist”
end if

Is there a way to proof that the “Dcount” is a Boolean variable?

Take the codes above and change the IfThen statements by inserting a “Not” in front of “Dcount” like..

If Not Dcount(..) = 0 Then

This will reversed the equation of the IfThen statement.

Or just simply remove the math equation

If Not DCount(…) Then
Or
If Dcount(..) Then

You need reverse the output value to the IfThen statement according to what you wish for.

Since both of these are Boolean variable and you’re looking for a “True” or “False” condition, there is no need for the math equation. (Note that there are certain cases where you don’t need “Not” because in a Dcount output = 0, the IfThen statement is treated as “False”. If Dcount output is 1 or more. It is “True”. Note that I’m not referring to the mathematical equation output.)

well it looks like i have opened a can of worms here, but i happy to say that it now works perfectly.
my two pennenth worth on the >0 >1 issue now that i have it working in practice is quite simple.
>0 will work if i am creating a new record, but not if i am amended a record,
however the >1 will let me amend, but not let me create a duplicate..
well i say not let me.. i mean inform me that a double booking has taken place.
therefore >1 wins :) ( on this occasion)
i thank you all again for your help