Conditional Link Master and Link Child (1 Viewer)

chuckcoleman

Registered User.
Local time
Today, 10:49
Joined
Aug 20, 2010
Messages
357
Hi, I've done some research on this but so far can't find something that works. I have two subforms on a main form; subform1 and subform2. There are two combo boxes on subform1. In the after update event of combo box1 it requery's subform2. In the after update event of combo box 2 it also requery's subform2. Subform2 has two fields in both the Link Master property and the Link Child property. This all works fine when there are values in both combo boxes. Combo box2 when clicked on allows the user to pick a manufacturing plant number and then in subform2 the materials are shown. What I'm trying to do is if the user doesn't pick an item in the combo box2, (it's null), then I want to show all of the materials from all plants.

I'm looking for help on the code to make this happen.

Thanks,

Chuck
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
You can programmatically modify the LinkMaster and LinkChildFields properties based on activity in the combos. Like, . . .
Code:
sub combo2_click
  with sfm
      if not isnull(me.combo2) then
         .LinkMasterFields = "Combo1;Combo2"
         .LinkChildFields = "ChildField1;ChildField2"
      else
         .LinkMasterFields = "Combo1"
         .LinkChildFields = "ChildField1"
      end if
   end with
end sub
 

chuckcoleman

Registered User.
Local time
Today, 10:49
Joined
Aug 20, 2010
Messages
357
Mark,

I'm scratching my head on an error I'm getting. Here's the code:
Private Sub PlantComboX_AfterUpdate()
With SFM
If Not IsNull(Me.PlantComboX) Then
.LinkMasterFields = "Forms![Vendor Form]![Vendor Material Form].Form![MaterialComboX];Forms![Vendor Form]![Vendor Material Form].Form![PlantComboX]"
.LinkChildFields = "Forms![Vendor Form]![Vendor Data].Form![Material];Forms![Vendor Form]![Vendor Data].Form![Plant]"
Else
.LinkMasterFields = "Forms![Vendor Form]![Vendor Material Form].Form![MaterialComboX]"
.LinkChildFields = "Forms![Vendor Form]![Vendor Data].Form![Material]"
End If
End With

The error is 424, Object Required and it's on the first LinkMasterFields line. I've verified that the form names are correct. The PlantComboX combo is on the [Vendor Material Form] which is a sub form to [Vendor Form]. [Vendor Data] is also a sub form to [Vendor Form]. I've also tried it in the LinkMasterFields lines without the full path; i.e. just [MaterialComboX] and [PlantComboX]. Same result.

Any ideas?
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
What's the name of your subform control?

In my code it's sfm. You have to replace that with the name of your subform control. Make sure the red part shows the name of your subform control.

Code:
sub Combo2_AfterUpdate()
  with [COLOR="DarkRed"][B]Me.NameOfMySubformControl[/B][/COLOR]
      if not isnull(me.combo2) then
         .LinkMasterFields = "Combo1;Combo2"
         .LinkChildFields = "ChildField1;ChildField2"
      else
         .LinkMasterFields = "Combo1"
         .LinkChildFields = "ChildField1"
      end if
   end with
end sub

And then, you just want the field/control name, not the "Form!Formname.Subformcontrol.Form!ControlName" reference as a string.
 

chuckcoleman

Registered User.
Local time
Today, 10:49
Joined
Aug 20, 2010
Messages
357
Mark,

I'm sorry to be such a pain but I've put another 4 hours into this and it still doesn't work. With the code below I get a Run-time error 2335 which says, "You must specify the same number of fields when you set the LinkChildFields and LinkMasterFields properties."

With Me.PlantComboX
If Not IsNull(Me.PlantComboX) Then
MsgBox ([Forms]![Vendor Form]![Vendor Material Form].Form!MaterialComboX)
MsgBox ([Forms]![Vendor Form]![Vendor Material Form].Form!PlantComboX)
Forms![Vendor Form]![Vendor Data].LinkMasterFields = "MaterialComboX;PlantComboX"
Forms![Vendor Form]![Vendor Data].LinkChildFields = "[Forms]![Vendor Form]![Vendor Data].Form!Material;[Forms]![Vendor Form]![Vendor Data].Form![Plant]"
Else
Forms![Vendor Form]![Vendor Data].LinkMasterFields = "Forms![Vendor Form]![Vendor Material Form].Form!MaterialComboX"
Forms![Vendor Form]![Vendor Data].LinkChildFields = "Material"
End If
End With

To refresh, I have a main form that has two sub forms. The first subform is [Vendor Material Form] and it has two combo boxes; [MaterialComboX] and [PlantComboX] My code compiles and it works until I pick a value from the [PlantComboX] which is when I get the 2335 error. A couple of notes:
1. The LinkMasterFields and LinkChildFields are on the OTHER subform, [Vendor Data]. That is why I had to explicitly state their locations in the .LinkMasterFields/.LinkChildFields statements.
2. The MsgBox is just for testing and they show me correctly the values that should be sent to the second subform in the Link properties.

As you can see I obviously do have the same number of fields in the two Link statements. I've tried various things but nothing I've tried solves the 2335 error. The error is ONLY happens when there is a value in the PlantComboX box; not when it's null. Unfortunately, that's when there is only one field, not when there are two fields which then causes the issue.

Any more help you can provide will be very much appreciated.

Thanks,

Chuck
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
You can post your database if you want, can't guarantee I'll look at it, but someone might.

If I was going to control a subform with another subform using LinkMaster and LinkChildFields I would consider an intermediate step. Put an unbound hidden textbox on the main form. Set SubformB to LinkChild/LinkMaster to that textbox. Then, set your combo update on SubformA to modify the contents of that main form textbox.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:49
Joined
Feb 19, 2013
Messages
16,553
@Mark
.LinkMasterFields = "Combo1;Combo2"
.LinkChildFields = "ChildField1;ChildField2"
That's the second thing I've learned today:). I've been using composite fields when required up to now and I guess this method makes better use of indexing
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
Right on CJ. Same for me, learning is THE payoff. :)
 

