how to link or de-link master/child fields in sub-form properties

[martinr]

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]

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]

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:

=> 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...)

 

[Galaxiom]

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:

=> 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:

On Error Resume Next

then put back to normal error handling afterward you change the properties with

On Error GoTo somewhere

or changed to pass any errors back up the chain of calls

On Error GoTo 0

On Error statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[GinaWhipp]

You can add a filter\clear filter button and use something like...

To clear Filter

Me.YourForm.LinkMasterFields = ""
Me.YourForm.LinkChildFields = ""

To set the Filter

Me.YourForm.LinkMasterFields = "YourFieldOrControl"
Me.YourForm.LinkChildFields = "YourFieldOrControl"

 

[vba_php]

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]

@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

=> formOrSubformControl.form.filter = "field = criteria"
=> formOrSubformControl.form.filteron = true ' false

 

[vba_php]

Well atleast edit the incorrect correct code if suggesting an alternate method. Yours will not work

=> 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?

 

[CJ_London]

the code just literally wrong?

got it in one. It will generate an error when compiled or executed.

 

[MajP]

Have to reference the form in the subform control. You said it but did not show it, so you should edit it.

 

[vba_php]

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?

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?

 

[CJ_London]

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]

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!

 

[GinaWhipp]

We are glad to help!

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.