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

[Indigo]

Good day all!

I am hoping someone can help me out with the following problem I can't seem to resolve. I am using Access 2016 where I have a form with a combobox that contains all of the jobs in our plant as it's Row source. The table, Jobs, contains the following fields: JobID, JobName and AreaID. I also have a table which connects Employees to the jobs they can do: EmplToJob. This table contains The EmployeeID and JobID as its two fields. So my full RowSource for the combobox is:

SELECT Jobs.JobID, Jobs.JobName, Jobs.AreaID
FROM Process INNER JOIN EmplToJob ON Jobs.JobID = EmplToJob.JobID
GROUP BY Jobs.JobID, Jobs.JobName, Job.AreaID;

Now each employee is assigned to one AreaID as their "home" group but some employees may be able to perform jobs in other areas. So I created a table where I could list all of the employees and the other areas that they are trained for jobs in: EmplAltArea which contains the EmployeeID and AreaID fields.

So, now I want the combo box, above, to be able to display all of the jobs for all of the areas that an Employee is certified to perform jobs in. I thought I could use a Multi-Select (set to simple) list box as criteria for my combobox and changed my row source, above, as follows:

SELECT Jobs.JobID, Jobs.JobName, Jobs.AreaID
FROM Process INNER JOIN EmplToJob ON Jobs.JobID = EmplToJob.JobID
WHERE (((Jobs.AreaID) In ([Forms]![frmEmplToJob]![lstAltArea])))
GROUP BY Jobs.JobID, Jobs.JobName, Job.AreaID;

but I just get blanks in my combobox... can anyone point me in the right direction or select a better option? Thank you. :banghead:

 

[jdraw]

Not sure I'm following but it seems you may be looking at a series of cascading comboboxes

What is the ultimate question?
Find open/available jobs in areas that this employee is certified for?

This is where a sample might be helpful for context.

 

[CJ_London]

you will have to build your query in VBA and loop through the list to create your list of selected items. Something like

dim listitem as variant
 
hiddentextbox=""  'hiddentextbox is a hidden control on your form
for each listitem in lstAltArea.itemsselected
    hiddentextbox=hiddentextbox & lstAltArea.itemdata(listitem) & ", " 
next
if right(hiddentextbox=", ",2) then hiddentextbox=left(hiddentextbox,len(hiddentextbox)-2)

then your SQL needs to be

SELECT Jobs.JobID, Jobs.JobName, Jobs.AreaID
FROM Process INNER JOIN EmplToJob ON Jobs.JobID = EmplToJob.JobID
WHERE Eval(Jobs.AreaID & " In (" & [Forms]![frmEmplToJob]![hiddentextbox] & ")")
GROUP BY Jobs.JobID, Jobs.JobName, Job.AreaID;

I'm assuming your AreaID is numeric.

 

[Indigo]

Thank you, CJ.... where would I place this code? And must the user select the items in the multi-select list box or will it read the values as displayed?

Yes, my AreaID is numeric.

 

[CJ_London]

where would I place this code?

I would suggest in the listbox afterupdate event

also realised missed a bit of code. I have corrected my original post so please review

user needs to select items

 

[Indigo]

Type mismatch error points to this line of code:

If Right(HiddenBox = ", ", 2) Then

When I select an item in the list box....

and the combo box is showing all of the Jobs for all areas...

 

[CJ_London]

sorry, bracket in wrong place

If Right(HiddenBox,2) = ", " Then

 

[Indigo]

Thank you, CJ, that cleared the error, but the combo box is still showing all jobs (which number over 80) instead of the 15 I want to see for two areas.

 

[CJ_London]

what are you doing to apply the sql generated to the combo rowsource?

 

[Indigo]

Do you mean this? (rowsource for combobox)

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

 

[arnelgp]

What result are you getting. Remove <>False from the query.

 

[Indigo]

Hi Arnel,

I am getting all of the jobs, instead of just the jobs for the areas I am attempting to filter on. When I remove "<>False" it makes no difference, the combo box is still not filtering the jobs.

 

[arnelgp]

add the EmployeeID to your Criteria:

"SELECT Jobs.JobID, Jobs.JobName FROM Process INNER JOIN EmplToJob On Jobs.JobID = EmplToJob.JobID Where Jobs.AreaID In (" & Forms!frmEmplToJob!HiddenBox & ") And EmplToJob.EmployeeID=" & Forms!frmEmplToJob!EmployeeID & ";"

 

[CJ_London]

Do you mean this? (rowsource for combobox)

No, I meant the vba code you are using to populate the rowsource

 

[Indigo]

        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)

 

[CJ_London]

you need to do more, all you have done is populate the hiddenbox control.

at the very least you need to requery the combo

 

[Indigo]

Arnel, Unfortunately by making that change, I only get the jobs specific to the employee. I need to see the jobs specific to the area and only the areas selected in the multi-select list box.

 

[Indigo]

CJ, thank you for your patience.

I have added a line to requery: Me.cboJobID.requery

and no change.... still get all the jobs....

 

[arnelgp]

Are there duplicate jobs On the combo?

 

[Indigo]

No, Arnel, all are unique.