Tab Control & Sub Forms

[Ken]

I have a tab control with 9 tabs that I want to control a sub-form.

Is there a way to make the sub-form query a table by an ID based on which
tab is selected?

 

[Marshall Barton]

I have a tab control with 9 tabs that I want to control a sub-form.

Is there a way to make the sub-form query a table by an ID based on which
tab is selected?

What are you trying to do??

If you want the subform to filter to a record based in the
tab page, then use code to set the ID value in a hidden text
box. Then use the text box in the subform control's Link
Master Fields property.

Sub tabcontrol_Change()
Select Case Me.tabcontrol
Case 1 'page index
Me.txtLink = 123 ' first page ID
Case 2
Me.txtLink = 234 ' second page ID
. . .
End Select

If you want the subform to load it's data from a different
table, then you can use similar code to set the subform's
RecordSource property.

You did not explain why you are using a tab control to
select an ID. What are you going to do when you need to
deal with another ID? If you think about that, you should
come to the conclusion that it is not a good idea to make a
form's design dependent on data values.

 

[Ken]

Marshall,

I am trying to do a comparison of one ID to another ID. They are not the
same ID so I can not use a master / child link which is what I tried
initially.

My form has a sub-form for bill of materials for Product #1 (ProdID). My
tabs have a list of other products that I would like to compare to (Product
#2 to #9) (ProdID = 2 to 9). I was hoping that as the user used the tabs the
sub-form I placed on the tab control could be used to filter my ProdID. The
first tab gets the data from Product #2 but the remaining tabs are blank.

Thanks.

 

[Marshall Barton]

I am trying to do a comparison of one ID to another ID. They are not the
same ID so I can not use a master / child link which is what I tried
initially.

My form has a sub-form for bill of materials for Product #1 (ProdID). My
tabs have a list of other products that I would like to compare to (Product
#2 to #9) (ProdID = 2 to 9). I was hoping that as the user used the tabs the
sub-form I placed on the tab control could be used to filter my ProdID. The
first tab gets the data from Product #2 but the remaining tabs are blank.

If I followed that, you want each successive tab to exclude
whatever products were selected in the previous tabs.

This requires that you filter the previously selected
products in each subform's record source. (The Filter
property might seem like an easier way, but prior to A2007
there was a bug that made a mess of things in a multiple
subform arrangement.) To do this, you could create a
special purpose query for each subform. Each query's
product ID field's criteria would be like:
Query2
Not IN(Forms!theform.subform1.Form.ProductID)

Query3
Not
IN(Forms!theform.subform1.Form.ProductID,Forms!theform.subform2.ProductID)

Query4
Not
IN(Forms!theform.subform1.Form.ProductID,Forms!theform.subform2.ProductID,Forms!theform.subform3.ProductID)

and so on.

If all the tab pages are using the same form object as its
subform, then it would be a lot cleaner if you did the
equivalent using VBA code in the tab control's Change event.
The code would construct the SQL statement and stuff it into
the tab page's subform's RecordSource property. Post back
with more details if you need help with this.