Stücklisten, Teil I

Stücklisten dienen dazu, die Einzelteile eines Objekts strukturiert darzustellen. Strukturiert bedeutet dabei, dass die Einzelteile jeweils zu Baugruppen zusammengefasst werden. Ein Objekt besteht also aus Baugruppen und/oder Einzelteilen, wobei jede Baugruppe wieder aus Baugruppen und/oder Einzelteilen besteht – bis am Ende jede Baugruppe in ihre Einzelteile aufgelöst ist. Einzelteile sind die kleinsten Einheiten in der Stückliste, sie können nicht weiter aufgeteilt werden.Dieser Beitrag soll zeigen, wie Sie Stücklisten mit Access verwalten. Dabei spielt das TreeView-Steuerelement eine große Rolle.

Die Stückliste kann dazu für verschiedene Zwecke genutzt werden: Sie können daraus zum Beispiel entnehmen, welche Mengen der verschiedenen Einzelteile benötigt werden. Dies ist sowohl für die Beschaffung der benötigten Einzelteile wichtig als auch für die Kalkulation der Materialkosten.

Datenmodell

Das Datenmodell der Lösung sieht im überblick wie in Bild 1 aus. Die Tabelle tblBaugruppen enthält alle Baugruppen, für die in unserem einfach gehaltenen Ansatz nur der Name angegeben werden soll. Die Tabelle enthält lediglich die beiden Felder BaugruppeID als Primärschlüsselfeld sowie das Feld Baugruppe zur Eingabe einer Bezeichnung für die Baugruppe. Diese Tabelle kommt noch ein zweites Mal in der übersicht des Datenmodells vor. Dies hat den Hintergrund, dass Sie jeder Baugruppe weitere Baugruppen unterordnen können. Eine Baugruppe besteht ja nicht direkt lediglich aus Einzelteilen, sondern kann auch andere aus Einzelteilen oder auch Baugruppen zusammengesetzte Baugruppen enthalten.

Bild 1: übersicht des Datenmodells für die Stückliste

Die Abbildung zeigt die Tabelle tblBaugruppen ein zweites Mal, damit wir die Beziehung zwischen zwei Baugruppen abbilden können.

Verknüpfung zwischen Baugruppen

Dies geschieht nämlich durch die Verknüpfungstabelle tblBaugruppenzuordnungen (s. Bild 2). Sie enthält die Felder BaugruppenzuordnungID (als Primärschlüsselfeld), BaugruppeVaterID zur Angabe der übergeordneten Baugruppe aus der Tabelle tblBaugruppen, das Feld BaugruppeKindID zur Angabe der untergeordneten Baugruppe sowie das Feld Menge.

Bild 2: Entwurf der Tabelle tblBaugruppenzuordnungen

Es kann ja auch sein, dass eine Baugruppe gleich mehrmals einer anderen Baugruppe zugeordnet werden soll (zum Beispiel vier Reifen zu einem Auto). Die gewünschte Anzahl tragen Sie dann in das Feld Menge ein.

Für dieses Feld legen wir außerdem den Wert 1 als Standardwert fest – dieser muss dann beim Anlegen gegebenenfalls angepasst werden.

Außerdem soll die Tabellen einen aus den beiden Feldern BaugruppeVaterID und BaugruppeKindID zusammengesetzten eindeutigen Index besitzen. Auf diese Weise verhindern wir, dass eine Kombination aus zwei Baugruppen mehrfach angelegt wird – zu diesem Zweck haben wir ja das Feld Menge hinzugefügt.

Weiter im Datenmodell: Neben den Baugruppen gibt es eine Tabelle mit den Daten der Einzelteile. Sie heißt tblEinzelteile und ist in Bild 3 abgebildet.

Bild 3: Entwurf der Tabelle tblEinzelteile

Wo sind die Projekte

Irgendwann führen alle Baugruppen und/oder Einzelteile zu einem vollständigen Projekt oder Produkt. Warum also gibt es in der Datenbank keine Tabelle für solche Elemente Wenn Sie möchten, können Sie natürlich eine solche hinzufügen. Allerdings ist schon jede einzelne Baugruppe ein Produkt für sich, und eigentlich auch jedes Einzelteil. Machen wir es uns also einfach und fügen einfach den beiden Tabellen tblBaugruppen und tblEinzelteile ein Ja/Nein-Feld namens IstProdukt hinzu, mit dem Sie festlegen können, ob ein solches Element nicht nur Bestandteil einer Baugruppe sein kann, sondern gleichzeitig ein Produkt. Wozu aber benötigen wir solche eine Unterscheidung Weil wir auf irgendeine Weise kennzeichnen wollen, welche Elemente als Hauptelemente im TreeView zur Darstellung des Aufbaus von Bauteilen dienen sollen.

