Subform requery after main form filter applied

[Wysy]

Hi,
I am struggling to solve this.
I have a main form with a filter on in the form OnLoad event. Filtering works fine. However i can not get the subform filter appropriately. The two is linked together. Without filtering everything works fine. I have tried to requery the subform in several ways including a command button on main form. No success.
How do i get it working?
thanks

 

[arnelgp]

what do you mean the "two is linked", you have Master/Child Link Fields on the subform?

 

[Wysy]

yes, the child link field is linked to a field on main form

 

[Gasman]

yes, the child link field is linked to a field on main form

If the filter does not involve the child field, then you will need to apply the filter to the subform or a version of it that will work with the subform.
You can link by more than one field?

 

[arnelgp]

are you filtering the subform using its filter? or the link is not working when the main form is being filtered?
if you can upload your db, it would be quick without much guessing of what it looks like.

 

[Wysy]

The method applied to the subform now works. So applied the same filter rule to the child link field:

    Me.Form.Filter = "HorseID In(" & y & ")"
    Me.Form.FilterOn = True
    Me.fsubMainTM.Form.Filter = "HorseID In(" & y & ")"
    Me.fsubMainTM.Form.FilterOn = True

where y is the taken from a recordset

Option Compare Database
Public y As Variant
Public Sub MassTMRecordset()
Dim rs As Recordset
Dim arrayHL() As Variant
On Error Resume Next

strRC = "SELECT HorseID FROM tbHorse WHERE Mark=-1"
Set rs = CurrentDb.OpenRecordset(strRC)
rs.MoveLast
rs.MoveFirst
arrayHL = rs.GetRows(rs.RecordCount)
y = arrayHL(0, 0)
For i = 1 To rs.RecordCount - 1
    x = arrayHL(0, i)
    y = y & " , " & x
Next i
end sub

 

[arnelgp]

i don't think filtering the subform is necessary when your Link fields on the subform is HorseID

 

[Wysy]

I did think so however it did not work.

 

[Gasman]

Why not just use
Me.fsubMainTM.Form.Filter = Me.Filter
Me.fsubMainTM.Form.FilterOn = Me.FilterOn

 

[Gasman]

I did think so however it did not work.

Is there a need to filter subform?, as although the subform will have all the records, you will only even get to see what is available with the mainform HorseID?
Possible a Refresh if the subform is still on a record that now should not be visible?

 

[Wysy]

i have tried refresh/requery on current afterupdate on load even command button to apply these but non of these worked.

 

[arnelgp]

it does work for me.

 

[Wysy]

Actually the filter parameters are passed from on form by marking the records and then the array created from the marked records, HorseID then used. Maybe this passing causes the problem? Instead of onLoad onOpen?

 

[Wysy]

sorry, i have tried it without passing and directly define it in the VBA window of the form

 

[Gasman]

Why not just update the recordsource where mark is true?

 

[Wysy]

how do i do it?

 

[Gasman]

What is the recordsource at the moment?
Why does it work for arnel?

 

[Wysy]

That might be indeed a problem. The marking changes the recordsource table records, since i have added yes/no fields into that table: tbBasic

 

[Wysy]

You wont beleive: just recreated the subform recordset i.e. I have deleted the recordsource line and re-entered the same...
It works now...

 

[Wysy]

thanks for all help