1:n-Beziehung mit Standardzuordnung verwalten

Im Beitrag “Rechnungsverwaltung: Kundenadressen ausgliedern” (www.access-im-unternehmen.de/1470) verwalten wir zu jedem Kunden mehrere Adressen, die dem Kunden über ein Fremdschlüsselfeld in der Adresstabelle zugewiesen sind. Für jeden Kunden legen wir in dieser Tabelle eine Standardadresse fest. Damit ist einiger Pflegeaufwand verbunden, denn wir müssen sicherstellen, dass immer genau eine Adresse je Kunde als Standardadresse markiert ist. In diesem Beitrag schauen wir uns an, wie wir sicherstellen können, dass die Daten immer konsistent sind und wir nicht plötzlich mehrere Standardadressen je Kunde vorfinden – oder gar keine.

Elemente der hier behandelten Konstellation

Die Konstellation, die wir in diesem Beitrag vorstellen, enthält folgende Elemente:

• Hauptentität: Die Hauptentität repräsentiert den übergeordneten Datensatz oder die Hauptkomponente, zu der die Beziehung besteht. Im einführenden Beispiel ist die Hauptentität der Kunde.
• Unterentitäten: Die Unterentitäten sind mit der Hauptentität verknüpft und repräsentieren zusätzliche Informationen oder Komponenten, die zu jeder Hauptentität gehören. Im Beispiel sind die Unterentitäten die Adressen.
• Fremdschlüssel: Um die Beziehung zwischen der Hauptentität und den Unterentitäten herzustellen, wird ein Fremdschlüsselfeld in den Unterentitäten verwendet, das auf die Hauptentität verweist. In unserem Beispiel ist dies das KundeID-Feld in der Adresstabelle.
• Standardentität: Es gibt eine Möglichkeit, eine der Unterentitäten als Standard oder Hauptentität für jede Hauptentität festzulegen. Dies wird durch ein zusätzliches Feld wie Standardadresse erreicht.

Weitere Beispiele für diese Konstellation

Neben dem in der Einleitung vorgestellten Beispiel mit den Kunden und den zugeordneten Adressen (siehe Bild 1) gibt es noch weitere Beispiele:

Bild 1: Beispiel für eine 1:n-Beziehung mit Standardzuordnung

• Personen und verschiedene Telefonnummern oder E-Mail-Adressen, die in einer separaten Tabelle gespeichert werden und von denen eine als Standard-E-Mail-Adresse oder Standardtelefonnummer markiert wird
• Produkte und verschiedene Lieferanten, von denen einer als Standardlieferant festgelegt wird
• Produkte und Variante: Für jedes Produkt gibt es verschiedene Varianten, von denen eine als Standardvariante vorgesehen wird.

Pflege von 1:n-Beziehungen mit Standardzuordnung

Übliche 1:n-Beziehungen, deren Pflege durch die Definition der Beziehung und der referenziellen Integrität mit oder ohne Löschweitergabe oder Aktualisierungsweitergabe geregelt ist, benötigen keinen zusätzlichen Aufwand – man legt einen Datensatz in der Detailtabelle an, beispielsweise tblAnreden, und wenn man einen Datensatz in der Mastertabelle wie tblKunden hinzufügt, wählt man einen der Datensätze aus der Detailtabelle aus.

Bei unserem Beispiel ist es im Prinzip genauso: Wir wählen für jeden Datensatz der Tabelle tblAdressen über das Fremdschlüsselfeld KundeID einen Datensatz aus der Tabelle tblKunden aus.

Nun wollen wir jedoch angeben, welche der Adressen aus der Tabelle tblAdressen die Standardadresse ist, die zum Beispiel beim Anlegen neuer Rechnungen oder Bestellungen auf Basis dieses Kunden herangezogen werden soll (siehe Bild 2).

Bild 2: Die 1:n-Beziehung mit Standardzuordnung im Formular

Wenn wir einem Kunden eine erste Adresse hinzufügen, soll diese direkt als Standardadresse markiert werden.

Das allein ist schon nicht einfach: Wir können nicht einfach den Standardwert des Feldes Standardadresse auf Wahr einstellen, denn wenn der Benutzer anschließend eine neue Adresse für diesen Kunden hinzufügt, würde das Feld Standardadresse für diesen Kunden ebenfalls auf Wahr eingestellt werden und wir hätten bereits zwei als Standardadresse markierte Adressen für diesen Kunden. Das Einstellen des Feldes Standardadresse auf den Wert Wahr entfällt also bereits.

Daraus lassen sich bereits erste Regeln für unser Vorhaben ableiten:

