UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Filter By Option Group And Combobox, Access 2010    
 
   
Delta729
post Jan 13 2018, 08:51 PM
Post#1



Posts: 138
Joined: 11-January 15



I have a form called [frmPO] where I have a filter to filter by the vendor, and that works fine. But I'm trying to add an option group so that I filters by Active, Inactive, or both Vendors.

I tried to combine the filters with the combobox, but I have something wrong (again!)

CODE
Private Sub Frame27_Click()
Dim strFilter As String
Select Case Me.Frame27
        Case 1 'Active
            Forms!frmPO.Frame27 = 1 And [Vendor] = Me.cboVendor
            Forms!frmPO.FilterOn = True
            Me.FilterOn = True
        Case 2 'Inactive
            Forms!frmPO.Frame27 = 2 And [Vendor] = Me.cboVendor
            Forms!frmPO.FilterOn = True
            Me.FilterOn = True
        Case 3 'Both/All records
            Me.FilterOn = False
    End Select
End Sub

This post has been edited by Delta729: Jan 13 2018, 08:53 PM
Attached File(s)
Attached File  Invoice_SES_DB.zip ( 67.8K )Number of downloads: 11
 

--------------------
[font="Georgia"][/font]Daniel
Go to the top of the page
 
moke123
post Jan 13 2018, 10:09 PM
Post#2



Posts: 1,279
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



try this

CODE
Private Sub Frame27_Click()

    MyFilter

End Sub

Private Sub cboVendor_AfterUpdate()

    MyFilter
    
End Sub

Private Sub MyFilter()

    Dim strFilter As String
    
    strFilter = "fVendor = " & Me.cboVendor & " and "

    Select Case Me.Frame27

    Case 1
        strFilter = strFilter & "POInactive = false"
        Me.Filter = strFilter
        Me.FilterOn = True
    Case 2
        strFilter = strFilter & "POInactive = true"
        Me.Filter = strFilter
        Me.FilterOn = True
    Case 3
        strFilter = Left(strFilter, Len(strFilter) - 5)
        Me.Filter = strFilter
        Me.FilterOn = True
    End Select

End Sub

Go to the top of the page
 
Delta729
post Jan 13 2018, 10:43 PM
Post#3



Posts: 138
Joined: 11-January 15



Works Perfectly, Thank you so much!

--------------------
[font="Georgia"][/font]Daniel
Go to the top of the page
 
GroverParkGeorge
post Jan 14 2018, 08:39 AM
Post#4


UA Admin
Posts: 32,836
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

May I suggest a slightly different strategy?


First, it's probably wise to change default control names to something more meaningful, as you've done with the cboVendor combobox. For example, fraPOStatus.

Also, You can pass arguments into a procedure. So we can also take advantage of that.

Note that I also used the Nz() function to prevent failure due to a Null error if the option group gets clicked when nothing has yet been selected in the Vendor combo box.

Finally, I also assumed here that the bound column in the Vendor combo box is an ID field, which would probably be an Autonumber, which would be a Long Integer.

CODE
Private Sub fraPOStatus_Click()
    Call MyFilter(Nz(Me.cboVendor, 0)
End Sub

Private Sub cboVendor_AfterUpdate()
    Call MyFilter(Nz(Me.cboVendor, 0)
End Sub

Private Function MyFilter(ByVal lngVendorID as Long)

    Dim strFilter As String
    
    strFilter = "fVendor =  " & lngVendorID

With Me
    Select Case .fraPOStatus
        Case 1
            strFilter = strFilter & " AND POInactive = false "
        Case 2
            strFilter = strFilter & " AND POInactive = true "
         Case 3
            'nothing needed here, we already have the base filter
    End Select
    .Filter = strFilter
    .FilterOn = True
End With
End Function

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 07:22 AM