Removing Master/Child fields via vba

[branston]

Hi,

I don't know if this is even possible, but I was wondering if you could help?

I have a form which contains 3 subforms, all linked to the main form on a 'firstname' field. This works fine, but in the firstname selection page (A form which leads to this one) I have an "All" option. When I select this and open the form it is blank, because none of the firstname records say "All". I was wondering if there was a bit of VBA that I could put in saying something like:
If the firstname field = All then remove the master/child links, else keep them in tact.

Any ideas if theres a way of doing this?

Thank you

 

[Galaxiom]

With Me.subformconrolname
   On Error Resume Next
   If Me.FirstNameField = "All" Then
       .LinkMasterFields = ""
       .LinkChildFields = ""
   Else
       .LinkMasterFields = "FirstNamefield"
       .LinkChildFields = "fieldname"
   End If
   On Error GoTo 0  ' (or your error handler)
End With

The Error change is to prevent the error with unmatched count of Link Fields.

 

[branston]

Oh great! Thank you. Just tried to put it in and with the error bit it said it couldn't find the if relating to the else... but if I remove that I get another error saying:
"Compile Error - Invalid of unqualified reference" and it highlights the .MasterLinkFields line.

 

[Galaxiom]

I have adjusted the position of the Error and With grouping.

Both the stat and end need to be run regardless of the If.

 

[branston]

OK, thank you. When you say Me.subformconrolname is the subformcontrolname just the name of the form you are using as a subform? Im getting an error there now (method or data member not found)

 

[Galaxiom]

The subformcontrol object on the main form whose SourceObject is the form you are using as the subform.

 

[branston]

by the way - I have had to edit it a bit as the code was on the form prior to this one... does that change things? Also the form has 3 subforms...

 

[branston]

The subformcontrol object on the main form whose SourceObject is the form you are using as the subform.

Sorry, what now?! My head's not managing to get round that!!

 

[Galaxiom]

On the main form there is a subformcontrol (box that highlights when you first click) which displays the subform. This control has a SourceObject property. That is a text string that is the name of the subform.

If you used the Wizard to make the subform the control and the form object will have the same name.

 

[branston]

OK, thank you. I'll give it a go but I think it might be a bit beyond me...! Especially as I just thought it would be nice, don't think its a vital function!
Thank you anyway!

 

[vbaInet]

OK, thank you. When you say Me.subformconrolname is the subformcontrolname just the name of the form you are using as a subform? Im getting an error there now (method or data member not found)

It's just a small matter of the property name.

MasterLinkFields should actually be LinkMasterFields. Same goes for ChildLinkFields.