Verpackungsprozesse mit Access: Formulare

Ein Kunde hatte neulich die Herausforderung, dass er Produkte bestehend aus einzelnen Bauteilen ausliefern möchte und dabei erfassen muss, welche Bauteile in welcher Menge in den verschiedenen Versandkartons landen. Der Verpackungsvorgang im Lager erfolgt individuell und in einer Weise, dass die Mitarbeiter die einzelnen Bauteile so in Kartons füllen, dass die Kartons optimal genutzt werden. Während des Verpackens sollen sie aufzeichnen, welcher Karton welche Bauteile enthält, damit diese beim Empfänger so ausgepackt werden können, wie die Bauteile benötigt werden. In diesem Beitrag beschreiben wir zunächst, wie das Datenmodell für dieses Vorhaben aussieht und erstellen basierend darauf die notwendigen Formulare. Schließlich geben wir auch noch die Stücklisten für die verschiedenen Kartons per Bericht aus.

Bestellungen eingeben

Die Grundlage für diese Lösung sind Bestellungen. Diese bearbeiten wir in dem einfachen Formular aus Bild 1. Hier können wir im Hauptformular den Kunden auswählen und das Bestelldatum eingeben.

Bild 1: Einfaches Bestellformular

Außerdem wählen wir im Unterformular die Produkte aus, die dieser Bestellung zugeordnet werden sollen und fügen die gewünschte Menge hinzu.

Um Preise und andere Informationen kümmern wir uns in diesem Fall aus Gründen der Übersichtlichkeit nicht.

Wenn eine Bestellung eingegeben wurde, können wir mit dem Button Verpacken zum nächsten Formular wechseln, dass die eigentliche Funktion dieses Beitrags abbildet.

Vorab noch die Information, dass jedes Produkt aus verschiedenen Bauteilen in unterschiedlicher Menge besteht, die im Hintergrund in Tabellen gespeichert sind.

Auf die genauen Zusammenhänge im Datenmodell gehen wir später ein.

Erfassung der verpackten Bauteile

Das Formular frmVerpackung ist das eigentliche Arbeitstier (siehe Bild 2). Hier sehen wir verschiedene Bereiche:

Bild 2: Formular zum Protokollieren der Verpackung

• Ganz oben finden wir nochmal die Daten zu der jeweiligen Bestellung.
• Darunter sehen wir die einzelnen Positionen der Bestellung, also die Produkte.
• Das Listenfeld links unten zeigt die Bauteile, die zu den Produkten dieser Bestellung gehören. Wenn das Formular geöffnet wird, zeigt diese Liste zunächst die Bauteile für alle Produkte der Bestellung an. Wenn wir jedoch auf eines der Produkte im Listenfeld darüber klicken, werden nur noch die Bauteile zu diesem Produkt angezeigt. Außerdem sehen wir dort die gesamte Menge des jeweiligen Bauteils, die Anzahl der gepackten Exemplare und die Restmenge, also die nicht gepackten Bauteile.
• Rechts finden wir Steuerelemente, mit denen wir einen Behältertyp auswählen und diesen zur aktuellen Bestellung hinzufügen können. Wir wählen einen Behälter aus und klicken auf Behälter hinzufügen, um diesen der Bestellung zuzuordnen. Dabei können wir dem Behälter noch einen individuellen Namen zuweisen (siehe Bild 3). Der angelegte Behälter landet schließlich im Listenfeld der Behälter zu dieser Bestellung. Außerdem können wir mit der Schaltfläche Behälter löschen auch den aktuell markierten Behälter löschen.



Bild 3: Anlegen von Behältern

• Unten rechts sehen wir die dem jeweiligen Behälter zugeordneten Bauteile. Die Zuordnung erfolgt über die Pfeil-Schaltflächen. Mit dem einfachen Pfeil nach rechts ordnen wir ein Stück des aktuell markierten Bauteils zum aktiven Behälter zu. Mit dem doppelten Pfeil weisen wir dem Behälter alle verbleibenden Bauteile zu.
• Mit dem einfachen und dem doppelten Pfeil nachh links können wir ein Bauteil oder alle Bauteile aus der Liste Inhalt entfernen.
• Wenn wir Veränderungen am Inhalt eines Behälters vornehmen, wird die dortige Menge angepasst. Außerdem wird auch die Liste Bauteile aktualisiert. Wenn wir beispielsweise ein Bauteil zu den gepackten Bauteilen hinzufügen, wird der Wert in der Spalte Gepackt um 1 erhöht und die Restmenge um 1 vermindert.

Auf diese Weise sieht der Mitarbeiter genau, wie viele Exemplare eines Bauteils noch gepackt werden müssen.

Nach dem Packen eines oder mehrerer Bauteile in einen Behälter braucht der Mitarbeiter nur das entsprechende Bauteil in der Liste Bauteile zu markieren und entweder entsprechend der Menge der gepackten Bauteile mehrfach auf die Schaltfläche mit den einfachen Pfeil nach rechts zu klicken, oder er packt alle Exemplare eines Bauteils in einen Behälter und klickt direkt auf die Schaltfläche mit den zwei Pfeilen.

Auch wenn er bereits ein einzelnes Bauteil von mehreren mit einem Klick auf die Schaltfläche mit dem einfachen Pfeil zu einem Behälter hinzugefügt hat, kann er alle verbleibenden mit einem Klick auf die Schaltfläche mit dem doppelten Pfeil dem aktuell gewählten Behälter zuordnen.

Datenmodell der Anwendung

Bevor wir in die Programmierung des Formulars einsteigen, wollen wir uns kurz das Datenmodell ansehen (siehe Bild 4). Die Tabellen, die mit dem Formular frmBestellungen verwaltet werden, heißen tblKunden, tblBestellungen und tblBestellungProdukte.

Bild 4: Datenmodell der Anwendung

Daneben gibt es noch weitere Stammdaten. Die Tabelle tblBauteile enthält alle Bauteile, die über die Tabelle tblProdukteBauteile dem jeweiligen Produkt zugewiesen werden. Das Feld Menge dieser Tabelle gibt an, wie viele Exemplare eines Bauteils zu einem Produkt gehören.

Genau so, wie wir den Produktnamen bei Auswählen einer Bestellung in die Tabelle tblBestellungProdukte schreiben, um diese unabhängig von den sich gegebenenfalls ändernden Stammdaten zu machen, wollen wir auch die Bauteile zu jeder Bestellposition in einer eigenen Tabelle historisieren.

Dazu verwenden wir die Tabelle tblBestellungProdukteBauteile. Sie ist einerseits mit der Tabelle tblBestellungProdukte verknüpft und andererseits mit der Tabelle tblBauteile. Damit halten wir fest, woher die Bauteile ursprünglich stammen. In den Feldern Bauteilname, Menge und Gewicht speichern wir die Eigenschaften zum Zeitpunkt dieser Bestellung. Die Menge wird berechnet aus dem Produkt des Feldes Menge der Tabelle tblBestellungProdukte und dem gleichnamigen Feld der Tabelle tblProdukteBauteile.

Damit kommen wir zu einem weiteren Teil des Datenmodells, der die Zuordnung der Bauteile einer Bestellung zu den dafür angelegten Behältern speichert. Die Behälter werden in der Tabelle tblBehaelter angelegt. Hier können wir über das Feld BehaeltertypID den Typ des Behälters aus der Tabelle tblBehaeltertypen auswählen. Jeder Behälter wird außerdem noch der jeweiligen Bestellung zugeordnet.

Die Tabelle tblBestellungBauteileBehaelter schließlich speichert, wie viele Exemplare eines Bauteils eines Produkts in einem der Behälter gespeichert sind.

Löschweitergaben im Datenmodell

Wir haben bei den meisten mit referenzieller Integrität definierten Beziehungen auch die Löschweitergabe aktiviert. So werden beispielsweise beim Löschen von Bestellungen auch automatisch die damit verknüpften Produkte, Bauteile et cetera gelöscht. Auch beim Löschen von Behältern sollen die Zuordnungen von Bauteilen gelöscht werden.

Programmierung des Formulars frmBestellungen

Wenn der Benutzer im Formular frmBestellungen ein Produkt im Unterformular auswählt, wird der Name des Produkts in das Feld Produktname der Tabelle tblBestellungProdukte eingetragen.

Damit stellen wir sicher, dass bei späteren Änderungen des Produktnamens der zum Zeitpunkt der Bestellung gültige Produktname in den Bestelldaten beibehalten wird. Das Formular sehen wir in Bild 5 in der Entwurfsansicht.

Bild 5: Entwurfsansicht des Formulars frmBestellungen

Wenn der Benutzer das Produkt ausgewählt hat, soll der Produktname aus der Tabelle tblProdukte in die Tabelle tblBestellungProdukte eingetragen werden. Das erledigen wir in der Vor Aktualisierung-Ereignisprozedur:

Private Sub ProduktID_BeforeUpdate(Cancel As Integer)
     Me.Produktname = DLookup("Produktname", _
         "tblProdukte", "ProduktID = " & Me.ProduktID)
End Sub

Nachdem der Benutzer die Menge für die aktuelle Position eingetragen hat und den Datensatz gespeichert hat, tragen wir die Daten in die Tabelle tblBestellungProdukteBauteile ein.

Das erledigen wir mit der Prozedur, die durch das Ereignis Nach Aktualisierung des Unterformulars ausgelöst wird (siehe Listing 1). Die Prozedur erstellt zwei Recordsets. rstBauteileQuelle enthält eine Abfrage, die alle Bauteile enthält, die zu dem im Unterformular hinzugefügten Produkt gehören. Der zweite enthält die Tabelle tblBestellungProdukteBauteile, also die Tabelle, in die wir die Bauteile für die aktuelle Bestellposition eintragen wollen.

Private Sub Form_AfterUpdate()
     Dim db As DAO.Database
     Dim rstBauteileQuelle As DAO.Recordset
     Dim rstBauteileZiel As DAO.Recordset
     
     Set db = CurrentDb
     Set rstBauteileQuelle = db.OpenRecordset("SELECT tblProdukteBauteile.ProduktID, tblProdukteBauteile.Menge, " _
         & "tblBauteile.BauteilID, tblBauteile.Bauteilname, tblBauteile.Gewicht FROM tblBauteile " _
         & "INNER JOIN tblProdukteBauteile ON tblBauteile.BauteilID = tblProdukteBauteile.BauteilID WHERE ProduktID = " _
         & Me.ProduktID, dbOpenDynaset)
     Set rstBauteileZiel = db.OpenRecordset("SELECT * FROM tblBestellungProdukteBauteile WHERE 1=2", dbOpenDynaset)
     
     Do While Not rstBauteileQuelle.EOF
         With rstBauteileZiel
             .AddNew
             !BestellungProduktID = Me.BestellungProduktID
             !BauteilID = rstBauteileQuelle!BauteilID
             !Bauteilname = rstBauteileQuelle!Bauteilname
             !Menge = rstBauteileQuelle!Menge * Me.Menge
             !Gewicht = rstBauteileQuelle!Gewicht
             .Update
         End With
         rstBauteileQuelle.MoveNext
     Loop
End Sub

Listing 1: Eintragen der Produkt- und Bauteildaten für die aktuelle Bestellung

In der folgenden Do While-Schleife durchlaufen wir alle Datensätze des ersten Recordsets und tragen die benötigten Werte aus den Tabellen tblBauteile und tblProdukteBauteile in die Tabelle tblBestellungProdukteBauteile ein. Dabei werden alle Daten übernommen – mit Ausnahme des Feldes Menge. Hier multiplizieren wir die Menge der Produktposition mit der Menge der Bauteile für dieses Produkt.

Programmierung des Formulars frmVerpackung

Da wir nun in der Tabelle tblBestellungProdukteBauteile alle Informationen über die zu verpackenden Bauteile haben, können wir uns dem Formular zum Verwalten des Packvorgangs zuwenden. Dieses heißt frmVerpackung und sieht in der Entwurfsansicht wie in Bild 6 aus.

Bild 6: Entwurf des Formulars frmVerpackung

Das Formular selbst verwendet die Tabelle tblBestellungen als Datensatzquelle. Es zeigt die beiden Felder BestellungID und Bestelldatum dieser Tabelle an.

Alle übrigen Daten werden überwiegend in Listenfeldern angezeigt. Einzige Ausnahme ist das Kombinationsfeld zur Auswahl des Behältertyps für das Anlegen eines neuen Behälters zur aktuellen Bestellung. Schauen wir uns also diese Listenfelder an.

Listenfeld zur Anzeige der Produkte einer Bestellung

Das erste Listenfeld heißt lstProdukte und soll alle Produkte einer Bestellung inklusive Menge anzeigen. Damit das Listenfeld beim Anzeigen eines Datensatzes immer die zur aktuellen Bestellung gehörenden Produkte anzeigt, weisen wir die Datensatzherkunft im Ereignis Beim Anzeigen des Formulars jeweils neu zu.

Dabei verwenden wir einen SQL-Ausdruck, der die benötigten Daten der Tabelle tblBestellungProdukte liefert und diese nach der aktuell im Formular angezeigten Bestellung filtert. Außerdem erledigen wir dort noch weitere Schritte, indem wir Prozeduren zum Aktualisieren der übrigen Listenfelder und der Schaltflächen aufrufen (siehe Listing 2).

