Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    14

    Unanswered: Filter Subform From Option Controls

    Hi Everyone,

    I'm a bit rusty with Access at the moment, been a while since i made anything big like I'm trying, and in need of a bit of help.

    Basically I have a form with a sub form in, this subform is already filtered by name by a drop down menu at the top of the main form, but in the main form, I have check boxes to show open and closed requests, and i somehow need to filter the sub form to show open requests when the "open" option is selected and closed requests when the "closed" option is selected.

    I have a feeling this will need to be done in VB on the actual button its self, but I cannot find nor figure out myself how to do this.

    I have attached the database I am working on
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Jamz View Post
    ... ilter the sub form to show open requests when the "open" option is selected and closed requests when the "closed" option is selected.
    How do you dertermine that a request is "open" or "closed"?
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    14
    Quote Originally Posted by Sinndho View Post
    How do you dertermine that a request is "open" or "closed"?
    Forgot to mention that part - there is table named WR that has a field called "DateClose" - when this is populated with a date, the request is closed, else this is blank which means its open.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Add a third option in [RequestTypeFrame]: All Requests (value=3)

    2. Use this code:
    Code:
    Private Sub RequestTypeFrame_AfterUpdate()
    
        Select Case Me.RequestTypeFrame
            Case 1  ' Open requests
                Me![View Requests subform].Form.Filter = "DateClose Is Null"
                Me![View Requests subform].Form.FilterOn = True
            Case 2
                Me![View Requests subform].Form.Filter = "DateClose Is Not Null"
                Me![View Requests subform].Form.FilterOn = True
            Case 3  ' All requests
                Me![View Requests subform].Form.FilterOn = False
        End Select
        
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    14
    Thanks for that - managed to get it working
    Last edited by Jamz; 01-12-12 at 05:07.

  6. #6
    Join Date
    Jan 2012
    Posts
    14
    Another thing I will need is to be able to filter down by work request number from the text box.

    I have created this (using the above kinda):

    Code:
    Private Sub txtwrnum_Change()
        Me![View Requests subform].Form.Filter = "WRNum Like '*" & [txtwrnum] & "*'"
        Me![View Requests subform].Form.FilterOn = True
    End Sub
    But it doesnt seem to work correctly, it only filters once i delete the data and "tab" out of the text box.

    I would like it to filter once i enter the details, then when the text box is blank, to show all records.

    Also I would like it to intigrate with the above code, so it will only show open requests with the work request num or closed requests.

    Is this do-able?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I would use the AfterUpdate event of the textbox, not the Change event.

    2. The filter string can specify more than one criteria:
    Code:
    "WRNum Like '*" & Me.txtwrnum.Value & "*' AND DateClose Is Null"
    Have a nice day!

  8. #8
    Join Date
    Jan 2012
    Posts
    14
    Thanks for that - works perfect.

    Thats the problem with me, once someone shows me the main part of the code, i can figure out what needs to be done to get it working for the rest.

    So my whole code now looks like:

    Code:
    Private Sub RequestTypeFrame_AfterUpdate()
     Select Case Me.RequestTypeFrame
            Case 1  ' Open Requests
                Me![View Requests subform].Form.Filter = "DateClose Is Null"
                Me![View Requests subform].Form.FilterOn = True
            Case 2 ' Closed Requests
                Me![View Requests subform].Form.Filter = "DateClose Is Not Null"
                Me![View Requests subform].Form.FilterOn = True
            Case 3  ' All Requests
                Me![View Requests subform].Form.FilterOn = False
        End Select
    End Sub
    
    Private Sub txtwrnum_AfterUpdate()
    
     Select Case Me.RequestTypeFrame
            Case 1  ' Open Requests
                Me![View Requests subform].Form.Filter = "WRNum Like '*" & Me.txtwrnum.Value & "*' AND DateClose Is Null"
                Me![View Requests subform].Form.FilterOn = True
            Case 2 ' Closed Requests
                Me![View Requests subform].Form.Filter = "WRNum Like '*" & Me.txtwrnum.Value & "*' AND DateClose Is Not Null"
                Me![View Requests subform].Form.FilterOn = True
            Case 3  ' All Requests
                Me![View Requests subform].Form.Filter = "WRNum Like '*" & Me.txtwrnum.Value & "*'"
                Me![View Requests subform].Form.FilterOn = True
        End Select
    End Sub
    Works perfect

    Now onto my other issue on my other thread

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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