Home > Artikel > Ausgabe 6/2018 > 1:1-Beziehungen in Formularen

1:1-Beziehungen in Formularen

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

1:1-Beziehungen können für eine ganze Reihe von Anwendungzwecken sinnvoll sein. Sie können damit beispielsweise die Liefer- und/oder die Rechnungsanschrift für einen Kundendatensatz in eigenen Tabellen speichern, um so verschiedene Ziele zu erreichen: beispielsweise die Anzahl der Felder je Tabelle zu verringern, verschiedene Teile der Daten mit unterschiedlichen Berechtigungen versehen und so weiter. In diesem Artikel schauen wir uns an, wie Sie die Formular zur Darstellung einer 1:1-Beziehung gestalten können.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1806_11BeziehungInFormularen.accdb.

Voraussetzungen

Grundlage für diesen Artikel ist ein weiterer Artikel namens 1:1-Beziehungen. Hier erfahren Sie, wie Sie eine 1:1-Beziehungen erstellen. Kurz zusammengefasst: Sie legen eine Tabelle etwa namens tblKunden an, welche die Basisdaten des Kunden speichert.

Weitere Tabellen wie tblKundenLieferadresse und tblKundenRechnungsadresse enthalten jeweils ein mit einem eindeutigen Index versehenes Fremdschlüsselfeld, über das eine Beziehung zur Tabelle tblKunden hergestellt wird. Wichtig dabei ist, dass Sie die Beziehung im Beziehungen-Fenster anlegen, indem Sie das Primärschlüsselfeld der Tabelle tblKunden auf das Fremdschlüsselfeld der Tabellen tblKundenLieferadressen und tblKundenRechnungsadressen ziehen.

Vorbereitung

Als Vorbereitung legen wir eine Abfrage an, welche die drei Tabellen tblKunden, tblKundenLieferadressen und tblKundenRechnungsadressen enthält. Sie weist im Entwurfsraster alle Felder der Tabelle tblKunden sowie alle Felder der beiden übrigen Tabellen mit Ausnahme des jeweiligen Primärschlüssel- und Fremdschlüsselfeldes auf (siehe Bild 1). Die Abfrage speichern wir unter dem Namen qryKundenLieferadressenRechnungsadressen.

Entwurf der Abfrage qryKundenLieferadressenRechnungsadressen

Bild 1: Entwurf der Abfrage qryKundenLieferadressenRechnungsadressen

Anschließend nehmen wir noch eine wichtige Änderung vor. Wenn wir die Abfrage so nutzen, wie Sie aktuell konfiguriert ist, liefert diese nur solche Datensätze der Tabelle tblKunden, für die es sowohl in der Tabelle tblKundenLieferadressen als auch in der Tabelle tblKundenRechnungsadressen einen verknüpften Datensatz gibt.

Daher müssen wir noch die Verknüpfungseigenschaften für die beiden Verknüpfungen einstellen. Das erledigen wir, indem wir doppelt auf einen der Verknüpfungspfeile klicken. Dies öffnet den Dialog aus Bild 2. Hier wählen wir die folgende Option aus:

Verknüpfungseigenschaften der 1:1-Beziehung zwischen den Tabellen tblKunden und tblKundenRechnungsadressen

Bild 2: Verknüpfungseigenschaften der 1:1-Beziehung zwischen den Tabellen tblKunden und tblKundenRechnungsadressen

Beinhaltet ALLE Datensätze aus 'tblKunden' und nur die Datensätze aus 'tblKundenRechnungsadressen', bei denen die Inhalte der verknüpften Felder beider Tabellen gleich sind.

Die gleiche Einstellung nehmen wir auch für die Beziehung zwischen den Tabellen tblKunden und tblKundenLieferadressen vor. Danach sieht der obere Teil des Abfrageentwurfs wie in Bild 3 aus. Wenn jetzt nur ein Eintrag in der Tabelle tblKunden vorliegt, aber keiner oder nur einer in den beiden übrigen Tabellen, werden die Felder der entsprechenden Tabelle im Abfrageergebnis leer angezeigt.

Abfrage mit angepassten Verknüpfungseigenschaften

Bild 3: Abfrage mit angepassten Verknüpfungseigenschaften

Fehler beim Ausführen der Abfrage

Das war zumindest die Theorie, denn in der Tat erscheint beim Wechsel in die Datenblattansicht der Abfrage die folgende Fehlermeldung:

