Filtering a subform by 2nd column in a combobox (1 Viewer)

REZ

Member
Local time
Today, 09:32
Joined
May 17, 2022
Messages
34
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

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Jan 23, 2006
Messages
15,361
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

Member
Local time
Today, 09:32
Joined
May 17, 2022
Messages
34
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

Enthusiastic Amateur
Local time
Today, 09:32
Joined
Sep 21, 2011
Messages
14,037
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

Super Moderator
Staff member
Local time
Today, 05:32
Joined
Jan 23, 2006
Messages
15,361
@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

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2013
Messages
16,553
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

Member
Local time
Today, 09:32
Joined
May 17, 2022
Messages
34
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.
 

Attachments

  • Database1.accdb
    1 MB · Views: 173

Gasman

Enthusiastic Amateur
Local time
Today, 09:32
Joined
Sep 21, 2011
Messages
14,037
Yep, too late for me. :(
 

mike60smart

Registered User.
Local time
Today, 09:32
Joined
Aug 6, 2017
Messages
1,899
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.
 

Attachments

  • Database1.zip
    59.8 KB · Views: 170

CJ_London

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 09:32
Joined
Aug 6, 2017
Messages
1,899
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

Registered User.
Local time
Today, 09:32
Joined
Aug 6, 2017
Messages
1,899
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

Enthusiastic Amateur
Local time
Today, 09:32
Joined
Sep 21, 2011
Messages
14,037
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

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2013
Messages
16,553
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

Member
Local time
Today, 09:32
Joined
May 17, 2022
Messages
34
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

Registered User.
Local time
Today, 09:32
Joined
Aug 6, 2017
Messages
1,899
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

Member
Local time
Today, 09:32
Joined
May 17, 2022
Messages
34
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

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 09:32
Joined
Aug 6, 2017
Messages
1,899
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.
 

Attachments

  • Form.JPG
    Form.JPG
    148.8 KB · Views: 153

REZ

Member
Local time
Today, 09:32
Joined
May 17, 2022
Messages
34
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.
 

Users who are viewing this thread

Top Bottom