Muli layered query

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hi,

I currently have a table that has consists of two
(relevant to this problem) columns. Column one is all the
owned properties, column 2 is a list of all the tenants
residing in the properties. I am trying to set up a form
where the user can first choose the property then choose
the specific tenant and then get all the records
accociated with the tenant (the other columns). As of now
I have set up a form that has one combo box with the row
values being the property column (unique values is
enabled) and a command button to open another form. The
second form is the problem as I'd like to show the
tenants listed in combo box (so that they can open a
third form with the relevant data). However, using
queries the best i can do is have the property listed as
many times as there is tenants. Is there any way to have
the tenants listed in the second combo box? Any answer
would be much appreciated.

Rich
 
If I understand what you are doing and what you are after,
the key to getting this to work is to have the Row Source
for the combo box to be set to a SQL string that
references the combo box on the first form.

From your description, I cannot tell how you set up the
second form. So, what I would recommend is you create a
new form that is not attached to any data source, and then
place a combo box on it that points to the list of all
tenants. Use the Wizard, and it will build the start of
the needed SQL statement for you.

After the wizard is done, you will need to add a WHERE
clause to restrict the contents to just the values
indicated by the combo box on the first form. To do this,
edit the Row Source property and add something like this
to the end of the SQL string:

WHERE [propertyIDfield] = [Forms]![first_form]![cboBox]

You will need to change "propertyIDfield", "first_form",
and "cboBox" to the correct names.

With this in place, when the second form opens its combo
box will be populated with the values dictated by the
current value in the combobox on the first form.

Try this and get it working. After that, you can play
with setting this up so both combo boxes are on a single
form by coding the AfterUpdate event for the property
combobox to do a Me.Refresh. This will cause the form to
be redrawn and the list for the tenant combo box to be
repopulated. You will want to set the value for the
tenant combo box to "" so you don't have stray data left
in it, too.

The code for this would look something like this:

Private Sub cboProperty_AfterUpdate()
Me.cboTenant = ""
Me.Refresh
End Sub

HTH

Rob
 
Back
Top