Filter a subform based on combo boxes (1 Viewer)

p4nny

New member
Local time
Today, 15:40
Joined
Mar 16, 2020
Messages
15
Hello

I'd like to filter the subform based on the combo boxes.

I'd like to be able to select "role title" then "country".

At the moment this only works independently. If I select "role title" and then "country" it loses the selected "role title"

Heres my vba
Code:
Private Sub country_AfterUpdate()
Dim Country As String
mycountry = "Select* from tblPlannedRoles where ([Country]= '" & country& "')"
Me.[tblPlannedRoles subform1].Form.RecordSource = mycountry

Me.[tblPlannedRoles subform1].Form.Requery

End Sub


Private Sub role_AfterUpdate()
Dim role As String
myrole = "Select* from tblPlannedRoles where ([role title]= '" & Me.role & "')"
Me.[tblPlannedRoles subform1].Form.RecordSource = myrole
Me.[tblPlannedRoles subform1].Form.Requery


End Sub

Much appreciated
 

Attachments

  • Capture1.GIF
    Capture1.GIF
    40.3 KB · Views: 88

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:40
Joined
Jul 9, 2003
Messages
16,245
Have a look at this blog on my website "Nifty Access"...

Combo Filter < All> or Some


I think it probably does what you want. The download is currently available for free, for 24-hours (until midnight tomorrow) Use this coupon code:- GetALL4Fr33_OrBuyMeA_Coffee
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:40
Joined
May 21, 2018
Messages
8,463
Untested (part of a bigger module), but give this a try.

Code:
Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Private Sub country_AfterUpdate()
  setfilter
End Sub

Private Sub role_AfterUpdate()
setfilter
End Sub

Private sub setFilter
  Dim role as string
  Dim Country As String
  dim fltr as string
  dim source as string

  if not isnull(me.role) then
    role = "[role title]= '" & Me.role & "'"
  end if
  if not isnull(me.Country) then
    Country = "Country = '" & Me.Country & "'"
  end if
  fltr = combineFilters(ct_And,Role,Country)
  source = "Select * from tblPlannedRoles  "
  if not flter = "" then
   source = Source & " WHERE " & fltr
   debug.print source
end if
    Me.[tblPlannedRoles subform1].Form.RecordSource = myrole
    Me.[tblPlannedRoles subform1].Form.Requery
end sub


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
 

Micron

AWF VIP
Local time
Today, 11:40
Joined
Oct 20, 2018
Messages
3,476
If that's how you designed your table then it is certainly not normalized, but it looks like you're good to 2030 ;)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:40
Joined
May 7, 2009
Messages
19,175
same answer as the others.
join the two filters:
Code:
Private Sub country_AfterUpdate()

    call new_recordsource

End Sub


Private Sub role_AfterUpdate()

    call new_recordsource

End Sub

private sub new_recordsource()
    dim strSQL as string
    strSQL = "select * from tblPlannedRoles"

    if me!country.listindex > -1 then
        strSQL = strSQL & " where ([country] = '" & [country] & "')"
    end if

    if me!role.listindex > -1 then
        if instr(strSQL, "where") > 0 then
            strSQL = strSQL & " And ([role title]= '" & Me.role & "')"
        else
            strSQL = strSQL & " where ([role title]= '" & Me.role & "')"
        end if
    end if

    Me.[tblPlannedRoles subform1].Form.RecordSource = strSQL
    Me.[tblPlannedRoles subform1].Form.Requery

end sub
 

Users who are viewing this thread

Top Bottom