how to link or de-link master/child fields in sub-form properties (1 Viewer)

martinr

Registered User.
Local time
Tomorrow, 10:23
Joined
Nov 16, 2011
Messages
73
i have a sub-form that is linked to the parent form (using a product_type field) as the default setting.
Is there a simple to way to enable/disable the parent/child link so the user could see all records if desired?
otherwise i expect i need to set up a filter and re-query the sub-form?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:23
Joined
Oct 29, 2018
Messages
21,358
Hi. To create or break the link between the forms, you can manipulate those properties using code. I'm not in front of a computer now, but look for LinkMasterFields or LinkChildFieds.
 

vba_php

Forum Troll
Local time
Today, 18:23
Joined
Oct 6, 2019
Messages
2,884
otherwise i expect i need to set up a filter and re-query the sub-form?
yes you can do that. there are many methods you can use to go about this. but guy's post is obviously the most practical and easiest. the filter properties you're looking for if you want to do it via that method are:
Code:
=> me.subformcontrol.form.filter = "field = criteria"
=> me.subformcontrol.form.filter.filteron = true/false
if you're going to do it to a sub though, I believe you have to refer to the form object itself, not the container/wrapper for the data it holds, which, in access terminology, is referred to as a "subform control" (same type of thing as combos/textboxes, etc...)
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:23
Joined
Jan 20, 2009
Messages
12,849
yes you can do that. there are many methods you can use to go about this. but guy's post is obviously the most practical and easiest. the filter properties you're looking for if you want to do it via that method are:
Code:
=> formOrSubform.filter = "field = criteria"
=> formOrSubform.filteron = true/false

Wrong. That is not what DBGuy is suggesting at all and nothing to do with what OP has asked.

if you're going to do it to a sub though, I believe you have to refer to the form object itself, not the container/wrapper for the data it holds, which, in access terminology, is referred to as a "subform control" (same type of thing as combos/textboxes, etc...)

Wrong again. The subformcontrol has the LinkMasterFields and LinkChildFields Properties.

Note that these two properties must always list the same number of fields (or controls) or you will get an error. So an error is thrown when you remove one of them. Consequently the action need to be preceded by turning off the error handling:
Code:
On Error Resume Next
then put back to normal error handling afterward you change the properties with
Code:
On Error GoTo somewhere
or changed to pass any errors back up the chain of calls
Code:
On Error GoTo 0

 

GinaWhipp

AWF VIP
Local time
Today, 19:23
Joined
Jun 21, 2011
Messages
5,901
You can add a filter\clear filter button and use something like...

To clear Filter
SQL:
Me.YourForm.LinkMasterFields = ""
Me.YourForm.LinkChildFields = ""

To set the Filter
SQL:
Me.YourForm.LinkMasterFields = "YourFieldOrControl"
Me.YourForm.LinkChildFields = "YourFieldOrControl"
 

vba_php

Forum Troll
Local time
Today, 18:23
Joined
Oct 6, 2019
Messages
2,884
I think you've mistaken my post, Galax. I'm not going to modify it, because I'm thinking that the question asker might know exactly what I'm talking about if he/she experiments with the method. So let us just see what they have to say when they come back and look at this thread. thanks.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:23
Joined
May 21, 2018
Messages
8,463
@vba_php
I'm not going to modify it, because I'm thinking that the question asker might know exactly what I'm talking about if he/she experiments with the method. So let us just see what they have to say when they come back and look at this thread.

Well atleast edit the incorrect code if suggesting an alternate method. Yours will not work
Code:
=> formOrSubformControl.form.filter = "field = criteria"
=> formOrSubformControl.form.filteron = true ' false
 

vba_php

Forum Troll
Local time
Today, 18:23
Joined
Oct 6, 2019
Messages
2,884
Well atleast edit the incorrect correct code if suggesting an alternate method. Yours will not work
Code:
=> formOrSubformControl.form.filter = "field = criteria"
=> formOrSubformControl.form.filteron = true ' false
I don't think I follow you, Maj. what's wrong with it? is it too convoluted and confusing to the question asker? or is the code just literally wrong? are you referring to the string SubformControl , when I said in the text above that the the subform control is *not* what should be referred to?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:23
Joined
May 21, 2018
Messages
8,463
Have to reference the form in the subform control. You said it but did not show it, so you should edit it.
 

vba_php

Forum Troll
Local time
Today, 18:23
Joined
Oct 6, 2019
Messages
2,884
Have to reference the form in the subform control. You said it but did not show it, so you should edit it.
DONE. thanks!
got it in one. It will generate an error when compiled or executed.
CJ,

I don't think a lot of you guys are recognizing the "summary"-type code I've written in my previous post. but I know it was wrong to begin with. it is now corrected. However, I was trying to tell the question asker how to do multiple things in a consolidated manner, similar to what microsoft does on their knowledgebase pages. see attached for what I'm talking about. perhaps I should've written this?
Code:
FOR number field criteria:

Me.subformControl.Form.Filter = "childField = " & me.parentField
me.subformcontrol.form.filteron = true/false

FOR string field criteria:

me.subformControl.form.filter = "childField = '" & me.parentfield & "'"
me.subformcontrol.form.filteron = true/false
does that look better you guys?

no_linking_of_fields.jpg
 

Attachments

  • parent_child_linking_clarify.zip
    45.2 KB · Views: 85

CJ_London

Super Moderator
Staff member
Local time
Today, 23:23
Joined
Feb 19, 2013
Messages
16,553
I know it was wrong to begin with
just wonder why you bother providing incorrect solutions for the OP to work out what you really mean.
similar to what microsoft does on their knowledgebase pages. see attached for what I'm talking about.
the attached is a basic access file, not a knowledgebase. So I don't see what you are talking about.

I'm going to drop the subject now, I'm sure the OP has enough to be able to move forward and I have work to get on with.
 

martinr

Registered User.
Local time
Tomorrow, 10:23
Joined
Nov 16, 2011
Messages
73
thanks for all your helpful suggestions /posts :)

i found that this worked Ok - just activated the code via button

Me.name_of_subform_control.LinkMasterFields = ""
Me.name_of_subform_control.LinkChildFields = ""

then re-applied the code with the matching field names inserted to reset the filter.
thanks again!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:23
Joined
Oct 29, 2018
Messages
21,358
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom