Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    29

    Unanswered: Filtering a Subform

    Hi,

    I am currently trying to figure out how to filter a subform. I've had an extensive search around the web to find a solution. There seems to be 2 possible solutions but I can't get either to work!

    One of them is the DoCmd.ApplyFilter command and there other is the FilterOn command.

    All I can currently do is filter the main form, not the sub form. I've been trying a few ideas out but, as stated above, nothing it working.

    This is what I have
    Code:
    tFundingMethod.frmMagistratesTimeRecord.Filter = "tFundingMethod = '" & "M" & "'"
    tFundingMethod.FilterOn = True
    I've also tried
    Code:
    DoCmd.ApplyFilter , "me.tFundingMethod = '" & "M" & "'"
    And this
    Code:
    fltString = "frmMagistratesTimeRecord.tFundingMethod = '" & "M" & "'"
    Me.Filter = fltString
    Me.FilterOn = fltString
    What I have is a form, frmMatters, and within this I have a subform. and a combo box. When I select an option form the combo box I want it to filter the frmMagistratesTimeRecord where tFundingMethod = M.

    Here is what my code is like without the filter
    Code:
    If TimeRecordSelecter = "Magistrates" Then
    subfrmPoliceTimeRecord.Visible = False
    subfrmMagistratesTimeRecord.Visible = True
    BillMags.Visible = True
    lblBillMatter.Visible = True
    lblBillOS.Visible = False
    lblBillDuty.Visible = False
    So as you can see when I choose the "Magistrates Option" it shows the Magistrates Subform but I also want to filter just where tFundingMethod (in the sub form) is equal to "M"

    Any help is appreciated.

    Thanks
    Andy.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Try
    Code:
    fltString = "frmMagistratesTimeRecord.tFundingMethod = '" & "M" & "'"
    Me.Filter = fltString
    Me.FilterOn = true
    Me.Geek = True

  3. #3
    Join Date
    Jul 2009
    Posts
    29
    Very small change and it works. You're a star! You've saved me a lot of hassle.

    It was this

    Code:
    fltString = "subfrmMagistratesTimeRecord.tFundingMethod = '" & "M" & "'"
    Me.Filter = fltString
    Me.FilterOn = true
    Thanks again
    Andy.

  4. #4
    Join Date
    Jul 2009
    Posts
    29
    I got a bit over enthusiastic and thought it worked but it doesn't...

    It seems to filter out everything, i.e even if the funding method is "M" it doesn't show when "Magistrates" is selected, nothing shows.

    It appears that the Main Form is being filter (highlighted filter button at the bottom) but the subform isn't (says unfiltered). Looks like it's not hitting the form I want.

    Any way around this?

    Thanks
    Andy.

    Think I have it working now, appologies... I've been on this for hours.

    Thanks again
    Andy.
    Last edited by andycambo; 09-08-09 at 16:47.

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Make sure you're applying the filter to whatever you intend to (main form or subform). That's why I seldom use the me. method when dealing with filters, I usually prefer to explicitly call out whatever I'm working at.
    Me.Geek = True

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When "talking" to a subform from the main (or parent) form, the syntax is:
    Code:
    Me.Subform.Form.<Something>
    , so in your case:
    Code:
    Me.frmMagistratesTimeRecord.Form.Filter = ...
    Me.frmMagistratesTimeRecord.Form.FilterOn = True
    See:Forms: Refer to Form and Subform properties and controls
    Have a nice day!

  7. #7
    Join Date
    Jul 2009
    Posts
    29
    Thanks for both of your responses, its working 100%

    Ive taken your advice and had a look at the websites you have linked. Ive just been trying it out in work and have managed to get it working. The form names and filters have changed because Im in work but the principle is still the same.

    Ive taken Sinndhos idea as it looks neater code wise but nckdryr thank-you once again for pointing me in the right direction. Youve been a great help in my Access/SQL quest so far!

    Here is what I have:


    Code:
    frmANDisbursement.Visible = False
    frmAMDisbursement.Visible = True
    
    Me.frmAMDisbursement.Form.Filter = "frmAMDisbursement.acType = '" & "Matter" & "'"
    Me.frmAMDisbursement.Form.FilterOn = True
    Thanks for all your help once again,

    Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •