Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 4/2005.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Sie erweitern die Artikelverwaltung um Funktionen zur Eingabe von Bestellungen und zum Drucken von Bestellscheinen.

Techniken

Access, Abfragen, 1:1-Beziehungen, Formulare, Unterformulare, Berichte, VBA

Voraussetzungen

Access 2000 oder höher

Bestellverwaltung

André Minhorst, Duisburg

In den vorherigen beiden Ausgaben haben Sie die Artikelverwaltung kennen gelernt, mit der Sie Artikel und Inventurdaten verwalten können. Damit haben Sie bereits die Voraussetzungen für die Musterlösung dieses Beitrags geschaffen - für die Bestellverwaltung. Damit können Sie Bestellungen eingeben und Bestellscheine drucken.

Funktionen der
Bestellverwaltung

Die Bestellverwaltung schlägt die Brücke zwischen der Artikelverwaltung und den im Beitrag Kunden und Kontakte verwalten (Ausgabe 4/2005, Shortlink 293) vorgestellten Funktionen. Die Bestellverwaltung bietet folgende Erweiterungen:

  • Formular zur Eingabe von Bestellungen
  • Bericht zur Ausgabe von Bestellscheinen
  • Das Formular bietet die Möglichkeit, Bestellungen einzugeben und zu bearbeiten. Dazu ist das Auswählen eines Kunden und dessen Liefer- und Rechnungsanschrift sowie die Eingabe beliebig vieler Positionen möglich.

    Zu jeder Position lässt sich ein individueller, vom ursprünglichen Preis abweichender Preis festlegen.

    Der Bericht gibt einen Bestellschein mit Bestell- und Lieferdatum, den Anschriften für Rechnung und Lieferung sowie die einzelnen Positionen aus.

    Datenmodell

    Alle Tabellen der Bestellverwaltung sind bereits in der Artikelverwaltung enthalten - wenn Sie die Bestellverwaltung "nachbasteln" möchten, brauchen Sie zumindest keine neuen Tabellen hinzuzufügen.

    Abb. 1 zeigt den Ausschnitt des Datenmodells, der für die Bestellverwaltung interessant ist.

    Betrachten Sie das abgebildete Datenmodell im Uhrzeigersinn: Die Tabelle tblArtikel enthält Informationen über die Artikel, wobei im Folgenden vor allem die Felder Bezeichnung, EinheitID, Mehrwertsteuersatz und Verkaufspreis eine Rolle spielen werden. Hinter dem Feld EinheitID verbirgt sich noch eine Lookup-Tabelle, die aber hier aus Platzgründen nicht abgebildet ist.

    Dann folgen zwei Tabellen namens tblBestandsaenderungen und tblPositionen. Die beiden Tabellen sind per 1:1-Beziehung miteinander verknüpft und enthalten die Informationen, die in der Nordwind-Datenbank durch die Tabelle Bestelldetails abgebildet werden. Hier sind die Informationen auf zwei Tabellen verteilt, weil ein Bestelldetail auch immer eine Bestandsänderung verursacht und diese in der Datenbank in einer eigenen Tabelle gespeichert wird - etwa um leicht Inventuren durchführen zu können.

    Abb. 1: Datenmodell der Bestellverwaltung

    Prinzipiell können Sie diese beiden Tabellen aber wie eine einzige behandeln - was in der Anwendung durch den Einsatz einer entsprechenden Abfrage auch geschieht.

    Am anderen Ende dieser beiden Tabellen hängt die Tabelle tblBestellungen - sie enthält die Informationen der Bestellung selbst, also den Kunden, das Bestell- und Lieferdatum und die Liefer- und Rechnungsadresse des Kunden.

    Wenn man den Zusammenhang zwischen diesen vier Tabellen (oder drei, wenn man die mittleren beiden als eine betrachtet) verstanden hat, steht der Programmierung einer Bestellverwaltung nichts mehr im Wege: Die mittleren beiden Tabellen sind nicht mehr und nicht weniger als die Verknüpfungstabellen einer m:n-Beziehung zwischen den Tabellen tblArtikel und tblBestellungen. Damit kann man jeder Bestellung jeden Artikel zuweisen und legt dabei in den Tabellen tblBestandsaenderungen und tblPositionen fest, welche Anzahl des Artikels die Bestellung enthält und wie der Preis und die Mehrwertsteuer für diese Bestellung lauten.

    Der doppelte Preis

    Warum nun soll der Preis in den Bestelldetails nochmals auftauchen, obwohl dieser doch schon in den Artikeldaten enthalten ist? Ganz einfach: Weil das der einzige Weg ist, um einen Artikel auch einmal zu einem anderen Preis zu verkaufen, und vor allem, um sicherzustellen, dass bei einer Preisänderung eines Artikels die Werte aller bereits aufgenommenen Bestellungen automatisch geändert werden.

    Das Gleiche gilt für die Mehrwertsteuer: Wenn sich diese einmal ändert, soll sich das natürlich nicht rückwirkend auf alle bereits vorhandenen Bestellungen auswirken. Deshalb werden diese Daten separat in den Bestelldetails gesichert.

    Bestellungen und Kunden

    Jede Bestellung ist genau einem Kunden zugeordnet. Jeder Kunde kann aber eine oder mehrerer Liefer- und Rechnungsadressen besitzen.

    Daher werden in der Tabelle tblBestellungen nicht nur die KundenID, sondern auch noch die ID der Bestelladresse und der Lieferadresse für diese Bestellung eingetragen.

    Auf diese Weise lässt sich später genau nachvollziehen, wohin Bestellung und Rechnung gegangen sind.

    Und wenn der Kunde einmal umzieht, legt man einfach je einen neuen Datensatz in den Tabellen tblLieferadresse und tblRechnungsadresse an - dann bleiben trotz weiterer Bestellungen die bestehenden Bestellungen unberührt.

    Warum die Tabellen mit den Adressdaten in der Art aufgeteilt sind, wie es das Datenmodell darstellt, erfahren Sie im Beitrag Kunden und Kontakte verwalten (Shortlink 293).

    Das Formular zum Verwalten der Bestellungen

    Für den gesamten unteren Zweig des Datenmodells aus Abb. 1 ist die Erweiterung der Artikelverwaltung aus dem oben genannten Beitrag verantwortlich.

    Die Pflege der Artikel wurde bereits in den beiden Beiträgen Artikelverwaltung mit Inventurfunktion, Teil 1 (Heft 2/2005, Shortlink 273) und Teil 2 (Heft 3/2005, Shortlink 281) beschrieben.

    Bleiben noch die Tabellen tblBestellungen, tblPositionen und tblBestandsaenderungen.

    Die Pflege dieser Daten ist identisch mit dem Anlegen von Bestellungen und kann in einem einzigen Formular samt Unterformular abgewickelt werden.

    Hauptformular

    Das Hauptformular hat als Datenherkunft die Tabelle tblBestellungen. Damit der obere Teil des Formulars so wie in Abb. 2 aussieht, ziehen Sie einfach die fünf dargestellten Felder aus der Feldliste in die Entwurfsansicht des Formulars.

    Abb. 2: Das Bestellformular mit Unterformular in der Entwurfsansicht

    Die drei Kombinationsfelder erfordern noch ein wenig Nacharbeit. Das erste dient der Auswahl des Kunden und soll den Kunden wie in Abb. 3 anzeigen. Dazu stellen Sie die Datensatzherkunft des Kombinationsfeldes wie folgt ein:

    SELECT tblKunden.KundeID, [Nachname] & IIf(Not IsNull([Nachname]) And Not IsNull([Vorname]),", ","") 
    & [Vorname] AS Kunde FROM tblKunden;

    Mit diesem Ausdruck sorgen Sie dafür, dass das Komma zwischen Nach- und Vorname nur angezeigt wird, wenn auch beide Felder gefüllt sind.

    Abb. 3: Auswahl von Kunden

    Liefer- und Rechnungsadressen anzeigen

    Die übrigen beiden Kombinationsfelder zur Anzeige von Lieferadressen und Rechnungsadressen sollen bei der Auswahl möglichst viele Daten anzeigen, damit man die richtige Adresse auswählen kann.

    Das aufgeklappte Kombinationsfeld soll daher wie in Abb. 4 aussehen. Nachname, Vorname und Firma sind dabei platzsparend zusammengefasst und werden auch in eingeklapptem Zustand angezeigt; die übrigen Daten sind in weiteren Feldern enthalten.

    Abb. 4: Auswahl einer Rechnungsadresse

    Die Datensatzherkunft des Kombinationsfeldes basiert in diesem Fall auf einer Abfrage, die beim Öffnen und Aktualisieren des Formulars zugewiesen wird. Die Abfrage fasst die beiden per 1:1-Beziehung verknüpften Tabellen tblAdressen und tblRechnungsadressen zusammen und erzeugt außerdem den in Abb. 4 erkennbaren Ausdruck <Nachname>, <Vorname> (<Firma>). Die dazu notwendige Funktion ist in
    Abb. 5 nicht zu erkennen und daher nachfolgend separat aufgeführt:

    Abb. 5: Datensatzherkunft des Kombinationsfeldes cboRechnungsadressen

    Lieferadresse: [Nachname] & Wenn(Nicht IstNull([Nachname]) Und Nicht IstNull([Vorname]);", ";"") 
    & [Vorname] & Wenn(Nicht IstNull([Firma]);" (";"") & [Firma] & Wenn(Nicht IstNull([Firma]);")";"")

    Lieferadressen

    Das Kombinationsfeld zur Auswahl der Lieferadressen ist genauso aufgebaut wie das zur Auswahl der Rechnungsadressen und soll daher nicht gesondert erläutert werden.

    Abhängigkeit der Kombinationsfelder

    Die beiden Kombinationsfelder zur Anzeige von Liefer- und Rechnungsadresse sind von der Auswahl des Kunden im oberen Kombinationsfeld des Formulars abhängig.

    Sie sollen jeweils alle Liefer- und Rechnungsadressen zu dem oben ausgewählten Kunden zur Auswahl anbieten.

    Das bedeutet, dass mit jeder neuen Auswahl im Kombinationsfeld KundeID auch die Datensatzherkunft der beiden Kombinationsfelder LieferadresseID und RechnungsadresseID aktualisiert werden muss.

    Um dies zu realisieren, gibt es zwei Möglichkeiten:

  • Man baut in die abhängigen Kombinationsfelder ein Kriterium ein, das den Wert des oberen Kombinationsfeldes berücksichtigt.
  • Man weist den abhängigen Kombinationsfeldern nach dem Aktualisieren des oberen Kombinationsfeldes per VBA die aktualisierte Datensatzherkunft zu.
  • Die zweite Variante hat den Vorteil, dass sie eine Abhängigkeit weniger beinhaltet: Das Formular ist von der Datensatzherkunft abhängig, aber nicht umgekehrt. Die erste Abhängigkeit enthält eine zusätzliche Abhängigkeit zwischen der Abfrage und dem Formular - nämlich das Kriterium der Abfrage.

    Um mit der zweiten Lösung zum Ziel zu kommen, benötigen Sie ein wenig VBA-Code (s. Quellcode 1).

    Die Ereignisprozedur Nach Aktualisierung des Kombinationsfeldes KundeID ruft die Prozedur AdressenAktualisieren auf.

    Diese Prozedur weist den Datensatzherkünften der Kombinationsfelder LieferadresseID und RechnungsadresseID die jeweils mit der aktuellen Kunden-ID als Kriterium versehene Abfrage zu und aktualisiert die Kombinationsfelder.

    Diese paar Zeilen Code befinden sich in einer eigenen Prozedur, weil die Funktion noch von einer anderen Stelle aus aufgerufen werden soll.

    Der zweite Anlass zum Aktualisieren der Datensatzherkunft dieser beiden Kombinationsfelder ist die Anzeige einer neuen Bestellung, die unter Umständen für einen anderen Kunden bestimmt sein kann.

    Quellcode 1: Aktualisierung der Kombinationsfelder zur Auswahl von Liefer- und Rechnungsadressen

    Private Sub KundeID_AfterUpdate()

        AdressenAktualisieren

    End Sub

    Private Sub AdressenAktualisieren()

        Me.LieferadresseID.RowSource = "SELECT * FROM qryLieferadressen WHERE KundeID = " & Me.KundeID

        Me.LieferadresseID.Requery

        Me.RechnungsadresseID.RowSource = "SELECT * FROM qryRechnungsadressen WHERE KundeID = " _
            & Me.KundeID

        Me.RechnungsadresseID.Requery

    End Sub

    Beim Wechseln des Datensatzes wird immer das Ereignis Beim Anzeigen ausgelöst. Quellcode 2 zeigt, was dort passiert: Zunächst wertet die Prozedur Form_Current das Öffnungsargument des Formulars aus. Beim Anlegen einer neuen Bestellung kann man direkt den Kunden übergeben, um dem Benutzer unnötige Arbeit zu ersparen.

    Der folgende Aufruf würde das Formular direkt mit dem Kunden Nummer 10 öffnen und einen neuen, leeren Datensatz anzeigen:

    DoCmd.OpenForm "frmBestellungen", _
        DataMode:=acFormAdd, OpenArgs:=2860

    Damit die beiden Kombinationsfelder direkt die richtigen Rechnungs- und Lieferadressen zur Auswahl anbieten, ruft auch diese Routine die Prozedur AdressenAktualisieren auf.

    Schließlich kontrolliert eine If...Then-Verzweigung, ob das Formular einen neuen und noch nicht geänderten Datensatz enthält. Falls der Datensatz neu ist, wird das Unterformular gesperrt.

    Es wird erst freigegeben, wenn im Hauptformular ein neuer Datensatz angelegt wurde - ansonsten könnte man im Unterformular Datensätze anlegen, ohne dass es einen entsprechenden Datensatz im Hauptformular gibt - was dafür sorgen würde, dass dieser Datensatz zwar in den Tabellen tblBestandsaenderungen und tblPositionen auftaucht, aber mangels korrespondierendem Datensatz in der Tabelle tblBestellungen nie wieder im Formular frmBestellungen sichtbar würde.

    Wenn ein neuer Datensatz schließlich "beschmutzt" wird, also die Eigenschaft Dirty den Wert True bekommt, löst dies auch das Ereignis Bei Änderung aus. Quellcode 3 enthält die dadurch gestartete Prozedur, die für die Freigabe des Unterformulars sorgt.

    Quellcode 2: Anzeigen eines Datensatzes

    Private Sub Form_Current()

        If Not Nz(Me.OpenArgs, "") = "" Then

            Me.KundeID = Me.OpenArgs

        End If

        AdressenAktualisieren

        If Me.NewRecord And Not Me.Dirty Then

            Me.frmPositionen.Enabled = False

            Me.frmPositionen.Locked = True

        Else

            Me.frmPositionen.Enabled = True

            Me.frmPositionen.Locked = False

        End If

    End Sub

    Quellcode 3: Aktivieren des Unterformulars bei geändertem Datensatz

    Private Sub Form_Dirty(Cancel As Integer)

        If Me.NewRecord Then

            Me.frmPositionen.Enabled = True

            Me.frmPositionen.Locked = False

        End If

    End Sub

    Das Unterformular
    frmPositionen

    Neben den Bestelldaten gehören zu einer Bestellung natürlich auch noch die Bestellpositionen. Diese werden in einem Unterformular verwaltet. Als Datenherkunft für das Unterformular dient die Abfrage qryWarenausgang. Die Abfrage fasst die beiden per 1:1-Beziehung verknüpften Tabellen tblBestandsaenderungen und tblPositionen zusammen.

    Abb. 6: Datenherkunft des Unterformulars frmPositionen

    Quellcode 4: Vorbelegen der Mehrwertsteuer und des Einkaufspreises einer Bestellposition

    Private Sub ArtikelID_AfterUpdate()

        Me.Mehrwertsteuer = DLookup("Mehrwertsteuersatz", _
            "tblArtikel", "ArtikelID = " & Me.ArtikelID)

        Me.Verkaufspreis = DLookup("Verkaufspreis", _
            "tblArtikel", "ArtikelID = " & Me.ArtikelID)

        Me!Datum = Me.Parent.Lieferdatum

    End Sub

    Das Formular enthält die folgenden Felder der Abfrage: ArtikelID, Anzahl, Mehrwert, Verkaufspreis, BestellungID und Vorzeichen. Die letzten beiden sind dabei nicht sichtbar, sie dienen lediglich dem Herstellen der Verknüpfung zum Hauptformular beziehungsweise zum Einstellen des Feldes Vorzeichen.

    Der Grund dafür ist, dass die Tabelle tblBestandsaenderungen sowohl Wareneingänge als auch Warenausgänge (also bestellte Artikel) enthält und die beiden betragsmäßig irgendwie voneinander getrennt werden müssen.

    Abb. 7: Das Unterformular und dessen Eigenschaften

    Und damit nicht in jeder Bestellposition eine auf den ersten Blick nicht nachvollziehbare negative Anzahl an Artikeln erscheint, speichert man das Vorzeichen in einem separaten Feld und holt es nur für die Ermittlung des Lagerbestandes hervor.

    Die Anordnung der sichtbaren Steuerelemente geht aus
    Abb. 7 hervor; die nicht sichtbaren Steuerelemente sind hinter den übrigen Steuerelementen verborgen.

    Mehrwertsteuer und
    Verkaufspreis einstellen

    Wie bereits weiter oben erwähnt, besitzt jede Position ein eigenes Feld für Mehrwertsteuer und Verkaufspreis. Diese Felder werden durch die Prozedur aus Quellcode 4 vorbelegt.

    Die Daten für die jeweiligen Artikel ermittelt die Prozedur per DLookup-Funktion aus der Artikel-Tabelle.

    Bestellungen und Positionen zusammenführen

    Zu guter Letzt müssen Sie nur noch das Formular frmPositionen in das Formular frmBestellungen einbauen und dafür Sorge tragen, dass die beiden Formulare ordnungsgemäß miteinander verknüpft sind.

    Wenn die Eigenschaften des Unterformularsteuerelements wie in Abb. 8 aussehen, haben Sie alles richtig gemacht und Sie können Bestellungen wie eingangs beschrieben anlegen.

    Ausgabe von Bestellscheinen

    Die Bestellungen wollen natürlich auch zusammengestellt und versendet werden - ganz abgesehen von der Rechnungsstellung. Letzteres ist freilich ein anderes Thema und wird in einem zukünftigen Beitrag behandelt.

    Abb. 8: Verknüpfung zwischen Haupt- und Unterformular

    Die Lösung dieses Beitrags soll zunächst einmal einen Bestellschein als Bericht ausgeben, mit dem die Bestellung zusammengestellt werden kann.

    Abb. 9: Datenmodell des Berichts rptBestellscheine

    Der Bericht soll Daten aus einer ganzen Reihe von Tabellen enthalten. Dementsprechend sieht das Datenmodell aus Abb. 9 auch relativ wüst aus.

    Da die benötigten Felder bei weitem nicht auf eine Bildschirmseite passen, finden Sie die entscheidenden Felder der beteiligten Tabellen in der Abbildung rot eingerahmt vor. Beachtung ist der Definition der Abfrage bezüglich der Liefer- und Rechnungsadressen zu schenken: Beide beziehen die zu druckenden Daten aus der Tabelle tblAdressen, aber welcher Datensatz daraus verwendet wird, entscheiden die Felder LieferdresseID und RechnungsadresseID der Tabelle tblBestellungen. Eine kleine Faustregel in Access heißt: Wenn die Werte derselben Tabelle in mehreren Zusammenhängen in der gleichen Abfrage verwendet werden, dann fügen Sie für jeden Fall eine neue Kopie der Tabelle in den Entwurf ein.

    Dementsprechend gibt es in der Abfrage zweimal die Tabelle tblAdressen - einmal mit dem Originalnamen und einmal als tblAdressen_1. Weitere Tabellen würden übrigens fortlaufend durchnummeriert. Desgleichen hängt an diesen beiden Tabellen auch je eine eigene Kopie der Tabelle tblAnreden.

    Erstellen des Berichts

    Der Bericht rptBestellschein verwendet die oben beschriebene Abfrage als Datenherkunft. Im Gegensatz zum Formular frmBestellungen ist hier kein Unterbericht notwendig, um die Positionen zu einer Bestellung aufzulisten. Berichte bringen die segensreiche Erfindung der Gruppierung mit sich. Dadurch kann man - ohne sich Gedanken um redundant angezeigte Daten zu machen - einfach alle Daten in eine Abfrage werfen. Das Abfrageergebnis erhält dann zwar für eine Bestellung mit zehn Position zehnmal die gleichen Daten für Felder wie BestellungID, Bestelldatum, Lieferdatum und auch für die Rechnungs- und die Lieferanschrift, aber durch geschickte Gruppierung zeigt man die redundanten Daten einfach nur einmal im jeweiligen Gruppierungskopf oder -fuß an. Genau so gehen Sie bei der Erstellung des Berichts auch vor.

    Nachdem Sie die Datenherkunft eingestellt haben, fügen Sie dem Bericht zunächst einmal die gewünschte Gruppierung hinzu. Diese soll die Datensätze nach dem Wert des Feldes BestellungID gruppieren.

    Aktivieren Sie einfach den Dialog Sortieren und Gruppieren und fügen Sie als Gruppierung das Feld BestellungID hinzu. Damit die Gruppierung im Bericht einen Bereich erhält, der die allgemeinen Informationen wie die Bestelldaten und die Anschriften aufnehmen kann, stellen Sie die Eigenschaft Gruppenkopf der Gruppierung auf den Wert Ja ein (s. Abb. 10).

    Nun müssen Sie nur noch die Steuerelemente in die richtigen Bereiche des Berichts einfügen und diese verschönern. Anschließend sieht die Entwurfsansicht des Berichts beispielsweise so wie in Abb. 11 aus. Die Felder Anrede, Vorname und Nachname werden für die Bestell- und die Lieferanschrift jeweils zusammengefasst. Der Ausdruck des Textfeldes txtNameLieferanschrift lautet folgendermaßen:

    =Wenn([tblAnreden.Anrede]="Herr";"Herrn ";"Frau ") & [tblAdressen.Vorname] & " " & [tblAdressen.Nachname]

    Abb. 10: Anlegen einer Gruppierung nach der Bestellung

    Der Ausdruck prüft erst, welche Anrede für den Namen angegeben ist, und wandelt dann gegebenenfalls "Herr" in "Herrn" um. Anschließend setzt er das Ergebnis mit dem Vor- und dem Nachnamen zusammen.

    Abb. 11: Entwurfsansicht des Bestellscheins

    Zusammenfassung und Ausblick

    Die Bestellverwaltung bildet das Grundgerüst für eigene Anwendungen, die sich leicht um weitere Funktionen ergänzen lassen.

    In einem zukünftigen Beitrag wird Access im Unternehmen diese Lösung noch einmal aufnehmen und um eine Faktura erweitern - damit ist die Bestellverwaltung dann komplett. Wenn Sie weitere Vorschläge für Erweiterungen oder Verbesserungen haben, schicken Sie diese einfach per E-Mail an info@access-im-unternehmen.de.

    Download

    Download

    Die .zip-Datei enthält folgende Dateien:

    Bestellverwaltung00.mdb

    Beispieldateien downloaden

    © 2003-2018 André Minhorst Alle Rechte vorbehalten.