Filter subform based on another subform

ppataki · Jan 25, 2011

Dear All,

I have a form that contains two subforms
SubformA contains LogonID, SubformB contains the same but with a different recordset
I would like that if I click on any records in SubformA, SubformB gets filtered to display records for UserID in SubformA

I have put this code to SubformA:

Code:

Private Sub Form_Current()
Forms!mainform!SubformB.Form.Filter = "participantid = '" & u565873 & "'"
End Sub

first just to test if filter works on subformB, but I get error message that it is an invalid reference
if I use this code

Code:

Private Sub Form_Current()
Forms!mainform!SubformB.Filter = "participantid = '" & u565873 & "'"
End Sub

I get error message that object does not support this property/method

Could you please advise?
Many thanks in advance

CBrighton · Jan 25, 2011

Well, the first piece of code is the correct way to do it, you need to use .Form.Filter because you are setting the filter on the form within the subform object, it's not a property of the subform object itself.

I assume u565873 is the control in subformA which holds the participantid and it is text format?

ppataki · Jan 25, 2011

it is not a control, it is text (for testing purposes)
afterwards I would like to put a control of the subform there

CBrighton · Jan 25, 2011

If you want to test with an ID manually input into the code for now you need to alter the code slighty. You have it correct for referencing a control but that is not what you are trying to do at the moment.

Try:

Code:

Forms!mainform!SubformB.Form.Filter = "participantid = 'u565873'"

This is still assuming ParticipantID is a text field.

ppataki · Jan 25, 2011

I tried it

Code:

Forms!frm_adminmode!sfr_AdminTrainingHistory.Form.Filter = "participantid = 'u565873'"

and it gives me error message that it is an invalid reference to the form
btw participantid is in subformB (sfr_AdminTrainingHistory)
the above code is in the OnCurrent event of subformA

Any suggestions please?

CBrighton · Jan 25, 2011

I think I'm at the limit of my theoretical problem solving, to get further I need something I can see & manipulate. If you attach a copy of the DB in 2003 format I'll have a look.

vbaInet · Jan 25, 2011

It can still be linked via the Link Master/Child Fields without the Filter property.

Is the name of the link field called ParticipantID? And is that the how it appears in both subforms record sources?

ppataki · Jan 26, 2011

Dears,
Please find attached the database
frm_adminmode is the main form
sfr_adminusers is subformA
sfr_admintraininghistory is subformB

so what I would like to achieve in the OnCurrent event of subformA is that subformB gets filtered based on LogonID field in subformA = ParticipantID in subformB
Many thanks

CBrighton · Jan 26, 2011

CBrighton said:
IIf you attach a copy of the DB in 2003 format I'll have a look.

I'm sure others will help with your current attachment but I cannot as I only have Access 2003 (can't open accdb files).

ppataki · Jan 26, 2011

I am sorry I overlooked that
In the meantime I figured out a solution:
insert a textbox to the main form that refers to the ID in subformA
then set the filter property of subformB to the textbox (ID=textbox)!
so VBA was not needed at the end!

Thank you

vbaInet · Jan 26, 2011

See the attached which still uses the Link Master/Child Fields. I created a new db shell just to get rid of corruption because it wasn't initially working so the new db is called TrackerNew and there are two versions - 2000 and 2007.

@CBrighton: I've uploaded a 2000 version (of the original db) in case you want to use a different approach.

ppataki · Jan 26, 2011

Thank you very much!
That is a similar method to solve the issue
Cheers!!

Filter subform based on another subform

ppataki

Registered User.

CBrighton

Surfing while working...

ppataki

Registered User.

CBrighton

Surfing while working...

ppataki

Registered User.

CBrighton

Surfing while working...

vbaInet

AWF VIP

ppataki

Registered User.

Attachments

CBrighton

Surfing while working...

ppataki

Registered User.

vbaInet

AWF VIP

Attachments

ppataki

Registered User.

Similar threads

Users who are viewing this thread