Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box Properties (2000)

    I have a form that has 4 combo boxes on it. Each combo box filters information from a subform until it is down to a specific item. I my case it sorts down to a specific room in a specific dorm on a specific campus. Once sorted, that information is then added to a table along with other information about a specific person. All that works great, but I'd like to take it one step further. Each room accommodates two people so after a specific room is chosen twice, I'd like it to no longer show up in the final combo box, which happens to be the room number. That way it would indicate that that room is no longer available. Is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Properties (2000)

    What is the structure of the table that holds the info?

    Does it look more or like less this (option A):

    <table border=1><td>Student</td><td>Room</td><td>John A</td><td align=right>123</td><td>Eddy B</td><td align=right>234</td><td>Bill G</td><td align=right>123</td></table>
    or more or less like this (option [img]/forums/images/smilies/cool.gif[/img]:

    <table border=1><td>Room</td><td>Student1</td><td>Student2</td><td align=right>123</td><td>John A</td><td>Bill G</td><tr><td align=right>234</td><td>Eddy B</td><td align=right>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Properties (2000)

    Currently it looks like option A. As it is setup now, there is nothing to prevent the user from putting multiple people in the same room.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Properties (2000)

    I assume you have a table containing info about all rooms. For demonstration purposes, I'll call it tblRooms, with a primary key (unique identifier) RoomID.

    Also for demonstration purposes, I'll call the table with student info tblStudents, with primary key StudentID. This table also contains a field RoomID that can contain a valid RoomID or may be empty if the student doesn't have a room.

    The following query will select all rooms that have less than 2 occupants:

    SELECT tblRooms.RoomID
    FROM tblRooms LEFT JOIN tblStudents ON tblRooms.RoomID = tblStudents.RoomID
    GROUP BY tblRooms.RoomID
    HAVING Count(tblStudents.StudentID)<2

    Also see attached screen shot (captions are in Dutch, but you'll get the general idea). You could use a query like this - adapted for your database, of course, and with extra conditions to take campus and dormitory into account - as Row Source for the last combo box.
    Attached Images Attached Images
    • File Type: png x.png (6.0 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •