Dynamic Selection Query with Criteria in Ms Access

I have a query in which I select multiple values. To create my form.

The results look like this :

the last Column [Candidat Nom] is a drop down list with an other query (lets call it Query 1) that select my drop down list values.

The selection is good and is what I'm looking for. Except I get the same value for all lines when they need to be different.

To simplify

let's take the following exemple

I have the following candidate that wants to join for the following job (Represented with their ID).

As we can see 2 candidates wants job N° 12. and 1 candidate for each other job.

What I get

All candidates are listed for every job.

What I want

What I actually did

is I put the following query (Query 1) on my column.

SELECT T_SALARIE.SALARIE_nom & " " & T_SALARIE.SALARIE_prenom AS Candidat Nom

FROM T_EMPLOI INNER JOIN (T_SALARIE INNER JOIN (T_SALARIE_EMPLOI LEFT JOIN T_STATUT_EMPLOI ON T_SALARIE_EMPLOI.SALARIE_EMPLOI_statut_id = T_STATUT_EMPLOI.STATUT_EMPLOI_id) ON T_SALARIE.SALARIE_NNI = T_SALARIE_EMPLOI.SALARIE_EMPLOI_salarie_nni) ON T_EMPLOI.EMPLOI_identifiant = T_SALARIE_EMPLOI.SALARIE_EMPLOI_emploi_identifiant

WHERE (((T_STATUT_EMPLOI.STATUT_EMPLOI_statut) Like "*valid*" Or (T_STATUT_EMPLOI.STATUT_EMPLOI_statut) Like "*décidé*")  AND ((T_EMPLOI.EMPLOI_entreprise_id)=1));

This gave me the result I want but with the issue I mentioned previously (Same result for each line)

I thought I needed a new Criteria. I added one, where It's going to select the candidate when the two "emploi ID" of my actual table (Shown before) and the one helping me select the candidates are equal.

With the following query:

SELECT T_SALARIE.SALARIE_nom & " " & T_SALARIE.SALARIE_prenom AS Candidat, T_SALARIE_EMPLOI.SALARIE_EMPLOI_emploi_identifiant

FROM T_EMPLOI INNER JOIN (T_SALARIE INNER JOIN (T_SALARIE_EMPLOI LEFT JOIN T_STATUT_EMPLOI ON T_SALARIE_EMPLOI.SALARIE_EMPLOI_statut_id = T_STATUT_EMPLOI.STATUT_EMPLOI_id) ON T_SALARIE.SALARIE_NNI = T_SALARIE_EMPLOI.SALARIE_EMPLOI_salarie_nni) ON T_EMPLOI.EMPLOI_identifiant = T_SALARIE_EMPLOI.SALARIE_EMPLOI_emploi_identifiant

WHERE (((T_STATUT_EMPLOI.STATUT_EMPLOI_statut) Like "*valid*" Or (T_STATUT_EMPLOI.STATUT_EMPLOI_statut) Like "*décidé*") AND ((T_SALARIE_EMPLOI.SALARIE_EMPLOI_emploi_identifiant)=[R_Select_COMOB]![ACTION_identifiant_emploi]));

But I keep on getting the following pop up that asks me to enter a Job ID

So how can I make the query for each line compare and select the right values?

I hope I was clear in explaining. If not please let me know so that I can add more details.

Thank you !

CodePudding user response：

Thanks to your help and specially @June7 for his propositions, I found a solution regarding my problem :

I added a criteria to select values based on JobID that I wasn't selecting in the first hand. And then based on the column (jobID) select the values needed

here is my final query :

SELECT 
[SALARIE_nom] & " " & [SALARIE_prenom] & " (" & [SALARIE_NNI] & ")" AS Salarié, T_SALARIE_EMPLOI.SALARIE_EMPLOI_salarie_nni, T_SALARIE_EMPLOI.SALARIE_EMPLOI_id, T_SALARIE_EMPLOI.SALARIE_EMPLOI_emploi_identifiant 

FROM 

(T_STATUT_EMPLOI INNER JOIN T_SALARIE_EMPLOI ON T_STATUT_EMPLOI.STATUT_EMPLOI_id = T_SALARIE_EMPLOI.SALARIE_EMPLOI_statut_id) LEFT JOIN R_Select_Salarie ON T_SALARIE_EMPLOI.SALARIE_EMPLOI_salarie_nni = R_Select_Salarie.SALARIE_NNI 

WHERE 

(((T_SALARIE_EMPLOI.SALARIE_EMPLOI_emploi_identifiant)=[Formulaires]![F_COMOB]![ACTION_identifiant_emploi]) AND ((T_STATUT_EMPLOI.STATUT_EMPLOI_statut) Like "*validé*") AND ((T_SALARIE_EMPLOI.SALARIE_EMPLOI_Entreprise) Like "*RTE*"));

And Then to update my values for each line. I added a VBA code that requery on input.

Private Sub ACTION_Candidats_P_Enter()
ACTION_Candidats_P.Requery
End Sub

With that my problem is solved.