Anyone know if it is possible to apply a filter to a control? Specifically, I want to reuse the same subform on multiple pages of a tab control, filtering the data in the subform according to criteria specified on each page of the tab control.
Each page on the tab control is named according to its page index. There are 18 pages, named 0 through 17. There are two text boxes on each page, named by concatonating the page name and the control name, i.e. "0Port_ID", "1Port_ID", etc.... The subform controls on each page use the same naming convention (0subfrm, 1subfrm, etc.).
Each page defaults as not visible, and I use the following module to make the pages visible according to the recordset returned by the main form query:
Dim TabName As Integer
Dim RecordCount As Integer
Dim PortID As String
Dim TestTypeID As String
Private Sub Form_Activate()
DoCmd.GoToRecord , , acFirst
TabName = 0
RecordCount = Me.RecordsetClone.RecordCount - 1
For TabName = 0 To RecordCount
'Defining variables as the names of the controls on the current page:
PortID = TabName & "Port_ID"
TestTypeID = TabName & "Test_Type_ID"
'Making the current page visible:
Me.TabReworks.Pages(TabName).Visible = True
'Filling in the caption with the txtTest field from the current record:
Me.TabReworks.Pages(TabName).Caption = txtTest
'Adding filter criteria to the relevant controls on the current page:
Me.TabReworks.Pages(TabName).Controls(PortID) = Port_ID
Me.TabReworks.Pages(TabName).Controls(TestTypeID) = Test_Type_ID
'Looping to the next record:
If Me.CurrentRecord = RecordCount + 1 Then
Exit For
Else: DoCmd.GoToRecord , , acNext
End If
Next TabName
End Sub
I would greatly appreciate any ideas, comments, or suggestions anyone may have. Thanks!
Each page on the tab control is named according to its page index. There are 18 pages, named 0 through 17. There are two text boxes on each page, named by concatonating the page name and the control name, i.e. "0Port_ID", "1Port_ID", etc.... The subform controls on each page use the same naming convention (0subfrm, 1subfrm, etc.).
Each page defaults as not visible, and I use the following module to make the pages visible according to the recordset returned by the main form query:
Dim TabName As Integer
Dim RecordCount As Integer
Dim PortID As String
Dim TestTypeID As String
Private Sub Form_Activate()
DoCmd.GoToRecord , , acFirst
TabName = 0
RecordCount = Me.RecordsetClone.RecordCount - 1
For TabName = 0 To RecordCount
'Defining variables as the names of the controls on the current page:
PortID = TabName & "Port_ID"
TestTypeID = TabName & "Test_Type_ID"
'Making the current page visible:
Me.TabReworks.Pages(TabName).Visible = True
'Filling in the caption with the txtTest field from the current record:
Me.TabReworks.Pages(TabName).Caption = txtTest
'Adding filter criteria to the relevant controls on the current page:
Me.TabReworks.Pages(TabName).Controls(PortID) = Port_ID
Me.TabReworks.Pages(TabName).Controls(TestTypeID) = Test_Type_ID
'Looping to the next record:
If Me.CurrentRecord = RecordCount + 1 Then
Exit For
Else: DoCmd.GoToRecord , , acNext
End If
Next TabName
End Sub
I would greatly appreciate any ideas, comments, or suggestions anyone may have. Thanks!