Subform link

[Guest]

Greetings! How about those Red Sox!
More to the point, I have a main form with three subforms. Sub1 is a list of
clients, sub 2 and 3 show details about those clients. I have a textbox on my
main form that reads the clientID from sub1 so that I can use that to link
the other two subforms. All is well if just sub2 or sub3 is on the form. But
when both 2 and 3 are on the form, I get constant refresh type activity. It
goes nuts. Why is this and is there a better strategy to accomplish this?
Thanks, guys!

 

[Allen Browne]

Hi sam.

It's very easy to get this type of cyclic dependency.

You could unbind the text box on the main form (i.e. remove anything in its
ControlSource). Then use the Current event of the first subform to assign it
a value, e.g.:
Private Sub Form_Current
Me.Parent![ClientID] = Me.ClientID
End Sub

Make sure that subform1 does not have the ClientID text box named in its
LinkMasterFields property.


If you are still stuck, you could directly assign the RecordSource of the
other subforms in the Current event of the first one. If you do that, Access
is likely to assign something to the their LinkMasterFields/LinkChildFields,
so you may need to clear those also:
Private Sub Form_Current
Dim strWhere As String

If IsNull(Me.[ClientID]) Then
strWhere = (False);"
Else
strWhere = "([ClientID] = " & Me.[ClientID] & ";"
End If
With Me.Parent![Sub2]
.Form.RecordSource = "SELECT * FROM [Table2] WHERE " & strWhere
.LinkMasterFields = vbNullString
.LinkChildFields = vbNullString
End With
'etc.
End Sub