0

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

The results look like this :

enter image description here

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.

enter image description here

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

enter image description here

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

enter image description here

All candidates are listed for every job.

What I want

enter image description here

enter image description here

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)

So

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

enter image description here

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 !

June7
  • 19,874
  • 8
  • 24
  • 34
Kais
  • 67
  • 1
  • 9
  • 1
    What you seem to be describing is conditional combobox/listbox. Cannot be done in query. Need code behind a form. Common topic, do research on it. Start with review of https://stackoverflow.com/questions/70483138/connected-combo-boxes-in-continuous-form-the-second-combo-doesnt-show-its-val – June7 Aug 05 '22 at 02:29
  • Does this answer your question? [Cascading combo boxes](https://stackoverflow.com/questions/22645191/cascading-combo-boxes) – June7 Aug 05 '22 at 02:40
  • Thank you so much for your suggestions @June7. For cascading combo boxes it didn't work for me. I'll look into into conditional combobox and let you know. – Kais Aug 05 '22 at 08:00
  • Conditional/cascading/dependent - all mean the same thing. – June7 Aug 05 '22 at 08:03
  • Oh ok, when I said that I was talking about adding criteria... but when I add them so that the selection will depend on Job ID I get the pop up shown in the description. When you talked about the code @June7, should I develop it to select the values or just for filtering ? I'm sorry but it just I don't really know how to proceed. – Kais Aug 08 '22 at 12:29
  • The code would be to set filter criteria in combobox RowSource. Follow many examples for cascading combobox. That prompt usually means Access can't find object. Is the combobox name spelled correctly? – June7 Aug 08 '22 at 13:32
  • I'll try to develop the code and let you know if it works. Yes the name is spelled correctly. – Kais Aug 09 '22 at 08:35
  • So the name is correct with `COMOB` instead of `COMBO`? – June7 Aug 09 '22 at 14:10
  • I don't know if I misspelled it somewhere in the question. But I made sure that the names are correct on my DB and they where. COMOB is the name of my query and I used COMBO just when I mentioned COMBOBOX. But please correct me if I made a mistake somewhere. – Kais Aug 09 '22 at 14:41
  • Query name is part of the control reference and your image shows COMOB so I wondered if that was correct. I don't use dynamic parameterized query objects. – June7 Aug 09 '22 at 14:48

1 Answers1

0

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.

Kais
  • 67
  • 1
  • 9