In vielen Beispieltabellen, die wir in diesem Magazin vorstellen, verwenden wir einfach das Primärschlüsselfeld als Kundennummer, Bestellnummer und so weiter. In manchen Fällen ist das nicht praktikabel, weil diese Nummern nach bestimmten anderen Regeln erstellt werden müssen. Dann bietet es sich an, dennoch ein Primärschlüsselfeld mit Autowertfunktion zu nutzen, um die Datensätze eindeutig zu identifizieren und dieses auch für das Herstellen von Beziehungen zu nutzen. Die Kundennummern oder Bestellnummern möchte man aber dennoch nicht von Hand eingeben, sondern die Datenbank soll das erledigen. Wie Sie das realisieren können, zeigt der vorliegende Beitrag.
Individuelle Nummern
Die Datenbankwelt wäre einfach, wenn man immer ein Primärschlüsselfeld mit Autowert als einziges eindeutiges Merkmal von Datensätzen nutzen könnte. Aber die Realität sieht anders aus. Manchmal gibt schon die Historie einer Anwendung vor, dass Kundennummern, Bestellnummern, Produktnummern und so weiter nach einem bestimmten Format generiert werden müssen, das nicht selten nicht nur Zahlen, sondern auch Buchstaben enthält.
Damit ist die Autowert-Funktion, die sich für die Ermittlung der Werte für Primärschlüsselfelder anbietet, überfordert – sie liefert nur Werte der Typen Long Integer oder Replikations-ID (sprich: GUID), und das im Falle der Zahlenwerte entweder aufsteigend oder zufällig.
Oft enthalten die verschiedenen Nummernkreise ein bestimmtes, aus Buchstaben bestehendes Kürzel, damit man direkt erkennen kann, ob es sich um eine Bestellnummer, eine Kundennummer oder andere Informationen handelt.
Wir sollten also in der Lage sein, mithilfe einer Funktion oder anderen technischen Möglichkeiten Werte für die gewünschten Anforderungen zu ermitteln.
Diese Anforderung könnte beispielsweise lauten, dass wir Werte benötigen, die mit dem Buchstaben A beginnen und danach aus acht Ziffern bestehen. Welche Herausforderungen können wir daraus ableiten?
Wir gehen davon aus, dass die Werte möglichst aufsteigend sein sollen, das heißt, dass der numerische Anteil so ermittelt wird, dass wir den bisher größten Wert herausfinden und diesem 1 hinzuaddieren. Auf A00000001 soll also A00000002 folgen. Das wäre alles kein Problem, wenn wir es mit einem Zahlenfeld zu tun hätten – wir würden dann einfach mit DMax den größten bisher vergebenen Wert ermitteln und diesen als Grundlage nutzen.
Ein möglicher Weg, dies zu umgehen, ist die Ableitung vom Primärschlüsselwert. Wie gesagt, wollen wir ein Primärschlüsselfeld mit Autowert als erstes eindeutiges Merkmal eines jeden Datensatzes nutzen, die zusätzliche Nummer (Kundennummer, Bestellnummer, …) ist nur eine weitere eindeutige Information. Wir können unsere Nummer also auch zusammensetzen aus dem Buchstaben A, dem Primärschlüsselwert und dazwischen eine Reihe Nullen, sodass wir das gewünschte Format von einer Länge von neun Zeichen erhalten. Beim Primärschlüsselwert 123 also beispielsweise A plus 00000 plus 123 gleich A00000123.
Wir schauen uns im Folgenden diesen Weg an und gehen von einer Kundennummer im oben genannten Format aus.
Kundennummer auf Basis des Primärschlüsselwertes
Im Beispiel verwenden wir eine Tabelle namens tblKunden, deren Primärschlüsselfeld namens ID als Autowert-Feld definiert ist. Das Feld Kundennummer haben wir als eindeutiges Feld definiert (siehe Bild 1).
Bild 1: Einrichtung der Kundennummer als Feld mit einem eindeutigen Index
Nun wollen wir dafür sorgen, dass möglichst automatisch beim Anlegen eines neuen Datensatzes auch das Feld Kundennummer mit einem Wert gefüllt wird, der das oben beschriebene Format enthält.
Unsere erste Idee war, die Format-Funktion als Standardwert für das Feld Kundennummer zu nutzen. Dort wollten wir einen Eintrag wie den folgenden hinterlegen:
=Format([ID];"A00000000")
Wie wir schnell herausgefunden haben, funktioniert das nicht, weil man für das Zuweisen von Standardwerten einfach nicht auf die anderen Feldwerte zugreifen kann.
Kundennummer per Datenmakro
Also nutzen wir eines der Tabellenereignisse der Tabelle, in diesem Fall zunächst mit dem Ereignis Vor Änderung. Hier wollten wir prüfen, ob der Datensatz soeben angelegt wurde und dann aus dem Wert des Feldes ID die gewünschte Kundennummer zusammenstellen. Dies zeigte sich als falscher Ansatz, da das Feld ID zu diesem Zeitpunkt noch nicht gefüllt ist.
Um dies herauszufinden, haben wir für das Ereignis Vor Änderung das Datenmakro aus Bild 2 angelegt. Hier prüfen wir in einem ersten Schritt, ob es sich bei der Änderung um das Anlegen eines neuen Datensatzes handelt. In diesem Fall liefert [IstEingefuegt] den Wert True und die Aktionen innerhalb des Wenn-Konstrukts werden ausgeführt.
Bild 2: Makro, um herauszufinden, ob das Feld ID beim Ereignis Vor Änderung bereits gefüllt ist.
Hier haben wir, um eine Meldung zu generieren, die Aktion AuslösenFehler missbraucht. Diese soll einen Text ausgeben, der sowohl den Wert des Feldes ID als auch den des Feldes Firma enthält.
Wie das Ergebnis aus Bild 3 zeigt, wird zwar der neue Datensatz referenziert, aber das Feld ID ist zu diesem Zeitpunkt noch leer. Mit diesem Datenmakro können wir die Aufgabe also nicht lösen.
Bild 3: Ergebnis des Makros Vor Änderung
Da das Makro auch das Speichern des Datensatzes unterbindet, entfernen wir seinen Inhalt wieder.
Anschließend haben wir es mit dem Datenmakro für das Ereignis Nach Einfügung probiert. Hier haben wir zuerst die Makroaktion NachschlagenDatensatz verwendet, um den neuen Datensatz zu referenzieren. Diesen haben wir dann mit der Methode DatensatzBearbeiten bearbeitet, indem wir für das Feld Kundennummer einen Wert eingestellt haben, der aus dem Buchstaben A und dem Wert des Feldes ID des neuen Datensatzes besteht (siehe Bild 4).
Bild 4: Das Datenmakro für das Ereignis Nach Einfügung
Das Ergebnis entspricht noch nicht ganz unseren Anforderungen, denn wir stellen im Feld Kundennummer ja zunächst nur den Buchstaben A mit dem neuen Wert für das Feld ID zusammen, also zum Beispiel A1, A2 und so weiter (siehe Bild 5).
Bild 5: Ergebnis des Datenmakros
Wir wollen aber eigentlich die Kundennummer beispielsweise wie A00000001 erhalten. Warum haben also nicht einfach die Format-Funktion verwendet? Ganz einfach: Die Format-Funktion steht in Datenmakros nicht zur Verfügung. Hier finden wir nur Einträge wie FormatDatumZeit, FormatProzent, FormatWährung und FormatZahl.
Also gehen wir einen kleinen Umweg und verwenden für den Parameter Wert in der Makroaktion FestlegenFeld den folgenden Ausdruck:
"A" & Rechts("0000000" & [rstKunden].[ID];8)
Dies fügt zunächst eine Zeichenfolge aus sieben Nullen (0000000) mit der ID zusammen. Wenn die ID noch einstellig ist, passt das Ergebnis gemäß unseren Anforderungen. Sobald sie aber zweistellig ist, erhalten wir eine Zeichenkette aus sieben Nullen und der ID, also beispielsweise 000000012, was in diesem Fall neun Stellen entspricht. Hier schneiden wir die überflüssigen Nullen ab, indem wir mit der Rechts-Funktion nur die rechten acht Zeichen ermitteln. Stellen wir diesem noch den Buchstaben A voran, erhalten wir schließlich das gewünschte Format (siehe Bild 6).
Bild 6: Kunden mit korrekter Kundennummer
Vor- und Nachteile dieser Lösung
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