Combo box looking up data in more than one table

[RDunlap]

Good morning everyone. This is my first post, but I have researched a lot
here and hope this is not a question that has already been answered.

I am using 5 tables in this situation: Issues, Building, Floor, Room, Office

All Issues deal with a Building that the user previously selected. Once we
get to assigning an Issue, it can apply to a Floor, or it can apply to a
Room, or it can apply to a particular Office. I have a data sheet view with
combo boxes. One combo box gives you a list to choose from, either Floor,
Room, or Office.

So for instance, if the user chooses Room, I display the room description,
but store the Room Key. This works fine until I have multiple records of
issues where some apply to a Floor and some apply to a Room and some apply to
an Office.

I do not know how to have the second combo box containing the "Description"
to display the proper description from 3 different tables. I can get it to
store the Key, but that key may be the key in the Floor table, the Room
table, of the Office table and I cannot figure out how to make this happen.
This seems like it should be able to be done, so I hope someone can help me
out.

I hope I explained it OK.

Thanks for your assistance.

 

[RDunlap]

I forgot to mention, these are cascading combo boxes and I have researched
that and have everything set up that way. The difference here is, the table
behind the secondary (and beyond) combo boxes may be different for different
records. I believe this would not be a problem is this was Single Form view,
but it must be a Datasheet view and that is where the problem lies.

As far as I can determine, it is a two-fold issue: Cascading Combo boxes
with multiple table AND using a dataset view

Hope this clarifies it a bit.