• Wenn eine neue Adresse angelegt wird und dies ist die erste Adresse für diesen Kunden, soll das Feld Standardadresse den Wert Wahr erhalten.
• Wenn eine neue Adresse angelegt wird und es ist nicht die erste Adresse für diesen Kunden, müssen wir prüfen, ob es bereits eine Standardadresse gibt. Aber was dann – welche Adresse legen wir dann als Standardadresse fest? Wir schlagen vor, dass in diesem Fall die zuletzt hinzugefügte Adresse als Standardadresse festgelegt werden soll.

Davon abgesehen müssten wir eine Vorgehensweise etablieren, die beim Löschen einer der Adressen in Aktion tritt. Mit dieser müssen wir prüfen, ob einer der verbleibenden Adressdatensätze die Standardadresse für diesen Kunden ist und falls nicht, müssten wir eine der verbleibenden Adressen zur neuen Standardadresse machen.

Man könnte hier einfach die zuerst angelegte Adresse zur Standardadresse machen oder auch die zuletzt angelegte – oder auch eine ganz andere, beispielsweise eine, die besonders oft für Bestellungen oder Rechnungen verwendet wurde. Wir können auch den Benutzer fragen, welche Adresse die neue Standardadresse werden soll. Und wenn wir sie einfach festlegen, sollten wir dem Benutzer darüber informieren.

Entfernen des Status als Standardadresse

Der Benutzer könnte auf die Idee kommen, den Haken für das Feld Standardadresse zu entfernen, weil er diese Adresse nicht mehr als Standardadresse verwenden möchte. Das wollen wir verhindern – der Benutzer soll die aktuelle Standardadresse nur durch Setzen einer neuen Standardadresse entfernen können.

Setzen des Status als Standardadresse

Wenn der Benutzer eine neue Standardadresse einstellen möchte, soll das durch einfaches Aktivieren des Kontrollkästchens für das Feld Standardadresse möglich sein. Wenn dies geschieht, müssen wir allerdings dafür sorgen, dass das Feld Standardadresse für alle anderen Datensätze der Adresstabelle, die diesem Kunden zugeordnet sind, entfernt wird.

Ermitteln der aktuellen Standardadresse

Schließlich wird es Situationen geben, in denen wir, beispielsweise zum Steuern der Darstellung in Formularen, die aktuelle Standardadresse für einen Kunden ermitteln müssen.

VBA-Funktionen zum einfachen Umsetzen der Anforderungen

Damit wir die oben genannten Anforderungen einfach umsetzen können, wollen wir dazu einen flexibel einsetzbaren Satz an VBA-Funktionen bereitstellen, mit denen wir nicht nur die Standardadresse aus den Adressen eines Kunden, sondern auch die Standarddatensätze für andere Datenmodelle ermitteln können.

Welche Funktionen benötigen wir also dazu?

• Ermitteln des aktuellen Standardwertes. Wenn diese Funktion den Wert 0 zurückliefert, wenn also kein Standardwert vorhanden ist, haben wir gleichzeitig bereits eine Funktion, um zu prüfen, ob ein Standardwert vorhanden ist.
• Setzen des aktuellen Standardwertes. Setzt voraus, dass zuvor bisherige Standardwerte entfernt wurden.
• Entfernen bisheriger Standardwerte. Setzt voraus, dass anschließend ein neuer Standardwert gesetzt wird.
• Ermitteln des neuen Standardwertes, falls keiner festgelegt ist.

Diese Funktionen müssen wir nun noch so verknüpfen, dass sie sinnvoll in einer Anwendung einsetzbar sind. Außerdem müssen wir noch festlegen, zu welchen Zeitpunkten die Integrität dieser Daten geprüft werden soll – also beispielsweise, ob der aktuell angezeigte Kundendatensatz eine Standardadresse aufweist. Also schauen wir uns nun die Zeitpunkte an, zu denen wir auf die Daten zugreifen und diese ändern. Dies ist dann die Grundlage für die tatsächlich zu definierenden Funktionen zur Pflege der 1:n-Beziehung mit Standardzuordnung.

Zeitpunkte des Zugriffs auf die 1:n-Beziehung mit Standardzuordnung

Wann also greifen wir tatsächlich auf die Standardzuordnungen zu? Wir sehen die folgenden Zeitpunkte:

• Öffnen eines Formulars, das die Daten des Masterdatensatzes und des Detaildatensatzes anzeigt: Hier sollte geprüft werden, ob eine Standardzuordnung besteht und gegebenenfalls eingerichtet werden.
• Wechsel zu einem anderen Datensatz im gleichen Formular
• Anlegen einer neuen Adresse für einen Kunden: Abfrage, ob eine Standardzuordnung besteht und gegebenenfalls einrichten
• Löschen einer Adresse eines Kunden: Abfragen, ob nach dem Löschen noch eine Standardzuordnung besteht und gegebenenfalls einrichten
• Ändern der Standardzuordnung durch den Benutzer: Löschen aller vorherigen Zuordnungen und anlegen der neuen Zuordnung

