Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    button to apply filter (2003)

    I have a form displaying client data that includes all records from a particular query. I would like to have a command button in the form header that will apply a filter to the form so that clients whose status is one of several static options that are normally selected from a combo box eg "deceased" and "discharged" (plus perhaps others). On clicking the button the button's visible property would be set to false - ie it disappears, and a second button's visible property would be set to true, this button - when clicked - would remove the applied filter. I imagine this would be a fairly common thing to do, could someone point me in the direction of info on doing this type of thing, or perhaps some sample code?

    Cheers, Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: button to apply filter (2003)

    I'd use a single command button and change its caption instead of using two and having only one of the two visible.
    Let's say the command button is named cmdFilter, that the combo box is named cboStatus and that it is used to filter on the text field Status.
    Here is an example of code for the On Click event procedure of the command button:
    <code>
    Private Sub cmdFilter_Click()
    If Me.FilterOn Then
    Me.cmdFilter.Caption = "Set Filter"
    Me.Filter = ""
    Me.FilterOn = False
    ElseIf Not IsNull(Me.cboStatus) Then
    Me.cmdFilter.Caption = "Remove Filter"
    Me.Filter = "[Status] = " & Chr(34) & Me.cboStatus & Chr(34)
    Me.FilterOn = True
    End If
    End Sub
    </code>
    Modify the names to fit your situation.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: button to apply filter (2003)

    OK I'm nearly there - I understand how the first part of the code works (the If section) but not really the ElseIf. I need to elaborate on my original post. I want the filter to show all records that meet 2 or 3 criteria simultaneously and can hard code this into the OnClick event ie the form needs to show all records whose status is deceased OR is discharged. How would you modify the ElseIf section of your sample code?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: button to apply filter (2003)

    If you want to hard-code the condition, you can use an Else part instead of an ElseIf part - no need to check whether a combo box has been filled in.
    <code>
    Private Sub cmdFilter_Click()
    If Me.FilterOn Then
    Me.cmdFilter.Caption = "Set Filter"
    Me.Filter = ""
    Me.FilterOn = False
    Else
    Me.cmdFilter.Caption = "Remove Filter"
    Me.Filter = "[Status] In ('Deceased', 'Discharged')"
    Me.FilterOn = True
    End If
    End Sub
    </code>
    As in my previous reply, this assumes that Status is a text field. If it is a number field with codes for the various statuses, you'd use something like
    <code>
    Me.Filter = "[Status] In (2, 5)"
    </code>
    where 2 and 5 are the relevant values. You can easily expand this to include more values.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: button to apply filter (2003)

    Thank you Hans! All working fine now. I realised that actually I wanted to exclude rather than include those terms (and there are heaps of options in the "status" combo box). This was easily sorted by changing the line to:
    Not In ('Deceased', 'Discharged').

    Roger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •