Multiselect dropdown

[IvanM]

Hi,

I would like to add a multi-select drop-down to a form that would basically allow me to select the drop-down revealing a populated list (this part isn't the problem) and select checkboxes next to the items in the list that are relevant to me.
Basically I'm after something like the Access Table or Excel Data selection Filters.

I'm using Access 2016.

Any help would be greatly appreciated, thanks in advance!

 

[isladogs]

See this thread
[url]https://www.access-programmers.co.uk/forums/showthread.php?t=290311&highlight=mutliselect+combo[/URL]
... and this video
https://www.youtube.com/watch?v=queKMe9MiSs&feature=youtu.be

 

[IvanM]

Hi,

thanks for that, I apologise for neglecting to mention that this control would be unbound and uses a union query to populate, so isn't based on a table field that I can set as multivalue. It's being used to filter records on the form...

 

[isladogs]

I'm slightly confused by that answer but ...
Why can't you use the union query as the combo box source
OR failing that create a temp table from your union query and then use that?

 

[Frothingslosh]

What about using a listbox instead? It uses up more real estate, but it's built for situations like that.

 

[Frothingslosh]

I'm slightly confused by that answer but ...
Why can't you use the union query as the combo box source
OR failing that create a temp table from your union query and then use that?

The source isn't the issue - he's looking for the ability to multi-select, which, AFAIK, comboboxes can't do. I can think of a couple ways to fake it, but they involve UI games with a listbox.

 

[IvanM]

Correct Frothing, screen real-estate is an issue too...
Ridders, apologies for the confusion, I AM using the union query as the source. The table comment was because as far as I've seen that's the way to create a field which when dropped onto a form will give the drop-down multivalue select option....or maybe I'm wrong on that one.

Basically, I'm looking for something akin to the drop-downs with check-boxes that you get in the datasheet view of the tables themselves? Also like the Data Filter drop-downs you get in Excel?

The source is from one field from a table with a union query to add the "(All)" option at the top, not that this is necessarily relevant other than for the reason above.

 

[isladogs]

I believe the video I suggested does exactly what you want.
I.e. multivalue combo box with Checkboxes

If you haven't checked it out, suggest you do

 

[static]

Treeview with checkboxes turned on.

Private Sub Command0_Click()
    If TreeView.Visible Then
        Dim tv As TreeView, n As Node
        Set tv = TreeView.Object
        Debug.Print "Selected items..."
        For Each n In tv.Nodes
            If n.Checked Then Debug.Print n.Text,
        Next
        Debug.Print
    End If
    TreeView.Visible = Not TreeView.Visible
End Sub

Private Sub Form_Load()
    GetTVData TreeView
    
    TreeView.Height = 4000
    TreeView.Width = 2000
    TreeView.Top = Command0.Top + Command0.Height
    TreeView.Left = Command0.Left
    TreeView.Visible = False
End Sub

Private Sub GetTVData(tv As Control)
    Dim tvo As TreeView
    Dim b As Boolean
    b = tv.Visible
    tv.Visible = True
    Set tvo = tv.Object
    tvo.Checkboxes = True
    tvo.Nodes.Clear
    For i = 1 To 10
        tvo.Nodes.Add , , , "item " & i
    Next
    tv.Visible = b
End Sub

 

[IvanM]

Hi Colin,

yes, you're right, the video shows a method of creating the kind of drop-down multi-select I want to create, however, that relies on a destination table that has a lookup with multi-value drop down field though and my source is from a query...
I'll take another look however and see if I can't find a route that way...

Cheers.

 

[isladogs]

Morning Static

I've always shied away from treeview as something to learn on another day

Would it be possible for you to post a very simple db showing how your last post would work

Also on another topic from yesterday, "Setting a group of controls visible/hidden or enabled/disabled or locked/unlocked"
https://www.access-programmers.co.uk/forums/showthread.php?t=293412
other exceptions are required.
For example you can't lock command buttons
I'm currently checking the full list of controls for other exceptions