Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159

    Filter command not working for subform

    I have a form with a command that filters my information based on the value selected in a combo. I don't get any error messages, so I'm not quite sure what the problem is, as I have already tried switching things up, but the problem is not fixed.



    The problem: I have a field labeled "Class." For the table I'm working on the values that can be chosen in the combo to be used as the filter includes: 1988 (Intl); 1989 (Intl); 1990 (Intl); 1991 (Intl); 1992 (Intl); 1993 (Intl); 1994 (Intl)

    Once I select one of those values, all my subform returns is just the first record before any filtering was done.

    Here is my code:
    Private Sub Command56_Click()
    If IsNull(Me.Class) Then
    Forms!International.Subs.Form.FilterOn = False
    Else
    Forms!International.Subs.Form.Filter = "Class = """ & Me.Class & """"
    Forms!International.Subs.Form.FilterOn = True
    End If
    End Sub

    Where:
    Class = name of field
    Me.Class = reference to the Combo, which is named Class
    Subs = name of subform
    International = name of form


    I have a similar table in which I use exactly the same filter command and it works. However, the values for this combo include: 2011; 2012; 2013; 2014; 2015

    This leads me to believe that having parenthesis in a combo box screws things up.

  2. #2
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Removed the parentheses from the 1988, 1989, etc. It had nothing to do with it not working.

  3. #3
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    I even removed the "Intl" part off completely, and just went with the year. Same issue. No matter which year I select in the combo, the first record which has the year 1989 is the only record that shows after hitting the filter command button.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    there are some consistency errors that are probably causing you the grief here...
    Quote Originally Posted by yes sir View Post
    Private Sub Command56_Click()
    If IsNull(Me.Class) Then
    Forms!International.Subs.Form.FilterOn = False
    Else
    Forms!International.Subs.Form.Filter = "Class = """ & Me.Class & """"
    Forms!International.Subs.Form.FilterOn = True
    End If
    End Sub

    Where:
    Class = name of field
    Me.Class = reference to the Combo, which is named Class
    Subs = name of subform
    International = name of form
    first: when you're referencing forms, make it consistent so there are no mishaps in vbe compiling. for instance, use either one of the following, but don't mix them:

    *forms("international").controls("subs").form.filt eron = false
    *forms!international!subs!.form.filteron = false


    the second correction that needs to be made is the concatenation of the control. the VBE does not like nested double quotes. It is way too cumbersome to use, and confusing. for strings AS control values and NOT just variables, use:

    forms("international").controls("subs").form.filte r = "class = '" & me.class & "'"

    other things too, if you don't already know them:

    *a 'field' <> a 'control name'. make sure to distinguish them. references always use the control name.
    *a 'subform source object' <> a 'subform control'. direct referencing to a subform nested on a main form always uses the 'control' name, NOT the 'source object' name.

    looks like you have a good handle on the latter already, but just a reminder!

  5. #5
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Thanks for the help, but still not working correctly. I'm getting the same result. For example, I click on 1990 (Intl) and only the first record from the original list shows.


  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    jay,

    you're pictures don't make sense. the original shows all the records, but there is only one for 1989. so...the filter in the second ss would be correct. the other thing to notice is that in the second picture, the FILTER indicator in the navigation bar of the subform is not highlighted. thus, there IS NO filter applied.

    (I think I'm right on that)

  7. #7
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    I agree with Adam, the pictures don't make sense. You have 1990 selected but it is showing a 1989 record. I don't suppose you'd like to upload a new copy for me to play with a little?

  8. #8
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    I showed the picture that way, because that is what it does.

    I select 1990 or 1991 or 1992 and it always just shows the one for 1989.


    As for the FILTER indicator not being on, in my other form that is eerily similar to the one that I have a picture for, has the class filter as well (and other filters for that matter) yet when I select a value and filter the records, the FILTER indicator does not show as being on.

  9. #9
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    I understand a little better but would still be able to help a lot more if I could see all the code, queries, etc...

  10. #10
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    As I mentioned I have two forms that are nearly the same. Each has its own purpose. One has American basketball players, the other has International basketball players.

    American, or rather College basketball players, have a class that can be one of the following: "2011";"2012";"2013";"2014";"2015"

    International players can have a class that can be one of the following: "1988 (Intl)";"1989 (Intl)";"1990 (Intl)";"1991 (Intl)";"1992 (Intl)";"1993 (Intl)";"1994 (Intl)"

    Both forms have a command button, which will display only the records that match the selected value in the corresponding combo box.

    Here is the code for the American form's command:
    Private Sub Command56_Click()
    If IsNull(Me.Class) Then
    Forms!Rosters.SUB.Form.FilterOn = False
    Else
    Forms!Rosters.SUB.Form.Filter = "Class = """ & Me.Class & """"
    Forms!Rosters.SUB.Form.FilterOn = True
    End If
    End Sub

    Here is the code for the International form's command:
    Private Sub Year_Click()
    If IsNull(Me.Class) Then
    Forms!International!Subs!.Form.FilterOn = False
    Else
    Forms!International!Subs!.Form.Filter = "Class = '" & Me.Class & "'"
    Forms!International!Subs!.Form.FilterOn = True
    End If
    End Sub


    Other Notes: The queries for each form is basically the same. With the exception being the International's query has certain criteria that must be met, to enable the International form to only show International players. This condition is met, by using a criteria "Is Null" in a field called College.


    The American form's command works. The International form's command when clicked (no matter what is selected in the cmb box) shows the first record that has a year of 1989 (Intl)

  11. #11
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Alright, I uploaded it. Thanks

  12. #12
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    The code looks fine. The problem is likely in the properties for one of your objects, or a type difference which is coming up with unexpected results, etc... But these are just guesses at this point.

  13. #13
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    You just need to set the Combo Box name as "Class" and your problem should be resolved.

    See how easy that was once you let me take a peek?

  14. #14
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    Quote Originally Posted by slave138 View Post
    You just need to set the Combo Box name as "Class" and your problem should be resolved.

    See how easy that was once you let me take a peek?

    Wow, to say I feel stupid is an understatement. I was looking for something way advanced and I went blind in doing so.

    Bet that doesn't happen again.

    Thanks

  15. #15
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    You're welcome. The little things are usually the hardest to find.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass Subform filter to subform in report
    By camftm in forum Programming
    Replies: 16
    Last Post: 07-19-2011, 07:12 AM
  2. Filter By Form not working!
    By Freybourne in forum Access
    Replies: 6
    Last Post: 06-22-2010, 09:41 PM
  3. Apply filter command
    By miziri in forum Forms
    Replies: 6
    Last Post: 01-21-2010, 02:22 AM
  4. command button to filter a subform issue -
    By countdrako in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 11:58 PM
  5. Open a linked subform with a command button
    By flablueeyedblond in forum Forms
    Replies: 0
    Last Post: 11-18-2005, 01:18 PM

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