Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Eine besondere Anforderung an das Datenmodell und die Gestaltung der Formulare zur Bearbeitung der Daten stellt sich immer dann, wenn sich der Inhalt eines oder mehrerer Felder ändert, die bereits mit anderen Tabellen verknüpft wurden. Ein Beispiel sind Bestellungen, und hier speziell die Preise von Artikeln: Diese werden meist mit der Zeit teurer, oder der Mehrwertsteuersatz ändert sich und hin und wieder gibt es Währungsreformen. Welche Probleme dies mit sich bringt und wie Sie diese lösen, zeigt dieser Beitrag.
Bestellungen verwalten – naive Variante
Eine Artikeltabelle enthält mindestens Informationen wie den Artikelnamen und den Preis des Artikels (s. Bild 2). Zur Vereinfachung verwenden wir zunächst keine separate Kundentabelle, deren Einträge wir über ein Fremdschlüsselfeld in der Tabelle tblBestellungen auswählen, sondern einfach nur ein Feld namens Kunde – dieses soll lediglich den Namen des Kunden aufnehmen.
Bild 1: Die denkbar einfachste Variante einer Artikeltabelle
Bild 2: Eine weitere Tabelle speichert die Basisdaten einer Bestellung.
Wie kombinieren wir nun eine Bestellung mit den gewünschten Artikeln Durch eine weitere Tabelle namens tblBestellpositionen. Diese enthält zwei Fremdschlüsselfelder: Das erste heißt BestellungID und verknüpft die Tabelle mit dem Feld BestellungID der Tabelle tblBestellungen, das zweite heißt ArtikelID und sorgt dafür, dass Sie einer Bestellung Einträge aus der Tabelle tblArtikel zuweisen können. Das Feld ArtikelID der Tabelle tblBestellpositionen legen Sie als Nachschlagefeld aus, welches die Felder ArtikelID und Artikel der Tabelle tblArtikel enthält – dies spart später bei der Formulargestaltung einige Arbeit. Ein drittes Feld namens Anzahl enthält die Anzahl des jeweiligen Artikels für diese Bestellung. Den Entwurf dieser Tabelle zeigt Bild 4 aus.
Bild 3: Die Tabelle tblBestellpositionen kombiniert Bestellungen und Artikel.
Bild 4: Datenmodell der naiven Variante der Bestellverwaltung
Sie finden diese Version der Bestellverwaltung übrigens in der Beispieldatei PreiseVerwalten_1.mdb. Da wir später andere Versionen mit geänderten Datenmodellen vorstellen, haben wir für jede Version eine eigene Beispieldatenbank angelegt.
Das Verwalten von Bestellungen geschieht in Haupt- und Unterformular. Das Hauptformular verwendet die Tabelle tblBestellungen als Datenherkunft und zeigt all ihre Felder an. Das Unterformular verwendet die Abfrage aus AbbBild 5. 5 als Datenherkunft. Diese Abfrage enthält alles, was wir brauchen: Das Feld ArtikelID der Tabelle tblBestellpositionen, um den gewünschten Artikel auszuwählen, das Feld Anzahl sowie das Feld Preis der Tabelle tblArtikel – diese soll nach dem Auswählen des Artikels den Preis beisteuern. Das erste Feld, BestellungID, wird im Unterformular nicht angezeigt, aber es soll die Beziehung zum Datensatz im Hauptformular herstellen – mehr dazu gleich.
Bild 5: Die neue Datenherkunft für das Unterformular
Wenn Sie das Unterformular sfmBestellungen erstellt und mit den drei Feldern ArtikelID, Anzahl und Preis versehen haben, können Sie es zum Hauptformular hinzufügen. Dazu öffnen Sie das Formular frmBestellungen in der Entwurfsansicht und ziehen das Unterformular sfmBestellungen per Drag and Drop aus dem Datenbankfenster (Access 2003 und älter) beziehungsweise dem Navigationsbereich (Access 2007 und jünger) in den Detailbereich. Das Ergebnis soll etwa wie in Bild 6 aussehen.
Bild 6: Entwurfsansicht des Formulars frmBestellungen mit dem Unterformular sfmBestellungen
Wie sorgen Sie dafür, dass das Unterformular nur solche Artikel anzeigt, die Sie für eine bestimmte Bestellung angelegt haben Dies erledigt Access unter den gegebenen Umständen automatisch: Es erkennt, dass das Unterformular eine Datenherkunft enthält, deren Fremdschlüsselfeld mit dem Primärschlüsselfeld der Datenherkunft des Hauptformulars verknüpft ist, und stellt die beiden Eigenschaften Verknüpfen von und Verknüpfen nach des Unterformular-Steuerelements auf die Namen der an der Verknüpfung beteiligten Felder ein.
Wenn Sie das Formular nun in der Formularansicht öffnen und im Hauptformular eine Bestellung angelegt haben, können Sie über das Kombinationsfeld im Unterformular ganz einfach Artikel zur Bestellung hinzufügen (s. Bild 8).
Bild 7: Eingabe der Artikel einer Bestellung
Bild 8: Die Einstellung des Standardwerts sorgt dafür, dass das Feld beim Anlegen eines neuen Datensatzes den angegebenen Wert erhält.
Meldung bei doppelten Artikeln vermeiden
Da wir weiter oben einen zusammengesetzten, eindeutigen Index für die beiden Felder BestellungID und ArtikelID der Tabelle tblBestellpositionen erstellt haben, zeigt Access eine Meldung an, wenn Sie versuchen, einen Artikel der gleichen Bestellung zweimal zuzuweisen.
Diese Meldung ist für den Benutzer jedoch unter Umständen nicht besonders aussagekräftig, sodass wir diese durch eine eigene Meldung ersetzen. Auf diese Meldung können Sie im Ereignis Bei Fehler des Unterformulars reagieren. Wählen Sie für diese Eigenschaft des Unterformulars den Wert [Ereignisprozedur] aus und klicken Sie auf die Schaltfläche mit den drei Punkten. Der VBA-Editor öffnet sich und zeigt gleich eine leere Prozedur an, die Access beim Auftreten eines Fehlers im Formular ausführt.
Ergänzen Sie diese Prozedur wie in Listing 1. Die erste Zeile prüft die Fehlernummer, die in unserem Fall 3022 lautet. Die Fehlernummer finden Sie heraus, indem Sie in der gleichen Prozedur ganz oben die folgende Zeile einfügen:
MsgBox "Fehlernummer: " & DataErr
Listing 1: Abfangen des Fehlers, der beim mehrfachen Anlegen eines Artikels in derselben Bestellung ausgelöst wird
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox "Sie haben diesen Artikel bereits zur Bestellung hinzugefügt." & vbCrLf & vbCrLf _
& "ändern Sie die Anzahl, um den Artikel mehrfach zu bestellen.", _
vbOKOnly + vbExclamation, "Doppelter Artikel"
Me.Undo
Response = acDataErrContinue
End If
End Sub
Die erste Zeile innerhalb der If…Then-Bedingung gibt eine Meldung aus, die den Benutzer darauf hinweist, dass er einen bereits vorhandenen Artikel zur Bestellung hinzugefügt hat (s. Bild 9). Die zweite macht die zuletzt am aktuell bearbeiteten Datensatz durchgeführten änderungen rückgängig (beim Neuanlegen einer Bestellposition wird diese wieder entfernt, und wenn der Benutzer den Artikel einer bereits bestehenden Bestellposition geändert hat, wird der zuvor ausgewählte Artikel wiederhergestellt). Fertig: Sie können nun einfache Bestellungen eingeben.
Bild 9: Diese Meldung erscheint, wenn ein Artikel mehrfach zu einer Bestellung hinzugefügt wird.
Probleme beim ändern des Preises
Mit der aktuellen Lösung geben Sie die Daten ein, die beispielsweise zum Erstellen von Berichten zur Ausgabe von Bestellscheinen oder Rechnungen nötig sind (mit Ausnahme der Kundendaten: Wir haben hier nur den Kundennamen in die Tabelle tblBestellungen eingetragen – normalerweise würden wir hier auf eine Tabelle namens tblKunden mit den detaillierten Kundendaten verweisen).
Diese Lösung funktioniert so lange zuverlässig, bis sich der Preis eines Artikels ändert, der bereits in einer Bestellung erfasst wurde. Nehmen wir an, Sie hätten eine Bestellung aufgenommen, bei der ein Artikel 10 Euro kostet, und dem Kunden eine anschließend eine entsprechende Rechnung geschickt. Kurze Zeit später erhöht sich der Preis des Artikels auf 11 Euro. Der Kunde meldet sich, weil er die Rechnung verlegt hat und eine Kopie der Rechnung benötigt. Diese lässt sich aus der Anwendung heraus nun zwar noch erstellen, allerdings wird der neue Preis von 11 Euro für den Artikel ausgewiesen.
Behandlung von Preisänderungen
Grundsätzlich gibt es mehrere Möglichkeiten, das Problem zu umgehen. Die erste und einfachste ist, dass die Rechnung als PDF-Dokument ausgegeben und archiviert wird. Damit wäre dem Auftragnehmer und auch dem Kunden gedient, denn beide können nun auf eine korrekte Version der Rechnung zugreifen.
Sobald der Benutzer der Bestellverwaltung jedoch einmal eine Statistik etwa über den Umsatz erstellen möchte und dabei die Tabellen tblBestellungen, tblBestellpositionen und tblArtikel hinzuzieht, erhält er nach einer Preisänderung eines Artikels, der in einer Bestellung enthalten ist, ein falsches Ergebnis. Also müssen wir einen anderen Weg finden, um die Preise in irgendeiner Weise zu speichern. Dabei gibt es zwei Varianten:
€¢ Die einfachere Variante verwendet ein Feld in der Tabelle tblBestellpositionen, das den zum Zeitpunkt der Bestellung gültigen Preis für den Artikel enthält.
€¢ Die zweite Möglichkeit ist das Speichern der verschiedenen Preise für einen Artikel im Laufe der Zeit. Dazu ist eine weitere Tabelle nötig, welche den Preis und den Zeitraum der Gültigkeit des Preises enthält.
Preis in der Bestellposition speichern
Die Variante mit dem zusätzlichen Preis-Feld hat zusätzlich den Charme, dass Sie einen Preis je nach Kunde oder auch nach Bestellmenge mit einem individuellen Rabatt versehen können. Der Nachteil ist, dass es keine genauen Informationen darüber gibt, wann und wie sich der Preis eines Artikels im Laufe der Zeit geändert hat. Sie können lediglich den aktuellen Preis in der Tabelle tblArtikel betrachten. Einen zeitlichen Verlauf des Preises erhalten Sie nicht: Selbst das Kontrollieren aller in der Tabelle tblBestellpositionen gespeicherten Datensätze für diesen Artikel liefert keine genauen Informationen, wenn Sie dort auch die individuelle Rabattierung erlauben. Bild 10 zeigt die änderung der Tabelle tblBestellpositionen für diese Variante (diese Version finden Sie übrigens in der Beispieldatenbank PreiseVerwalten_2.mdb).
Bild 10: Die Tabelle tblBestellpositionen mit einem zusätzlichen Feld zum Speichern eines individuellen Artikelpreises
Desweiteren ändert sich die Datenherkunft des Unterformulars im Formular frmBestellungen. Die Abfrage qryBestellpositionen sieht nunmehr wie in Bild 11 aus. Die Tabelle tblArtikel wird dort gar nicht mehr benötigt, da das für die Bestellung relevante Feld Preis bereits in der Tabelle tblBestellpositionen enthalten ist.
Bild 11: Datenherkunft des Unterformulars zur Anzeige der Bestellpositionen
Fehlt noch eine kleine änderung im Unterformular sfmBestellungen. Damit der Benutzer den Preis des Artikels nicht jedes Mal selbst eintragen muss, soll standardmäßig der aktuell in der Tabelle tblArtikel gespeicherte Preis in das Feld Preis der Tabelle tblBestellpositionen geschrieben werden. Dies gelingt leider nicht über die Einstellung eines entsprechenden Standardwerts, da diese Eigenschaft nur statische Werte oder Access-Funktionen, aber keine benutzerdefinierten Funktionen erlaubt. Wir müssen also dafür sorgen, dass das Unterformular beim Anlegen eines neuen Datensatzes in der Tabelle tblBestellpositionen und hier speziell nach dem Auswählen eines neuen Artikels den aktuellen Preis des hinzugefügten Artikels ausliest und diesen in das Feld Preis einträgt.
Das richtige Ereignis hierfür heißt Nach Aktualisierung und wird durch das Kombinationsfeld zur Anzeige des Artikels ausgelöst.
Die fertige Prozedur sieht wie folgt aus und verwendet die DLookup-Funktion, um den Inhalt des Feldes Preis aus der Tabelle tblArtikel für den Datensatz auszulesen, dessen ArtikelID mit dem aktuellen Wert des Kombinationsfeldes übereinstimmt:
Private Sub ArtikelID_AfterUpdate()
Me!Preis = DLookup("Preis", "tblArtikel", _
"ArtikelID = " & Me!ArtikelID)
End Sub
Der Benutzer kann diesen Preis dann nach seinem Gusto mit einem Rabatt versehen.
Preis in Abhängigkeit von der Zeit speichern
Die detaillierte Notation des Preisverlaufs über eine zusätzliche Tabelle der Preise der verschiedenen Artikel liefert hier exaktere Informationen. Sie können in dieser Tabelle den Preis zu einem bestimmten Zeitpunkt eindeutig ermitteln. Und auch auf die individuelle Rabattierung eines Artikels brauchen Sie nicht zu verzichten: Dies ist weiterhin möglich, und zwar über ein dafür vorgesehenes Feld in der Tabelle tblBestellpositionen, das den Rabatt in Prozent enthält.
Schauen wir uns zunächst die notwendigen änderungen am Datenmodell an, damit die Preise zeitabhängig gespeichert werden können – die Version der Beispieldatenbank finden Sie in der Datei PreiseVerwalten_3.mdb. Das Feld Preis kann in der Tabelle tblArtikel entfallen, da wir es in eine weitere Tabelle namens tblPreise auslagern. Diese sieht wie in Bild 13. Auch in der Tabelle tblBestellpositionen ist kein Platz mehr für eine Preisangabe. Da es für jeden Artikel mehrere Preise geben kann, benötigt die Tabelle ein Fremdschlüsselfeld zur Verknüpfung mit dem jeweiligen Artikel. Für die Angabe des Zeitraums gibt es wiederum verschiedene Möglichkeiten:
Bild 12: Preise der Artikel in Abhängigkeit vom Datum
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo