Remove Subform Linking with VBA

Question

I have two subforms which reside on a Parent form.  Subform2 is linked to subform1 using Subform2's Master and Child Property fields. 

When I click on the [Loan Type] field in Subform1, Subform2 should filter according to the value in [Loan Type].  This works fine.

If the value in [Loan Type] = "<ALL>" then I want to programmatically remove the links in order to display all records.  I inserted the code below into the OnCurrent Event of Subform1.

When I open the Parent form I get:

Run-time error '2101': The setting your are entering isn't valid for this property.

when the code tries to insert a zero-length string in the LinkChildFields and LinkMasterFields property fields.  Any idea why I'm getting this error?

Private Sub Form_Current()

if Me.[Loan Type] = "<ALL>" then

'***ERROR OCCURS HERE***

Forms!Parent!Subform2.LinkChildFields = ""

Forms!Parent!Subform2.LinkMasterFields = ""

Else

Forms2!Parent!Subform2.LinkChildFields = "[Loan Type]"

Forms2!Parent!Subform2.LinkMasterFields = "Forms!Parent!Subform1.Form![Loan Type]"

End If

Thanks,

Manuel

Answer 1

The way I deal with that kind of arrangement is to add an invisible text box (named txtLink) to the main form's header or footer section.  Then add a line of code to the subform1's Current event:

   Parent.txtLink = Me.[Loan Type]

Then set subform2's LinkMaster propery to txtLink and LinkChild to [Loan Type]

To "unlink" them, you can just set the txtLink text box to any invalid value.  If Loan Type is a Text field, you can set txtLink to <All>  Otherwise, Null will probably  work.

If the Loan Type combo box is bound to a record source field, then subform1's Current event would normally be the right event.  But you may also want to do the same thing in the Loan Type combo box's AfterUpdate event.  If the Loan Type combo box is unbound, then only use its AfterUpdate event

Answer 2

Hi, Marshall.  Thanks for the quick reply.  To clarify, Subform 2 IS NOT IN Subform 1.  Both Subform1 and Subform2 reside on the Parent form as two separate forms.  The forms are linked together using the Master/Child fields, as illustrated above.

Hope this helps...

Manuel

Sorry, but that doesn't work. You cannot link separate subforms like that. You can only link a subform to the parent form, not another subform. Which is probably why you are getting the error message. 

If you want separate subform to be synchronized with another subform, you will need to set the Recordsource of the subform so its filtered by a value in the other subform. 

Another thing you can try is set a control on main form to synchronize its value with the selected record in subform 1, then link subform 2 to that. Not sure if it will work with an unbound so you might have to just filter it that way.

Answer 3

What is Loan Type?  A Combo Box or a Text Box?  What is data is in the Control when you first open the Form?

Answer 4

Hi, Marshall.  Thanks for the quick reply.  To clarify, Subform 2 IS NOT IN Subform 1.  Both Subform1 and Subform2 reside on the Parent form as two separate forms.  The forms are linked together using the Master/Child fields, as illustrated above.

Hope this helps...

Manuel

Answer 5

Not sure I got the picture, but I think you are saying subform2 is in subform1 and your code resides in subform1, right?  If so, the main form has nothing to do with it and the code should be:

If Me.[Loan Type] = "<ALL>" Then

Me.Subform2.LinkChildFields = ""

Me.Subform2.LinkMasterFields = ""

Else

Me.Subform2.LinkChildFields = "[Loan Type]"

Me.Subform2.LinkMasterFields = "[Loan Type]"

End If