Checking the subform and changing the link on the fly

[DawnTreader]

Hello All

is there a way to change the linkmasterfields and linkchildfields on the fly
based on the possible values in the subform?

i have a subform that needs to be linked based on either 1 or 2 fields
depending on whether or not the data in the underlying table has a value in a
field.

my current code looks like this:

Private Sub sfrmtblStaticPartPrices_Enter()

Dim subfrmRepisEmpty As Integer

subfrmRepisEmpty = Nz(DLookup("ServiceRepID", "tblStaticValues", "IMWPN
= " & "'" & VisualID & "'"), 0)
MsgBox subfrmRepisEmpty
Select Case subfrmRepisEmpty
Case 0
Me.Parent!sfrmtblStaticPartPrices.LinkMasterFields = "VisualID"
Me.Parent!sfrmtblStaticPartPrices.LinkChildFields = "IMWPN"
'
Forms!frmUserPartListCostingMain.Form!frmUserPartListCosting.Form!sfrmtblStaticPartPrices.LinkMasterFields = "VisualID"
'
Forms!frmUserPartListCostingMain.Form!frmUserPartListCosting.Form!sfrmtblStaticPartPrices.LinkChildFields = "IMWPN"
Case Else
Me.Parent!sfrmtblStaticPartPrices.LinkMasterFields =
"VisualID;ServiceRepID"
Me.Parent!sfrmtblStaticPartPrices.LinkChildFields =
"IMWPN;ServiceRepID"
'
Forms!frmUserPartListCostingMain.Form!frmUserPartListCosting.Form!sfrmtblStaticPartPrices.LinkMasterFields = "VisualID;ServiceRepID"
'
Forms!frmUserPartListCostingMain.Form!frmUserPartListCosting.Form!sfrmtblStaticPartPrices.LinkChildFields = "IMWPN;ServiceRepID"
End Select
Me.Requery
End Sub

as you can see i have tried to use stepping up a form by the "parent"
addressing and by jumping right out of the forms all together using the full
"address" to the form.

i may have the syntax on the paths to the subform wrong, but something tells
me i cant do this.

is there another way of attempting what i am doing?

 

[Jeanette Cunningham]

The code to change the link master and child fields needs to run in the
parent form, not the subform.
You could use dlookup's in the main form to check which fields to use for
the linking with the subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[DawnTreader]

Hello Jeanette

and how do i tell a main form datasheet view to fire my code before opening
the subform?

 

[Jeanette Cunningham]

So, you have a form in data sheet view with a subform on it?
Is there something in the data for that form that you can use to determine
what the link fields should be?

and how do i tell a main form datasheet view to fire my code before
opening
the subform?

You can use code like this on a main form to set up the subform.

Me.NameOfSubformControl.SourceObject = "'SubformName"
Me.LinkChildFields = "FieldName"
Me.LinkMasterFields = "FieldName"

Note, replace my object names with your own object names.

I can't see any way that you can put code in the subform to tell the main
form to change the link fields for the subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Grr - hit send too early.

Replace the code below
You can use code like this on a main form to set up the subform.

Me.NameOfSubformControl.SourceObject = "'SubformName"
Me.LinkChildFields = "FieldName"
Me.LinkMasterFields = "FieldName"

with this

Me.NameOfSubformControl.SourceObject = "'SubformName"
Me.NameOfSubformControl.LinkChildFields = "FieldName"
Me.NameOfSubformControl.LinkMasterFields = "FieldName"


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia