Filtered PopUp Form, Multiple Selection

[airman]

Hello,
I am building a sort of property management database, and I have run into a bit of a problem. The database will store information about buildings, leases, maintenance etc..

My problem has to do with units in buildings. The tracking of units was requested near completion of the database, and now I have to somehow incorporate it into the database.

Previously, the user when filling out lease information, simply had to manually fill in the unit numbers. It was just a text box, and the user could input what ever he/she wanted. For example unit:1 or unit:3,4,5 etc.

Now since we want to monitor unit sizes, instead of having the user just manually type it in, we want the user to select actual units.

I have several tables but I think the ones that are of concer here are:

Buildings
Leases
Units
Tenants

Units table right now stores the following:
unitId
buildingName
unitNumber
size
(and others that aren't really important) the unit table will store unit information for all buildings and is related to the buildings by the buildingName

So when viewing unit information on the building form, the units are in a subform, and are filtered by the buildingName. Everything works here.

Now for my problem. What I am looking to do is when a user is entering lease information in, instead of choosing the buildingName from a drop downbox and then just typing in the unit numbers, I would like to have the user select the buildingName from the dropdown box, but then besides the unit field, press a button which will open a popup form, displaying all the unit numbers for that building. Then the user can select which units the lease pertains to (it can be more than one unit), and this will populate the unit field.

I want the popform to look something like this

□ unit 1 □ unit 2
□ unit 3 □ unit 4
□ unit 5 □ unit 5

etc.

I think I know how to filter the form, so that it only displays relavent units to the selected building, but I have no idea how to display multiple records on one page, and allow the selection of multiple records.

Sorry about the long post but I was trying to be really clear in what I am trying to achieve (hopefully it was clear not confusing). Any input you guys might have, is very much appreciated.

Eric

 

[Tim L]

I want the popform to look something like this

□ unit 1 □ unit 2
□ unit 3 □ unit 4
□ unit 5 □ unit 5
...
I think I know how to filter the form, so that it only displays relavent units to the selected building, but I have no idea how to display multiple records on one page, and allow the selection of multiple records.

Eric, I'm fairly sure that you don't want to have two unit 5's

Can you say for certainty that all buildings will always have the same number of Units so that you can have such a rigid presentation of the options?

It is difficult to suggest a clear solution because there is not enough information about the records, fields and tables and how they are related, however, I hope that what follows makes sense; although if your db structure is not suitable it won't work without some alteration.

I think that the easy solution would be to have, on the Buildings form, a subform (continous or datasheet) from which the available Units are selected using a combo box. Having chosen an available Unit the combo box would then refresh, therefore reducing the available options, for the next selection; in this manner you would then have a list of only the Units leased whenever you view the record. (The RecordSource for the combo box would be able to weed out all Units in the selected building that are available for rent.) I've attached an example of how I think it could work.

If you want to use the tick box options (as suggested by your layout) then you will need to consider how you are going to disable a tick box if a Unit is already leased to another tenant.

HTH

Tim