reservation system for class project

[AdamVT]

I am trying to create a reservation system for my class project and need help. My goal is to make a system where I can reserve many room on a given date and the rooms that are reserved are not shown on the combo box that I have on the form. To keep things simple I only have 3 types of rooms and 3 quantity of each. So for example if I reserve all 3 Suites rooms from 1/1 to 1/3 then I should not be able to see anymore suites in the combo box for those dates but they should be visible on 1/4. I am having a really difficult time on thinking of a way to create tables/relationships that would allow me to do this. Please help!!! Thanks

[June7]

Check out some existing projects.

Google: Access reservation database

Here is one possible http://www.freedownload3.com/softwar..._template.html

[dblife]

One approach might be,
A table for rooms.
A table for room reservations.
The combo - base the row source on a query rather than a table.
The query might have the join type set so only rooms from the room table which do not appear in the reservation table show up.
When the combo is used, it will only show rooms not booked so far.

[AdamVT]

Thanks guys. I created a query that gives me all the available rooms for a range of date but the problem I’m having is that the query is not running with the arrival and departure date text box I have on the form. Every time I click the drop down button on the combo box in the form the “enter parameter value” box comes up asking to enter the dates. I want the parameter dates to be pulled from the text box. This is my SQL code I have in the query if that helps:

PARAMETERS [frmreservation].[txtArrivalDt] DateTime, [frmreservation].[txtDepartureDt] DateTime;
SELECT [tblreservation].[Room], [tblreservation].[ArrivalDt], [tblreservation].[DepartureDt]
FROM tblreservation
WHERE ((([tblreservation].[ArrivalDt]) Between [Please enter arrival date] And
   [Please enter checkout date]-1))  
   Or ((([DepartureDt]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
   Or ((([tblreservation].[ArrivalDt])<[Please enter arrival date]) And
   (([DepartureDt]-1)>[Please enter checkout date]-1));

[June7]

I have never used PARAMETERS clause. Apparently it is not set up properly for your query. I just put the form textbox reference in the query. Where you have [Please enter...], refer to the textbox. Like:
ArrivalDt BETWEEN Forms!frmreservation.txtArrivalDt AND Forms!frmreservation.txtDepartureDt - 1

[AdamVT]

Yeah June7 that makes a lot of sense. Damn man you should be our prof. It seems like he only responses to emails like once every 3 years and has no clue on how to use access, yet he gets paid 6 figures. compared to him you would be god who responses at speed of light haha. but this is the code that works:

SELECT [tblReservation].[Room], [tblReservation].[ArrivalDt], [tblReservation].[DepartureDt]
FROM tblReservation
WHERE ((([tblReservation].[ArrivalDt]) Between Forms!frmReservation.txtArrivalDt And
   Forms!frmReservation.txtDepartureDt - 1))  
   Or ((([DepartureDt]-1) Between Forms!frmReservation.txtArrivalDt And Forms!frmReservation.txtDepartureDt)) 
   Or ((([tblReservation].[ArrivalDt])<Forms!frmReservation.txtArrivalDt) And
   (([DepartureDt]-1)>Forms!frmReservation.txtDepartureDt-1));

Once again thanks man!!