ComboBox Selecting Specific Records Filter

[Ange Kappas]

I am trying to make a workable database with Access for a reservations
program for a small hotel that I own BUT I am stuck at either a SQL command
or Visual Basic code which will tell me which rooms are free when I am
trying to make a reservation.

Let me be more specific..

I have a table which has all the reservations passed in (Fields being
RoomNo, ReservationName , ArrivalDate, DepartureDate etc).

My form is based on this table and after I enter the reservation name,
arrival date and departure date, on my form I have a combo box which pops up
with all the rooms.

I would like the combo box to show ONLY the rooms which are available and
not reserved and
that is after I have entered arrival and departure dates on my form.

Now I don't know if a query show be used or some kind of SQL command on the
Row Source property or even a Visual Basic Code.

If anyone can help it would be much appreciated.

E Mail address: an*****@hol.gr

[Mike Storr]

On Sun, 1 Feb 2004 18:23:01 +0200, Ange Kappas wrote:

I am trying to make a workable database with Access for a reservations
program for a small hotel that I own BUT I am stuck at either a SQL command
or Visual Basic code which will tell me which rooms are free when I am
trying to make a reservation.

Let me be more specific..

I have a table which has all the reservations passed in (Fields being
RoomNo, ReservationName , ArrivalDate, DepartureDate etc).

My form is based on this table and after I enter the reservation name,
arrival date and departure date, on my form I have a combo box which pops up
with all the rooms.

I would like the combo box to show ONLY the rooms which are available and
not reserved and
that is after I have entered arrival and departure dates on my form.

Now I don't know if a query show be used or some kind of SQL command on the
Row Source property or even a Visual Basic Code.

If anyone can help it would be much appreciated.

E Mail address: an*****@hol.gr

Without knowing all the fields that would be involved in your criteria, an
example of the RowSource for the combo box would be

SELECT tblName.RoomNo FROM tblName WHERE ((tblName.Reser vationName Is Null)
AND (tblName.Arriva lDate Is Null) AND (tblName.Depart ureDate Is Null));

This will bring back a list of rooms to choose from where nothing has been
entered for them. I'm also assuming that there is no other table containing
a list of valid Room numbers. Ideally, all your room numbers would be in a
table other than the one containing reservation info. The reservations
table would have the room number as a foreign key from the Rooms table.
If this is the case, the query would change slightly.
--
Mike Storr
veraccess.com