Creating a calendar form in Access 2000

[kilkerr1]

Hi all.

I'm really stumped on this and would appreciate any help/advice. I need to create a calendar form for a room booking system in Access. The user needs to be able to view a day planner (room names along the top, and time slots down the side), which shows the availability of each room. They then need to be able to click on a specific time slot to write in their name and that slot is then booked. Other users can then see it's booked.

I wondered whether anyone could please point me in the right direction of examples, or have any other advice as I'm really confused about how to even begin with this, and am getting increasingly desperate..!

Many thanks!

 

[Pampers]

You can use the MS Date- en Timepicker. On the toolbox in designmode, click the activeX controls, and choose the date and time picker.

Pampers
You never too young to learn

 

[MichaelRed]

There are some custom calendars described in these fora, including the type of "Professional appointments" calendar you mention. You can also google for these.

MichaelRed

 

[MajP]

I am reading this question differently. You are not asking about a calendar control. You want a form that looks like this

Time Period Room 1 Room 2 Room3 Room N
0600-0700 Mark J. Available Cindy
0700-0800 Avaliable Brian M. Cathy
....
....
....

I hate to answer this one because there are probably a ton of availble (maybe freeware) that does this with all kinds of bells and whistles. Also this is ripe for mission creep.
If you wanted a real simple one this is how I would do it.
Make a time period table that has 24 (1 hour) periods. My scale is 1 hour increments so I add 24 records, you could do 1/2 or 15 min periods. But this is nothing but a list of periods.

tblTimePeriods
autoPeriodID
dtmStartTime
dtmEndTime
strPeriod

Make a room table

tblRoom
autoRoomID
strRoomName

Now make a join table for reserving a room

tbl_Room_Period
intRoomID
intPeriodID
strPersonRequestingRoom (probably another foriegn key)
other details about the meeting
index this table so that the composite index of RoomID and Period ID are unique.

Now to keep this real simple, a person can sign up for 1 or more time periods. The trick would be to design your interface so that you populate this join table with a RoomID and PeriodID.

Now to show this in the above Format. First make a cartesian product between tblTimePeriods and tblRooms

qryRoomPeriods

SELECT tblRooms.strRoom, tblTimePeriods.dtmStartTime, tblTimePeriods.dtmEndTime, tblTimePeriods.autoTimePeriodID, tblRooms.autoRoomID
FROM tblRooms, tblTimePeriods
ORDER BY tblRooms.strRoom;

This gives you a query that has record for every room and every time period.

Now join qryRoomPeriods to the joinTblRoom_Period using a left outer join.
This produces a query that has every room and every time period, and the details for reserved rooms where the room ID, and time period ID match. Something like

Room 1 12:00:00 AM 6:00:00 AM John
Room 2 12:00:00 AM 6:00:00 AM
Room 3 12:00:00 AM 6:00:00 AM
Room 1 6:00:00 AM 12:00:00 PM Mark
Room 2 6:00:00 AM 12:00:00 PM
Room 3 6:00:00 AM 12:00:00 PM
Room 1 12:00:00 PM 6:00:00 PM
Room 2 12:00:00 PM 6:00:00 PM John
Room 3 12:00:00 PM 6:00:00 PM
Room 1 6:00:00 PM 12:00:00 AM
Room 2 6:00:00 PM 12:00:00 AM
Room 3 6:00:00 PM 12:00:00 AM

qryTimePeriods

SELECT qryRoomPeriods.strRoom, qryRoomPeriods.dtmStartTime, qryRoomPeriods.dtmEndTime, joinTblPersonRoom.strName
FROM qryRoomPeriods LEFT JOIN joinTblPersonRoom ON (qryRoomPeriods.autoRoomID = joinTblPersonRoom.roomID) AND (qryRoomPeriods.autoTimePeriodID = joinTblPersonRoom.periodID);

Now do a cross tab on the above.

StartTime Room 1 Room 2 Room 3
0:00 John
6:00 Mark
12:00 John
18:00

TRANSFORM First(qryTimeSlots.strName) AS [The Value]
SELECT qryTimeSlots.dtmStartTime
FROM qryTimeSlots
GROUP BY qryTimeSlots.dtmStartTime
PIVOT qryTimeSlots.strRoom;

I put StartTime on the left, but you would probably want a string representing the period (0500-0600), etc. Do this in the periods table.

Turn this into a form and you have a solution.
This would work,although rudimentary, but I would search the web for something like this. I am sure there is something out there that would work well.

 

[MajP]

If the above makes sense, and you want to pursue that approach. I will explain how to click on the time period on the form and present a popup to reserve the room.

 

[MajP]

