I'll answer the last question first. You need a separate record for each floor. If you don't set up your Facilities table that way, I doubt that you can get your conditional value list to work.
I'll guess that a record in Facilities has these fields (but maybe with different names):
__pkFacilityID-->serial number to uniquely identify each floor record
Floor --> a text field to identify the unit name or floor number? for each record in your table.
You appear to already have this relationship in place as your first tier value list works:
Main::Facility Type = Facilities::Facility Type
Now take your second occurrence (instance) of Facilities and link it to Main like this:
Main::Facility Name = Facilities 2::Facility Name
Define the value list for Floor to use values from Facilities 2 (NOT Facilities) and specify "Include only related values, starting from Main".
Ahhhhhhhhhhhhhhhhhh!!!!!! Ha.... as usual, your answer works like a charm. Thanks so much. (as a side note, I actually forgot to add serial numbers to the facilities... I'll do that now though... thanks for that as well.)
One more question... below where the user selects the FACILITY TYPE then FACILITY NAME then UNIT, I'd like it to auto populate address feilds based on the FACILITY NAME. All the address data is stored in the same FACILITIES table. I'm drawing a bit of a blank here... I know I've done it before but I forget what I did.
Does that mean that you have the address for a given facility entered multiple times? (Given that one record represents a "floor" or "unit" of that facility.
That seems very inefficient and can raise a number of issues when the need arises to modify the data in these fields for an existing facilities record.
A separate table where you have one record (and one set of address fields) for each facility would make more sense to me and I'd link to it by an Id number, not the name.
Hmmm... yeah I guess that's what I was getting at about having a repeating field for the UNITS rather than individual records - thought that's probably more chaos than it's worth.
So you're suggesting 3 tables: CASES, FACILITIES and UNITS?
Okay... rebuilding. Just to clarify, there should be a unique serial number for both the Facilities and the Units, right??
Yes. If you base your main relationships on names instead of ID numbers, a change of name requires carefully updating match fields in both parent and child records or you lose the link to the child records. Using a serial number field avoids this issue.