chuckcoleman

Registered User.
Local time
Today, 10:49
Joined
Aug 20, 2010
Messages
357
I'm sorry to report that I still haven't been able to make this work. I did the intermediate step and that hasn't worked. Do you know how I can display in a text box the values in the .LinkMasterFields and .LinkChildFields so I can see what those properties are seeing? This might help me. Thanks,

Chuck
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
But you set those properties in your code, right? If you load a subform programmatically, access might change those settings, but if you set them after the subform loads, then they will be what you set them to.

But to show them, add a textbox to your form, say it's called Text0. Then, add a command button to your form, maybe it's called Button1. Now, write code . .
Code:
Sub Button1_Click()
    With Me.YourSubformControl
        Me.Text0 = .LinkMasterFields & " | " & .LinkChildFields
    End With
End Sub
This shows the values of the properties when you click the button.
Makes sense?
 

chuckcoleman

Registered User.
Local time
Today, 10:49
Joined
Aug 20, 2010
Messages
357
Frustrated. I've successfully tackled some tough problems but this is just driving me nuts! In my subform where the Link Master Fields and Link Child Fields is normally filled in, I created a command button and a text box like you said. In the code you provided I used as the YourSubformControl the Subform Name from the properties of that subform. The full path to that form would normally be shown as Forms![Vendor Form]![Vendor DataX].Form![and a control name]. When I click on the command button I get Run-Time error 2465; can't find the field '|1' referred in your expression. This shouldn't be this hard!
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
I hear your frustration.

The button I am talking about goes on the parent form. LinkMasterFields is a property of a control on the parent form.

And you don't even need a textbox, you can just do it with a message box . . .
Code:
Sub Button1_Click()
    With Me.YourSubformControl
        [B]MsgBox[/B] .LinkMasterFields & " | " & .LinkChildFields
    End With
End Sub

In a form/subform relationship there are three important objects, the parent form, the subform control, and the subform.
Code:
Parent Form
 + Subform Control
    + Subform
The parent form and the subform control are very tightly coupled. The subform can very easily be swapped in and out of the subform control by changing the SourceObject property of the subform control. You may not fully appreciate the importance of the subform control. The subform control is a completely independent object from the subform, and functions as a host to the subform, but it belongs to the parent form. LinkMasterFields and LinkChildFields are properties of the subform control.
 