Die SQL-Anweisung konnte nicht ausgeführt werden, da sie mehrdeutige äußere Verknüpfungen enthält. Damit eine der VErknüpüfungen zuerst ausgeführt wird, müssen Sie eine separate Abfrage erstellen, die die erste Verknüpfung ausführt, und dann diese Abfrage in die SQL-Anweisung einschließen.

Das heißt also: Wir müssen erst eine Abfrage erstellen, welche die Daten der Tabelle tblKunden mit einer der beiden per 1:1-Beziehung verknüpften Tabellen liefert und diese dann in einer zweiten Abfrage mit der anderen verknüpften Tabelle zusammenführen.

Die erste Abfrage ist schnell erstellt. Dazu löschen Sie einfach die Tabelle tblKundenRechnungsadressen aus der Abfrage qryKundenLieferadressenRechnungsadressen und ändern den Namen auf qryKundenLieferadressen. Der Entwurf sieht dann wie in Bild 4 aus. Der Wechsel in die Datenblattansicht birgt die nächste Überraschung: Auch hier taucht diese Fehlermeldung auf.

Erster Teil der Abfrage mit zwei 1:1-Beziehungen

Bild 4: Erster Teil der Abfrage mit zwei 1:1-Beziehungen

Können wir die Daten der 1:1-Beziehung also gar nicht so darstellen, dass nur die Daten einer Tabelle ausgegeben werden, wenn es in der zweiten Tabelle keine verknüpften Daten gibt? Das wäre ungünstig, weil wir ja dann nur neue Datensätze in einem Formular auf Basis dieser Abfrage anlegen könnten. Wenn wir hier nur die Daten zur Tabelle tblKunden hinzufügen und kein Feld der Tabelle tblKundenLieferadressen füllen, können wir dies auch nachträglich nicht mehr erledigen, weil dieser Datensatz der Tabelle tblKunden schlicht nicht mehr angezeigt werden kann – zumindest nicht in einer Abfrage zusammen mit der Tabelle tblKundenLieferadressen.

Formular zur Anzeige der Daten

Nun haben wir durch das obige Problem zwei Möglichkeiten: Wir verwenden eine Version der Abfrage, welche die Daten der drei Tabellen zusammenführt, als Datensatzquelle des Formulars und sorgen gleich beim Anlegen der ersten Werte für die Felder der Tabelle tblKunden dafür, dass jeweils ein Datensatz in den Tabellen tblKundenLieferadressen und tblKundenRechnungsadressen erstellt wird.

Dann hätten wir allerdings nicht den Vorteil, dass nur für solche Datensätze, die tatsächlich eine Lieferadresse und/oder Rechnungsadresse erhalten, auch ein Datensatz in einer der Tabellen tblKundenLieferadressen und/oder tblKundenRechnungsadressen angelegt wird. Sprich: Wir könnten auch gleich eine einzige Tabelle anlegen, die wieder alle Felder des Kunden inklusive Rechnungsadresse und Lieferadresse enthält.

Die Alternative ist, mit zwei Unterformularen zu arbeiten, welche die Daten der Tabellen tblKundenRechnungsadressen und tblKundenLieferandressen enthalten. Wir schauen uns einfach beide Lösungen an.

Formular mit automatischen Datensätzen

Als Datensatzquelle des ersten Formulars verwenden wir also eine Abfrage, die alle drei Tabellen, also tblKunden, tblKundenRechnungen und tblKundenLieferadressen enthält (siehe qryKundenRechnungsadressenLieferadressen).

Dann fügen wir die Felder etwa wie in Bild 5 zum Formularentwurf des neuen Formulars namens frmKunden1zu1EinFormular hinzu. Dadurch, dass die doppelt vorkommenden Felder in der Abfrage alle noch mit dem Tabellennamen versehen werden, haben wir allerdings noch eine kleine Fleißaufgabe für die Bezeichnungsfelder vor uns.

Erster Entwurf des Formulars

Bild 5: Erster Entwurf des Formulars

Nachdem diese erledigt ist und wir noch je einen Rahmen für die Felder der Lieferadresse und der Rechnungsadresse hinzugefügt haben, sieht das Formular in der Formularansicht wie in Bild 6 aus.

Formular optisch aufbereitet

Bild 6: Formular optisch aufbereitet

Hier haben wir außerdem noch die Beschriftungsfelder angepasst sowie die Aktivierreihenfolge richtig eingestellt. Nun experimentieren wir ein wenig, indem wir einen neuen Datensatz anlegen.

Experimente im Formular mit einer Abfrage über die beiden 1:1-Beziehungen

Wir legen einen neuen Datensatz an, wobei wir mit den oberen Feldern beginnen und dann die Felder mit der Lieferadresse und der Rechnungsadresse füllen. In dieser Reihenfolge gelingt das ohne Probleme – der Datensatz wird korrekt mit den verknüpften Datensätzen in den drei Tabellen gespeichert und der Datensatz erscheint nach dem Verlassen und dem erneuten Anzeigen wieder im Formular frmKunden1zu1EinFormular.

Nun beginnen wir mit dem Anlegen bei den Feldern der Tabelle tblLieferadresse, also im Rahmen mit der Überschrift Lieferadresse, tragen dann die Rechnungsadresse ein und erst dann die Daten der Tabelle tblKunden. Auch das gelingt – der Datensatz wird gespeichert und im Formular angezeigt.

Wenn wir nur die Daten der Lieferadresse und/oder der Rechnungsadresse eintragen, ohne auch nur ein Feld der Tabelle tblKunden auszufüllen und dann versuchen, den Datensatz zu speichern, erhalten wir die Fehlermeldung aus Bild 7.

Fehler beim Versuch, nur Daten in die Tabelle tblKundeLieferadresse zu schreiben

Bild 7: Fehler beim Versuch, nur Daten in die Tabelle tblKundeLieferadresse zu schreiben

Dieses Problem beheben wir gleich mit ein paar Zeilen VBA-Code.

Eine weitere Variante ist, Daten in die Felder der Tabelle tblKunden einzugeben, aber dann nur die Felder keiner oder nur einer der beiden Tabellen tblKundenLieferadressen oder tblKundenRechnungsadressen zu füllen.

Das funktioniert zwar ohne Fehler und die Daten werden auch in den betroffenen Tabellen gespeichert. Wenn allerdings nicht in jeder der Tabellen tblKundenLieferadressen und tblKundenRechnungsadressen ein Datensatz zu dem Datensatz aus tblKunden enthalten ist, werden die Daten nicht von der Abfrage qryKundenRechnungsadressenLieferadressen zurückgeliefert. Diese Abfrage liefert nur solche Daten, für die es in allen drei Tabellen entsprechend verknüpfte Datensätze gibt.

Auch hier können wir per VBA dafür sorgen, dass zumindest ein ansonsten leerer Datensatz in der nicht vom Benutzer ausgefüllten Tabelle tblKundenLieferadressen oder tblKundenRechnungsadressen angelegt wird. Wie das geht, lesen Sie weiter unten.

Speichern ohne Kundendaten verhindern

Um den Fehler zu verhindern, der beim Versuch auftritt, die Lieferadresse oder Rechnungsadresse ohne Angabe der Basisdaten zum Kunden zu speichern, gibt es zwei Möglichkeiten: Entweder wir fangen den Fehler ab und ersetzen diesen durch eine benutzerdefinierte, gut verständliche Fehlermeldung, nach dessen Anzeige der Fokus in eines der noch auszufüllenden Felder verschoben wird.

Oder wir legen automatisch einen Datensatz in der Tabelle tblKunden an, sobald der Benutzer mit der Bearbeitung eines der Felder der Tabellen tblKundenLieferadressen oder tblKundenRechnungsadressen begonnen hat.

Die erste Variante zeigen wir im Formular frmKunden1zu1EinFormular_FehlerAbfangen. Diesem Formular fügen wir für die Ereigniseigenschaft Bei Fehler eine Ereigniseigenschaft hinzu, die wie folgt aussieht:

Private Sub Form_Error(DataErr As Integer, _

Response As Integer)

Debug.Print DataErr

End Sub

Die dort enthaltene Anweisung nutzen wir zunächst, um herauszufinden, wie die Fehlernummer des ausgelösten Fehlers lautet. Wenn wir das Formular nun in der Formularansicht öffnen und in einem Feld einer der beiden Tabellen tblKundenLieferadressen oder tblKundenRechnungsadressen einen Wert eintragen, bevor wir den neu angelegten Datensatz speichern, löst dies den oben genannten Fehler aus.

Im Direktbereich können wir dann die Nummer des Fehlers auslesen, der in diesem Fall 3201 lautet.

Diesen Fehler behandeln wir dann in der folgenden Version der Ereignisprozedur Form_Error gezielter, indem dafür einen eigenen Zweig in einer neu hinzugefügten Select Case-Bedingung anlegen:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr

Case 3201

MsgBox "Bitte geben Sie einen Wert in eines der Felder im oberen Bereich ein, bevor Sie den Datensatz speichern."

Me!EMail.SetFocus

Response = acDataErrContinue

End Select

End Sub

