combo filter sub form (1 Viewer)

[murray83]

Hi all, i have searched here and google for this and found this https://www.access-programmers.co.u...-selection-filters-results-in-subform.323733/

but not sure/how i can get it to work for my example attached, as it did filter one the role ( now its not dam thing ) but also would like it to filter on the BU Selected

have tried the master/child thing but keeps saying about unbound form

thanks for looking and reading as always

 

[Gasman]

What do expect to happen?
There is no code in any events for the unbound controls?
Create a sub in the form and call it from both afterupdate events of those controls.

You need to test each if each have a value and possibly combine them if both do.
Put the filter into a string variable. Then you can debug.print it and see if it is correct.

You have ID in all three tables. Give them decent names RoleID, BUID etc and add FK for any foreign keys, else Access as well as you will get confused.

You could link on one field (Role?) , then filter on the other, or just filter on both. Up to you.

 

[Mike Krailo]

The subform you have there is showing data in the tbl_All_Data_Access table which does have Roles in it. I do not however see any data for BU whatever that two letter code is. So if the data for BU is not in your subform data, how do you expect to use that unbound combo to filter BU? And yes the filtering controls are not yet setup, but I don't see how that's going to happen with the current data in the subform. You'll have to be more specific in what it is you are trying to filter.

EDIT: Nevermind, I see what BU is now. You want anything beginning with those two letters from the security context value column.

 

[murray83]

what you mean something like this

The subform you have there is showing data in the tbl_All_Data_Access table which does have Roles in it. I do not however see any data for BU whatever that two letter code is. So if the data for BU is not in your subform data, how do you expect to use that unbound combo to filter BU? And yes the filtering controls are not yet setup, but I don't see how that's going to happen with the current data in the subform. You'll have to be more specific in what it is you are trying to filter.

EDIT: Nevermind, I see what BU is now. You want anything beginning with those two letters from the security context value column.

yeah i can add bu to that table but i think be easier to just have it as its own table, as yu have seen its just the first 2 char

 

[murray83]

What do expect to happen?
There is no code in any events for the unbound controls?
Create a sub in the form and call it from both afterupdate events of those controls.

You need to test each if each have a value and possibly combine them if both do.
Put the filter into a string variable. Then you can debug.print it and see if it is correct.

You have ID in all three tables. Give them decent names RoleID, BUID etc and add FK for any foreign keys, else Access as well as you will get confused.

You could link on one field (Role?) , then filter on the other, or just filter on both. Up to you.

so do you mean something like this
Private Sub txtSearch_AfterUpdate()

Forms!Form1.tbl_All_Data_Access_subform.Form.Filter = "Security_Context_Value"
Forms!Form1.tbl_All_Data_Access_subform.Form.FilterOn = True

End Sub



 

[Gasman]

No, as that is not combining both controls?
As you need to use the code from at least two different control events, I would create a sub in the form and call that from each control AfterUpdate event.
Then also if you decide to have yet another third criteria, you just change it in one place.
You need to test if each has a value before you use the value to build the filter.
As the subform is in your form, use Me and not the Forms! syntax. That is only needed if you are running code not in the target form.

 

[MajP]

I would have BU a calculated column in the query and not a calculated control.
in the query bound to the subform
BU: Left([Security_Context_Value],2)

something like this

Private Sub cmb_BU_AfterUpdate()
  FilterSub
End Sub

Private Sub txtSearch_AfterUpdate()
  FilterSub
End Sub
Private Sub FilterSub()
  Dim buFilter As String
  Dim RoleFilter As String
  Dim fltr As String
 
  If Not IsNull(cmboRoles) Then
    RoleFilter = "role = '" & Me.cmboRoles & "' AND "
  End If
  If Not IsNull(cmboBU) Then
    buFilter = "BU = '" & Me.cmboBU & "'"
  End If
 
  fltr = RoleFilter & buFilter
  If Left(fltr, 4) = "AND " Then fltr = Left(fltr, Len(fltr) - 4)
  Debug.Print fltr
  'code here to filter subform
End Sub

 

[Mike Krailo]

Here is similar rendition that uses the additional BU field and instead of using a subform, this one is just a normal continuous form with the two combo filters at the top of the form. Would have had it sooner, but I accidentally wiped out the new form when I attempted to clean things up and had to re-do it from scratch. Note that the tblRole and tblBU are not involved in any of this.

 

[murray83]

cheers for the input and yeah i did look at calculated field myself i shall check both out thanks a million

 

[Gasman]

I would have BU a calculated column in the query and not a calculated control.
in the query bound to the subform
BU: Left([Security_Context_Value],2)

something like this

Private Sub cmb_BU_AfterUpdate()
  FilterSub
End Sub

Private Sub txtSearch_AfterUpdate()
  FilterSub
End Sub
Private Sub FilterSub()
  Dim buFilter As String
  Dim RoleFilter As String
  Dim fltr As String

  If Not IsNull(cmboRoles) Then
    RoleFilter = "role = '" & Me.cmboRoles & "' AND "
  End If
  If Not IsNull(cmboBU) Then
    buFilter = "BU = '" & Me.cmboBU & "'"
  End If

  fltr = RoleFilter & buFilter
  If Left(fltr, 4) = "AND " Then fltr = Left(fltr, Len(fltr) - 4)
  Debug.Print fltr
  'code here to filter subform
End Sub

@MajP
Shouldn't we be checking the end of the filter for AND ?

 

[MajP]

@Gasman,
I did not test that code so I did say "Something like", but I thought I do check the end for that

 fltr = RoleFilter & buFilter
  If Left(fltr, 4) = "AND " Then fltr = Left(fltr, Len(fltr) - 4)

When I do this I am normally doing it for lots of controls. More than two. So I do it as this and never write new code to create these types of filters. I do not write code to get the filter from a control. I have a function
getFilterFromControl

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String

  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If

  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function

I think my demo has 10 or more controls from multi select listboxes, combos, text, etc. Of all the code I have written, that group is pretty my greatest time saver. Use it all the time.