Beziehung zwischen Baugruppen und Einzelteilen

Um die Einzelteile den Baugruppen zuzuordnen, benötigen wir wiederum eine Verknüpfungstabelle zur Herstellung einer m:n-Beziehung. Diese heißt tblBaugruppenEinzelteile und enthält das Primärschlüsselfeld BaugruppeEinzelteilID, das Fremdschlüsselfeld BaugruppeID zum Festlegen der Baugruppe aus der Tabelle tblBaugruppen, das Fremdschlüsselfeld EinzelteilID zum Festlegen des Einzelteils aus der Tabelle tblEinzelteile und das Feld Menge zur Angabe der Anzahl der Einzelteile.

Auch für das Feld Menge dieser Tabelle stellen wir 1 als Standardwert ein, außerdem legen wir für die beiden Felder BaugruppeID und EinzelteilID einen zusammengesetzten, eindeutigen Index fest (s. Bild 4).

Bild 4: Entwurf der Tabelle tblBaugruppenEinzelteile

Die Tabelle tblEinzelteilKategorien schließlich enthält verschiedene Kategorien für die Einzelteile. Diese Tabelle hat den Hinterrgrund, dass vielleicht später einmal die Einzelteile verschiedener Kategorien unterschiedlich rabattiert werden – gegebenenfalls auch noch in Abhängigkeit vom jeweiligen Kunden. Die Tabelle enthält die beiden Felder EinzelteilKategorieID (als Primärschlüsselfeld) und Einzelteilkategorie.

Die Tabelle tblEinzelteile ist über das Feld EinzelteilkategorieID mit dieser Tabelle verknüpft.

Darstellung der Baugruppen und Einzelteile

Die Lösung soll verschiedene Darstellungen ermöglichen. Zunächst einmal wollen wir eine einfache Möglichkeit zur Eingabe der Einzelteile schaffen. Dazu legen Sie ein neues Formular namens frmEinzelteile an, das ein Unterformular sfmEinzelteile enthält. Das Unterformular soll alle Einzelteile enthalten, das Hauptformular das aktuell im Unterformular ausgewählte Einzelteil. Das Unterformular statten wir außerdem mit einer Möglichkeit zum Filtern nach verschiedenen Kriterien aus.

In der Entwurfsansicht sieht das Unterformular wie in Bild 5 aus. Das Feld EinzelteilkategorieID haben wir über den Kontextmenü-Eintrag ändern zu|Kombinationsfeld in ein Kombinationsfeld umgewandelt, damit die Einträge der Tabelle tblEinzelteilkategorien direkt nachgeschlagen werden können. Dazu haben wir die Eigenschaft Datensatzherkunft auf den folgenden Ausdruck eingestellt:

Bild 5: Aufbau des Unterformulars sfmEinzelteile

SELECT EinzelteilkategorieID, Einzelteilkategorie FROM tblEinzelteilkategorien 
ORDER BY Einzelteilkategorie;

Die Eigenschaften Spaltenanzahl und Spaltenbreiten erhalten die Werte 2 und 0cm. Das Formular erhält für die Eigenschaft Standardansicht den Wert Datenblatt.

Haupt- und Unterformular synchronisieren

Damit Haupt- und Unterformular die gleichen Datensätze markieren, muss beim Laden des Hauptformulars die folgende Prozedur ausgeführt werden. Diese stellt das Recordset des Hauptformulars auf das Recordset des Unterformulars ein:

Private Sub Form_Load()
    Set Me.Recordset =  Me!sfmEinzelteile.Form.Recordset
End Sub

Einzelteilkategorie auswählen

Damit neben der Auswahl der Datensätze der Tabelle tblEinzelteilkategorien im Hauptformular nicht nur die bestehenden Einträge gewählt, sondern auch direkt neue Einträge eingefügt werden können, fügen Sie dem Kombinationsfeld eine Prozedur hinzu, die durch das Ereignis Bei nicht in Liste ausgelöst wird (s. Listing 1).

Private Sub cboEinzelteilkategorieID_NotInList(NewData As String, Response As Integer)
    Dim db As DAO.Database
    If MsgBox("Kategorie ''" & NewData & "'' anlegen", vbYesNo) = vbYes Then
        Set db = CurrentDb
        db.Execute "INSERT INTO tblEinzelteilkategorien(Einzelteilkategorie) VALUES(''" 
            & NewData & "'')", dbFailOnError
        Response = acDataErrAdded
    End If
