Solved: Room Booking System / Calendar

[MichaelH]

Hi Everyone,

I'm looking for some direction to help my wife with an Access project she is building; I have some Excel VBA, neither of us has Excel VBA (yet!).

What started out as a simple idea is still in the design stage and I'm seeing problems I would appreciate help with.

Here is the basic project
A room booking system that will enable a choice of room for a choice of customer for a choice of date(s).
Then, reports that will calculate the occupancy rate of the rooms on a month-by-month basis.
And a view of room availability (either Calendar type view or Excel Table type view)

A form to get the choices seems straight forward except the dates - would the CALENDAR PROJECT on VBAExpress be of use as an example here? can that be downloaded and if so how?
I've found an ActiveX but it only allows 1 day to be selected at a time.
Date selections could get sophisticated, but a simple multi date selection might have to suffice (e.g. ctrl+click on specific dates from a Calendar) real advanced would be e.g. you want to book every Friday in June, or every Thursday throughout 2005, or Mon to Friday every week with / or without week-ends included, or every other Tuesday.....better stop there!

To get the reports I believe we need a record per day for each room booked, then these will be easy to add up per month, can you generate a record per date selected as above?

Could you point me in the right direction please, thsi would be a good learning exercise for us, thanks

Michael

[OBP]

Michael, you are correct, the Calender is an ActiveX control and would display the calender on a form for you. However I don't think it will do want you want without a great deal of work. For each room you will need to display the Calender with all the previously booked days indicated on the calender so you know which days are available.
I would have thought a drop down list of available dates for the selected room would be easier to handle.
I am quite happy to work with you to develop the database in whichever format you wish to go with, although I have never used the Calender ActiveX control. If you want my assistance just drop me a private email on this forum with your email address and I will send you mine.

[OBP]

I have had a look at the Calender ActiveX control and the Calender can be controlled by a field on the form, it also controls the data in the field. This means that you will need a Calender for the Start Date and a Calender for the Finish Date on the form.
It does not appear possible to indicate the start to finish "period" on the Calender.

[Ken Puls]

Hi guys!

Michael, are you running a Bed & Breakfast or some other venture. Your stats desire seems very hotel like...

I have no idea what your ultimate plans for the product might be, but if you are planning on distributing it, you may want to consider using BlueCactus's [url=http://www.vbaexpress.com/forum/showthread.php?t=3854]VBA Calendar Control[/vba] instead of the activeX version. I've run into problems with activeX on different machines (sometimes not even installed), and this also works on a mac for ultimate forward thinking flexability. It may not be for you, but I throw it out there.

Cheers!

[xCav8r]

It seems like only yesterday, but back in 1997-1998 I created a fairly sophisticated room scheduling system in Access 97 for a large company I worked for. Actually, it was a resource scheduling system that booked, people, rooms, equipment, etc, and we eventually moved it off Access for performance reasons.

The Access interface was only used by a small group of people, and although it was infinitely more sophisticated than the web tools that had been built around the database, it never had a truly graphical representation of room bookings. Instead, we used a large form with lots of subforms displaying the most important rooms with one subform showing all resource bookings that was an easy hop to other forms for more details views of individual resources.

The advantage of the graphical representation, of course, is it's much easier to see when you've overbooked something. To get around that, I created a simple conflict checker to avoid problems. For the staff who regularly used the Access application, it worked just fine.

Ultimately, I used two methods for reservations. The first and most frequent stored a unique reservation ID, a resource, a start date and time (single field), and an end date and time (single field). The second method stored recurring patterns (modeled after Outlook's recurrence method). We had a number of tasks that would run every day, one of which was to project patterns out two years. So, basically, recurring bookings got stored in the first table. If I'm not mistaken, I eventually kicked that up to five years, because there were a few cases where two hadn't been sufficient.

[xCav8r]

It's slowly coming back to me now. I used the Calendar Control to set the date for the main calendar form, and all the subforms were linked to it via date. This made it easy to browse the schedule by date. I created double-click procedures for each of the reservations of the resources to make it easier to get more information about them or edit them. And, I also used a double-click procedure on the resource name to make a new reservation.

[MichaelH]

Thanks everyone,

No we are not running a B&B, my wife works for a Community Centre that allows rooms to be booked out to the various community / charity organisations in the area.

She has progressed and got a reasonable Room Booking Form working now, without actually using a Calendar Control, just input date fields.

She's now working on a Excel type view to see existing bookings so you know if a room is available for a requested date.

Anyway, I'll close this thread, and thanks for the advice.

Michael