Prev: Help with dCount - “Data Type mismatch in criteria expression”
Next: Help with dCount - "Data Type mismatch in criteria expression"
From: tighe on 25 May 2010 11:19
all,

a little background i was using Screen.ActiveForm![Firm_ID], in a function
to use as a query criteria, now all my [Firm_ID]s, are on tabbed subforms.
my quick fix was have a txt1= [subformname].Form![Firm_ID], but the subform
name could be one of 7, so i am tring to figure out how to capture the name
of the current subform based on the tab page the user is on or the current
records Firm_ID.

i looked around and have been playing with:
Dim ctl As control
s = Screen.ActiveForm.Name
For Each ctl In Forms(s).Controls
If ctl.ControlType = acSubform Then
If ctl.visible Then 'many are visible, so i tried, current,
enabled, others, everything errors or returns page0
MsgBox "The visible subform control is called "
Exit For
End If
End If
Next

i also found what tab page i am
on:Forms![Acct_Full_Processing]![TabCtl71].Value
but i am not sure how further this idea to return the form the user is on or
the Firm_id associated with the current record of the particular sub form.

all forms are continuous
running Xp AC2007

TIA
From: Marshall Barton on 25 May 2010 11:43
tighe wrote:
>a little background i was using Screen.ActiveForm![Firm_ID], in a function
>to use as a query criteria, now all my [Firm_ID]s, are on tabbed subforms.
>my quick fix was have a txt1= [subformname].Form![Firm_ID], but the subform
>name could be one of 7, so i am tring to figure out how to capture the name
>of the current subform based on the tab page the user is on or the current
>records Firm_ID.
>
>i looked around and have been playing with:
> Dim ctl As control
> s = Screen.ActiveForm.Name
> For Each ctl In Forms(s).Controls
> If ctl.ControlType = acSubform Then
> If ctl.visible Then 'many are visible, so i tried, current,
>enabled, others, everything errors or returns page0
> MsgBox "The visible subform control is called "
> Exit For
> End If
> End If
> Next
>
>i also found what tab page i am
>on:Forms![Acct_Full_Processing]![TabCtl71].Value
>but i am not sure how further this idea to return the form the user is on or
>the Firm_id associated with the current record of the particular sub form.
>
>all forms are continuous
>running Xp AC2007
>

Each tab page has a Controls collection so you could search
in that for the (one) subform control on the page.

With Forms(s)
For Each ctl In .TabCtl71.Pages(.TabCtl71).Controls
If ctl.ControlType = acSubform Then
txt1= ctl.Form.Firm_ID

--
Marsh
MVP [MS Access]
From: tighe on 25 May 2010 15:18
that works perfectly!

thanks Marsh

"Marshall Barton" wrote:

> tighe wrote:
> >a little background i was using Screen.ActiveForm![Firm_ID], in a function
> >to use as a query criteria, now all my [Firm_ID]s, are on tabbed subforms.
> >my quick fix was have a txt1= [subformname].Form![Firm_ID], but the subform
> >name could be one of 7, so i am tring to figure out how to capture the name
> >of the current subform based on the tab page the user is on or the current
> >records Firm_ID.
> >
> >i looked around and have been playing with:
> > Dim ctl As control
> > s = Screen.ActiveForm.Name
> > For Each ctl In Forms(s).Controls
> > If ctl.ControlType = acSubform Then
> > If ctl.visible Then 'many are visible, so i tried, current,
> >enabled, others, everything errors or returns page0
> > MsgBox "The visible subform control is called "
> > Exit For
> > End If
> > End If
> > Next
> >
> >i also found what tab page i am
> >on:Forms![Acct_Full_Processing]![TabCtl71].Value
> >but i am not sure how further this idea to return the form the user is on or
> >the Firm_id associated with the current record of the particular sub form.
> >
> >all forms are continuous
> >running Xp AC2007
> >
>
> Each tab page has a Controls collection so you could search
> in that for the (one) subform control on the page.
>
> With Forms(s)
> For Each ctl In .TabCtl71.Pages(.TabCtl71).Controls
> If ctl.ControlType = acSubform Then
> txt1= ctl.Form.Firm_ID
>
> --
> Marsh
> MVP [MS Access]
> .
>
 | 
Pages: 1
Prev: Help with dCount - “Data Type mismatch in criteria expression”
Next: Help with dCount - "Data Type mismatch in criteria expression"