Damit erhalten wir die Meldung mit dem folgenden Text, wenn der Benutzer versucht, den Datensatz ohne Eingabe von Werten in die Felder der Tabelle tblKunden zu speichern:

Bitte geben Sie einen Wert in eines der Felder im oberen Bereich ein, bevor Sie den Datensatz speichern.

Außerdem verschiebt die Prozedur den Fokus auf das Textfeld, das an das Feld EMail der Tabelle tblKunden gebunden ist.

Datensatz in tblKunden automatisch anlegen

Die zweite Variante finden Sie im Formular frmKunden1zu1EinFormular_DSAnlegen. Wir wollen hier dafür sorgen, dass bei der Eingabe eines Wertes in eines der Felder der Tabellen tblKundenLieferadressen oder tblKundenRechnungsadressen auch ein Datensatz in der Tabelle tblKunden angelegt wird. Das ist allerdings weniger einfach als gedacht.

Wenn wir in einer einfachen Tabelle etwa in der Datenblattansicht das erste Feld bearbeiten, wird durch die Autowert-Funktion direkt der Primärschlüsselwert in die Tabelle eingetragen. Das ist einer Abfrage mit den wie in unserem Beispiel verknüpften Daten nicht der Fall. Hier können wir Werte für die Felder einer beliebigen der drei beteiligten Tabellen eingeben, die Autowert-Funktionen für die Tabellen werden erst später aufgerufen.

In der Abfrage qryKundenRechnungsadressenLieferadressen ist das bei den beiden Tabellen tblKundenLieferadressen und tblKundenRechnungsadressen beispielsweise erste der Fall, wenn wir den jeweiligen Datensatz der verknüpften Tabelle verlassen.

Wenn wir also etwa das Feld Firma der Tabelle tblKundenLieferadressen füllen und dann den Fokus auf das Feld Firma der Tabelle tblKundenRechnungsadressen verschieben, wird das Feld LieferadresseID mit einem Autowert gefüllt. Das Fremdschlüsselfeld KundeID der Tabelle tblKundenLieferadressen erhält dann zunächst den Wert 0, da ja auch das Primärschlüsselfeld KundeID der Tabelle tblKunden noch nicht gefüllt wurde.

Mit welchem Ereignis können wir hier überhaupt arbeiten? Eine Idee ist es, das Bei Geändert-Ereignis heranzuziehen, dass ja ausgelöst wird, sobald der Benutzer das erste Feld der Datensatzquelle bearbeitet. Dieses Ereignis wird in unserer Konstellation interessanterweise auf drei verschiedene Arten ausgelöst: wenn wir ein Feld der Tabelle tblKunden ändern, wenn wir ein Feld der Tabelle tblKundenLieferadressen ändern oder wenn wir ein Feld der Tabelle tblKundenRechnungsadressen ändern.

Ausgehend vom Ereignis Bei Geändert durch eine der beiden letztgenannten Tabellen müssten wir, um das Anlegen eines Datensatzes in der Tabelle tblKunden zu erreichen, den Fokus auf eines der an die Tabelle tblKunden gebundenen Steurelemente und die Eigenschaft Dirty auf den Wert True einstellen. Das wiederum löst allerdings wieder das Ereignis Bei Geändert aus und sorgt so für eine Endlosschleife. Damit können wir also nicht arbeiten.

Also schauen wir uns nach einem anderen Ereignis um, zum Beispiel Vor Aktualisierung. Das vorherige Experiment, den Datensatz einfach nach dem Verschieben des Fokus auf ein Element der Tabelle tblKunden zu verschieben und die Eigenschaft Dirty auf True einzustellen, funktioniert auch hier nicht – damit zaubern wir keinen Wert in das Primärschlüsselfeld.

Eine alternative Möglichkeit ist es, den Datensatz durch das Einstellen eines der Felder auf einen Wert in den Geändert-Modus zu versetzen. In diesem Fall stellen wir den Wert des Feldes ErfasstAm mit der Funktion Date() auf das aktuelle Datum ein:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!ErfasstAm = Date

End Sub

Damit gelingt es dann auch, den Datensatz zu speichern. Leider wird so auch bei jeder nachfolgenden Änderung das Datum im Feld ErfasstAm aktualisiert. Das Problem können wir allerdings leicht beheben, indem wir eine Prüfung in die Ereignisporzedur Form_BeforeUpdate einfügen, die prüft, ob ErfasstAn bereits einen Wert enthält und dieses Feld nur dann füllt, wenn es leer ist:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!ErfasstAm) Then

Me!ErfasstAm = Now

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!