Results 1 to 2 of 2
  1. #1
    vector39 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    76

    Button that filters datasheet based on multi selections from list box

    Hi all
    I have a list box set to allow multiple selections. This list box is on a split form. It's values are from another table. I'm trying to get this list box to filter the data sheet based on whatever the user selects. I was thinking of doing this where the user makes the selection(s) and then pressing a button which would filter the datasheet. The code I have below is from a form that opens up a report based on whatever selections made but with some changes since this should filter a datasheet and not open a report:



    Code:
    Private Sub cmdCodeFilter_Click()
    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    
    'make sure a selection has been made
    If Me.lstProgramCodes.ItemsSelected.Count = 0 Then
      MsgBox "Must select at least 1 program code"
      Exit Sub
    End If
    
    'add selected values to string
    Set ctl = Me.lstProgramCodes
    For Each varItem In ctl.ItemsSelected
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)
    
    
    'Debug.Print strWhere
    'filter datasheet
    Me.Filter = "ProgramCode IN(" & strWhere & ")"
    Me.FilterOn = True
    
    End Sub




    Similar idea, however I'm not sure how the code at the bottom is supposed to look. What comes up is this whenever I press the button:
    Click image for larger version. 

Name:	enterparameter.PNG 
Views:	11 
Size:	4.8 KB 
ID:	30888


    Any help is greatly appreciated!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    To do this WITHOUT any code, dont use the multi-select list (programming reqd)
    instead use a single select list and a 'pick' table.
    Dbl-click items in the list box, this runs an append query to add the item to a tPicked table.


    After all picks have been made, run a query that joins your tDAta table to the tPicked table.
    Only those choices will show.

    1 query to add 1 item to the tPicked table.
    =INSERT INTO tPicked ( Name ) SELECT [Forms]![frmMyForm]![lstAvail] AS Expr1

    1 delete query to empty tPicked table.
    =delete * from tPicked

    1 delete query to remove 1 item from tPicked table.
    =delete * from tPicked where [field] =[Forms]![frmMyForm]![lstPicked]

    1 select query to show the data joined to the tPicked table

    Click image for larger version. 

Name:	pick state-lbl.png 
Views:	11 
Size:	34.2 KB 
ID:	30889

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

Similar Threads

  1. List of emails based on name selections.
    By Coxck in forum Access
    Replies: 1
    Last Post: 01-07-2016, 03:29 PM
  2. Replies: 3
    Last Post: 04-08-2014, 01:02 PM
  3. Open a Report based on List Box Selections
    By CKING in forum Reports
    Replies: 12
    Last Post: 10-16-2013, 11:03 AM
  4. LookupField1 filters selections for lookupField2?
    By AccessAccess in forum Database Design
    Replies: 7
    Last Post: 03-26-2013, 12:13 PM
  5. Replies: 1
    Last Post: 02-03-2012, 03:51 PM

Tags for this Thread

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