Funktionen zum Verwalten der Standardzuordnungen

Basierend auf diesen Anforderungen haben wir die folgenden Funktionen abgeleitet, die wir anschließend im Detail betrachten:

• HoleStandardzuordnung: Liest die ID des Datensatzes mit der Standardzuordnung für die angegebene Tabelle, Standardfeld, Fremdschlüsselfeld und Fremdschlüsselwert ein.
• SetzeStandardzuordnung: Setzt den Wert des mit Standardfeld übergebenen Feldes in der angegebenen Tabelle für den angegebenen Fremdschlüsselwert im Feld des Parameters Fremdschluesselfeld auf True.
• EntferneStandardzuordnungen: Stellt für alle Datensätze der mit strTabelle angegebenen Tabelle mit dem Wert aus lngFKID im Feld aus strFKFeld auf False ein.
• HoleErstezuordnung: Liest den Primärschlüsselwert des ersten Datensatzes der mit strTabelle angegebenen Tabelle ein, deren Feld aus strFKFeld den Wert aus lngFKID aufweist.
• HoleOderSetzeStandardzuordnung: Kombination aus mehreren der vorgenannten Funktionen, die versucht, die Standardadresse zu holen und falls dies nicht gelingt, eine neue Standardadresse zu setzen.
• MehrfacheStandardzuordnungenEntfernen: Entfernt für die mit strTabelle angegebene Tabelle alle doppelten Standardzuordnungen mit Ausnahme der Ersten.

Standardzuordnung holen

Die Funktion HoleStandardzuordnung ermittelt mit der DLookup-Funktion den Primärschlüsselwert des Datensatzes der Tabelle aus strTabelle, dessen Fremdschlüsselfeld den Fremdschlüsselwert aus lngFKID enthält, und gibt diesen als Funktionsergebnis zurück (siehe Listing 1).

Public Function HoleStandardzuordnung(strTabelle As String, strStandardfeld As String, strPKFeld As String, _
         strFKFeld As String, lngFKID As Long) As Long
     Dim lngPKID As Long
     lngPKID = Nz(DLookup(strPKFeld, strTabelle, strFKFeld & " = " & lngFKID & " AND " & strStandardfeld & " = -1"), 0)
     HoleStandardzuordnung = lngPKID
End Function

Listing 1: Funktion zum Ermitteln der Standardzuordnung

Standardzuordnung setzen

Die Funktion SetzeStandardzuordnung aktualisiert die mit strTabelle übergebene Tabelle, indem Sie mit einer UPDATE-Anweisung das Feld aus strStandardfeld für den Datensatz auf den Wert -1 aktualisiert, der im Feld aus strPKFeld den Wert aus lngPKID aufweist (siehe Listing 2).

Public Sub SetzeStandardzuordnung(strTabelle As String, strStandardfeld As String, strPKFeld As String, lngPKID As Long)
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE " & strTabelle & " SET " & strStandardfeld & " = -1 WHERE " & strPKFeld & " = " & lngPKID, _
         dbFailOnError
End Sub

Listing 2: Funktion zum Setzen einer Standardzuordnung

Standardzuordnungen entfernen

Die Funktion EntferneStandardzuordnungen stellt das Feld aus strStandardfeld mit einer UPDATE-Anweisung für alle Datensätze der Tabelle aus strTabelle auf den Wert 0 ein, deren Feld aus strFKFeld den Wert aus lngFKID aufweist (siehe Listing 3).

Public Sub EntferneStandardzuordnungen(strTabelle As String, strStandardfeld As String, strFKFeld As String, _
         lngFKID As Long)
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE " & strTabelle & " SET " & strStandardfeld & " = 0 WHERE " & strFKFeld & " = " & lngFKID, _
         dbFailOnError
End Sub

Listing 3: Funktion zum Entfernen von Standardzuordnungen

Erste Zuordnung holen

Die Funktion HoleErsteZuordnung verwendet den Aufruf der DLookup-Funktion, um den Wert des Feldes aus strPKFeld aus dem ersten Datensatz der Tabelle aus strTabelle zu holen, dessen Feld aus strFKFeld den Wert aus lngFKID enthält (siehe Listing 4).

Public Function HoleErsteZuordnung(strTabelle As String, strPKFeld As String, strFKFeld As String, _
         lngFKID As Long) As Long
     HoleErsteZuordnung = Nz(DLookup(strPKFeld, strTabelle, strFKFeld & " = " & lngFKID), 0)
End Function

Username or Email

Passwort

Angemeldet bleiben