By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

Link subform A on Tab 1 to subform B on Tab 2

P: 1
Hello, I have 3 Forms.
My MainForm is linked to Subform A and now I want to link Subform A to Subform B;

My problem is now, that for design reasons I have to put subform A and B in Tabbs.
May 29 '15 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,107
There is an Easy way and a Better way to do this. For this example, there is a MainForm which is a SalesDashboard with SubFormA listing Companies for the Currently Selected SalesPerson. Then there is an additional SubForm, SubformB, a Datasheet of Projects that are limited to only show Projects for the Currently Selected Company.

Easy Way
Base SubFormB on a Query that has criteria that references SubFormA on the MainForm. This is an example of this type of Query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Project.*, Project.CompanyID
  2. FROM Project
  3. WHERE (((Project.CompanyID)=[Forms]![SalesDashboard]![SubFormA]![Form]![CompanyID]));

Better Way
Base SubFormB on the Table of Projects instead of a Query. Then create code to update the Filter of SubFormB whenever a different record is selected on the SubFormA. The update code, should be located in the common area between the SubForms, aka the MainForm. But then the question becomes about how and when to fire the code that sets the Filter. This is the fun part. In SubFormA, there is this some code like this:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private nParent As String
  5. Public Sub setParent(ByRef sParent As String)
  6.     nParent = sParent
  7. End Sub
  8. Private Sub Form_Current()
  9.     If Len(nParent) > 0 Then
  10.         If isLoaded(nParent) Then
  11.             Forms(nParent).subFormCurrent
  12.         End If
  13.     End If
  14. End Sub
This code provides a way to let SubFormA know who it's Parent is, as well as call back to the Parent Form (MainForm) when it has changed records.
This uses the isLoaded() function that comes in handy and should be put in a Module:
Expand|Select|Wrap|Line Numbers
  1. Function isLoaded(ByRef sFormName As String) As Boolean
  2.     ' Determines if a Form is loaded
  3.     Dim i As Integer
  5.     isLoaded = False
  6.     For i = 0 To Forms.Count - 1
  7.         If Forms(i).FormName = sFormName Then
  8.             isLoaded = True
  9.             Exit Function
  10.         End If
  11.     Next
  12. End Function
With these two pieces in place, all that is left is to write the code for the MainForm:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Sub Form_Load()
  5.     ' Setup SubForms
  6.     Call Me.SubFormA.Form.setParent(Me.Name)
  7.     Me.SubFormB.Form.Filter = "1=0"
  8.     Me.SubFormB.Form.FilterOn = True
  10. End Sub
  12. Public Sub subFormCurrent()
  14.     ' Filter SubForm
  15.     Dim sFilter As String
  16.     sFilter = "CompanyID=" & Nz(Me.SubFormA.Form!CompanyID, "")
  17.     Me.SubFormB.Form.Filter = sFilter 
  18.     Me.SubFormB.Form.FilterOn = True
  20. End Sub
Hopefully this code is accurate Syntax wise as it was knitted together from a couple different places.
May 29 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.