Capture subform Name and Record Source 2

[sterlecki]

I have a form with a tab control and multiple page controls. Each page has at least one subform on it. Subform data originates from a query based on it's own table or directly from the table.

All tables are in a one to many relationsip with the table that uses page one and it's subform.

From a Command Button Located on the active page of the "Main Form" (ie each page will have one) I would like to capture in a variables:

1. The name of the subform located on the activepage

2. The record source of said subform

I am having trouble referring to them in a generic sense (ie I don't want to code in the specific name of the subform} in order to get to subform.name

or subformName.recordsource

Screen.ActiveControl refers to the "Main Form" and I don't know how to get the subform to be the active control.

 

[MajP]

Private Sub Command7_Click()
 MsgBox getSubFrmName()
 MsgBox getSubFrmRecordsource
End Sub

Public Function getSubFrmName()
  getSubFrmName = getSubFrm.Name
End Function
Public Function getSubFrmRecordsource()
  getSubFrmRecordsource = getSubFrm.RecordSource
End Function
Public Function getSubFrm() As Access.Form
  Dim pg As Access.Page
  Dim ctl As Access.Control
  Set pg = Me.TabCtl0.Pages(TabCtl0.Value)
  For Each ctl In pg.Controls
    If ctl.ControlType = acSubform Then
      Set getSubFrm = ctl.Form
      Exit Function
    End If
  Next ctl
End Function

 

[sterlecki]

Thanks for the quick reply MajP

I set this up as 3 modules and placed your code in the on click event of the cmd button.

I changed the name of TabCtl0 to TabCtl60 to match the name of my tab.

Debugger Hangs:

Public Function getSubFrm() As Access.Form
  Dim pg As Access.Page
  Dim ctl As Access.Control
  Set pg = Me.TabCtl60.Pages([highlight]TabCtl60[/highlight].Value)
  For Each ctl In pg.Controls
    If ctl.ControlType = acSubform Then
      Set getSubFrm = ctl.Form
      Exit Function
    End If
  Next ctl
End Function

Compile Error: Variable not defined.
I'm not sure if there should be a statement such as:

Dim tabCtl As TabControl and something else.

Anyway I think you're close and I appreciate the help.

 

[MajP]

Sorry, it is not close, it is perfectly correct. I would not post code that does not work without specifying so. You have spelled the name of your control incorrectly. If you spell it correctly intellisense would show the properties.

You can error check by simply
msgbox tabCtl60.name
at the beginning of your code. But if intellisense does not come up then your name is incorrect.

 

[PHV]

Provided that the function was created in the main form's module:
Set pg = Me.TabCtl60.Pages(Me.TabCtl60.Value)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Should not matter. It is as optional in the forms module as .value. This would work fine

Set pg = TabCtl0.Pages(TabCtl0)

 

[PHV]

The matter of point was this:

Provided that the function was created in the main form's module

 

[sterlecki]

Ok boys let's put away the tape measure and zip 'em back up.

TabCtl60 is correct

MajP: Your code is "perfect" however as I said earlier

I set this up as 3 modules...

Meaning that I thought these functions were intended for their own modules. If this was not your intent then "my error" but that's why we need help now and then.

PHV: Your suggestion to place these Public Functions in the "Main Form" module is what completed MajP's "perfectly correct" code. It works just fine now and helps me to the next phase of my code.

Many thanks to both of you for the spirited discussion and a star for each. I learned quite a bit today about code, modules and functions.

 

[MajP]

If you want to make this a general function that can be used for any tab control then in a standard module

Public Function getSubFrmName(tabCtl As Access.TabControl)
  getSubFrmName = getSubFrm(tabCtl).Name
End Function
Public Function getSubFrmRecordsource(tabCtl As 

Access.TabControl)
  getSubFrmRecordsource = getSubFrm(tabCtl).RecordSource
End Function

Public Function getSubFrm(tabCtl As Access.TabControl) As Access.Form
  Dim pg As Access.Page
  Dim ctl As Access.Control
  Set pg = tabCtl.Pages(tabCtl.Value)
  For Each ctl In pg.Controls
    If ctl.ControlType = acSubform Then
      Set getSubFrm = ctl.Form
      Exit Function
    End If
  Next ctl
End Function

and call the function from a any form module like so

Private Sub Command7_Click()
 MsgBox getSubFrmName(Me.TabCtl0)
 MsgBox getSubFrmRecordsource(Me.TabCtl0)
End Sub

 

[sterlecki]

Thanks MajP

I will try this. I'm sure this will be a useful variation.

Right now the previous code in the "Main Form" module works great since all of the command buttons will be located on some tab in the "Main Form". Keeping all this together is great.

Once again thanks for your help.