maandag 16 januari 2017

Access: een listbox met meervoudige selectie en een gekoppelde en dynamische grafiek

In Access kunnen we listboxen gebruiken voor een (meervoudige) keuze uit een dataset. Dankzij VBA en MS GRAPH kunnen we een grafiek of meerdere grafieken mee laten lopen met de gemaakte keuze.


Met name voor een meervoudige selectie zijn in VBA nogal wat stappen nodig om het een en ander voorelkaar te krijgen. Voor grafieken gebruikt Access het instrument MS GRAPH met zijn eigen objectmodel.

We zullen de stappen langslopen.

Het voorbeeld is gebaseerd op de database NOORDENWIND van MICROSOFT. Voor de listbox heb ik de volgende SQL string gebruikt:

SELECT Klantnummer, Bedrijf, Contactpersoon
FROM tblKlanten
ORDER BY Klantnummer

Stap 1: gemaakte keuzes uit listbox in een array opslaan

    ReDim arrLijst(Me.lboMeervoudig.ItemsSelected.Count)
    intTeller = 0
    For Each objItem In Me.lboMeervoudig.ItemsSelected
        arrLijst(intTeller) = Me.lboMeervoudig.Column(0, objItem)
        intTeller = intTeller + 1
    Next

Stap 2: inhoud array overhevelen naar strValue met komma als scheidingsteken

    For intTeller = 0 To UBound(arrLijst) - 1
        strValue = strValue & arrLijst(intTeller) & ","
    Next

Stap 3: gekozen klantnamen in een array stoppen

    ReDim arrKlant(Me.lboMeervoudig.ItemsSelected.Count)
    intTeller = 0
    For Each objKlant In Me.lboMeervoudig.ItemsSelected
        arrKlant(intTeller) = Me.lboMeervoudig.Column(1, objKlant)
        intTeller = intTeller + 1
    Next

Stap 4: klantnamen uit array sorteren

SortArray arrKlant

SortArray verwijst hier naar een functie die de sortering uitvoert:

Function SortArray(ArrayToSort() As Variant) As Variant
    Dim intEerste          As Integer
    Dim intLaatste         As Integer
    Dim intI               As Integer
    Dim intJ               As Integer
    Dim strTemp            As String
     
    intEerste = LBound(ArrayToSort)
    intLaatste = UBound(ArrayToSort)
    For intI = intEerste To intLaatste - 1
        For intJ = intI + 1 To intLaatste
            If ArrayToSort(intI) > ArrayToSort(intJ) Then
                strTemp = ArrayToSort(intJ)
                ArrayToSort(intJ) = ArrayToSort(intI)
                ArrayToSort(intI) = strTemp
            End If
        Next intJ
    Next intI
End Function

Stap 5: string omzetten naar een string die in een filter gebruikt kan worden

    strResult = "("
    Do While InStr(strValue, ",") > 0
        strResult = strResult & "'" & Left(strValue, InStr(strValue, ",") - 1) & "',"
        strValue = Mid(strValue, InStr(strValue, ",") + 1)
    Loop
    strResult = Left(strResult, Len(strResult) - 1) & ")"

Stap 6: opbouw SQL string voor de grafiek

    strSQL = " SELECT Year([Orderdatum]) AS Jaar, "
    strSQL = strSQL & " Sum([Prijs per eenheid]*[hoeveelheid]) AS Totaal "
    strSQL = strSQL & " FROM (tblKlanten INNER JOIN tblOrders "
    strSQL = strSQL & " ON tblKlanten.Klantnummer = tblOrders.Klantnummer) "
    strSQL = strSQL & " INNER JOIN tblOrderRegels ON "
    strSQL = strSQL & " tblOrders.[Order-id] = tblOrderRegels.[Order-id] "
    strSQL = strSQL & " where tblKlanten.klantnummer in " & strResult
    strSQL = strSQL & " GROUP BY Year([Orderdatum]) "

Stap 7: aansturen MS GRAPH grafiek

    With Me.grfOmzetPerJaar
        .RowSource = strSQL
        .HasTitle = True
        .ChartTitle.Text = Left(strKlant, Len(strKlant) - 2)
        .ChartType = 51 'xlColumnClustered = XlChartType Enumeration
        .ApplyDataLabels xlDataLabelsShowValue
    End With
    
    With Me.grfOmzetPerJaar.Axes(1) 'xlCategory=1
      .HasTitle = True
      .AxisTitle.Caption = "Omzet per jaar"
    End With 'X-Axis
    
    With Me.grfOmzetPerJaar.Axes(2)
      .MinimumScaleIsAuto = True
      .MaximumScaleIsAuto = True
    End With

De totale code achter de listbox bij de gebeurtenis AfterUpdate ziet er dan als volgt uit:

Private Sub lboMeervoudig_AfterUpdate()
    Dim arrLijst(), arrKlant()
    Dim intTeller As Integer
    Dim objItem As Variant, objKlant As Variant
    Dim strResult As String, strValue As String, strKlant As String
    Dim strSQL As String
    Dim db As dao.Database
     
    'gemaakte keuzes in een array stoppen
    ReDim arrLijst(Me.lboMeervoudig.ItemsSelected.Count)
    intTeller = 0
    For Each objItem In Me.lboMeervoudig.ItemsSelected
        arrLijst(intTeller) = Me.lboMeervoudig.Column(0, objItem)
        intTeller = intTeller + 1
    Next
    
    'inhoud array overhevelen naar variabele strValue met een komma als scheidingsteken
    For intTeller = 0 To UBound(arrLijst) - 1
        strValue = strValue & arrLijst(intTeller) & ","
    Next
    
    'gekozen klanten in een array stoppen
    ReDim arrKlant(Me.lboMeervoudig.ItemsSelected.Count)
    intTeller = 0
    For Each objKlant In Me.lboMeervoudig.ItemsSelected
        arrKlant(intTeller) = Me.lboMeervoudig.Column(1, objKlant)
        intTeller = intTeller + 1
    Next
    
    'array met klanten sorteren
    SortArray arrKlant
    
    'inhoud array overhevelen naar variabele strKlant met een komma en spatie als scheidingsteken
    For intTeller = 1 To UBound(arrKlant)
        strKlant = strKlant & arrKlant(intTeller) & ", "
    Next    
    Me.klantentotaal = Left(strKlant, Len(strKlant) - 2)
   
    'string omzetten naar een string die in een filter gebruikt kan worden
    strResult = "("
    Do While InStr(strValue, ",") > 0
        strResult = strResult & "'" & Left(strValue, InStr(strValue, ",") - 1) & "',"
        strValue = Mid(strValue, InStr(strValue, ",") + 1)
    Loop
    strResult = Left(strResult, Len(strResult) - 1) & ")"
    
    'sql voor de grafiek
    strSQL = " SELECT Year([Orderdatum]) AS Jaar, "
    strSQL = strSQL & " Sum([Prijs per eenheid]*[hoeveelheid]) AS Totaal "
    strSQL = strSQL & " FROM (tblKlanten INNER JOIN tblOrders "
    strSQL = strSQL & " ON tblKlanten.Klantnummer = tblOrders.Klantnummer) "
    strSQL = strSQL & " INNER JOIN tblOrderRegels ON "
    strSQL = strSQL & " tblOrders.[Order-id] = tblOrderRegels.[Order-id] "
    strSQL = strSQL & " where tblKlanten.klantnummer in " & strResult
    strSQL = strSQL & " GROUP BY Year([Orderdatum]) "
    
   'grafiek aansturen   
    With Me.grfOmzetPerJaar
        .RowSource = strSQL
        .HasTitle = True
        .ChartTitle.Text = Left(strKlant, Len(strKlant) - 2)
        .ChartType = 51 'xlColumnClustered = XlChartType Enumeration
        .chartarea.interior.Color = RGB(252, 230, 100)
        .chartarea.Border.Color = RGB(252, 230, 100)
       .ApplyDataLabels xlDataLabelsShowValue
    End With
    
    With Me.grfOmzetPerJaar.Axes(1) 'xlCategory=1
      .HasTitle = True
      .AxisTitle.Caption = "Omzet per jaar"
    End With 'X-Axis
    
    With Me.grfOmzetPerJaar.Axes(2)
      .MinimumScaleIsAuto = True
      .MaximumScaleIsAuto = True
      End With
    
    Me.Refresh
    
    'sluiten objecten
    db.Close
    Set db = Nothing
End Sub


maandag 9 januari 2017

Access: keuzes uit een listbox met meervoudige selectie

In Access kunnen we op een formulier listboxen maken waarbij we kunnen kiezen voor de meervoudige selectie-optie. We kunnen meerdere keuzes maken. De vraag is nu hoe we deze keuze met behulp van VBA scripts door kunnen geven aan een volgende formulier.

Het onderstaande is een voorbeeld van zo'n listbox met meervoudige selectie.


De meerdere keuzes moeten we hier maken met de CTRL toets ingedrukt. De uiteindelijke keuze maken we dan met de combinatie CTRL toets ingedrukt en dubbelklikken.

Achter deze gebeurtenis heb ik dan de volgende VBA code geplaatst:

Private Sub lboMeervoudig_DblClick(Cancel As Integer)
    Dim arrLijst()
    Dim intTeller As Integer
    Dim objItem As Variant, strResult As String
    
    'gemaakte keuzes in een array stoppen
    ReDim arrLijst(Me.lboMeervoudig.ItemsSelected.Count)
    intTeller = 0
    For Each objItem In Me.lboMeervoudig.ItemsSelected
        arrLijst(intTeller) = Me.lboMeervoudig.Column(0, objItem)
        intTeller = intTeller + 1
    Next
    
    'inhoud array overhevelen naar variabele strResult met een komma als scheidingsteken
    For intTeller = 0 To UBound(arrLijst) - 1
        strResult = strResult & arrLijst(intTeller) & ","
    Next
    
    'In Access is het niet mogelijk via OpenArgs meerdere parameters of een array door te geven
    'alleen een string is mogelijk; meerdere waarden moeten dus in een string opgeslagen worden
     DoCmd.OpenForm "frmKeuzelijstOntvanger", OpenArgs:=strResult
End Sub

Bovenstaande code geeft de gemaakte keuzes dus door aan een formulier frmKeuzelijstontvanger. Achter dit formulier heb ik bij de gebeurtenis Bij laden de volgende VBA code geplaatst:

Private Sub Form_Load()
    Dim intPos As Integer
    Dim strValue As String, strResult As String
    strValue = Me.OpenArgs
    
    'string omzetten naar een string die in een filter gebruikt kan worden
    strResult = "("
    Do While InStr(strValue, ",") > 0
        strResult = strResult & "'" & Left(strValue, InStr(strValue, ",") - 1) & "',"
        strValue = Mid(strValue, InStr(strValue, ",") + 1)
    Loop
    strResult = Left(strResult, Len(strResult) - 1) & ")"
    
    'filteren op gemaakte keuze
    Me.Filter = "klantnummer IN " & strResult
    Me.FilterOn = True
End Sub

In het opgeroepen formulier verschijnen dan keurig de gemaakte keuzes: