Creating a combo box to show vacant rooms based on related tables

Vote count:

0

Data Structure: (I would post a picture but it didn't let me)

TABLE[M_Transients]---------->TABLE[M_Trans_Room]<----------TABLE[M_Rooms]

       LINKED ON TRANSIENT_ID              lINKED ON ROOM_ID

Transient_ID(PK)            Transient_ID(FK)              ROOM_ID(PK)

male (Y/N)                    ROOM_ID(FK)              Room Designator(text)

female(Y/N)                 TRANS_ROOM_ID(PK)            Male Room (Y/N)

Last Name(text)                                         Female Room(Y/N)

First Name(text)                                        BUILDING_ID(FK)

Married (Y/N)                                           Married Room (Y/N)

Restricted(Y/N)                                       Restricted Room(Y/N)  

My Question is as follows: How do I create a combo box that will show only the vacant rooms that are appropriate for each transient during data entry. I am using a Form right now with the Parent source M_Transient and a subform with source M_trans_Rooms where Room_ID is a lookup field that selects the ROOM_ID and Room Designator from M_Rooms.

Example: A male transient is selected I want the form combo box to display only the vacant male rooms.

To me this means: compare M_trans_Room with M_rooms to get a vacancy list. then somehow filter the data based on the yes/no selection of male or female in M_transients. Maybe I need to restructure the data?

asked 48 secs ago

weaksauce867

1

Creating a combo box to show vacant rooms based on related tables