Filter Subform From Option Controls

Jamz · #1 (**permalink**) 01-11-12, 07:57

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

Sinndho · #2 (**permalink**) 01-11-12, 08:35

Quote:

Originally Posted by Jamz

... 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"?

Jamz · #3 (**permalink**) 01-11-12, 08:52

Quote:

Originally Posted by Sinndho

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.

Sinndho · #4 (**permalink**) 01-11-12, 12:47

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

Jamz · #5 (**permalink**) 01-12-12, 04:03

Thanks for that - managed to get it working

Jamz · #6 (**permalink**) 01-12-12, 04:44

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?

Sinndho · #7 (**permalink**) 01-12-12, 12:41

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"

Jamz · #8 (**permalink**) 01-13-12, 03:06

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

Sinndho · #9 (**permalink**) 01-13-12, 03:35

You're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech