Results 1 to 5 of 5
  1. #1
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16

    apply and remove filters on a subform using a combo-box on the main unbound form

    Hi Everyone,



    I know this issue has many threads already, however I can't find a thread that tackles removing the filter from a subform.

    I have a main unbound form (used as a switchboard) with a tab control and a subform "sfrmHeavyMaintenanceRegister" inserted in the first page of the tab control. I want to be ale to filter the subform using a combo-box "cboRecordStatus" on the first page of the tab control, I also want to be able to remove the filter with a command button "cmdClearFilter".

    Is there any simple code to achieve this?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    refer to the subform form object filter and filteron properties e.g.

    me.subformname.form.filter="[Status]=" & cboRecordStatus
    me.subformname.form.filteron=true

    and for the cmdclear button

    me.subformname.form.filter=""

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code:
    sub cboRecordStatus_Afterupdate()
    
    if isNull(cboRecordStatus) then
       me.subformname.form..filterOn = false
    
    else
         me.subformname.form.filter="[Status]=" & cboRecordStatus
         me.subformname.form.filterOn=true
    end if
    end sub

  4. #4
    Carlie017 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    16
    I've tried the code, it returns no records when a value is entered in the combo-box. It does however return all records when the combo-box value is null.

    Have I missed something?

    Combo-box on main form "cboRecordStatus"
    SubForm "sfrmHeavyMaintenanceRegister"
    SubForm Field "LockRecord"

    Code:
    Private Sub cboRecordStatus_Afterupdate()
    
    If IsNull(cboRecordStatus) Then
       Me.sfrmHeavyMaintenanceRegister.Form.FilterOn = False
    
    Else
         Me.sfrmHeavyMaintenanceRegister.Form.Filter = "[LockRecord]=" & cboRecordStatus
         Me.sfrmHeavyMaintenanceRegister.Form.FilterOn = True
    End If
    End Sub

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    is lockrecord a text datatype? if so then you need

    "[LockRecord]='" & cboRecordStatus & "'"

    alternatively check your cboRecordStatus control bound column/rowsource

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2017, 01:26 AM
  2. Replies: 1
    Last Post: 03-16-2017, 10:16 AM
  3. Apply Filter to Subform on Main Unbound Form
    By StuW in forum Programming
    Replies: 2
    Last Post: 10-20-2016, 10:02 AM
  4. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  5. Apply multiple filters to subform
    By Elwood07 in forum Forms
    Replies: 10
    Last Post: 07-06-2015, 12:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums