Hey, My access database will be used to make computer reservations for an internet cafè. The user makes their reservation date by using a calendar control, they choose their start and end time, and they also choose the amount of computers they can book (no more than X). Would it be possible to create a query that will limit the user from overbooking the computers?

look up dcount. i think you can do that with it! in the forms beforeupdate do a test
if dcount(computersreserved)>= X then
Dont allow
else
allow

Thank you for your feedback. I wasn't aware of a dcount function, cheers.

DCount counts the amount of records instead of what is in them. Please help, I've been trying to solve this for days.

use a dlookup to get the values where computer reserve= yes and person=whomever
then count based on that

No joy, rainman... I've tried it so many ways, I can get queries working but I can't get the value into a form field... I think I'll be asking for the nearest sanitarium next. Appreciate it rainman.

Here is a screendump of what my form(s) look like, incase that helps with coming up with a solution. Cheers all.
http://img236.imageshack.us/img236/5749/screendumpgb4.jpg

I actually thought the DCount was the way to go, with the appropriate criteria. Aren't you looking to see how many computers are reserved already on that date/time? In any case, if you have a query that returns the correct result, simply use DLookup to get the value from the query. If you post the SQL of the working query, someone may be able to give you a better solution.

Since this post is in the Queries forum, I'll try to reply to the query-relevant part of the post.

Assuming a very basic table structure like the following:

tblReservations
---------------
ComputerID (Text)
ReservedFrom (Date/Time)
ReservedTo (Date/Time)


The following query will return a value greater than zero if the computer to be reserved is already booked during the requested time:

SELECT COUNT(*)
FROM tblReservations AS T1
WHERE T1.ComputerID = [ParamComputerID]
AND T1.ReservedFrom < [ParamReserveTo]
AND T1.ReservedTo > [ParamReserveFrom]


The following query will tell you the total number of computers booked during the requested time period:

SELECT COUNT(*)
FROM tblReservations AS T1
AND T1.ReservedFrom < [ParamReserveTo]
AND T1.ReservedTo > [ParamReserveFrom]

I actually thought the DCount was the way to go, with the appropriate criteria. Aren't you looking to see how many computers are reserved already on that date/time? In any case, if you have a query that returns the correct result, simply use DLookup to get the value from the query. If you post the SQL of the working query, someone may be able to give you a better solution.

I can only get the query working by opening it directly and typing in the parameters on prompt.

http://img114.imageshack.us/img114/2617/screendump22ns0.jpg

You can probably do it with a DSum, but the simplest way for you might be to simply replace the various criteria with form references. IOW, instead of

[BookingDate:]

use

Forms!FormName.ControlName

That looks like the job pbaldy, I'll give it try after a munch. Thanks again.

Can anyone spot the problem?


http://img252.imageshack.us/img252/5681/screendumpavt9.jpg

http://img252.imageshack.us/img252/5677/screendumpbag8.jpg

http://img443.imageshack.us/img443/8320/screendumpchs1.jpg

Run the query by itself and see if the column has an alias (or look in SQL view). My bet is that it does. IOW, it's not named "ComputersNeeded", it's probably "SumOfComputersNeeded" or something like that.

Oh, and don't use .Text in your code. It will error if the control doesn't have focus, which it doesn't.

Roger that pbaldy, the '2001' error has been solved but my field doesn't display the number. Thanks for those spending their time to help me with my work, appreciate it alot.

Can you post a sample db?

Shall I email the original db for more precise debugging?

You should be able to attach it here, after compacting and zipping. We really only need that form and anything it depends on, but if the whole db will fit then you can certainly attach it.

Is this what you want?

Edit: attachment deleted per request of OP.

I can still Overbook more than 20 Computers on the same Date and Times. I'll just sit here and thoroughly look through it again out.

Thank you, for going out of your way and taking the time to look through it though. I'll make sure to give you all positive rating.

Access is highlighting the string when I debug the error:
ComputersBooked = DLookup("SumOfComputersNeeded", "QryComputers")

What is the message you are receiving?

You're getting the error on the file I posted? I tested on 2 machines (A2k & A2003) and didn't get an error.

As to the overbooking issue, your code only found how many were booked. To avoid overbooking, you'll need to add some code so a new record can't be saved if it would result in too many being booked.