click on page form tab to filter subform

K

KrisN

I have page tab with following names: Flat Rate, Global Rate, MSRP Rate. I
would like to have subform only show the rates for each page tab. How do I
go about doing this. I am new to VBA and from reading all the threads, I
can't seem to find the one that fits my exact need. Please explain
step-by-step since I am new. Let me know if I need to provide more details.
Thank you.
 
G

Graham Mandeno

Hi Kris

Presumably you have a field in the RecordSource of your subform that
contains one of the three values (Flat Rate, Global Rate, MSRP Rate) or some
other text or code that corresponds to these values.

Create an unbound textbox on your main form named "txtLink". The textbox
can be made invisible, but best to leave its Visible property on for
debugging purposes.

Set the LinkMasterFields property of your subform to txtLink, and set
LinkChildFields to the name of the field to be filtered in the subform.

Now, create a Change event procedure for your tab control as follows:

Private Sub TabCtlName_Change()
Select Case TabCtlName
Case 0: txtLink = "Flat Rate"
Case 1: txtLink = "Global Rate"
Case 2: txtLink = "MRSP Rate"
End Select
End Sub

You may have to tweak this slightly to accommodate the order of your tabs
and the matching values to filter.

Now, as you select a different tab, your textbox will have its value set to
the value you want to filter the subform. This filtering will happen
automatically because of the link fields setup.

Finally, you want the correct filter to be applied when you load the form,
so in the Form_Load event procedure, call the change procedure:

Call TabCtlName_Change
 
K

KrisN

Thank you so much!!!! This is exactly what I was looking for and it works
like a charm. You are the best! :)

Graham Mandeno said:
Hi Kris

Presumably you have a field in the RecordSource of your subform that
contains one of the three values (Flat Rate, Global Rate, MSRP Rate) or some
other text or code that corresponds to these values.

Create an unbound textbox on your main form named "txtLink". The textbox
can be made invisible, but best to leave its Visible property on for
debugging purposes.

Set the LinkMasterFields property of your subform to txtLink, and set
LinkChildFields to the name of the field to be filtered in the subform.

Now, create a Change event procedure for your tab control as follows:

Private Sub TabCtlName_Change()
Select Case TabCtlName
Case 0: txtLink = "Flat Rate"
Case 1: txtLink = "Global Rate"
Case 2: txtLink = "MRSP Rate"
End Select
End Sub

You may have to tweak this slightly to accommodate the order of your tabs
and the matching values to filter.

Now, as you select a different tab, your textbox will have its value set to
the value you want to filter the subform. This filtering will happen
automatically because of the link fields setup.

Finally, you want the correct filter to be applied when you load the form,
so in the Form_Load event procedure, call the change procedure:

Call TabCtlName_Change

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

KrisN said:
I have page tab with following names: Flat Rate, Global Rate, MSRP Rate. I
would like to have subform only show the rates for each page tab. How do
I
go about doing this. I am new to VBA and from reading all the threads, I
can't seem to find the one that fits my exact need. Please explain
step-by-step since I am new. Let me know if I need to provide more
details.
Thank you.
Click to expand...
Click to expand...
 
Top