\n<\/strong><\/span>To toggle back and forth between (Yes\/No Data Type) All and Completed (Tasks, Projects, Orders, etc.)…<\/p>\n
- \n
- Step 1<\/li>\n<\/ul>\n
In the Header<\/strong> of you Form<\/strong> place an UNBOUND Check Box and name it chkMyFilter<\/strong><\/p>\n
- \n
- Step 2<\/li>\n<\/ul>\n
In the After_Update event of the UNBOUND Check Box copy\/paste everything between the Private Sub and End Sub lines below. After you have copied it change pDone<\/strong> to the name of your Yes\/No field in your Table, not the name of the field Control name on the Form.<\/p>\n
Private Sub chkMyFilter_AfterUpdate()<\/p>\n
\r\n\u00a0\u00a0\u00a0 If Me.chkMyFilter = True Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.Filter = "pDone = " & vbTrue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.FilterOn = True\r\n\u00a0\u00a0 Else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Me.Filter = ""\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.FilterOn = False\r\n\u00a0\u00a0 End If\r\n<\/pre>\n
End Sub<\/p>\n
Close and save (or save and then close) and try it out.<\/p>\n
<\/p>\n
Example 2
\n<\/strong><\/span>In this example you can use values from a look-up table to filter your records. This allows a lot more flexibility because I can add\/edit and\/or subtract items from the list via the table. My table is…<\/p>\ntblSources<\/p>\n
- \n
- Step 1<\/li>\n<\/ul>\n
In the Header of you Form place an UNBOUND Combo Box and name it cboS<\/strong>SourceID. (The double S is not a mistake, it is because in the Detail section of my Form I have a Combo Box named cboSourceID, so, I put an additional S<\/strong> to indicate this is my cbo*Search<\/strong>*SourceID used to Filter my records.<\/p>\n
- \n
- Step 2<\/li>\n<\/ul>\n
Set the Row Source of cboSSourceID the same as the Combo Box in the Detail section of your Form. For this example I used…<\/p>\n
SELECT tblSources.sSourceID, tblSources.sSource
\nFROM tblSources
\nWHERE (((tblSources.sActive)=True))
\nORDER BY tblSources.sSortOrder;<\/p>\nOther settings<\/strong>:<\/p>\n
Column Count =2
\nBound Column = 1
\nColumn Widths – 0″;1″<\/p>\n- \n
- Step 3<\/li>\n<\/ul>\n
In the After_Update event of the UNBOUND Combo Box copy\/paste everything between the Private Sub and End Sub lines below. After you have copied it change pSourceID<\/strong> to the field name in your Table, not the name of the field control name on the Form and in your Table and change the cboSSourceID<\/strong> to the name of your UNBOUND Combo Box in the Header.<\/p>\n
Private Sub cboSSourceID_AfterUpdate()<\/p>\n
\r\n\u00a0\u00a0 Me.Filter = "[pSourceID] = " & Me.cboSSourceID\r\n\u00a0\u00a0 Me.FilterOn = True\r\n<\/pre>\n
End Sub<\/p>\n
- \n
- \n
- \n
- Step 3a<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
To clear the Filter you can use the Double_Click of cboSSourceID<\/strong>…<\/p>\n
Private Sub cboSSourceID<\/strong>_DblClick(Cancel As Integer)<\/p>\n
\r\n\u00a0\u00a0\u00a0Me.Filter = ""\r\n\u00a0\u00a0 Me.FilterOn = False\r\n\u00a0\u00a0 Me.cboSSourceID = ""\r\n<\/pre>\n
End Sub<\/p>\n
…or add the above code a Clear Filter<\/strong> Command Button.<\/p>\n
Close and save (or save and then close) and try it out.<\/p>\n
<\/p>\n
Example 3
\n<\/strong><\/span>This example allows you to create a Query as much or as little criteria as you like for the filter. You can use one Command Button to toggle back and forth between All\/Filtered, for this example I am using one Command Button using the Caption property of the Command Button to toggle back and forth.<\/p>\n- \n
- Step 1<\/li>\n<\/ul>\n
Create two Record Sources, one that will show all the Records, such as the Table and another that shows just the filtered Records such as a Query, i.e.:<\/p>\n
tblPending for all the Records
\nqryPending for the filtered Records.<\/p>\nqryPending
\nSELECT tblPending.pPendingID, tblPending.pActionID, tblPending.pQuantity, tblPending. pReceiveDate, etc…
\nFROM tblPending
\nWHERE (((tblPending.pActionID) Is Null) AND ((tblPending.pReceiveDate) Is Null));<\/p>\n- \n
- Step 2<\/li>\n<\/ul>\n
Set the Record Source of your Form using the *filtered* query. In this example that would be qryPending. (If you want to open to showing ALL records you will need to reverse the code!)<\/p>\n
- \n
- Step 3<\/li>\n<\/ul>\n
![\"ShowAll\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/07\/ShowAll.png\")
![\"Filtered\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2014\/07\/Filtered.png\")
<\/p>\nPlace a Command Button in your Forms Header and name it cmdShowAll<\/strong>. If the Wizard pops up, just press Cancel, we are going to put our own code there so we won\u2019t be using any of the Wizards code. Change the Caption property to say \u201cSHOW ALL<\/strong>\u201d. For my Command button I used these settings…<\/p>\n
Font Name= Tahoma
\nFont Size = 7
\nFont Weight = Bold<\/p>\nSide note:\u00a0 The buttons will change Caption depending on which Record Source is showing, which is why you only need to add one Command Button.\u00a0 Make sure you follow Step 4 and Step 5 for the Caption to toggle properly.<\/p>\n
- \n
- Step 4<\/li>\n<\/ul>\n
At the top of your Forms Module under…<\/p>\n
Option Compare Database
\nOption Explicit<\/p>\nSide note: If those two lines are not at the top of your Module put them there, particularly, Option Explicit which will *stop* when it finds an undeclared variable(s).<\/p>\n
Mine says SHOW ALL or COMPLETED ONLY but yours can say whatever you want. Just remember to make sure the Command Button is wide enough to show the longest Caption.<\/p>\n
…place these two lines<\/p>\n
\r\nConst ShowAll = "SHOW ALL"\r\nConst ShowFiltered = "COMPLETED ONLY"\r\n<\/pre>\n
- \n
- Step 5<\/li>\n<\/ul>\n
In the On_Click event of the Command Button copy\/paste everything between the Private Sub and End Sub lines below.<\/p>\n
Private Sub cmdShowAll_Click()<\/p>\n
\r\nOn Error Resume Next 'Lazy mans error code\r\n\r\n\u00a0\u00a0 Select Case Me.cmdShowAll.Caption\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case ShowFiltered\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.RecordSource = "qryPending"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.cmdShowAll.Caption = ShowAll\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case ShowAll\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.RecordSource = "tblPending"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Me.cmdShowAll.Caption = ShowFiltered\r\n\u00a0\u00a0 End Select\r\n<\/pre>\n
End Sub<\/p>\n
Close and save (or save and then close) and try it out.<\/p>\n
NOTE<\/strong><\/span>: When filtering a Subform use…<\/p>\n
\r\nOn Error Resume Next 'Lazy mans error code\r\n\u00a0\u00a0 Me.NameOfSubform.Form.Filter ="[FieldBeingSearchedOn] = '" & Me!txtYourSearchTextBox & "'"\r\n Me.NameOfSubform.Form.FilterOn = True\r\n<\/pre>\n
<\/p>\n
Here are more filtering examples…<\/p>\n
Allen Browne\u2019s Search Form<\/a>
\nAllen Browne\u2019s Filter a Form on a Field in a Subform<\/a><\/p>\n<\/div>\n
- Step 5<\/li>\n<\/ul>\n
- Step 4<\/li>\n<\/ul>\n
- Step 3<\/li>\n<\/ul>\n
- Step 2<\/li>\n<\/ul>\n
- Step 1<\/li>\n<\/ul>\n
- Step 3a<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
- \n
- Step 3<\/li>\n<\/ul>\n
- Step 2<\/li>\n<\/ul>\n
- Step 1<\/li>\n<\/ul>\n
- Step 2<\/li>\n<\/ul>\n