DataBase - Can I have a Subform's VBA "see" the value passed by a"LinkMasterFields" field?

Hi,

I am working on improving an independent subform so that I do not have
to put any code on the main forms it is being used on.

Jon and Salad just helped me figure out that I can trigger code on
changes to the links based on the subform's "OnCurrent" event.

However, I now have a new problem.  When the value I am linking with my
master/child fields does not exist in my Subform's table, I need to use
the value in the master field.

I have tried googling yet again, and cannot find an answer.  Lots and
lots of pages on setting which fields are linked.  But, not on pulling
out the values being passed in.
me.linkmasterfields.value does not seem to work
me.linkmasterfields.value(0) does not either.
me.recordsource only gives me the name of the table.
me.recordsourcequalifier gives an error.

me.parent.[myfieldname] works.  But, I am not sure the field name is
the same everywhere.

Anyone know if it is possible to have the subform address exactly what
value is being passed for a given "linkmasterfields" column?

Thanks,

Jon

I created a command button on the MAIN form.  I called it
CommandSubform.  I run the following code
Private Sub CommandSubform_Click()
MsgBox Me.MYSUBFORMNAME.LinkMasterFields & vbNewLine & _
Me.MYSUBFORMNAME.LinkMasterFields
End Sub
and it spits out the links.  I could have modified it like so, I guess

Private Sub CommandSubform_Click()
Me.MYSUBFORMNAME.LinkMasterFields = "Test"
Me.MYSUBFORMNAME.LinkMasterFields = "Test"
End Sub

You need to reference the subform control on the Parent to find out what the
LinkMasterFields field is and then get the value of the field from the
Parent itself.

Dirk Goldgar's function below will get you the Subform control.  Then this
will get you the value you want (again in the On Current event of the
Subform):

Dim v As Long
v =  Me.Parent(ParentSubformControl(Me).LinkMasterFields).Value

************************
Function ParentSubformControl(frmMe As Access.Form) As Access.subForm

Dim frmParent As Access.Form
Dim ctl As Control

On Error Resume Next
Set frmParent = frmMe.Parent
If Err.Number <> 0 Then
' This is not a subform.
Set ParentSubformControl = Nothing
Else
For Each ctl In frmParent.Controls
If ctl.ControlType = acSubform Then
If ctl.Form Is frmMe Then
Set ParentSubformControl = ctl
Exit For
End If
End If
Next ctl
End If

Set ctl = Nothing
Set frmParent = Nothing

End Function

*************************

In the form that you are using as the sub-form control's source object you can
use code like the following to return the sub-form control's LinkMasterFields
value.  If that is not what you are attempting to do, I apologize for wasting
your time.

Private Function fGetMasterLinkField()
Dim frm As Form
Dim ctl As Control
Dim strResult As String

Set frm = Forms(Me.Parent.Name)

For Each ctl In frm.Controls
If ctl.ControlType = acSubform Then
If ctl.SourceObject = Me.Name Then
strResult = ctl.LinkMasterFields
Exit For
End If
End If

Next ctl

'   Debug.Print IIf(Len(strResult) > 0, strResult, "No Link master field")
fGetMasterLinkField = strResult

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County