• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Multiple Choice Option Group

Main Table:" Event Detail" with primary key Event ID
Related Table: "Reservations" with primary key ReservationID

One Event has many Reservations.

I have a subform "Reservations Subform" that allows the user to input data into the underlying "Reservations" table.  The fields are:

ReservationID (primary key)

The other info I need to store is the room(s) specific to a reservation, and ultimately, the total cost associated with their use.  To make selection as simple as possible, I would ideally have a group of checkboxes displaying all available rooms, with the user being able to select whichever combination of rooms applies.  For example:

EventID: 1
Reservation ID: 1
Event Date: 5/5/06
SetupTime: 9am
StartTime: 10am
EndTime: 12pm
ExpectedPartipants: 25
Rooms: XLane XLyonsXLodato _Barnes _McDowell _Cranston
Total Usage Fees: $300 ($100 + $100 + $100 for each room)

EventID: 1
ReservationID: 2
Event Date: 5/6/06
SetupTime: 12pm
StartTime: 7pm
EndTime: 9pm
ExpectedPartipants: 25
Rooms: _Lane _Lyons_Lodato XBarnes XMcDowell _Cranston
Total Usage Fees: $200 ($100 + $100 for each room)

How do I best accomplish this?  I'm assuming for the sake of data integrity that the "Rooms" data should be held in a junction table cataloging each reservation's multiple room selections, but can I create an option group of checkboxes (or use a listbox, even) to do so?

My primary concerns are 1) ease of selection - a subform on the subform allowing the user the create multiple "room" records using a combobox requires a lot of space, as I need all records to be visible and  2) I would eventually like to create a report that graphically displays reservations in a calendar format, so I need to respect design parameters so I'm ultimately able to accomplish this without an unbelievable amount of code (I realize it will take a lot as is!).
  • 7
  • 6
1 Solution
This link should provide a good guide for you to follow as far as the table/query structure:

             It is relevant to any kind of reservation/booking database.


as to your forms, you could have individual checkboxes or a multiselect list box to select the rooms reserved for the current booking. I don't think an option group for your checkboxes is feasible because, as you stated, you can have multiple room selections for each reservation, and an option group, by definition, would limit you to one option (selection) for each group at any given time.

I personally like the checkbox idea if the room inventory is small enough to fit the checkboxes across the form.  There are several things that you can do with the checkboxes, such as color highlight the available rooms, or highlight the checkboxes in different colors based on room size/price;  you could also use the tool tip property to store and display info about each room.

Ps: if you intend to use a calendar on your form, here is a link to a free downloadable calendar on a native access form, which is advisable over an activeX calendar as stated in the link.

here is a good link for you regarding the presentation of checkboxes on a report.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

mmbadgerAuthor Commented:
Useful links, but I'm still stumped.  I've gone so far as to create a table that holds all of the "Room Reservations" info...i.e. one reservation can have multiple rooms associated with it.

Is there a way to create a checkbox or list format that will create the "many" room records with one entry point? (I am envisioning one list with all room names, click on multiple chosen rooms, which creates 3 separate underlying records BUT displays the selected rooms in a concise format?).
mmbadgerAuthor Commented:
I've been doing some research, and it appears Access 2007 will have the feature I'm looking for....any way to approximate it in 2003?

Here are a couple of links:



You were not specific as to what you were referring to on the links you provided. I think you are asking me if there are database templates available for you to use. The answer is Yes.  I am providing a few links for you to look at.   Most of the links are not based on MS Access, but since they have free trial periods, you should download them to get a better idea of the kind of functionality you need and style of user interface you want to provide.  I will try to find you a free ms access template.  But hopefully this will be enough material to get you started.

As to the structure of your tables, I think you need to separate and concentrate on the table design first.  After you got a normalized and workable table design, the you can concentrate on the user interface in terms of forms/subforms, etc.  the Microsoft link for a reservation database that I gave you, was intended to be a structural guide to a basic reservation system, from which you can modify/adapt as needed. you need to have at least 2 tables....a booking (reservation table and a Rooms table with primary and foreign key relations defined on the tables.  In looking at the info you provided, it looks like you may also need an Event table. And in answer to your question the form/subform is an ideal way to present your one reservation
to many rooms relationship to the user.

Links to sample reservation and booking systems:
  claims to be fully compatible with ms access




mmbadgerAuthor Commented:
No, I didn't really need sample databases.  I was specifically referring to the "multiselect combobox" that would enable me to create many room records for a single reservation (i.e. it's a many to many relationship).

I'm looking for a way to approximate this feature in Access 2003.  Is there any way to do it?
mmbadgerAuthor Commented:
BTW, all of my tables are already created, already normalized, including necessary junction tables....I just need help with this specific feature.

The reason I was asking about structure initially was to indicate that I would rather go the "normalized" route than having to use code to concatenate values (though I'm not completely opposed to this option, I would just like to keep the structure normalized, if possible).

Anyway, can someone help with this feature?
I believe you mean multi-select list box. They have existed in every version of Access from 1997 thru the latest edition with very little change.   I am providing 3 links for you. The first link is to one of several free download demo database by Martin Greene and the other is a how to library of access database demos by Dr. Roger Carlson, that can be downloaded for a $1 donation. The last link is a howto by Allen Browne for Using a MultiSelect list box to select reports.  Whether used for reports or on a form, the multi-select coding and operation is much the same.


                 (see multi select demo about half way down the page- latest Access version available 2002;
                   Access will convert to 2003 for you when you boot up the demo file.)

                   (look under the letter "M" for multi-column list boxes and multi-select list boxes;
                   look under the letter "L" for list boxes.)
                   Files are Access 2000, which Access will convert for you as soon as you open the database.

                      using a multi-select list box for selecting reports

Found almost the perfect model for your database.  It has a very similar structure to what you've outlined for your database and has all of the functionality that you've indicated, and then some. It was developed using an SQL ODBC compliant database.

                      Fastbook Event Management System
mmbadgerAuthor Commented:
Thanks for the links.  I had done research on the multi-select listboxes, and they weren't recommended for what I'm proposing (largely because they don't allow the creation of multiple "related" records, thus going against normalization).

I like the multi-select listbox look, but want to make sure that my data is normalized so that the database holds up over time.  If there isn't a way to approximate the look of the listbox while still preserving a normal data structure, no problem...I can leave the database as/is and ask users to select from a pull-down menu for each room choice...a bit less user-friendly, but it serves my purposes.

The eventsoft database does what I'm looking for, but the powers that be don't want to spend money - which is why I'm creating from scratch.  Thanks, though, for the ideas.
mmbadgerAuthor Commented:
I ended up using one of the rogersaccesslibrary solutions, which uses an unbound listbox and buttons/on click events to pop up a form that allows the user to select multiple values.  These values are written to a separate table which hold the values in a One-to-Many relationship with the main table...so it's not simple checkboxes, but I think it's a better interface than the one I had going with multiple combos and still manages to preserve normal data structure.


I've awarded you the points for a very thorough back-and-forth!  Thanks!
Thanks. Glad I could help.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now