I have a form with a listbox showing 15 competencies linked by Comp_ID and a subform linked to query1 showing all staff with all competencies. when i highlight a competency from the list box the subform reduces the records based on that competency.. GREAT! what i want to do is use a multi selection on the list box that then shows me staff who have all selected comptencies. how do i do this?
Multi Listbox (1 Viewer)
- Thread starter Lochwood
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 10:11
- Joined
- May 7, 2009
- Messages
- 19,232
if you have master/child links to your subform remove it.
add code to the listbox afterupdate event:
private sub lst_afterupdate()
dim var as variant
dim strFilter as string
for each var in lst.itemsselected
strfilter = "," & chr(34) & me.lst(var) & chr(34) & strFilter
next
strFilter=mid(strFilter,2)
me.subform.form.recordsource="select * from table1 where comp_id in (" & strFilter & ")"
end sub
add code to the listbox afterupdate event:
private sub lst_afterupdate()
dim var as variant
dim strFilter as string
for each var in lst.itemsselected
strfilter = "," & chr(34) & me.lst(var) & chr(34) & strFilter
next
strFilter=mid(strFilter,2)
me.subform.form.recordsource="select * from table1 where comp_id in (" & strFilter & ")"
end sub
Ranman256
Well-known member
- Local time
- Yesterday, 22:11
- Joined
- Apr 9, 2015
- Messages
- 4,337
Instead of using multi select list box, which requires programming,
You could use a single select list,dbl-click the item,which adds the item to a 'tPicked' table.
(Via append query)
Then just join the tPicked table to the data table in a query to get those records.
You could use a single select list,dbl-click the item,which adds the item to a 'tPicked' table.
(Via append query)
Then just join the tPicked table to the data table in a query to get those records.
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 22:11
- Joined
- May 21, 2018
- Messages
- 8,527
If it is "all" it gets a little more complicated. You have to count how many of the selected courses they have and count how many are selected in the listbox. Continuing from arnelgp's code
My qryEmpComp
I assume you have an employee table, competencytable, and a junction table.
Not sure if in arnelgp's code if this is correct syntax:
me.lst(var) I believe it is me.lst.ItemData(var) or me.lst.column(row,col)
My qryEmpComp
Code:
SELECT employees.employeeid,
employees.lastname,
tblcompetency.competency_name,
employees.firstname,
(SELECT Count([compid])
FROM tblemployeecompetency AS A
WHERE A.empid = employees.employeeid) AS CountOfComp,
tblcompetency.competency_id
FROM (employees
INNER JOIN tblemployeecompetency
ON employees.employeeid = tblemployeecompetency.empid)
INNER JOIN tblcompetency
ON tblemployeecompetency.compid = tblcompetency.competency_id;
Code:
Private Sub lstComp_AfterUpdate()
Dim var As Variant
Dim strFilter As String
Dim lst As Access.ListBox
Set lst = Me.lstComp
Dim strSql As String
For Each var In lst.ItemsSelected
'strFilter = "," & Chr(34) & lst.ItemData(var) & Chr(34) & strFilter
strFilter = "," & lst.ItemData(var) & strFilter
Next
strFilter = Mid(strFilter, 2)
strSql = "select * from qryEmpComp where competency_id in (" & strFilter & ") And CountOfComp = " & lst.ItemsSelected.Count
Debug.Print strSql
If Not strFilter = "()" Then
Me.qryEmpComp_subform.Form.RecordSource = strSql
End If
End Subme.lst(var) I believe it is me.lst.ItemData(var) or me.lst.column(row,col)
Only show staff with any 3 selected that have all 3.
