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 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
|
Public Function ConstitutionTableau(CodEns As String) As Variant
Dim I As Integer, J As Integer
Dim NbCol As Integer, NbLigne As Integer
Dim CodMag As Integer
Dim Tableau() As Variant
Dim RcdLigne As ADODB.Recordset
Dim RcdCol As ADODB.Recordset
Dim Cnn As ADODB.Connection
Set Cnn = CurrentProject.Connection
'Recherche du nombre de colonne
Set RcdCol = New ADODB.Recordset
RcdCol.Open "SELECT DISTINCT [ens-mag], [cdm-mag] FROM Releve GROUP BY [ens-mag], [cdm-mag] HAVING [ens-mag]='" & CodEns & "'", Cnn
NbCol = 0
Do While Not RcdCol.EOF
NbCol = NbCol + 1
RcdCol.MoveNext
Loop
RcdCol.Close
NbCol = NbCol + 1 'Pour la première colonne EAN
'Recherche du nombre de ligne
Set RcdLigne = New ADODB.Recordset
RcdLigne.Open "SELECT EAN FROM Releve GROUP BY EAN;", Cnn
NbLigne = 0
Do While Not RcdLigne.EOF
NbLigne = NbLigne + 1
RcdLigne.MoveNext
Loop
RcdLigne.Close
NbLigne = NbLigne + 1 'Pour ne pas effacer la première ligne de titre
If NbLigne <> 0 And NbCol <> 0 Then
'Constitution du tableau
ReDim Tableau(NbCol, NbLigne)
'Lecture des magasins
Set RcdCol = New ADODB.Recordset
RcdCol.Open "SELECT DISTINCT Releve.[cdm-mag], magasin.[lib-mag] FROM Releve INNER JOIN magasin ON (Releve.[ens-mag] = magasin.[ens-mag]) AND (Releve.[cdm-mag] = magasin.[cdm-mag]) WHERE Releve.[ens-mag]='" & CodEns & "' GROUP BY Releve.[cdm-mag], magasin.[lib-mag]", Cnn
'Constitution des colonnes du tableau
Tableau(1, 1) = "EAN"
For I = 2 To NbCol
Tableau(I, 1) = RcdCol.Fields("lib-mag")
RcdCol.MoveNext
Next I
RcdCol.Close
'Constitution des lignes du tableau
'Lecture des ean
Set RcdLigne = New ADODB.Recordset
RcdLigne.Open "SELECT EAN FROM Releve GROUP BY EAN;", Cnn
For J = 2 To NbLigne
Tableau(1, J) = RcdLigne.Fields("EAN")
RcdLigne.MoveNext
Next J
RcdLigne.Close
' Remplit le tableau de valeurs.
Dim IcolRef As Integer
Dim JLigneRef As Integer
Set RcdLigne = New ADODB.Recordset
RcdLigne.Open "SELECT Releve.EAN, magasin.[lib-mag], Releve.Prix FROM (Releve INNER JOIN magasin ON Releve.[cdm-mag] = magasin.[cdm-mag]) INNER JOIN enseigne ON Releve.[ens-mag] = enseigne.CDEENS WHERE Releve.[ens-mag]='" & CodEns & "' ORDER BY magasin.[lib-mag]", Cnn
Do While Not RcdLigne.EOF
'Recherche l'indice de la colonne
For I = 2 To NbCol
If Tableau(I, 1) = RcdLigne.Fields("lib-mag") Then
IcolRef = I
Exit For
End If
Next I
'Recherche de l'indice de la ligne
For J = 2 To NbLigne
If Tableau(1, J) = RcdLigne.Fields("EAN") Then
JLigneRef = J
Exit For
End If
Next J
Tableau(IcolRef, JLigneRef) = RcdLigne.Fields("Prix")
RcdLigne.MoveNext
Loop
RcdLigne.Close
End If
ConstitutionTableau = Tableau
End Function |
Partager