Results 1 to 6 of 6
  1. #1
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110

    Problem filtering a subform

    I want to filter a subform based on the data in one of the fields. In Got Focus event of that field I write that data to an unbound field on the main form. Then the user clicks a command button on the main form which filters the subform based on the data in the unbound field. The user can then click another command button that turns the filter off. So far, so good. But when the user follows the same process to filter for a different value, the form keeps getting filtered for the original value. My code looks like this:



    User clicks field in subform: On Got Focus: Forms!MainForm!UnboundField = the value in the field that the user clicked on

    User clicks Command Button1: Forms!MainForm!SubForm.Form.Filter = "FieldName = Forms!MainForm!UnboundField"
    Forms!MainForm!SubForm.Form.FilterOn = True

    User clicks Command Button 2: Forms!MainForm!SubForm.Form.FilterOn = False
    Forms!MainForm!SubForm.Form.Filter = ""

    But when the user clicks a field in the subform with a different value and clicks Command Button1, the subform is filtered using the value used for the previous filter.

    Any help would be appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why bother with unbound control? Just reference the subform field in Filter.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree that there are other ways to filter the sub form, but I'll try to answer your question.

    Let's say there are 5 fields in the sub form, "sfrmCustomer", record source: L_Name, F_Name, City, State and Zip.

    Each of the 5 controls (NOT fields - forms have controls) has the On Got Focus event set to something like:
    Code:
    Forms!MainForm!UnboundField = the value in the field that the user clicked on
    -------------------------------------------------------------------------------------------
    So the code for the Last Name Got Focus event would be
    Code:
    Private Sub training_L_Name()
        Forms!MainForm!UnboundField = Forms!MainForm!sfrmCustomer.Form.L_Name
    End Sub
    Code for the First Name Got Focus event:
    Code:
    Private Sub training_F_Name()
        Forms!MainForm!UnboundField = Forms!MainForm!sfrmCustomer.Form.F_Name
    End Sub
    The other controls follow suit.
    -------------------------------------------------------------------------------------------


    Now the buttons.
    Button1 click event would be
    Code:
    Private Sub Button1_Click()
        Forms!MainForm!sfrmCustomer.Form.Filter = "L_Name = Forms!MainForm!UnboundField"
        Forms!MainForm!sfrmCustomer.Form.FilterOn = True
    End Sub
    Button2 click event would be
    Code:
    Private Sub Button1_Click()
        Forms!MainForm!sfrmCustomer.Form.Filter = ""
        Forms!MainForm!sfrmCustomer.Form.FilterOn = False
    End Sub
    So far, so good??

    Problems:
    1) The first problem is that you need to concatenate the value to the filter string. Otherwise you will be trying to filter using the string "F_Name = Forms!MainForm!UnboundField"
    2) My question to you is "How do you change the field to control on? You change the filter VALUE using the unbound control in the main form, but I do not see where the control name is changed.


    You click on a name in the Last name control on the sub form, the value in the main form unbound control changes, but the Button1 code for the control to be filtered on in the sub form NEVER changes the control name.



    Does this help?



    You could have another unbound control (but hidden) in the main form to hold the control name.

    Add a line to each of the ON FOCUS events
    Code:
    Forms!MainForm!ubCtlName = Me.L_Name.Name
    Change the Button1 code to something like (air code):
    Code:
     Forms!MainForm!sfrmTraining2.Form.Filter = " & Forms!MainForm!ubCtlName & " = Forms!MainForm!UnboundField"

    If the above doesn't help,
    Attached Files Attached Files

  4. #4
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    Not sure you quite follow what I'm trying to do. The user will only ever filter on the subform control named LocationType. That control could contain any of a dozen or so values and many records in the recordset may have the same value. If the user clicks the LocationType control and the value in that particular record is, for example, "Type One", the form gets filtered so that the subform only displays the records where the value in the LocationType field is "Type One". When the user clicks the button to clear the filter, all the records are displayed regardless of the value in LocationType. So far, so good. Then the user clicks the LocationType field in a record where the value is "Type Five". The unbound field now contains the value "Type Five", but when the user clicks the button to apply the filter the form once again displays the records for Type One instead of those for Type Five even though the value in the unbound field on the main form has changed to "Type Five".

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try concatenating as suggested.

    Forms!MainForm!SubForm.Form.Filter = "LocationType = '" & Me.LocationType & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    cebrower is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Location
    Spring Lake, MI
    Posts
    110
    That worked perfectly. Thank you June7!

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

Similar Threads

  1. New Form problem after not filtering
    By caniread in forum Forms
    Replies: 5
    Last Post: 02-11-2019, 03:49 PM
  2. Query Filtering Problem
    By David Reading in forum Queries
    Replies: 7
    Last Post: 09-19-2017, 11:03 AM
  3. Replies: 6
    Last Post: 09-10-2012, 11:29 AM
  4. Replies: 14
    Last Post: 03-07-2012, 03:46 AM
  5. Problem filtering a report
    By mrk68 in forum Reports
    Replies: 1
    Last Post: 05-03-2009, 09:31 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