Use a Multi-Select List box as criteria for a combobox

[arnelgp]

how about if you remove the Join and remove EmplToJob.
i don't see any purpose of it in the query.
EmpltoJob as JobId and EmployeeID.
only the JobID is being used and for what?

 

[Indigo]

Hi Arnel, I actually picked up on that already and removed the join and I still get all of the jobs.... my row source now reads:

SELECT Jobs.JobID, Jobs.JobName
FROM Jobs 
WHERE ((([Jobs].[AreaID] & " In (" & [Forms]![frmEmplToJob]![HiddenBox] & ")")<>False))
GROUP BY Jobs.JobID, Jobs.JobName;

and makes no difference.

I updated the code to read:

    Dim ListItem As Variant
    HiddenBox = "" 'hiddentextbox is a hidden control on your form
    For Each ListItem In lstAltArea.ItemsSelected
        HiddenBox = HiddenBox & lstAltArea.ItemData(ListItem) & ", "
    Next
    If Right(HiddenBox, 2) = "," Then HiddenBox = Left(HiddenBox, Len(HiddenBox) - 2)
    
    Me.cboJobs.Requery

I have never used this IN Clause before and I can't find a lot about it when I search online.

Part of me wonders if this line in the query is causing a problem?

((([Jobs].[AreaID] & " In (" & [Forms]![frmEmplToJob]![HiddenBox] & ")")<>False))

Just with all of the double quotes when AreaID is numeric....?

 

[arnelgp]

Can you post your db or a teimmed version.

 

[Indigo]

Unfortunately, I cannot post the database nor a trimmed version. It's a split back-end / front-end and there is confidential employee information in it.

 

[CJ_London]

try this variation

SELECT Jobs.JobID, Jobs.JobName
FROM Jobs
WHERE ", " & [Jobs].[AreaID] & ", " Like "*, " & [HiddenBox] & ", *"
GROUP BY Jobs.JobID, Jobs.JobName;

and temporarily make hiddenbox visible so you can see what you have calculated

 

[Indigo]

Hi CJ,

Unfortunately, that produced no results.

However, I have come up with a solution. It required me to do more research on the IN Clause and I found some info here:

https://www.w3schools.com/sql/sql_in.asp

So what I did is point the IN Clause to the query that supports the list box as follows:

SELECT Jobs.JobID, Jobs.JobName
FROM Jobs
WHERE (((Jobs.AreaID) In (SELECT AreaID FROM qryEmplArea)))
GROUP BY Jobs.JobID, Jobs.JobName;

I decided that I did not need to have the listbox select working and am just displaying the Areas that the employees are certified in on the listbox.

Thanks for putting up with me.