Subform filtering (1 Viewer)

[36372]

Hi all, i'm working on a database for my HR manager.
There is a little functionality that i'd like to put in to help her out a little.

I have a form containing a subform.
the subform contains all the members of staff.

I would like to put a way of filtering the information in the sub form.

For example we have about 10 sites, so i'd like to put a textbox on the main form and a radio button that allows her to filter the projects column if the projects radio box is clicked. Or if a different radio button is clicked filter a different column.

I think that i'm going to have to do this in VBA. I'm guessing i'd have to change the record source or query being called to update the table... but not sure where to start??

Any suggestions

 

[John Big Booty]

In the on click event of your Command Button you will need some code along the lines of;

    Dim strFilter As String
    
    Me.Refresh
    
    If Me.YourOptionGroup = 1 Then
         strFilter = "ID =" & Me.YourFilterTextBox
    Else If Me.YourOptionGroup = 2 Then
         strFilter = "SName ='" & Me.YourFilterTextBox & "'"
   Else
         strFilter = "Project ='" & Me.YourFilterTextBox & "'"
   End If

    Forms!YourMainForm!YourSubForm.Form.Filter = strFilter
    Forms!YourMainForm!YourSubForm.Form.FilterOn = True

This code will work however there is an important caveat that needs to be made; The code as it stands will not validate the search term in your Filter Box to ensure that it matches the search term selected from your option group. The code is designed merely to demonstrate the principal of filtering a sub form. Note the slight difference in the syntax when your filter is working with Numeric values such as ID, and string values such as SName.