Subform requery after main form filter applied (1 Viewer)

Wysy

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,169
what do you mean the "two is linked", you have Master/Child Link Fields on the subform?
 

Wysy

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
yes, the child link field is linked to a field on main form
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:59
Joined
Sep 21, 2011
Messages
14,042
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?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
The method applied to the subform now works. So applied the same filter rule to the child link field:
Code:
    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
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,169
i don't think filtering the subform is necessary when your Link fields on the subform is HorseID
 

Wysy

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
I did think so however it did not work.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:59
Joined
Sep 21, 2011
Messages
14,042
Why not just use
Me.fsubMainTM.Form.Filter = Me.Filter
Me.fsubMainTM.Form.FilterOn = Me.FilterOn
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:59
Joined
Sep 21, 2011
Messages
14,042
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? :unsure:
Possible a Refresh if the subform is still on a record that now should not be visible?
 

Wysy

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
i have tried refresh/requery on current afterupdate on load even command button to apply these but non of these worked.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,169
it does work for me.
 

Attachments

  • HorseWithNoName.accdb
    516 KB · Views: 177

Wysy

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
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

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
sorry, i have tried it without passing and directly define it in the VBA window of the form
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:59
Joined
Sep 21, 2011
Messages
14,042
Why not just update the recordsource where mark is true?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:59
Joined
Sep 21, 2011
Messages
14,042
What is the recordsource at the moment?
Why does it work for arnel?
 

Wysy

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
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

Registered User.
Local time
Today, 06:59
Joined
Jul 5, 2015
Messages
333
You wont beleive: just recreated the subform recordset i.e. I have deleted the recordsource line and re-entered the same...
It works now...
 

Users who are viewing this thread

Top Bottom