Remove Of Filter -> Not Refreshing Subform

[1DMF]

Hi,

I have a master form with multiple tabs and on the tabs I have various subforms.

One tab->subform is not directly linked to the master form by a specific field/key (master/child) but via a query...

Like "" & IIf([Forms]![Contacts].[MembershipLevel]='Appointed Rep',Left([Forms]![Contacts].[Membership Number],8),[Forms]![Contacts].[Membership Number]) & "*"

this is the main selection criteria against the master records of the main form, basically if on the main record the subform shows records for ALL members of that company, if on specific member record it only shows their linked records. (as the first 8 char of a membership number represents company)

This is working fine, if you change the main DB record , all linked subform records are corrctly re-selected and displayed....EXCEPT.....

lets say you were on master DB record 3, and then were looking at the subform attached records, if you do a filter on the sub form, then remove the filter once finished, the main form goes back to the first DB record (this always happens when filter is removed).

However the subform displays all the records for the main DB record 3, rather than refreshing and showing subform records for DB main record 1.

Hope your still with me on this...

I've tried on current me.refresh, me.requery, onfilter etc.. etc..

Nothing I seem to do will force the subform to requery/refresh and get the appropriate attached subform records.

And this behaviour only happens if you apply a filter then remove the filter against the subform.

otherwise, moving around the main DB records, shows the correct subform records no problem.

Why is filter/remove of filter causing this behaviour and how do I get the subform to correctly requery and display the correct subform records when filter is removed?

thanks.
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

Well I don't know why it is not relinking the subform records, but I have fixed the problem.

for the main form / master record i've added to the 'on current' event...

[Forms]![myform]![mysubform]![Form].recordsource = 'my select statement'

So when ever the master record changes , i've forced a reselect of the attached subform records.

I'm guessing it's to do with not having a real binding key between master & child, as all other sub forms don't suffer from this problem.

Oh well this seems to do the job, unless anyone can think of a better place to put it as it only seems to be an issue when filter is removed.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

*bump* anyone?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!