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