Results 1 to 6 of 6

Filtering split form with selections from a listbox

  1. #1
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72

    Filtering split form with selections from a listbox

    Hello, all. I have created a popup form [multiselect] that has a combo box in it [state]. There are others, but once I solve one, I can apply to others. I could put a button on the popup that I would like to call code that filters the data on a split form [ServiceManagement] based on the field [SiteState].



    Since SQL is the underlying database, split forms lose the "multi-select" capability, so I'm trying to find a workaround.


    I am unfortunately just not figuring out what that code should look like.


    Does that make any sense? Any suggestions?


    SC

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    656
    try filtering the data using the built in access methods. then review the Filter, FilterOn, Order,OrderBy properties. Store those values in a table that is setup with a key. reference that key in your drop down and a lil vba to retrieve and apply filter settings.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,612
    If you need the multi-select code, this should get you started:

    http://www.baldyweb.com/multiselect.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72
    Thanks! I'll play with that!

  5. #5
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72
    I've modified your code, and get no outcome. Any recommendations would be appreciated! svcfilter is the form, state is the listbox. This code is under a button on the splitform, where sitestate is the field being filtered against.

    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant


    'make sure a selection has been made
    If Forms!svcfilter.State.ItemsSelected.Count = 0 Then MsgBox "Must select at least 1 state"
    Exit Sub
    End If


    'add selected values to string
    Set ctl = Forms!svcfilter.State
    For Each varItem In ctl.ItemsSelected
    'strWhere = strWhere & ctl.ItemData(varItem) & ","
    'Use this line if your value is text
    strWhere = strWhere & "'" & Forms!ctl.ItemData(varItem) & "',"
    Next varItem
    MsgBox strWhere
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)


    Me.Filter = strWhere
    me.filteron=true

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,612
    The filter must include the field name (in my code, it was in the OpenReport line). In your case, right after:

    strWhere = Left(strWhere, Len(strWhere) - 1)

    try adding

    strWhere = "sitestate In(" & strWhere & ")"

    If you message box or debug.print the final string, it would look like:

    sitestate In('CA', 'NV', 'FL')
    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: 7
    Last Post: 01-30-2015, 03:27 PM
  2. Replies: 13
    Last Post: 08-22-2013, 02:37 PM
  3. Filter Split Form using ListBox and .ME
    By clchris_80 in forum Access
    Replies: 3
    Last Post: 01-17-2013, 07:30 PM
  4. Filtering records on a split form
    By Accessbeginner23 in forum Programming
    Replies: 1
    Last Post: 07-02-2012, 03:23 PM
  5. Replies: 9
    Last Post: 01-20-2011, 02:22 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
  •  
Tech Forums: Microsoft Office Forums