End Sub

Listing 1: Hinzufügen neuer Einträge in die Tabelle tblEinzelteilkategorien über das Kombinationsfeld

Diese Prozedur fragt den Benutzer bei der Eingabe eines noch nicht vorhandenen Wertes, ob ein neuer Eintrag in der Tabelle tblEinzelteilkategorien angelegt werden soll. Falls ja, legt die Prozedur den Datensatz gleich mit einer entsprechenden SQL-INSERT INTO-Anweisung an. Auf diese Weise sparen wir uns an dieser Stelle ein extra Formular für die Verwaltung der Kategorien (dieses würde notwendig, wenn Sie einmal Kategorien ändern oder löschen wollen).

Baugruppen verwalten

Für die Verwaltung der Baugruppen wollen wir ein ähnliches Formular wie für die Einzelteile anlegen. Wir benötigen also wieder ein Hauptformular, diesmal namens frmBaugruppen, sowie ein Unterformular namens sfmBaugruppen.

Allerdings weist die Tabelle tblBaugruppen im aktuellen Zustand mit Baugruppe und IstProdukt ohnehin nur zwei Felder mit Geschäftsdaten auf. Wir müssen also nicht unbedingt ein Hauptformular mit den Detaildaten und ein Unterformular mit einer Listenansicht erstellen. Eine Liste im Unterformular reicht völlig aus, das Hauptformular soll nur weitere Steuerelemente wie etwa die OK-Schaltfläche nachliefern. Im Entwurf sieht das Formular wie in Bild 6 aus.

Bild 6: Haupt- und Unterformular zur Darstellung von Baugruppen

Wenn Sie nun in die Formularansicht des Formulars frmBaugruppen wechseln, erwartet Sie der Anblick auf Bild 7.

Bild 7: Bearbeiten von Baugruppen in der Formularansicht mit einem Unterformular in der Datenblattansicht

Baugruppen und Einzelteile im TreeView

Nun wenden wir uns interessanteren Themen zu: zum Beispiel dem Formular frm-StuecklisteTreeView, welches ein TreeView-Steuerelement zur hierarchischen Anzeige der Baugruppen und Einzelteile enthält.

Statten Sie dazu ein neues Formular namens frmStuecklisteTreeView zunächst mit einem TreeView-Steuerelement namens ctlTreeView sowie mit einer Schaltfläche namens cmdOK aus.

Stellen Sie die Eigenschaften Horizontaler Anker und Vertikaler Anker für das TreeView-Steuerelement jeweils auf Beide ein (s. Bild 8). Für die Eigenschaft Vertikaler Anker der Schaltfläche cmdOK legen Sie den Wert Unten fest.

Bild 8: Verankern des TreeView-Steuerelements, sodass es sowohl vertikal als auch horizontal angepasst wird

Auf diese Weise passen sich TreeView-Steuerelement und Schaltfläche an die Ausmaße des Formulars an – allerdings stehen diese Eigenschaften erst ab Access 2007 zur Verfügung. Sollten Sie noch eine ältere Access-Version nutzen – dies wäre ein Grund zum Wechseln.

Legen Sie außerdem ein ImageList-Steuerelement an, denn wir wollen die Einträge des TreeView-Steuerelements mit entsprechenden Icons etwas aufhübschen. Dieses Steuerelement soll den Namen ctlImageList erhalten.

ImageList-Steuerelement füllen

Das ImageList-Steuerelement müssen wir zunächst mit den benötigten Bildern füllen. Wir benötigen eines für das Root-Element des TreeView-Steuerelements, eines für ein Projekt – in diesem Fall ein Hierarchie-Icon, eines für eine Baugruppe (hier durch mehrere Zahnräder abgebildet) sowie ein Icon für ein Einzelteil, hier ein einzelnes Zahnrad. Damit diese wie in Bild 9 in das ImageList-Steuerelement gelangen, fügen Sie entweder selbst geeignete Bilder hinzu oder erledigen dies per VBA-Code.

Bild 9: Hinzufügen der benötigten Icons für das TreeView-Steuerelement

In unserem Fall war das notwendig, da das ImageList-Steuerelement nicht mit dem Bildformat zurechtkam. Also lesen wir die Bilder erst in ein StdPicture-Objekt ein und speichern diese dann im ImageList-Steuerelement.

