Comboboxes and SQL UNION QUERY

[airman]

Hello,
I have set up a form which will be used for filtering reports. I would like to be able to generate a single report and in order to do that I will have to filter three fields.

I am using three combo boxes to achieve this. The three fields I am filtering are as follows: Tenant Name, Building Name, and Unit

The Tenant Name and Building Name work perfectly but I can't get the rowsource for Unit to display any values.

Here is what I have so far:

For the tenant name:
rowsource:

 SELECT Tenants.[Tenant Name] FROM Tenants ORDER BY [Tenant Name];

The above displays all the tenants that currently have leases.

Once the Tenant Name is selected the Building combobox is populated with all the buildings leased to the selected tenant (Tenants can lease more than one building). This is achieved by the following:
rowsource:

SELECT distinct Leases.LeaseID,  Leases.[buildingName]  FROM Leases WHERE (((Leases.Tenant)=[forms]![Lease Offer]![tenantCombo])) UNION select distinct null, null FROM Leases ORDER BY Leases.[buildingName];

This also works perfectly. Only the buildings that are occupied by the tenant are displayed.

Now for my problem. Because, not only can the tenant be holding a lease in more than one building, but they can also have more then one lease in the specific building (ie for a specific Unit number). So what I am trying to do is to have the Unit combobox display only the unit numbers that the selected tenant is leasing in the selected building.

I tried to simply modify the rowsource of building name but it is not working (the combo box is always null).
rowsource:

SELECT distinct Leases.LeaseID,  Leases.[Unit]  FROM Leases WHERE (((Leases.Tenant)=[forms]![Lease Offer]![tenantCombo]) AND  ((Leases.buildingName)=[forms]![Lease Offer]![buildingCombo])) UNION select distinct null, null FROM Leases ORDER BY Leases.[Unit];

So this is where I need help. This is very important to my project because, it is the only way I see being able to filter my report to a single specific lease.



Also, not sure if it will be useful, but I had trouble to get just the first two comboboxes working but I eventually succeeded, so if anyone is interested on how to achieve these to update here is the code:

It is only basically three functions:

'this is the "On Current" even of the form

Private Sub Form_Current()
buildingCombo.Requery
unitCombo.Requery
End Sub

'this is "On Change" event in the the tenant combo box. the building part 'works but the unit combo doesn't. However, I'm pretty sure it is a problem 'with the actual query not the code below

Private Sub tenantCombo_Change()
buildingCombo.Value = Null
unitCombo.Value = Null
buildingCombo.Requery
unitCombo.Requery
End Sub

'this is "On Change" event in the building Combobox, however it is not doing 'anything for me now.

Private Sub buildingCombo_Change()
unitCombo.Value = Null
unitCombo.Requery
End Sub

Thanks in advance for any input you might have.

Eric

 

[airman]

Ok I solved my own problem. It was mostly just a work around, but for a good reason. You see I realized, that when there is going to be multiple instances of leases for the same tenant in the same building but different unit, my building combobox will simply display the building name multiple times.

Example: Building A
Building A
Building A

Now this is pretty useless, but it was displaying it this way because it was based on the key LeaseID

Since I already put an invisble textbox in the (leases.buildingName) to store the Building Name (Building Name is another combo box in leases table displaying buildings from Buildings table) i just added the specific unit number to this text box. I did this in the Lease form as follows:

Private Sub Form_Current()
Me.buildingName.Value = Me.Building_Name.Column(1) & " unit " & Me.Unit
End Sub

Private Sub Tenant_Change()
Me.buildingName.Value = Me.Building_Name.Column(1) & " unit " & Me.Unit
End Sub

Now the building combobox displayed the following for multiple instances of buildings:
Building A unit 1
Building A unit 2
Building A unit 5
etc.


Since the building combobox has two columns leaseID and buildingName, and leaseID being the primary key for the table leases, all I needed is the two comboboxes and I could filter my report (I aslo inserted the field leaseID into my report and made it invisible).

I filtered the report with the following command button:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

    Dim stDocName As String

    stDocName = "Leases"
    DoCmd.OpenReport stDocName, acPreview, , "[LeaseID]=" & Me.buildingCombo.Column(0)
    

Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click
    
End Sub

And now everything works

Sorry for the long post, and hopefully this makes sense and might benefit someone in the future.

Eric