Requery subform after filter

[JasperDrop]

I hope someone can help me and restore my faith in Access. This is my first real build in Access and I’m loving it, but sometimes it drives me crazy, I’ve spend 15+ hours researching this problem (big thanks to internet en people like you !) and I’m stuck.

Situation:

I have a main form (Frm ViewModel) with a subform (Frm class_attributes) in datasheet-mode based on a Query.
Frm ViewModel has two controls, a ComboBox Select Model (presents you with a list of data models ) and a ComboBox Select Class (presents you with a list of Classes selected from a class-table, based on the model-id derived from the Select Model – ComboBox).
The subform Frm class_attrbutes shows you all class – attribute combinations based on the model-id derived from the Select Model ComboBox.
After the initial selection of the classes and attributes, the subform Frm class_attribute can be filtered on class by the Select Class ComboBox.

Problem

As long as I just switch between models through the Select Model ComboBox, it all works fine. The subform Frm class_attributes is updated (Requery) correctly.

The problems start when I filter the subform Frm class_attributes, either through VBA OR by using the GUI filter possibilities of the sub form itself.

Private Sub select_class_AfterUpdate()

[Forms]![Frm ViewModel]![Frm class_attributes].[Form].Filter = "class.object_name ='" & [Forms]![Frm ViewModel]![class_name] & "'"
[Forms]![Frm ViewModel]![Frm class_attributes].[Form].FilterOn = True

The filter works fine, but once you’ve used it:
- The filter stays active, even when turned off, so when you Requery the subform it reutrns results with the original filter.
- The filter is removed, but now the subform after requery keeps showing the selection of classes & attributes for the model_id where the filter was first set.

I tried all possible combinations of FilterOn = False and .Requery but it doesn’t solve it.
I tried reassigning the RecordSource but since the underlying SQL is extensive (a few joins) Access seems to have a problem with this

[Forms]![Frm ViewModel TST]![Frm class_attributes].RecordSource = "SELECT [class.hierarchy], [class.object_name], [class.technical_name], [class.definition], [parent_class.object_name] AS ParentClassName, [parent_class.level], [A.AttributeName], [A.AttributeTechnicalName], [A.AttributeDefinition], [A.AttributeType], [A.AttributeTypeDescription] FROM [parent_class] RIGHT JOIN (class LEFT JOIN (SELECT [attribute.object_name] AS AttributeName, [attribute.technical_name] AS AttributeTechnicalName, [attribute.class_id], [attribute.definition] AS AttributeDefinition, [attribute_type.object_name] AS AttributeType, [attribute_type.description] AS AttributeTypeDescription FROM attribute INNER JOIN [attribute_typ]e ON [attribute_type.ID] = [attribute.attribute_type_id]) AS A ON [class.ID] = [A.class_id]) ON [parent_class.ID] = [class.parent_class_id] WHERE ((([class.model_id]) = '" & [Forms]![Frm ViewModel TST]![model_id_hide] & "')) ORDER BY [class.hierarchy];"

I tried making the sub-form a bound form by manually linking the masterfield to the Select Class ComboBox
I played around with the On-Focus and OnCurrent-events
I tried some things with AcDialog, but that I didn’t really understood.


What am I doing wrong, OR which other approach should I use?

 

[nathansav]

Can you post an example?

 

[JHB]

How and where do you remove the filter, (show the code)?
Did you actually check if the filter got removed?
What is "class.object_name", (this part "class." looks suspect for me.)?
Else post a stripped version of your database with some sample data, (zip it) + information which form give you the problem.

Ps. Use code tags when you post code, it is easier to read.

 

[EternalMyrtle]

Can you save the record source as a query and just use:

Me.RecordSource="YourQueryName"

?

I admit that I didn't read this carefully so I apologize in advance if my reply is stupid...

 

[JasperDrop]

Thank you all for your input and effort. After posting my problem, I took a final crack at it and solved it by reassinging the subform control - SourceObject. I found out that the requerying of the form was working fine, so the problem had to be the subform control on the mainform. Why i hav to rassign the SourceObject i'm not sure, but it works !!