Dropdown list with Adodb query

user_14002405 0
Viewed 137

I would like to create the dropdown list with the results from my query. I'm looking for help please because I don't know how to display this results on the list.

The exemple of the list:

enter image description here

My query is :

 'DROPDOWN LIST

  Private Sub cb_gest_Change()
  If Not FSD.cb_gest.MatchFound And FSD.cb_gest <> "" Then
  MsgBox "Saisie impossible, le gestionnaire n'existe pas !", , "Contrôle 
  Gestionnaire"
  FSD.cb_gest = ""
  Else
  FSD.Cells(29, COL_DATA) = FSD.cb_gest
  End If
  End Sub

  'DROPDOWN LIST

  Sub init_combo()
  Dim Resultat As ADODB.Recordset
  Dim Requete As String
  FSD.cb_gest.Clear
  Requete = "select lb_gestion from DB_GESTIONNAIRE "
  Requete = Requete + "WHERE (d_deb_valid <= TRUNC(SYSDATE) OR d_deb_valid IS 
  NULL) AND (d_fin_valid >= TRUNC(SYSDATE) OR d_fin_valid IS NULL)"
  Requete = Requete + " ORDER BY LB_GESTION"
  Set Resultat = New ADODB.Recordset
  Resultat.ActiveConnection = oBase
  Resultat.Source = Requete
  Resultat.Open
  While Not Resultat.EOF
  FSD.cb_gest.AddItem Resultat!lb_gestion
  Resultat.MoveNext
  Wend
  If FSD.Cells(29, COL_DATA).Value <> "" Then
  FSD.cb_gest = FSD.Cells(29, COL_DATA).Value
  Else
  FSD.Cells(29, COL_DATA).Value = ""
  End If
  End Sub

Thank you for your help !

vba excel
1 Answers

Consider a different, codeless approach:

  • Add a new sheet to the host document / workbook
  • Import the external data from the "Data" Ribbon tab (select "From SQL Server")
    • Excel creates a ListObject table backed with a QueryTable object that uses a WorkbookConnection that can be configured to automatically refresh on open, or left alone as a one-time pull.
  • Select the produit column in the ListObject/table; Excel highlights the entire column content and leaves the heading un-selected.
  • From the "Formulas" Ribbon tab, click the "Define Name" command in the "Defined Names" group.
    • Name the range ProductsList, verify it refers to TableName[produit] so that it automatically grows and shrinks to fit the column contents.
  • Change the data validation list to =ProductsList.
  • Hide the worksheet housing the query and table, if needed.

No code needed, and the validation list will always keep up with the query results as they are refreshed.

Side note, the query appears to be making inefficient cross-joins, and at least one of them is a where-join that can be expressed as an inner join. Are you sure the query is yielding the expected records (I'm suspecting it's yielding a ton of duplicates, depending on how many records exist in the cross-joined tables)?

SELECT prod.cd_produit AS produit 
FROM db_dossier sousc, db_produit prod, db_protocole proto, db_tiers tiers, db_personne pers
WHERE sousc.cd_dossier = 'SOUSC' AND sousc.lp_etat_doss NOT IN ('ANNUL','A30','IMPAY') AND sousc.is_produit = prod.is_produit

Instinct would be to remove the tables we're not selecting or filtering anything from - if this query produces the same expected output, then assuming primary and foreign keys are defined I believe its execution plan would be more efficient:

SELECT prod.cd_produit AS produit 
FROM db_dossier AS sousc
INNER JOIN db_produit AS prod ON sousc.is_produit = prod.is_produit
WHERE sousc.cd_dossier = 'SOUSC' AND sousc.lp_etat_doss NOT IN ('ANNUL','A30','IMPAY')
user_11885130
Related