Results 1 to 8 of 8
  1. #1
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49

    Create function to filter subform

    Hello everyone.



    With the help of some googling, I managed to write a sub for filtering my subform, using 2 list boxes and 1 checkbox, and pressing a button
    I use the same code to open a filtered report (pasting it under another button's Click event), and it works fine.
    The thing is that it seems more suitable to have a function that I could use when needed, rather that having the same lines of code again and again. Could you help me convert this sub into a working function?

    Code:
    Private Sub btn_filter_Click()
    
    
        Dim var As Variant
        Dim filtr As String
        Dim mdlt As Variant
        
        If Me.list_Shifts.ItemsSelected.Count > 0 Then
            For Each var In Me.list_Shifts.ItemsSelected
                Dim shft As String
                ' Get the initial Shift filter:
                shft = "Pt_Shift=" & Me.list_Shifts.ItemData(var)
                ' loop through the next listbox and add that to the shft string
                 If Me.list_Modality.ItemsSelected.Count > 0 Then
                     For Each mdlt In Me.list_Modality.ItemsSelected
                        ' ' since this is the "last" filter listbox, we can
                        ' ' add this string to the "master" filter string
                         If Len(filtr) > 0 Then
                            filtr = filtr & " OR "
                         End If
                         filtr = filtr & shft & " AND Pt_Modality=" & Me.list_Modality.ItemData(mdlt)
                         ' check for the Inactive filter
                         If Me.chk_Pt_InactiveList.Value = False Then
                            filtr = filtr & " AND Pt_Inactive=0"
                         End If
                     Next mdlt
                ' If the Modality filter is not applicable, apply the Shift and Inactive filters
                 Else
                    If Len(filtr) > 0 Then
                        filtr = filtr & " OR "
                    End If
                    filtr = filtr & shft
                    If Me.chk_Pt_InactiveList.Value = False Then
                        filtr = filtr & " AND Pt_Inactive=0"
                    End If
                 End If
             Next var
        ' If nothing is selected in the Shift filter, do not use it
        ' and continue with the next filters
         Else
             shft = "Pt_Shift Is Null"
                 If Me.list_Modality.ItemsSelected.Count > 0 Then
                     For Each mdlt In Me.list_Modality.ItemsSelected
                        ' ' since this is the "last" filter listbox, we can
                        ' ' add this string to the "master" filter string
                         If Len(filtr) > 0 Then
                            filtr = filtr & " OR "
                         End If
                         filtr = filtr & shft & " AND Pt_Modality=" & Me.list_Modality.ItemData(mdlt)
                         ' check for the Inactive filter
                         If Me.chk_Pt_InactiveList.Value = False Then
                            filtr = filtr & " AND Pt_Inactive=0"
                         End If
                     Next mdlt
                ' If the Modality filter is not applicable, apply the Shift and Inactive filters
                 Else
                    If Len(filtr) > 0 Then
                        filtr = filtr & " OR "
                    End If
                    filtr = filtr & shft
                    If Me.chk_Pt_InactiveList.Value = False Then
                        filtr = filtr & " AND Pt_Inactive=0"
                    End If
                 End If
    
    
        End If
        
        Me.sbfrm_PatientList.Form.filter = filtr
        Me.sbfrm_PatientList.Form.FilterOn = True
        
    End Sub
    thanx in advance

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's just for this form, it's easy; just paste that code into a form-level function and call it from the different buttons. You can have it accept an argument that determines whether it filters the subform or opens a report at the end. You could also use a single button with a checkbox or option group that determine whether it prints or filters.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Well, I have alredy tried that, but obviously I miss something, because it does not return the string I need (filtr). I have checked it with print.debug and filtr variable has no value. And I think that I'm not going to use it outside this form

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't say why without seeing your code. I wouldn't have it return anything, I'd have it open/filter as desired. If you wanted it to return something, you need:

    FunctionName = Whatever

    in your code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    All the code I have is the sub I posted before that generates the [filtr] string that I use for filtering my subform:
    Me.sbfrm_PatientList.Form.filter = filtr

    If I create a function FilterForm() pasting the above code (without the last two lines) and then call it within a click sub:

    Code:
    Private Sub Command78_Click()
    Dim curfiltr As String
    curfiltr = FilterForm
    Me.sbfrm_PatientList.Form.filter = curfiltr
    End Sub
    nothing happens

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Like I said, at the end of the function you'd need to set the return value:

    FilterForm = filtr
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Ok, now I got where I should have set it.
    Of course, it worked, thank you very much!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 12-05-2016, 02:23 PM
  2. Replies: 1
    Last Post: 03-17-2016, 09:30 PM
  3. Replies: 1
    Last Post: 08-16-2012, 01:51 PM
  4. Replies: 6
    Last Post: 05-05-2012, 08:43 AM
  5. Replies: 4
    Last Post: 08-05-2011, 07:27 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