Filtering on a crosstab queried subform

[andatabase]

Hello,
I have a crosstab query (dynamic fields) which is now acting as a 'subform' in a form. I want to filter based on user selection, but some of the things I have tried are unsuccessful.
1. As it's a query and not an actual subform, the option to update the record source. I only see a source object property, query.queryname.
2. This is a crosstab query and the reason I have done it this way is to ensure that values are populated in the relevant year, so every year there is a new column.
3. The filtering is quite straightforward, just a simple filter function should be acceptable, but it seems that connecting the query this way limits the functionality

Any suggestions? Any attempt to modify the source objects results in Access asking for the parameter box (which is the linked field in the query, connecting to the main form).

Thanks

 

[mike60smart]

In the Linked Field in the Query put a Form Reference to the Main Form PK in the Criteria Row

[Forms]![NameofMainForm]![NameofSubform].[Form]![NameOfPKControl]

 

[CJ_London]

depends on what filtering you are doing but the other method is to use the linkchild/master properties of the subform control. The child contains the name of your crosstab field, the master is the name of a textbox/combo/whatever control

 

[andatabase]

In the Linked Field in the Query put a Form Reference to the Main Form PK in the Criteria Row

[Forms]![NameofMainForm]![NameofSubform].[Form]![NameOfPKControl]

Do I add this to the 'Link Child Fields'?
Thanks for your suggestion.

 

[andatabase]

depends on what filtering you are doing but the other method is to use the linkchild/master properties of the subform control. The child contains the name of your crosstab field, the master is the name of a textbox/combo/whatever control

This is what I have done. I want to filter the results even further.

 

[mike60smart]

Do I add this to the 'Link Child Fields'?
Thanks for your suggestion.

This will depend on how your relationships are setup

Normally as CJ suggested you would have the LinkMaster & LinkChild Fields set to the PK on the Main Form
and the related Child FK on the subform

 

[CJ_London]

his is what I have done. I want to filter the results even further.

Filtering on what?

You can apply a filter to the subform from the main form

Subformname.form.filter=“somefield=“ & txtnum

Subformname.form.filteron=true

Suggest show the sql to your crosstab and identify which fields you want to be able to filter further.

 

[DHookom]

I think you could adapt this report solution for a form/subform.