Private Sub Form_Current()
     Me.lstProdukte.RowSource = "SELECT BestellungProduktID, ProduktID AS ID, Produktname, Menge " _
         & "FROM tblBestellungProdukte WHERE BestellungID = " & Me.BestellungID
     Call lstBauteileAktualisieren
     Call lstBehaelterAktualisieren
     Call lstInhaltAktualisieren
     Call SchaltflaechenAktualisieren
End Sub

Listing 2: Füllen der Listenfelder beim Anzeigen eines Datensatzes

Wozu aber wollen wir dieses Listenfeld überhaupt nutzen? Es soll dazu dienen, die Anzeige der Bauteile im Listenfeld lstBauteile nach dem jeweils ausgewählten Produkt zu filtern.

Wenn das Formular geöffnet wird, soll dieses Listenfeld zunächst alle Bauteile anzeigen, die zu den Produkten der Bestellung gehören. Vielleicht sollen die Bauteile zu einem Produkt aber jeweils in eigenen Behältern gepackt werden. Dann kann der Mitarbeiter auf das jeweilige Produkt klicken und erst einmal nur diese Bauteile packen, bevor er mit dem folgenden Produkt fortfährt.

Also haben wir auch zwei Prozeduren, die das Listenfeld lstBauteile füllen – eine trägt alle Bauteile der aktuellen Bestellung ein, die andere ermittelt alle Bauteile zum aktuell markierten Produkt.

Wenn wir normalerweise einen Eintrag in einem Listenfeld anklicken, können wir diesen nicht wieder abwählen – außer, wir wählen einen anderen Eintrag aus. Wir wollen aber auch wieder alle Bauteile der aktuellen Bestellung anzeigen können, obwohl wir vielleicht gerade ein Produkt gewählt haben, um nur dessen Bauteile anzuzeigen. Also müssen wir uns einen kleinen Trick überlegen, um den aktuell markierten Eintrag im Listenfeld lstProdukte wieder abzuwählen.

Dazu wollen wir beim Auswählen eines Produkts prüfen, ob dieses bereits zuvor selektiert war. Dies erfordert eine Variable außerhalb der Prozedur, um den zuletzt selektierten Eintrag zu speichern:

Private varProduktSelektiertVorher As Variant

Für das Ereignis Nach Aktualisierung des Listenfeldes lstProdukte hinterlegen wir nun die folgende Ereignisprozedur:

Private Sub lstProdukte_AfterUpdate()
     If Me.lstProdukte = Nz(varProduktSelektiertVorher, 0) Then
         Call lstBauteileAktualisierenNachBestellungID
         varProduktSelektiertVorher = Null
         Me.lstProdukte = Null
     Else
         Call lstBauteileAktualisierenNachProduktID
         varProduktSelektiertVorher = Me.lstProdukte
     End If
End Sub

Diese prüft, ob der aktuell angeklickte Eintrag dem in varProduktSelektiertVorher gespeicherten Eintrag entspricht. Falls ja, will der Benutzer offensichtlich gerade den gewählten Listeneintrag abwählen. Also stellen wir das Listenfeld auf den Wert Null ein, um die Selektion aufzuheben und leeren auch die Variable varProduktSelektiertVorher. Außerdem rufen wir die Prozedur lstBauteileAktualisierenNachBestellungID auf, um im Listenfeld lstBauteile alle Bauteile anzuzeigen, die zur aktuellen Bestellung gehören.

Wenn der Benutzer ein Produkt ausgewählt hat, das zuvor nicht selektiert war, rufen wir die Prozedur lstBauteileAktualisierenNachProduktID auf, um im Listenfeld lstBauteile alle Bauteile der aktuellen Bestellung anzuzeigen. Außerdem legen wir den Wert von varProduktSelektiertVorher auf den aktuell markierten Eintrag fest.

Listenfeld zur Anzeige der Bauteile einer Bestellung

Das Listenfeld lstBauteile soll, wie zuvor beschrieben, entweder die Bauteile für die aktuelle Bestellung anzeigen oder die Bauteile zu dem im Listenfeld lstProdukte ausgewählten Produkt.

Wir schauen uns zuerst die Auflistung aller Bauteile zur aktuellen Bestellung an, da diese auch direkt beim Öffnen des Formulars erscheinen soll. Dazu verwenden wir die Abfrage qryProdukteBauteileNachBestellung als Datensatzherkunft.

Diese Abfrage ist relativ kompliziert und sieht in der Entwurfsansicht wie in Bild 7 aus. Sie enthält Daten aus den Tabellen tblBestellungProdukte, tblBestellungProdukteBauteile und tblBestellungBauteileBehaelter. Wir haben diese Tabellen mit den Alias-Bezeichnungen bp, bpbt und bbb versehen.

