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.
LinkMasterFields
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
LinkMasterFields
Cheers ...
Dave.
Dave.
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: LinkMasterFields
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:
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
Code: Select all
110 Me.YourSubform.SourceObject = "subfrmXYZ"
120 Me.YourSubform.LinkChildFields = "[YourPrimaryKey]"
130 Me.YourSubform.LinkMasterFields = "[YourPrimaryKey]"
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: LinkMasterFields
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:
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:
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: LinkMasterFields
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...
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LinkMasterFields
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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: LinkMasterFields
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.
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.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LinkMasterFields
You're correct, the LinkMasterFields property should be a name, not a value.
Try
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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: LinkMasterFields
I tried the quotation marks, obviously in the wrong place.
Works great now, cheers Hans
Works great now, cheers Hans
Cheers ...
Dave.
Dave.