Unanswered: Populating a sub form using a combo box on the main form
I have an access database for managing data in a property managing firm. The key function is to process monthly statement for each landlord where it shows all the tenants and the payments for each tenant and gives the total collection for each landlord. The database has the following objects:
• Monthly Statements
• Statement Qry
• Details extended
• Statement subform
Explanation (How it works currently)
The statement Qry query draws data from Landlords and Monthly statements tables.
Details extended query gets data from Details and Tenants tables.
Statements query gets data from Landlords, Statements, Details and Tenants tables.
The Statements form gets data from Orders Qry and it has a subform (Orders subform) that gets its data from ‘Order details extended’ Query.
The statement report gets data from the “statement Qry” query.
The Orders Form
The combo box that selects the landlord from Customers table
Control Source: OwnerID
Row source: SELECT DISTINCT Owners.OwnerID, Owners. Owners Name FROM Owners ORDER BY Owners. Owners Name;
The subform has a combo box that selects tenant names from the tenants table
Sql: SELECT Tenants.TenantID, House_Code, Tenants. TenantName FROM Tenants ORDER BY Tenants.TenantID;
Currently the Orders form works in such a way that the user selects the Landlord, and some of the details of the landlord appears. Then the user starts selecting the tenants under the selected landlord using the combo box on the subform.
My main challenge is that, I am looking for a way to make it possible for all the tenants under the selected landlord (using the combo box on the main form) to be populated into the subform instead of being selected one by one.
The system calculates the total collection per landlord. I want a report that shows the total collection:
• per landlord per month
• all the collections per month
• all the unpaid houses, and vacant houses per landlord
How do I get the number of vacant houses from the report e.g under tenant name column on the report, how do I get the number of rows that read ‘Vacant’?