Bild 7: Entwurf der Abfrage qryProdukteBauteileNachBestellung

Warum so kompliziert? Weil diese Abfrage gleich mehrere zusätzliche Informationen neben den eigentlichen Bauteilen berücksichtigen soll:

• Wir haben im Formular ein Kontrollkästchen namens chkVollstaendigGepackteProdukteAusblenden eingebaut. Mit diesem wollen wir alle Einträge aus der Liste lstBauteile ausblenden können, die bereits vollständig einem Behälter zugeordnet wurden. So sieht der Mitarbeiter nur die Bauteile, die noch gepackt werden müssen. Den Wert dieses Feldes wollen wir in der Abfrage berücksichtigen.
• Außerdem wollen wir neben dem Feld Menge, das die Gesamtmenge dieses Bauteils anzeigt, noch zwei weitere berechnete Felder unterbringen. Das erste heißt Gepackt und soll die Anzahl der bereits gepackten Bauteile ausgeben. Das zweite heißt Restmenge und liefert die noch zu packenden Bauteile.

Die Abfrage ist gruppiert nach den Feldern BestellungBauteileID, Produktname, Bauteilname, BauteilID und BestellungID. Warum eine Gruppierung? Weil zu jedem Bauteil mehrere Zuordnungen zu den verschiedenen Behältern vorkommen können und wir die gepackten Mengen aus den verschiedenen Behältern aufsummieren wollen. Diese Summen landen in den Feldern Gepackt und Restmenge.

Außerdem wollen wir vollständige gepackte Bauteile ausblenden können, wenn wir das Kombinationsfeld chkVollstaendigGepackteProdukteAusblenden aktiviert haben. Dazu haben wir eine entsprechende Bedingung im Having-Teil der Abfrage untergebracht, die wir in Bild 8 nochmal in der SQL-Ansicht dargestellt haben. Wir nutzen die beiden Parameter prmBestellungID zum Filtern nach der anzuzeigenden Bestellung und prmAusblenden zum Filtern nach den auszublendenden Bauteilen.

Bild 8: SQL-Ansicht der Abfrage qryProdukteBauteileNachBestellung

Warum nutzen wir Parameter? Weil wir sonst die vollständige SQL-Abfrage im VBA-Code unterbringen und dort mit den Werten für die Parameter füllen müssten, was es noch unübersichtlicher machen würde.

Also verwenden wir diese Abfrage als Grundlage für das Listenfeld lstBauteile, indem wir ein Recordset auf Basis der Abfrage erstellen und dieses mit den erforderlichen Parameterwerten ausstatten.

Dazu referenzieren wir in der Prozedur lstBauteileAktualisierenNachBestellungID (siehe Listing 3) das aktuelle Database-Objekt über die Funktion CurrentDb und speichern die Referenz in der Variablen db.

Private Sub lstBauteileAktualisierenNachBestellungID()
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim rst As DAO.Recordset
     Dim prmBestellungID As DAO.Parameter
     Dim prmAusblenden As DAO.Parameter
     
     Set db = CurrentDb
     Set qdf = db.QueryDefs("qryProdukteBauteileNachBestellung")
     Set prmBestellungID = qdf.Parameters("prmBestellungID")
     prmBestellungID = Me.BestellungID
     Set prmAusblenden = qdf.Parameters("prmAusblenden")
     prmAusblenden = Me.chkVollstaendigGepackteProdukteAusblenden
     Set rst = qdf.OpenRecordset
     Set Me.lstBauteile.Recordset = rst
End Sub

Listing 3: Füllen des Listenfelds lstBauteile mit den Bauteilen einer Bestellung

Dann holen wir uns über die QueryDefs-Auflistung einen Verweis auf die Abfrage qryProdukteBauteileNachBestellung und tragen diesen in die Variable qdf ein.

Mit den beiden Parameter-Variablen prmBestellungID und prmAusblenden definieren wir die Parameter und weisen ihnen die aktuelle BestellungID und den Wert des Kontrollkästchens chkVollstaendigGepackteProdukteAusblenden zu.

Nun können wir mit OpenRecordset ein Recordset auf Basis von qdf öffnen und dieses der Eigenschaft Recordset des Listenfelds lstBauteile zuweisen.

Listenfeld zur Anzeige der Bauteile einer Bestellung nur für ein Produkt

Wenn der Benutzer auf eines der Produkte im Listenfeld lstProdukte geklickt hat, sollen nur die Bauteile für dieses Produkt in lstBauteile erscheinen.

Dazu nutzen wir eine ähnliche Vorgehensweise wie bei der Anzeige aller Bauteile der kompletten Bestellung.

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