chuckcoleman

Registered User.
Local time
Today, 10:49
Joined
Aug 20, 2010
Messages
357
WOW, I GOT IT!......or more accurately, WE GOT IT! Thanks Mark, the real salvation was the last posting you provided where I could create a command button on the main form so I could see what Access was passing to the LinkMatsterFields and LinkChildFields. Without that I couldn't tell if I was passing a value or a string. Here's the code that made it work. Note, there is other code in there to clean up and reset items. It was the combination of using the intermediate step with Text boxes on the main form as well as the command button to allow me to see what was being passed. Thank you very much for all of the time and help you provided! I greatly appreciate it!

With Me.PlantComboX
If Not IsNull(Me.PlantComboX) Then
Forms![Vendor Form]![MasterX] = ""
Forms![Vendor Form]![ChildX] = ""
Forms![Vendor Form]![VendorDataX].LinkMasterFields = Forms![Vendor Form]![MasterX]
Forms![Vendor Form]![VendorDataX].LinkChildFields = Forms![Vendor Form]![ChildX]
Forms![Vendor Form]![MasterX] = "Forms![Vendor Form]![Vendor Material Form].Form![MaterialComboX];[Forms]![Vendor Form]![Vendor Material Form].Form![PlantComboX]"
Forms![Vendor Form]![ChildX] = "[Material];[Plant]"
Forms![Vendor Form]![VendorDataX].LinkMasterFields = Forms![Vendor Form]![MasterX]
Forms![Vendor Form]![VendorDataX].LinkChildFields = Forms![Vendor Form]![ChildX]
Else
Forms![Vendor Form]![MasterX] = "Forms![Vendor Form]![Vendor Material Form].Form![MaterialComboX]"
Forms![Vendor Form]![ChildX] = "[Material]"
Forms![Vendor Form]![VendorDataX].LinkMasterFields = Forms![Vendor Form]![MasterX]
Forms![Vendor Form]![VendorDataX].LinkChildFields = [Forms]![Vendor Form]![ChildX]
End If
End With

Thanks again!

Chuck
 

MarkK

bit cruncher
Local time
Today, 08:49
Joined
Mar 17, 2004
Messages
8,178
Cool!

A couple of observations, if you want to tweak a little more . . .
1) you never use the existing With block, so that can be removed
2) you would benefit from a With block for Forms!VendorForm, amending the code, gives . . .
Code:
    With Forms!VendorForm
        If Not IsNull(Me.PlantComboX) Then
[COLOR="Blue"]            .MasterX = ""
            .ChildX = ""
            .VendorDataX.LinkMasterFields = .MasterX
            .VendorDataX.LinkChildFields = .ChildX[/COLOR]
[COLOR="DarkRed"]            .MasterX = .VendorMaterialForm.Form.MaterialComboX & ";" & Me.VendorMaterialForm.FormPlantComboX
            .ChildX = "Material;Plant"
            .VendorDataX.LinkMasterFields = .MasterX
            .VendorDataX.LinkChildFields = .ChildX[/COLOR]
        Else
            .MasterX = .VendorMaterialForm.Form.MaterialComboX
            .ChildX = "Material"
            .VendorDataX.LinkMasterFields = .MasterX
            .VendorDataX.LinkChildFields = .ChildX
        End If
    End With
. . . and then, as you tidy up (and remove spaces from names) then you start to see that the code in red completely overwrites the work done by the code in blue, so you don't need both in that block.

Congrats though, excellent stickwithitness,
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:49
Joined
Jan 20, 2009
Messages
12,849
I get a Run-time error 2335 which says, "You must specify the same number of fields when you set the LinkChildFields and LinkMasterFields properties."

Since the Child and Master properties are set separately, changing the number of fields will cause that error as soon as the first one is changed.

The solution is to temporarily disable error handling.

Code:
On Error Resume Next
' Change LinkMasterFields
' Change LinkChildFields
On Error GoTo yourerrorhandler '(or OnError GoTo 0 if you don't have one.)
 

Users who are viewing this thread

Top Bottom