I've got a small DB to handle what you are asking for. If you will send me your email address I will send it back as an attachment. (I have look all over here to see if I can attach it here but I haven't found out how toattach it here). If you know how to attach to this reply post it here I'll keep watch for two weeks. Otherwise if you are interested Email me at groseclose_k@yahoo.com
Basically, build two tables one for tblHouse, another for tblBeds. Set the releationships to a one-to-many => houses to beds.
Build two qry, one to order houses (qryHouses), another with fields from both tbls.(housenum and bednum/name in beds) qryBedCheck. Include the house number/id in the second qry. Under criteria use [Forms]![frmHouseCall]![combo0]
This uses the data from the unbound combo box below to select the information for the correct house.
Create a form with an unbound combo box and link it to the qryHouse This will set up the lookup list of house numbers (names). For row source use Select Distinctrow [qryHouses].[HouseID] From [qryHouses]; Note:the semicolon at the end is vital. Add a cmd button to the form and link it to a macro.
All the macro does is open the second form and then stops the macro.
The second form, (use the macro to open this form, OpenFrom) uses the house/fields from the qryBedCheck and a thrid (Subform) with the Bed/fields (TableView)from the qryBedCheck. Move the house/fields to the header of the form and create a subform area in the detail for the Beds/fields. Set this form's record source to the qryBedCheck. The last thing is to set the record selectors to off.
When you open the first form it will give you a lookup of the house numbers to choose from and put that value in the box. You click the cmd button to continue. This sends the house value to the qryBedCheck which selcets the choose house and any records (beds) associated with that house. The macro opens the second form with the subform in the detail (house info on top in the header, and bed info below in the detail). You can then scroll (record selector from subform) down the beds to see which are empty or who is in which ones or add new beds.
Sorry this is so long. Like I said above I have a model db if you want it, email me.
KG
groseclose_k@yahoo.com
