Simple Microsoft access question

[mmolyneux]

OK, real basic Access question, as I'm new to this. I've got two tables setup. One contain contact name & address for a group of landlords. The second contains a list of properties and which landlord owns them. I've created a form that lets me enter the property address and then with a dropdown box select which landlord owns the property. However what I now want to do, for example, is to create a form with two dropdown boxes. The first will allow me to select a landlord, and the second box will allow me to select a property that belongs only to THAT landlord. Help! Stupid question I know, but it's bugging me. Thanks

 

[grnzbra]

Can any of the properties be owned by more than one of the landlords?

 

[mmolyneux]

No, the property can only be owned by one landlord. Thanks

 

[grnzbra]

That makes life a little simpler. Basically, you need to drive the first ComboBox from table 1, using the key field and the landlord's name. Drive the second combo box from a query which uses the value from the first ComboBox as the criteria. (When you create the combo box with a wizard, it will ask you what field will be the output field and what you want to do with it. So you would select LandLordKey and Use It Later. You would include the landlord name (You might want to concatinate the first and last names of the landlord.

You then set the criteria for the second ComboBox driving query to the value of the first ComboBox. This will make available the list of properties owned by the selected landlord. You may have to refresh this query when a change is made to the first combo box (the OnChange event for the first combo box.)

 

[mmolyneux]

You've lost me!

 

[grnzbra]

Sorry.

Create a query that has the landlords table for a source. In the queries properties set Unique Records to "Yes". Include the key field of the table and the landlord's name (LastName and FirstName).

Create the ComboBox and use this query as the source. The combobox wizard will ask you which fields to use. Select them all. It will ask what field is going to be used as the output. Indicate the key field. It will ask how it is going to be used (Insert a value? Hold the value until later?) Tell it to hold the value until later. OK, you're done with the first combo box.

Create another query with the property table as a source. Include the key field, the foreign key field with the landlord key, and whatever other fields you need to see. The foreign key containing the keys for the landlords table gets the criteria set to the first combo box.