Referring to Tab Control Page Caption

[Coolboy55]

Hi,

I have a subform whose RecordSource I want to be an SQL statement that selects only records from the Tasks table where the TaskType field matches the caption of the active page of the main form's tab control. In other words, I have a main form with a tab control with several pages, and I want the subforms on each page of the tab control to select only records whose TaskType field matches the name of that page of the tab control.

I'm having trouble with the syntax, I think. Here's what I have:

SELECT Tasks.*
FROM Tasks
WHERE ([Tasks].[TaskType])=[Forms]![Tasks].[TabControl].[Pages(0)].[Caption];

Can anyone help me out? :)

Thanks!

CB55

[sierra7]

Hi
I think what you should do here is to have a dummy field on you main form (it can be invisible), then use the On_Change event of the Tab Control to set the value in the dummy field.

The dummy field would be the reference for Link Master Field in your subform(s). You sub-forms query just has to find the data without reference to anything else and then Access will filter via the Link Child Field

As you clicked the different tabs the dummy field would be updated and the sub-form show the appropriate data.

I would probably try and only use one subform. Put it behind the Tab Control and set the Tab control to Transparent might work. (or reduce the height of the Tab Control.)

If there were performance issues when switching between Tab you might consider repeating the same sub-form on each Tab but each with it's own 'dummy' field for the MasterLinkField , but the trade-off would be that this would take longer to load (as all instances may have to load befor you see the first Tab. Try it an see!) In this case you would not need code for the On_Change event as the dummies would be hard coded.

S7