Eine einfache Prozedur, um dies zu erledigen, finden Sie im Modul mdl-ImageListFuellen beziehungsweise in Listing 2.

Public Sub ImageListFuellen(strFormular As String, strImageList As String, _
        ParamArray strImages() As Variant)
    Dim objPicture As stdole.StdPicture
    Dim strImage As String
    Dim strImageName As String
    Dim objNode As MSComctlLib.Node
    Dim objImageList As MSComctlLib.ImageList
    Dim i As Integer
    Dim frm As Form
    DoCmd.OpenForm strFormular, acDesign
    Set frm = Forms(strFormular)
    Set objImageList = frm(strImageList).Object
    For i = LBound(strImages) To UBound(strImages)
        strImage = strImages(i)
        strImageName = Mid(strImage, InStrRev(strImage, "\") + 1)
        strImageName = Left(strImageName, InStrRev(strImageName, ".") - 1)
        Set objPicture = mdlOGL0713.LoadPictureGDIP(strImage)
        objImageList.ListImages.Add , strImageName, objPicture
    Next i
    DoCmd.Close acForm, frm.Name
End Sub

Listing 2: Prozedur zum Füllen des ImageList-Steuerelements mit Bilddateien

Der Aufruf dieser Prozedur sieht etwa wie folgt aus:

ImageListFuellen  "frmStuecklisteTreeView",  "ctlImageList",  "c:\gearwheel.png", _
"c:\gearwheels.png",  "c:\home.png",  "c:\elements_hierarchy.png"

Die Prozedur erwartet den Namen des Formulars, in dem sich das ImageList-Steuerelement befindet, sowie den Namen des ImageList-Steuerelements als Parameter. Außerdem können Sie eine durch Kommata getrennte Liste der hinzuzufügenden Elemente angeben.

Die Prozedur ImageListFuellen öffnet das Formular im Entwurf, referenziert das ImageList-Steuerelement und durchläuft alle Elemente der Bilderliste. Dabei liest sie erst den Pfad in die Variable strImage ein und ermittelt daraus den reinen Dateinamen ohne Verzeichnis und Dateiendung. Dann erstellt sie ein StdPicture-Objekt auf Basis des in der Datei gespeicherten Bildes und fügt dieses mit der Add-Methode der ListImages-Auflistung in das ImageList-Steuerelement ein. Schließlich schließt sie das Formular und speichert dieses gleichzeitig.

Achtung: Die per VBA hinzugefügten Bilder bleiben nur dauerhaft im ImageList-Steuerelement erhalten, wenn die Prozedur das Formular in der Entwurfsansicht öffnet – in der Formularansicht gelingt dies nicht. Deswegen müssen zur Laufzeit hinzugefügte Bilder auch bei jedem öffnen des Formulars neu hinzugefügt werden – außer, Sie möchten das Formular zur Laufzeit in der Entwurfsansicht öffnen, was zum Beispiel mit der Runtime oder mit .mde/.accde-Datenbanken nicht möglich ist.

Laden des Formulars

Nun erstellen Sie eine Ereignisprozedur, die durch das Ereignis Beim Laden des Formulars ausgelöst wird. Dieses soll die Eigenschaften des TreeView-Steuerelements einstellen, das ImageList-Steuerelement zuweisen und eine Prozedur aufrufen, die das TreeView-Steuerelement mit den Daten füllt.

In Listing 3 finden Sie zunächst die Deklaration zweier Variablen zum Referenzieren der beiden Steuerelemente ctlTreeView und ctlImageList.

Dim WithEvents objTreeView As MSComctlLib.TreeView
Dim objImageList As MSComctlLib.ImageList
Private Sub Form_Load()
     Set objTreeView = Me!ctlTreeView.Object
     Set objImageList = Me!ctlImageList.Object
     With objTreeView
         .Appearance = ccFlat
         .BorderStyle = ccNone
         .ImageList = objImageList
         .LineStyle = tvwTreeLines
         .OLEDragMode = ccOLEDragAutomatic
         .OLEDropMode = ccOLEDropManual
         .Style = tvwTreelinesPlusMinusPictureText
     End With
     TreeViewFuellen
End Sub

Listing 3: Diese Prozedur passt das TreeView-Steuerelement an und startet den Füllvorgang.

Die durch das Ereignis Beim Laden ausgelöste Ereignisprozedur Form_Load füllt zunächst die beiden Variablen objTreeView und objImageList mit Verweisen auf die Object-Eigenschaft der entsprechenden Steuerelemente. Dann stellt sie einige Eigenschaften des Treeview-Steuerelements ein. Dies könnten Sie zwar auch über das Eigenschaftsfenster des TreeView-Steuerelements erledigen. Wenn Sie dies allerdings per Code tun, können Sie die entsprechenden Anweisungen direkt für weitere TreeView-Steuerelemente übernehmen und brauchen sich nicht durch das Eigenschaftsfenster zu klicken.

Schließlich ruft die Prozedur die Routine TreeViewFuellen auf, welche die Elemente der entsprechenden Tabellen einliest.

Produkte im TreeView anzeigen

Weiter oben haben wir definiert, dass sowohl Baugruppen als auch Einzelteile als Produkte eingesetzt werden können. Die einzige Kennzeichnung, die Produkte von herkömmlichen Baugruppen und Einzelteilen unterscheidet, ist der Wert True im Feld IstProdukt der beiden Tabellen tblBaugruppen und tblEinzelteile. Auf diese Weise ermöglichen wir es auch, dass als komplette Produkte klassifizierte Baugruppen oder Einzelteile weiterhin als Elemente einer Baugruppe verwendet werden können.

Wir wollen nun in der Prozedur TreeViewFuellen zunächst ein Root-Objekt namens Produkte anlegen. Unterhalb dieses Objekts sollen alle Baugruppen und Einzelteile erscheinen, die als Produkt gekennzeichnet sind. Die Prozedur TreeViewFuellen ruft dann für jedes Produkt die rekursive Routine TreeView-Fuellen_Rek auf, welche die untergeordneten Baugruppen und Einzelteile einliest.

Die Prozedur TreeViewFuellen erstellt zunächst ein Recordset auf Basis der Abfrage qryProdukte (s. Listing 4). Diese ist jedoch keine herkömmliche SELECT-Abfrage, sondern eine UNION-Abfrage. Sie führt die Ergebnisse der gespeicherten Abfragen qryProdukteBaugruppen und qryProdukteEinzelteile zusammen.

Private Sub TreeViewFuellen()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Dim objNode As MSComctlLib.Node
     Set db = CurrentDb
     Set rst = db.OpenRecordset("SELECT * FROM qryProdukte", dbOpenDynaset)
     objTreeView.Nodes.Clear
     Set objNode = objTreeView.Nodes.Add(, , "r0", "Produkte", "home")
     With objNode
         .Expanded = True
     End With
     Do While Not rst.EOF
         Set objNode = objTreeView.Nodes.Add("r0", tvwChild, "r0|" & rst!Key _
             & rst!ProduktID, rst!Produkt, "elements_hierarchy")
         If rst!Key = "b" Then
             TreeViewFuellen_Rek db, rst!ProduktID, objNode.Key
         End If
         rst.MoveNext
     Loop
End Sub

Listing 4: Füllen der ersten und zweiten Ebene des TreeView-Steuerelements

Die Abfrage qryProdukteBaugruppen sieht wie in Bild 10 aus, und liefert alle Datensätze der Tabelle tblBaugruppen, deren Feld IstProdukt den Wert True aufweist.

Bild 10: Abfrage von Baugruppen, die auch als Produkt ausgezeichnet sind.

Ganz ähnlich ist die Abfrage qryPro-dukte-Einzelteile aufgebaut (s. Bild 11). Allerdings liefert diese die Datensätze der Tabelle tblEinzelteile, die als Produkt gekennzeichnet sind. Die UNION-Abfrage qryProdukte fasst die Inhalte beider Abfragen zusammen. Sie enthält den folgenden Code:

Bild 11: Abfrage der Einzelteile, die auch als Produkt ausgezeichnet sind.

SELECT BaugruppeID AS ProduktID, Baugruppe AS Produkt
FROM qryProdukteBaugruppen
UNION
SELECT EinzelteilID AS ProduktID, Einzelteil AS Produkt
FROM qryProdukteEinzelteile
ORDER BY Produkt;

Hier ist zu beachten, dass Sie den jeweiligen Feldern der beiden Tabellen per AS-Schlüsselwort den gleichen Spaltennamen zuweisen müssen.

Zurück zur Prozedur TreeViewFuellen: Diese erstellt nun zunächst das Root-Element mit der Bezeichnung Produkte und dem Bild, das im ImageList-Steuerelement unter dem Key mit dem Wert home gespeichert ist. Das neue Element erhält als Key den Wert r0. Außerdem stellt die Prozedur die Eigenschaft Expanded auf True ein, damit das Element immer aufgeklappt ist.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo