1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| Public Sub RechercherDsBase(ByVal Selection As String, ByVal Champs As String, Optional ByVal MaDate As String)
Dim db As DAO.Database ' ou déclarer Dim db AsDatabase ne change rien
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iCols As Integer
strSQL = "SELECT SAVP_Data.[N° FICHE], SAVP_Data.[DATE D'ENTREE], SAVP_Data.[Soldé], SAVP_Data.[REF PRODUIT], SAVP_Data.[N° SERIE MP], SAVP_Data.[N° LOT], " & _
"SAVP_Data.[CODE CLIENT], SAVP_Data.[Nom du CLIENT], SAVP_Data.[Ref_Pdt_Client], SAVP_Data.[N° SERIE CLIENT], " & _
"SAVP_Data.[DOC CLIENT], SAVP_Data.[RAPPORT QUALITE CLIENT], SAVP_Data.[D_Pdt_Rep], SAVP_Data.[Date Transmis Expedition] " & _
"FROM SAVP_Data "
Select Case Selection ' Complétion de la commande SQL
Case "Référence Eaton" ' OptionButton01
strSQL = strSQL & "WHERE (SAVP_Data.[REF PRODUIT] = """ & Champs & """);"
Case "N° Série Eaton" ' OptionButton02
strSQL = strSQL & "WHERE (SAVP_Data.[N° SERIE MP] = """ & Champs & """);"
Case "N° BL Eaton" ' OptionButton03
strSQL = strSQL & "WHERE (SAVP_Data.[Extraction BL de SAP] = """ & Champs & """);"
Case "Référence Client" ' OptionButton04
strSQL = strSQL & "WHERE (SAVP_Data.[Ref_Pdt_Client] = """ & Champs & """);"
Case "Code Client" ' OptionButton05
strSQL = strSQL & "WHERE (SAVP_Data.[CODE CLIENT] = """ & Champs & """);"
Case "N° Série Client" ' OptionButton06
strSQL = strSQL & "WHERE (SAVP_Data.[N° SERIE CLIENT] = """ & Champs & """);"
Case "N° Doc/Cmd Client" ' OptionButton07
strSQL = strSQL & "WHERE (SAVP_Data.[DOC CLIENT] = """ & Champs & """);"
Case "N° Rapport NC Clt" ' OptionButton08
strSQL = strSQL & "WHERE (SAVP_Data.[RAPPORT QUALITE CLIENT] = """ & Champs & """);"
Case Else
Call Messagerie("")
End Select
Set db = DBEngine.OpenDatabase(Workbooks("SAVPRO.xlsm").Sheets("Paramètres").Range("C4").Value & "SAVPRO.accdb") 'Etablissement de la connection avec la base access SAVPRO
Set rs = db.OpenRecordset(strSQL)
For iCols = 0 To rs.Fields.count - 1 ' Copie de l'entête du recordset dans l'onglet
Workbooks("SAVPRO.xlsm").Sheets("ResultRech").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
Workbooks("SAVPRO.xlsm").Sheets("ResultRech").Range("A2").CopyFromRecordset rs ' Copie du contenu du recordset dans l'onglet
'rs.Close
db.Close ' On ferme !
Set rs = Nothing
Set db = Nothing
End Sub |
Partager