That idea would work for reserving a room for one day, but does not allow different days. I was trying to do this with little code because not everyone knows VBA, but to add in years to this logic I would think doing an unbound form would be simpler. This would require much less complicated queries and tables, but will require coding it.
So my approach would be, have a form with an unbound date field "txtBxDate" at the top, along the side you make labels for your time periods, along the top you list your rooms. Now fill in this grid with unbound text boxes. If I understand you right you want to show all rooms on one page. Therefore I assume you only have a few rooms. This is more like reserving 1 or 5 conference rooms, not booking 1 or 300 hotel rooms. If you have a lot of rooms you would have to change the design.
Now you can use a calendar cotrol to move txtBxDate to any date and scroll buttons with a function to move one date forward or back.
Now as the date changes you would write code to read from

tbl_Room_Period
intRoomID
dtmStartDate
dtmEndDate
strPersonRequestingRoom (probably another foriegn key)
other details about the meeting

and fill the unbound text boxes. With the reservations that exist. You could use the Tag property in each unbound box to annotate what room and time period (ex Room1;0600-0700).

Now as you click on the open period it pops up a form for that room on that day and that time period (based on txtBxdate value, and the tag property). Add your information to write to tbl_Room_Period. Close the pop up form, and populate the reservation form.
If you are good in VBA, I personally would do this all with a custom class, custom collection, and "with event" procedures. That way if any of the text box grids are clicked, I can trap that event with one event; instead, of making a event procedure for every room and every time period.
Is this even what you want, or am I off the mark?

 

[kilkerr1]

Thanks everyone for useful comments, but MajP you're closest to what I need, yes that's exactly what I want. Thanks ever so much for your in-depth help. I've not digested it all yet, but I plan to get on it right now, and get back to you with progress. You're a star!

 

[kilkerr1]

MajP, I think I need more of your invaluable help, please! I'm going with your second VB suggestion, as I need more than just one day. I've made the form with unbound boxes, and got the Date field working with Calendar control. Cool. But I think I'm a unsure of the underlying tables and how they relate to each other. Are you saying there should only be that one table? Or include tables from your first example, i.e. room and person? If you coud detail the tables and how they should join that would be really helpful. Also, how do I get the unbound text boxes to fill? Sorry, I'm a dullard, I know..!

 

[MajP]

Something to consider, before you go down this path, do you by chance run MS Outlook? If you do, and you/administrator can set up shared folders. I would make an calendar for each room. Then you can make a custom form to reserve rooms. I build a lot of Outlook applications, and most users would much rather work in Outlook and pop open a calendar than having to open up another Application. Plus all the work is done for you. If you have to go Access:

I would probably have a room table which contains information about rooms. The reason is you are alway going to want to add more capability. So you may want fields like strShortName (Conf Rm B), strLongName (Conference Room B), strRoomNumber (1234B) etc.

tblRooms
roomID (something unique to a room, probably the rm #)
other fields you think you need
examples.
strRoomNumber
strShortName
strLongName
strLocation
intCapacity

(A person table if you are going to want them to pull from a list of people, instead of always having to enter their name)

tblPersons
autoIDPerson
strLastName
strFirstName
strMI
other fields

So enter your room and people information
Now the complicated table. This is where you store the information for the Date,Time,Room, and Person which represent a room reservation

joinTblDate_Time_Room_Person
autoReservationID (auto number)
intPersonID (links back to person table)
intRoomID (links back to room table)
dtmStartPeriod (this includes the Date and the time)
dtmEndPeriod

Once you have this, the loading of the form and the saving of the data back to the table needs to be coded.

Lets start with the loading. Assume that there is data in the join table. Here is a very easy solution. Assume that you have four rooms and 12 time periods. That is 48 text boxes. You could put a dlookup in each of these boxes. So text box 0700-0800 for room ID 4 would look through a query based on the above table, linked to the Person table, and room table. The criteria would be
Where room = ID 4
where the date = the date of the text box on the form
where the time of dtmStart is <= 0700 and the dtmEndPeriod is >= to 0800.

and return a persons name.

We could loop through a recordset, but this would be easier if you are not versed in VBA.
Ponder this, and then will discuss writing to the table.

 

[kilkerr1]

MajP - that's great, I've followed your instructions re. tables and form, with a couple of changes for the amount of rooms. I also have the calendar control and date field set up on another form from before. So it's all looking good. How about this writing to the table? Oh, and I will mainly be owing you my life after this..! Thanks.

 

[mp9]

Also, to remove the reliance on the resource-heavy calendar control, check out an Access-form-based solution at

http://www22.brinkster.com/accessory/forms/005.shtml

complete with downloadable sample db.

 

[kilkerr1]

Thanks for the suggestion, mp9.