Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtered Records (A2K)

    This is a routine that works perfectly and resides within a form called: frmCNMaster

    In frmCNMaster I have a List box called: lstBox

    I have a control button called: cmdFilter

    Private Sub cmdFilter_Click()
    ' This one allows multiple selections
    Dim varItem As Variant
    Dim strWhere As String
    For Each varItem In Me.lstSelect.ItemsSelected
    strWhere = strWhere & ", " & Me.lstSelect.ItemData(varItem)
    Next
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "F00_Function In (" & strWhere & ")"
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub


    I have a 2nd command button called: FKS_99

    Private Sub FKS_99_Click()
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered Records (A2K)

    You could use OpenArgs in combination with code in the On Load event of the form you open. But it is simpler to specify the WhereCondition argument:

    Private Sub cmdFilter_Click()
    ' This one allows multiple selections
    Dim varItem As Variant
    Dim strWhere As String
    For Each varItem In Me.lstSelect.ItemsSelected
    strWhere = strWhere & ", " & Me.lstSelect.ItemData(varItem)
    Next
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "F00_Function In (" & strWhere & ")"
    End If
    DoCmd.OpenForm FormName:="frmCNMaster", WhereCondition:=strWhere
    End Sub

    I don't understand your other code. If FKS_99 is a command button, what is the meaning of ... F00_Function < " & Me.FKS_99

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    I know you keep hearing something similar to this but, Bang On as Usual!

    I was almost close in my assessment, but closies don't cound except in horseshoes.

    The other code with respect to Function, etc. was used to reset the filtered data while still in the form. This was necessary when the data was being filtered within the form. Now that I can apply the filter from outside of the form, it is automatically reset when closed and reopened at a later time. I know what I mean in spite of what it sounds like.

    Bottom line, again, thank you for your help,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    I thought of something while working on this. I
    Cheers,
    Andy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered Records (A2K)

    Will users be content with sorting on a single field, or do you want to offer the capability to specify several fields to sort on (say, first sort on FieldA, then on FieldB, then on FieldC)?

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    You have to know that there will always be someone who will want to see multiple field choices such as Inventory Part Numbers by Date Received into Stock sequence just to aggravate. I'd be appreciative to see both single and multiple solutions of the same problem just for learning purposes and for sure anyone else looking in on this thread. It might save someone else aggravation as well. Either solution would, as I said, would be appreciated.
    Cheers,
    Andy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered Records (A2K)

    You could put a combo box cboSort on your search form with Row Source Type set to Field List and Row Source set to qry CNMstr.
    Change the code to open frmCNMaster to

    DoCmd.OpenForm FormName:="frmCNMaster", WhereCondition:=strWhere
    If Not IsNull(Me.cboSort) Then
    With Forms!frmCNMaster
    .OrderBy = Me.cboSort
    .OrderByOn = True
    End If

    See my next reply for multiple sort capability.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered Records (A2K)

    The attached database demonstrates how you can provide sorting on multiple fields, with an ascending/denscending choice for each. The demo is based on a Microsoft example for reports; I adapted it for forms. Open frmOrderBy to test it, then look at the code behind the form.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    Many thanks for both replies. This will give me more than enough to while away the rest of this sunshiny weekend. Hope you get to enjoy yours. I'll let you know how this works out,
    Cheers,
    Andy

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    Nice weather or not, I couldn't wait for the weekend to be over in order to try this out, so here
    Cheers,
    Andy

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered Records (A2K)

    It should be easy to combine the two approaches. For example, copy the part from the first procedure that assembles strWhere into the second procedure, and add a WhereCondition argument to DoCmd.OpenForm.

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    Thanks kindly for pointing me in the right direction. I'll be on this tonight or tomorrow. I'll post it when I get it going correctly. Or, maybe might have an intermediary question.

    Thanks again,
    Cheers,
    Andy

  13. #13
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Hans,

    Can
    Cheers,
    Andy

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtered Records (A2K)

    Good for you! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  15. #15
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered Records (A2K)

    Dear Hans;
    In a previous Post you provided code to use when using combo boxes to filter the records in a subform. This works great; however the selection criteria is not passed on to the subform. So when I print the a report based on the subform; the report contains all the records within the table. While the subform displays only the records based on the combo box selected. How do you pass on the combo box value to the FilterOn on the subform. Great Code for being able to allow multiple sorting values; how about code to allow multiple items for filtering?

Page 1 of 2 12 LastLast

Posting Permissions

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