LinkMasterFields

[D Willett]

I have a tabcontrol with 3 tabs, each tab has a subform (sbfqryU, sbfqryA, sbfqryC) showing data from various queries.
Under the tab control, I have another subform (sbfExtraComment) which has a LinkMasterField of: [sbfqryU].Form![Est_NO] which works well.
I Requery the subform from sbfqrU with a click event on the field of [Est_NO]:Forms!frmStatus.sbfExtraComment.Requery

If I change to the 2nd tab, I want to still use the form under the tab control. The problem lies with changing the LinkMasterFields from VBA.
How do you do this? The source of sbfExtraComment only ever uses one table.

[Wendell]

I do something like that in a case where we actually change the subform on the fly depending on the kind of data being processed - the code looks rather like this:

Code: Select all

110       Me.YourSubform.SourceObject = "subfrmXYZ"
120       Me.YourSubform.LinkChildFields = "[YourPrimaryKey]"
130       Me.YourSubform.LinkMasterFields = "[YourPrimaryKey]"

Now for some caveats - you may not need to change the Link properties if you have the same keys from each form. Also note that you may see some screen flash and other stuff when you execute that code - if you do you may want to turn screen updating off and then back on. I hope I have understood your objective, you want to synchronize the subform under the tab control to which ever tab has focus. In that case the code would te tied to the On Change event of the tab control

[D Willett]

I see where you're coming from but I don't think it's quite right.
Each tab has it's own subform. ( sbfqryU, sbfqryA, sbfqryC ) the form I need to change the LinkMasterField is sbfExtraComment which is underneath ( Not embedded ) the TabControl:

[Wendell]

I rather inferred that from your description - and I gathered you were applying a filter to sbfExtraComment based on the record selected in the master form. What I dind't anticipate was that you were using a continuous form. And you can link a subform to a form that is other than the form the subform control is embedded in - but things do get a bit tricky. Without seeing the code you are using it gets difficult to see whether you can apply a new filter based on the record currently active in Tab2 or Tab3 and get what you want. In any event the OnChange event is where the code should run. Using the approach I suggested would eliminate the need to use filters as a subform does all that linking automatically - but I've not had good success in linking to a continuous form...

[HansV]

Hi Dave, I think you'll need code in the On Change event of the tab control, as Wendell suggests. Let's say the tab control is named ctlTab.

Code: Select all

Private Sub ctlTab_Change()
  Select Case ctlTab.Value
    Case 0 ' first tab
      Me.sbfExtraComment.LinkMasterFields = Me.sbfqryU!Est_NO
    Case 1 ' second tab
      Me.sbfExtraComment.LinkMasterFields = Me.sbfqryA!Est_NO
    Case 2 ' third tab
      Me.sbfExtraComment.LinkMasterFields = Me.sbfqryC!Est_NO
  End Select
  ' Optional: requery subform - shouldn't be necessary
  Me.sbfExtraComment.Requery
End Sub

[D Willett]

Hi Hans ( Thanks for the help Wendell)

I've tried a couple of variations of your code and each time the tab changes, I get asked for a parameter of the first record.

Select Case TabCtl0
Case 0 'First Page
'sbfqryU.Requery
Me.sbfExtraComment.LinkMasterFields = Me.sbfqryU.Form!EST_NO
Case 1 'Second page
'sbfqryA.Requery
Me.sbfExtraComment.LinkMasterFields = Me.sbfqryA.Form!EST_NO
Case 2 'Third page
'sbfqryC.Requery
Me.sbfExtraComment.LinkMasterFields = Me.sbfqryC.Form!EST_NO
Case 3 'Third page
'sbfqryF.Requery
Me.sbfExtraComment.LinkMasterFields = Me.sbfqryF.Form!EST_NO
Case 4 'Third page
'sbfqryX.Requery
Me.sbfExtraComment.LinkMasterFields = Me.sbfqryX.Form!EST_NO
End Select

I fear EST_NO is a text field checking back to the table.

[HansV]

You're correct, the LinkMasterFields property should be a name, not a value.

Try

Code: Select all

Select Case TabCtl0
Case 0 'First Page
    Me.sbfExtraComment.LinkMasterFields = "sbfqryU!EST_NO"
Case 1 'Second page
    Me.sbfExtraComment.LinkMasterFields = "sbfqryA!EST_NO"
Case 2 'Third page
    Me.sbfExtraComment.LinkMasterFields = "sbfqryC!EST_NO"
Case 3 'Fourth page
    Me.sbfExtraComment.LinkMasterFields = "sbfqryF!EST_NO"
Case 4 'Fifth page
    Me.sbfExtraComment.LinkMasterFields = "sbfqryX!EST_NO"
End Select

[D Willett]

I tried the quotation marks, obviously in the wrong place.

Works great now, cheers Hans