Limit subform record source based on current tab

[BruceM]

I have a form/subform based on related tables (one-to-many). The main form
has a tab control. I would like to filter the subform record source based
on the selected tab. Since the tabs themselves don't have useful Click
events, I have used the Got Focus event of the first control in the tab
order to set the subform record source. There is a function to extract the
tab's caption:

Public Function TabCapt(frm As Form) As String

Dim lngIndex As Long ' PageIndex property
lngIndex = frm.tabCAR.Value
TabCapt = frm.tabCAR.Pages.Item(lngIndex).Caption 'Caption of current tab

End Function

Then there is a function that works with TabCapt:

Public Function EvSQL()

Dim strEvSQL As String
strEvSQL = conEvSQL & TabCapt(Form) & """"
Forms!frmCAR!fsubEvLink.Form.RecordSource = strEvSQL

End Function

Finally, in the Got Focus property for the first control in the tab order:
=EvSQL()

TabCapt and EvSQL are two different functions because sometimes I need the
caption by itself. Anyhow, the system works, but it seems rather
roundabout. In particular, am I overlooking something that would cause
EvSQL to run when the tab is clicked (or when the current tab changes)
rather than by using the GotFocus event?

 

[BruceM]

I forgot to mention that conEvSQL is a public constant that lists the fields
used in the SQL:
"SELECT CAR_ID, EvLink " & FROM tblEvLink WHERE CAR_Section = """
There are more fields, but that's the idea.

 

[Carl Rapson]

The tab control's Change event fires when tabs are selected. In that event,
you can check the tab control's Value property to see the page index of the
page that was selected, then get the page caption from the tab control's
Pages collection for that page index. Something like:

tabControl.Pages(tabControl.Value).Caption

Be warned, the Change event can also fire when the form is first opening, so
you may need to put in some kind of test for form opening to bypass code in
the Change event.

Carl Rapson

 

[BruceM]

So that was the event I needed. Works wonderfully. Thanks for the pointer.
It doesn't look as if I will need to bypass anything at the form's Open
event, but I'll keep that in mind in case something crops up that my testing
so far hasn't shown. It also lets me get rid of that rather awkward string
of functions that I was using, plus I can run the code in one location for
all tabs. You answered another question, too, in that I can run Me.Dirty =
False at the tab control's Change event, which will cause Before Update to
run, so I can perform validation when the user attempts to leave the current
tab.