Filtering a subform by 2nd column in a combobox

[REZ]

I've got a form Frmexpenses based on Tblexpenses. Each record can be allocated to a property, a property group or a landlord. there's a qryPropUnion which puts together all of them, and on the Frmexpenses I've based a combobox CmbProp on this Union query. The second column in the union query has the landlord for the property/property group and is repeated when Landlord is in the first column too.
I now want to filter this form based on the landlord. I created an unbound textbox on the form which gets updated with CmbProp.column(1).
I've set Frmexpenses as a subform on a new form, and got an unbound textbox with all Landlords. When I select a Landlord I want the subform to filter, by the unbound text box Landlord, but it is asking for a parameter Landlord, and the subform is coming up blank.
How do I go about filtering?

 

[jdraw]

Review the links in Similar Threads at the bottom of the page. Often, someone has posted a solution, or at least some helpful code and/or suggestions.

 

[REZ]

Review the links in Similar Threads at the bottom of the page. Often, someone has posted a solution, or at least some helpful code and/or suggestions.

I've spent a lot of time looking at similar threads. I've not found anything to help me.

 

[Gasman]

Your issue sounds more like you are not giving the correct control name to filter on?
You would need to show the form control in question, and your filter code.

Else upload the DB with enough data and instructions on how to recreate the issue.
I only have 2007, so might not be able to open it, but others will be able to.

 

[jdraw]

@REZ ,
As Paul/Gasman suggested, post some code relevant to the form, subform and control, or, better yet, post a copy of the database with instructions.

 

[CJ_London]

You have not provided the sort of information required to provide any meaningful help.

provide actual details (Not a description) of the relevant tables and the form , subform and combo details such as field names and types, record source sql for forms and combo

 

[REZ]

I've tried to re-created the problem on this database.
On the form "Expenses" I'm trying to filter the subform with an onclick event.

 

[Gasman]

Yep, too late for me.

 

[mike60smart]

I've tried to re-created the problem on this database.
On the form "Expenses" I'm trying to filter the subform with an onclick event.

Hi

You will need to study Relationships and setting Primary/ForeignKeys
It is recommended that you have an Autonumber Primary Key in Every table and the related Foreign Key should be a LongInteger DataType.

I have corrected this in your database and attach the modified version.

 

[CJ_London]

was about to same the same thing. Mike may have fixed it but you also need to be consistent with field names Property seems to have 'code', propcode' and 'propid' for example

Think your tblExpenses which is not linked at the moment should be linked to tblPropDetails - but you need to fix datatypes as well

Don't understand your thinking for the form - why a union query for the the propid combo? if you have that you can get the landord/code since it is in the same record. Also don't understand why you are grouping in you cmbFilter since you are only bringing through a PK

What I can say you cannot filter the form based on a column in a combo - you have to filter on the field.

so modify your subform recordsource to bring through the Landlord code as well by linking tbleExpenses to tblPropDetails. Then you don't need any code - simply set the subform linkchild/master properties

 

[mike60smart]

Hi CJ

I did exactly this "modify your subform recordsource to bring through the Landlord code as well by linking tbleExpenses to tblPropDetails. Then you don't need any code - simply set the subform linkchild/master properties" in the version I updated

 

[mike60smart]

I've tried to re-created the problem on this database.
On the form "Expenses" I'm trying to filter the subform with an onclick event.

Hi
Not really understanding the purpose of the database.

Normally Clients rent properties which are owned by Landlords.

Your relationship does not follow this criteria.

Can you explain the purpose?

 

[Gasman]

Whilst I cannot see the DB, I would have thought you had one owner of a property, be it a Property Group or a landlord (which effectively is a Property Group of one?)
So I would have an identifier of Group or Landlord (NonGroup) and then no union required?

 

[CJ_London]

OP has a table for landlords, one for properties and a linking table between the two including dates (so presumably the OP can track when properties change hands. i.e. a many to many relationship. Also has an expenses table which I think is to track maintenance costs against property and consequently which landlord to bill.

 

[REZ]

Thanks for all the replies, but my main problem is that I can't link the tblexpenses to the properties in the usual way
Expenses are usually linked to a single property, but sometimes one expense relates to a group of properties or to a Landlord as a whole.
That is why I made the union query, and that's why there is no realtionship between tblexpense and Tblproperties.
If I use the union query in the record source of the Frmexpense, it becomes uneditable.

 

[mike60smart]

Thanks for all the replies, but my main problem is that I can't link the tblexpenses to the properties in the usual way
Expenses are usually linked to a single property, but sometimes one expense relates to a group of properties or to a Landlord as a whole.
That is why I made the union query, and that's why there is no realtionship between tblexpense and Tblproperties.
If I use the union query in the record source of the Frmexpense, it becomes uneditable.

Hi Rez
What does my example not do for you?

 

[REZ]

I've added the property, so you can see the property as well as the landlord, but if I add a new record that I want to attribute to just a Landlord i.e. the Landlord will be in both the Landlord fields and Property fields, how would I do that?

 

[CJ_London]

but sometimes one expense relates to a group of properties or to a Landlord as a whole.

in that case sounds like you need another joining table between expenses and properties - no records when landlord only, list of propertyPK's in the group when not - perhaps with a split of the expense if not spread over them equally.

 

[mike60smart]

I've added the property, so you can see the property as well as the landlord, but if I add a new record that I want to attribute to just a Landlord i.e. the Landlord will be in both the Landlord fields and Property fields, how would I do that?

Hi REZ

It might be a better layout if the Data Input Form is something like that shown in the attached.

1. Select a LandLord Group
2. Subform displays a list of Landlords for the Group selected.
3. Enter Propertie(s) associated with the Group selected.
4. Enter any Expenses as required.

 

[REZ]

Thanks all. I've been away from my desk for a few days. Just read through your suggestions
A joining table won't work, some expenses cannot be split (e.g. a gardening expense for an entire complex)
I think I'll keep my form for general viewing, using the union query in the underlying control source, allowing the user to filter as needed.
For input/editing a separate input form will open, which will not have to filter by the Landlord, so will not include the union query allowing the user to edit.