Private Sub ChercherAPS_Click()
Dim dossier AS STRING
Dim cible AS Workbook
Dim val1 AS Variant
Dim lg AS INTEGER
Dim Obs1 AS STRING
Dim dercol AS INTEGER
Dim idagent
Dim oConnect AS New ADODB.Connection
Dim Pconn AS STRING


Pconn = "DRIVER={HFSQL32};" & _
"Data source=HFSQL32" & ";" & _
"USER ID=admin" & ";" & _
"PASSWORD=***********" & ";" & _
"Initial Catalog=MaDB" & ";"

val1 = LstAPS.Value
chemin = ThisWorkbook.PATH
dercol = Cells(1, Cells.Columns.COUNT).END(xlToLeft).Column

SET cible = ThisWorkbook
dossier = val1
WITH cible.Worksheets("Salaires")
lg = .Columns("A").Find(dossier, , xlValues, xlWhole).Row
END WITH

Obs1 = Sheets("salaires").Cells(lg, 77).Value
OBSEnCours = Obs1

'Récupération idagent pour select absence injustifiée MySQL
idagent = Sheets("salaires").Cells(lg, dercol)

If idagent <> 0 Or idagent <> "" Then
'SELECT MySQL
Dim rs AS ADODB.Recordset

SET rs = New ADODB.Recordset

'ConnectDB
'Absences injustifiées
Strsql = "SELECT absencesagent.IDInformations AS IDInformations,absencesagent.DateDebut AS DateDebut,absencesagentjustificatif.Justificatif AS Justificatif,absencesagentdecision.LibelleDecision AS Decision,decisionDate As DecisionDate FROM absencesagent LEFT JOIN absencesagentjustificatif ON absencesagentjustificatif.IDAbsencesAgentJustificatif = absencesagent.Justificatif LEFT JOIN absencesagentdecision ON absencesagentdecision.IDAbsencesAgentDecision = absencesagent.Decision WHERE absencesagent.IDAgent = " & (idagent) & " AND absencesagent.Type = 4 ORDER BY IDInformations DESC LIMIT 1;"

oConnect.OPEN (Pconn)

IF oConnect.State = adStateOpen THEN
'MsgBox "Connecté"
rs.Open Strsql, (Pconn)

Do Until rs.EOF

'Remplissage de la liste
Me.ListBoxAbs.AddItem rs!IDInformations
n = Me.ListBoxAbs.ListCount - 1
Me.ListBoxAbs.List(n, 1) = rs!DateDebut
Me.ListBoxAbs.List(n, 2) = rs!Justificatif
Me.ListBoxAbs.List(n, 3) = rs!Decision
'Me.ListBoxAbs.List(n, 4) = rs!DecisionDate

rs.MoveNext
Loop

rs.Close
Else
MsgBox "ko"
End If





'Sanctions
Strsql = "SELECT sanctiontype.Type AS Type,sanctionmotif.Motif AS Motif,sanction.DateSanction AS DateSanction FROM Sanction LEFT OUTER JOIN sanctiontype ON sanction.TypeSanction = sanctiontype.IDSanctionType LEFT OUTER JOIN sanctionmotif ON sanction.MotifSanction = sanctionmotif.IDSanctionMotif WHERE sanction.IDAgent = " & (idagent) & " AND TypeSanction IN (2,3,4,5,6,7,17) Order BY IDSanction DESC LIMIT 3;"

rs.OPEN Strsql, Pconn

Do Until rs.EOF
'Remplissage de la liste
Me.ListBoxSanction.AddItem rs!Type
n = Me.ListBoxSanction.ListCount - 1
Me.ListBoxSanction.List(n, 1) = rs!Motif
Me.ListBoxSanction.List(n, 2) = rs!DateSanction

rs.MoveNext
Loop

rs.Close
End If
End Sub