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
> Filtering A Subform, Access 2016    
 
   
ken123
post May 27 2020, 09:55 AM
Post#1



Posts: 103
Joined: 13-June 08



I'm having trouble filtering a subform (Datasheet view) from a main form.
I've placed the code below in a Module, and have a button which activates the code
When I run the code, nothing occurs. Not receiving any errors.
Any thoughts on how to go about troubleshooting this, or if something stands out as obviously wrong?

main form: frm_EquipmentSearch (filter criteria controls are located here)
Subform: frm_EquipmentSource (info i'm trying to filter)

CODE
Sub SetFormFilter()

   ' declare variable to use for criteria
   Dim varFilter As Variant
  
   ' initialize criteria to be nothing
   varFilter = Null

   ' reference the form that the code is behind
   With [Forms]![frm_EquipmentSearch]  'Me
  
    If Not IsNull(.txtSearchBldg) Then
        varFilter = (varFilter + " AND ") _
        & "[Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form![BLDGNUM]= '" & .txtSearchBldg & "'"
    End If
    If Not IsNull(.txtSearchRM) Then
        varFilter = (varFilter + " AND ") _
        & "[Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form![RMNUM]= '" & .txtSearchRM & "'"
    End If
    If Not IsNull(.txtSearchRR) Then
        varFilter = (varFilter + " AND ") _
        & "[Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form![RRNUM]= '" & .txtSearchRR & "'"
    End If
    If Not IsNull(.txtSearchSys) Then
        varFilter = (varFilter + " AND ") _
        & "[Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form![SYSTEMID]= '" & .txtSearchSys & "'"
    End If
    If Not IsNull(.txtSearchSN) Then
        varFilter = (varFilter + " AND ") _
        & "[Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form![DEVSN]= '" & .txtSearchSN & "'"
    End If

    If Not IsNull(varFilter) Then
        ' if there is a filter, then apply it and turn filter on
        .Filter = varFilter
        .FilterOn = True
    Else
        ' no filter, turn filter off to show all records
        .FilterOn = False
    End If
            
   End With ' Me

End Sub


Go to the top of the page
 
Doug Steele
post May 27 2020, 10:19 AM
Post#2


UtterAccess VIP
Posts: 22,305
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Your Filter string should be referring to fields in the recordset, not controls on the form.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
tina t
post May 27 2020, 04:04 PM
Post#3



Posts: 6,691
Joined: 11-November 10
From: SoCal, USA


QUOTE
I'm having trouble filtering a subform (Datasheet view) from a main form.

CODE
   With [Forms]![frm_EquipmentSearch]  'Me
  
    If Not IsNull(.txtSearchBldg) Then
    ...
    ...
    End If

    If Not IsNull(varFilter) Then
        ' if there is a filter, then apply it and turn filter on
        .Filter = varFilter
        .FilterOn = True
    Else
        ' no filter, turn filter off to show all records
        .FilterOn = False
    End If
            
   End With ' Me

in addition to Doug's observation, your code is setting the mainform .Filter property, not the subform .Filter property.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ken123
post May 28 2020, 05:49 AM
Post#4



Posts: 103
Joined: 13-June 08



Thank you.
I make this same mistake a lot, focusing on the form, and forgetting it is referencing the recordset.

Go to the top of the page
 
ken123
post May 28 2020, 06:53 AM
Post#5



Posts: 103
Joined: 13-June 08



In case anyone is interested in seeing the functional code in the future:

CODE
Sub SetFormFilter()

   ' declare variable to use for criteria
   Dim varFilter As Variant
  
   ' initialize criteria to be nothing
   varFilter = Null

   ' reference the form that the code is behind
   With [Forms]![frm_EquipmentSearch]  'Me
  
    If Not IsNull(.txtSearchBldg) Then
        varFilter = (varFilter + " AND ") _
        & "[BLDGNUM]= '" & .txtSearchBldg & "'"
    End If
    If Not IsNull(.txtSearchRM) Then
        varFilter = (varFilter + " AND ") _
        & "[RMNUM]= '" & .txtSearchRM & "'"
    End If
    If Not IsNull(.txtSearchRR) Then
        varFilter = (varFilter + " AND ") _
        & "[RRNUM]= '" & .txtSearchRR & "'"
    End If
    If Not IsNull(.txtSearchSys) Then
        varFilter = (varFilter + " AND ") _
        & "[SYSTEMID]= '" & .txtSearchSys & "'"
    End If
    If Not IsNull(.txtSearchSN) Then
        varFilter = (varFilter + " AND ") _
        & "[DEVSN]= '" & .txtSearchSN & "'"
    End If

    If Not IsNull(varFilter) Then
        ' if there is a filter, then apply it and turn filter on
        [Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form.Filter = varFilter
        [Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form.FilterOn = True
    Else
        ' no filter, turn filter off to show all records
        [Forms]![frm_EquipmentSearch]![frm_EquipmentSource].Form.FilterOn = False
    End If
            
   End With ' Me

End Sub



2 buttons that activate filter and reset filter:
CODE
Private Sub cmdBtnFilter_Click()
    SetFormFilter  'equipment filter module
End Sub

Private Sub cmdBtnReset_Click()
    'clear all the variables and remove filters
    Me.txtSearchBldg = Null
    Me.txtSearchRM = Null
    Me.txtSearchRR = Null
    Me.txtSearchSys = Null
    Me.txtSearchSN = Null
     SetFormFilter
End Sub
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 06:23 AM