m:n-Beziehung mit Listenfeld und Datenblatt

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Es gibt verschiedene Möglichkeiten, eine m:n-Beziehung zwischen zwei Tabellen in Formularen abzubilden: Mit zwei Listenfeldern, mit einem Haupt- und einem Unterformular und viele weitere. In diesem Beitrag schauen wir uns eine Kombination aus Listenfeld und Datenblatt an. Dabei betrachten wir das Beispiel von Fahrzeugen und Ausstattungsmerkmalen. Eigentlich sollte man meinen, das wäre eine reine m:n-Beziehung, in der die Verknüpfungstabelle nur die Zuordnung der Merkmale zu den Fahrzeugen vornimmt. Allerdings liefert einer unserer Kunden ein Beispiel, bei dem es etwas aufwendiger wird: Zusätzlich zur reinen Zuordnung soll auch noch festgelegt werden können, welche Ausstattungsmerkmale mit auf das Preisschild sollen und welche Serien- und welche Sonderausstattungen es gibt. Kein Problem: Dann bauen wir einfach eine ergonomische Lösung für diesen Fall, wie dieser Beitrag zeigt.

Man könnte annehmen, dass die Zuordnung von Ausstattungen zu Fahrzeugen über eine einfache m:n-Verknüpfungstabelle zu realisieren ist, welche jeweils ein Fremdschlüsselfeld zum Herstellen einer 1:n-Beziehung zur Tabelle der Fahrzeuge enthält und eines zum Herstellen einer 1:n-Beziehung zur Tabelle der Ausstattungsmerkmale. Doch einer unserer Kunden hatte eine Anforderung, die darüber hinausgeht: Er möchte nicht nur festlegen, ob ein Ausstattungsmerkmal zu einem Fahrzeug gehört oder nicht, sondern auch noch angeben können, ob dieses auf dem Preisschild gelangen soll.

Falls Sie sich fragen, warum man nicht einfach alle Ausstattungen dort unterbringt: Manchmal hat ein Fahrzeug einfach so viele Ausstattungsmerkmale, dass diese nicht alle auf das Preisschild passen, dass im Fahrzeug hinter die Windschutzscheibe gelegt wird. Dann heißt es aussortieren! Bisher hat der Kunde dies manuell erledigt.

Das heißt, er hat auf Basis der m:n-Beziehung zwischen den Fahrzeugen und den Ausstattungen eine Komma-separierte Liste generiert, die er zunächst in einem Memofeld gespeichert hat. Dann hat er diesen Text in ein zweites Memofeld kopiert und die Merkmale, die am wenigsten Kaufanreiz bieten oder so selbstverständlich sind, dass sie nicht aufgeführt werden müssen, aus der zweiten Liste entfernt – solange, bis diese auf das Preisschild passte.

Das ist allerdings etwas umständlich und auch fehleranfällig:

• Erstens musste er, wenn er einmal versehentlich ein Ausstattungsmerkmal hinzugefügt hat, das nicht zum Fahrzeug gehört oder wenn er eine Ausstattung nachträglich hinzufügen wollte, alle Texte überarbeiten.
• Zweitens ist es grundsätzlich immer anzustreben, so wenig wie möglich von Hand Daten nachträglich zu bearbeiten.

Also mussten wir uns zwei Dinge überlegen:

• Wie können wir die Wünsche des Kunden im Datenmodell abbilden?
• Und wie setzen wir diese Änderungen in der Benutzeroberfläche um, sodass die Eingabe so intuitiv und einfach wie möglich ist – und möglichst wenig Nachbearbeitung erfordert?

Aktuelles Datenmodell

Bevor wir starten, schauen wir uns das aktuelle Datenmodell an. Eigentlich hatte der Kunde die Fahrzeuge und Ausstattungen in jeweils einer Tabelle gespeichert und die Zuordnung der Ausstattungen zu den Fahrzeugen über eine 1:n-Beziehung mit Mehrfachauswahl realisiert. Dies haben wir in einem ersten Schritt, den wir im Beitrag m:n-Daten wie im mehrwertigen Feld selektieren (www.access-im-unternehmen.de/1424) erläutern, in eine echte m:n-Beziehung umgewandelt.

Die Ausgangssituation sieht also wie in Bild 1 aus. Wir sehen zwei Tabellen namens tblFahrzeuge und tblAusstattungen. Diese werden über die Tabelle tblFahrzeugeAusstattungen über jeweils ein Fremdschlüsselfeld verbunden.

Bild 1: Ausgangsdatenmodell

Wir haben außerdem einige Beispieldatensätze hinzugefügt. Diese sehen in den Tabellen in der Datenblattansicht wie in Bild 2 aus.

Bild 2: Beispieldaten und Zuordnung über die m:n-Verknüpfungstabelle

Anpassung des Datenmodells

Der erste Teil der Aufgabe besteht darin, folgende Informationen in das Datenmodell aufzunehmen:

• Soll eine Ausstattung auf dem Preisschild angezeigt werden?
• Wenn sie angezeigt wird, soll sie unter der Kategorie Serienausstattung oder Sonderausstattung erscheinen?

Wenn wir davon ausgehen, dass es nur die beiden Kategorien Serienausstattung oder Sonderausstattungen gibt, könnten wir in einem ersten Schnellschuss zwei Ja/Nein-Felder in der Tabelle tblFahrzeugeAusstattungen unterbringen, die beispielsweise die Namen Serienausstattung und Sonderausstattung tragen.

Aber meistens kommen dem Kunden im Verlauf immer noch weitere Kunden, was sich mit der neuen Funktion noch so alles anstellen lässt – in diesem Fall vielleicht noch eine weitere Kategorie. Außerdem hat die aktuelle Idee einen Haken: Wir können theoretisch festlegen, dass ein Ausstattungsmerkmal gleichzeitig zur Serienausstattung und zur Sonderausstattung gehört, indem wir beide Ja/Nein-Felder anhaken (siehe Bild 3).

Bild 3: Erster Entwurf für die zusätzlichen Informationen

Wir müssten schließlich bereits im Formular verhindern, dass beide Felder angekreuzt werden.

Zusammengefasst mit der Idee, dass neben Serienausstattung oder Sonderausstattung noch eine weitere Kategorie hinzukommen könnte, die im Bericht für das Preisschild aufgeführt werden soll, sollten wir diesen ersten Entwurf also verwerfen.

Die nächste Frage ist: Wird ein Ausstattungsmerkmal in mehr als einer Kategorie auf dem Preisschild erscheinen? Das hört sich wenig sinnvoll an. Ausgehend davon, dass eine Ausstattung nun entweder

• nicht auf dem Preisschild erscheint oder
• unter einer Kategorie wie Serienausstattung, Sonderausstattung oder einer anderen Kategorie erscheint,

können wir den folgenden Entwurf ins Auge fassen: Wir erstellen eine neue Tabelle namens tblAusstattungskategorien mit den Feldern AusstattungskategorieID und Ausstattungskategorie und mit Werten wie Serienausstattung oder Sonderausstattung. Diese sieht in der Entwurfsansicht wie in Bild 4 aus.

Bild 4: Entwurf der Tabelle tblAusstattungskategorien

Der m:n-Verknüpfungstabelle tblFahrzeugeAusstattungen fügen wir ein neues Feld namens PreisschildAusstattungskategorieID hinzu. Warum nennen wir dieses Feld nicht einfach AusstattungskategorieID? Weil wir flexibel bleiben wollen und es vorkommen kann, dass der Kunde noch für eine andere Ausgabeart als Preisschilder festlegen möchte, ob die Ausstattung dort ausgeben werden soll und unter welcher Kategorie.

Das Feld legen wir als Nachschlagefeld an, die ihre Werte aus der Tabelle tblAusstattungskategorien entnimmt. Warum als Nachschlagefeld? Weil so später, wenn wir das Feld in ein Formular ziehen, direkt ein Kombinationsfeld auf Basis dieses Nachschlagefeldes erstellt wird.

Außerdem fügen wir der Tabelle noch ein Ja/Nein-Feld namens Preisschild hinzu. Die Tabelle sieht im Entwurf nun wie in Bild 5 aus.

Bild 5: Entwurf der Tabelle tblFahrzeugeAusstattungen mit den neuen Feldern

Wir stellen außerdem noch die Eigenschaft Beschriftung der Felder wie folgt ein:

• AusstattungID: Ausstattung
• PreisschildAusstattungskategorieID: Kategorie
• AufPreisschild: Auf Preisschild

Diese Beschriftungen werden später bei der Übernahme in das Formular übernommen, sodass wir diese dort nicht nochmals anpassen müssen.

Im Datenmodell spiegeln sich die Erweiterungen nun wie in Bild 6 wieder.

Bild 6: Das Datenmodell mit der Tabelle für die Ausstattungskategorien

Ausstattungen auswählbar machen

Schließlich wandeln wir das Feld AusstattungID noch in ein Nachschlagefeld um. Da wir dafür bereits im oben genannten Beitrag eine Beziehung angelegt haben, fügen wir die Nachschlagefeld-Eigenschaften kurzerhand manuell hinzu.

Dazu aktivieren wir im Tabellenentwurf für das Feld AusstattungID in den Eigenschaften die Registerseite Nachschlagen.

Hier stellen wir als Erstes den Wert der Eigenschaft Steuerelement anzeigen auf Kombinationsfeld ein. Dadurch blendet Access einige weitere Eigenschaften ein, die wir wie in Bild 7 anpassen.

Bild 7: Einstellen des Feldes AusstattungID als Nachschlagefeld

Wichtig ist hier, dass die nachzuschlagenden Daten aus der Tabelle tblAusstattungen nach dem Namen der Ausstattungen sortiert ausgegeben werden.

Ideen für die Benutzeroberfläche

Wie aber wollen wir die Benutzeroberfläche gestalten? Wenn wir schon eine m:n-Verknüpfungstabelle verwenden, wie sie auch beispielsweise bei Bestellungen, Bestellpositionen und Artikeln verwendet wird, können wir diese dann nicht auch so abbilden? Also so, dass wir in einem Hauptformular das Fahrzeug abbilden und im Unterformular in der Datenblattansicht die zugeordneten Ausstattungen? Mit den weiteren Steuerelementen, mit denen wir angeben, ob die Ausstattung auf dem Preisschild erscheinen soll und ob es sich um eine Serien- oder eine Sonderausstattung handelt?

Das wird auf jeden Fall Bestandteil der Lösung sein, aber nicht allein. Wie beginnen jedoch erst einmal mit diesem Teil.

Fahrzeuge und Ausstattungen in Haupt- und Unterformular

Wir beginnen mit dem Unterformular namens sfmFahrzeugeAusstattungenPreisschild, dem wir die Tabelle tblFahrzeugeAusstattungen als Datensatzquelle zuweisen. Den Namen sfmFahrzeugeAusstattungen verwenden wir nicht, weil dieser in der Beispieldatenbank bereits für ein anderes Objekt vergeben ist.

Wir ziehen die drei Felder AusstattungID, PreisschildAusstattungskategorieID und AufPreisschild in den Formularentwurf und stellen die Eigenschaft Standardansicht auf den Wert Datenblatt ein (siehe Bild 8). Danach speichern und schließen wir das Formular.

Bild 8: Das Unterformular sfmFahrzeugeAusstattungenPreisschild

Das Hauptformular erstellen wir direkt im Anschluss. Es soll den Namen frmFahrzeugeAusstattungenPreisschild erhalten und verwendet die Tabelle tblFahrzeuge als Datensatzquelle. Wir ziehen lediglich die beiden Felder FahrzeugID und Fahrzeug in den Detailbereich des Formularentwurfs.

Außerdem ziehen wir dort das Formular sfmFahrzeugeAusstattungenPreisschild aus dem Navigationsbereich als Unterformular hinein.

Wenn wir das Unterformular-Steuerelement markieren und im Eigenschaftenblatt die Seite Daten aktivieren, sollten die beiden Eigenschaften Verknüpfen von und Verknüpfen nach jeweils den Wert FahrzeugID enthalten (siehe Bild 9).

Bild 9: Haupt- und Unterformular in der Entwurfsansicht

Wechseln wir nun in die Formularansicht, sind wir eigentlich schon fertig: Das Hauptformular zeigt die Fahrzeuge an und im Unterformular können wir komfortabel die Ausstattungen auswählen. Die übrigen beiden Steuerelemente erlauben es uns, die Kategorie auszuwählen und ob die Ausstattung auf dem Preisschild erscheinen soll (siehe Bild 10).

Bild 10: Das Formular in Aktion

Problem: Menge der Ausstattungsmerkmale

Allerdings haben wir ein Problem: Die Liste der Ausstattungsmerkmale enthält mehr als 150 Einträge. Das heißt also, dass das Zusammenstellen eines Fahrzeugs, das durchschnittlich vielleicht 30-50 Ausstattungen enthält, ungefähr so viel Spaß macht wie die Steuererklärung.

Selbst wenn Mitarbeiter die Anfangsbuchstaben aller Ausstattungsmerkmale kennen und diese eingeben, dauert es doch noch eine Weile, bis man alle Daten eingegeben hat.

Einfachere Auswahl per Listenfeld

Also müssen wir hier noch nachlegen, um dem Benutzer ein schnelleres und effizienteres Arbeiten zu ermöglichen. Wir wollen dazu zunächst ein Listenfeld hinzufügen, das alle aktuell nicht ausgewählten Ausstattungsmerkmale anzeigt und mit dem wir per Doppelklick weitere Elemente zur Auswahl hinzufügen können.

Also verschieben wir das Unterformular in der Entwurfsansicht etwas nach rechts und fügen links davon ein Listenfeld namens lstAusstattungen hinzu. Dieses soll einfach nur die Ausstattungen in alphabetischer Reihenfolge anzeigen und den Wert des Feldes AusstattungID der Tabelle tblAusstattungen als unsichtbare erste Spalte liefern.

Die anzuzeigenden Ausstattungen hängen jedoch, da wir nur die noch nicht zugeordneten Ausstattungsmerkmale ausgeben wollen, von den Einträgen der Tabelle tblFahrzeugeAusstattungen ab, die mit dem aktuell im Hauptformular angezeigten Fahrzeug verknüpft sind.

Die Datensatzherkunft wollen wir daher per VBA zuweisen. Die dazu verwendete Prozedur heißt AusstattungslisteFuellen. Wir rufen diese zu verschiedenen Gelegenheiten auf – zum Beispiel beim Anzeigen eines neuen Datensatzes im Hauptformular oder nach dem Hinzufügen eines der Ausstattungsmerkmale zur Tabelle tblFahrzeugeAusstattungen.

Da die Abfrage, um die im Listenfeld anzuzeigenden Datensätze zu ermitteln, etwas komplizierter zu formulieren ist, nutzen wir den Abfrageeditor dazu. Wir wollen mit dieser Abfrage alle Datensätze der Tabelle tblAusstattungen liefern, die noch nicht mit einem Datensatz der Tabelle tblFahrzeugeAusstattungen verknüpft sind.

Dazu benötigen wir erst einmal eine Abfrage, die uns alle Ausstattungen liefert, die mit dem aktuellen Fahrzeug verknüpft sind.

Diese enthält die Tabelle tblAusstattungen als Datenquelle. Wir ziehen beide Felder der Tabelle in das Entwurfsraster. Für das Feld AusstattungID legen wir ein Kriterium fest, dass alle Datensätze liefert, deren Wert im Feld AusstattungID noch nicht über die Tabelle tblFahrzeugeAusstattungen mit einem Fahrzeug verknüpft sind. Um welches Fahrzeug es sich handelt, geben wir zunächst über den Parameter [prmFahrzeugID] an (siehe Bild 11).

Bild 11: Abfrage zur Ermittlung der noch nicht verwendeten Ausstattungen

Wechseln wir in die Datenblattansicht, erscheint eine Inputbox für die Eingabe der FahrzeugID des zu untersuchenden Fahrzeugs. Geben wir hier für unsere Beispieldaten den Wert 1 ein, was dem Fahrzeug entspricht, dem wir die Ausstattungen mit den Bezeichnungen Ausstattung 1 und Ausstattung 2 zugeordnet haben, erhalten wir das Abfrageergebnis aus Bild 12.

Bild 12: Die Abfrage qryLstAusstattungen

Die Abfrage funktioniert also schon einmal. Nun wollen wir diese in die Funktion AusstattungslisteFuellen integrieren. Dazu wechseln wir in die SQL-Ansicht der Abfrage und kopieren den SQL-String in die Zwischenablage (siehe Bild 13).

Bild 13: SQL-Ausdruck der Abfrage

Von hier fügen wir die SQL-Anweisung in den VBA-Code der Prozedur AusstattungslisteFuellen ein, die wir im Klassenmodul des Formulars frmFahrzeugeAusstattungenPreisschild einfügen.

Die SQL-Anweisung erleichtern wir um einige unnötige Elemente wie vorangestellte Tabellennamen und Klammern und erhalten dann eine Prozedur wie die folgende:

Private Sub AusstattungslisteFuellen()
     Dim strSQL As String
     Dim lngFahrzeugID As Long
     lngFahrzeugID = Me!FahrzeugID
     strSQL = "SELECT AusstattungID, Ausstattung " _
         & "FROM tblAusstattungen " _
         & "WHERE AusstattungID " _
         & "Not In (" _
         & "  SELECT AusstattungID " _
         & "  FROM tblFahrzeugeAusstattungen " _
         & "  WHERE FahrzeugID = " & lngFahrzeugID _
         & ");"
     Me!lstAusstattungen.RowSource = strSQL
End Sub 

Hier ermitteln wir die ID des aktuell im Formular angezeigten Fahrzeugs und fügen diese dann dort in den SQL-Ausdruck ein, wo sich zuvor der Parameter [prmFahrzeugID] befunden hat.

Schließlich weist die Prozedur die so zusammengesetzte SQL-Anweisung der Eigenschaft Datensatzherkunft des Listenfeldes zu.

Diese Prozedur rufen wir nun beispielsweise in der Ereignisprozedur auf, die durch das Ereignis Form_Current ausgelöst wird:

Private Sub Form_Current()
     Call AusstattungslisteFuellen
End Sub

Danach brauchen wir nur noch für das Listenfeld lstAusstattungen die Eigenschaft Spaltenanzahl auf 2 und Spaltenbreiten auf 0cm einzustellen, damit nicht der Inhalt des Feldes AusstattungID, sondern der des Feldes Ausstattung angezeigt wird.

Damit erhalten wir erst einmal die Ansicht des Formulars aus Bild 14.

Bild 14: Das Listenfeld zeigt die verbleibenden Ausstattungen an.

Ausstattung per Doppelklick hinzufügen

Damit kommen wir zu dem Schritt, der dem Benutzer die Arbeit massiv erleichtern soll: Das Hinzufügen von Ausstattungen aus dem Listenfeld per Doppelklick. Dazu fügen wir dem Listenfeld eine Ereignisprozedur hinzu, die durch das Ereignis Beim Doppelklicken ausgelöst wird (siehe Listing 1).

Private Sub lstAusstattungen_DblClick(Cancel As Integer)
     Dim db As DAO.Database
     Dim strSQL As String
     Dim lngFahrzeugID As Long
     Dim lngAusstattungID As Long
     Set db = CurrentDb
     lngFahrzeugID = Me!FahrzeugID
     lngAusstattungID = Me!lstAusstattungen
     strSQL = "INSERT INTO tblFahrzeugeAusstattungen(FahrzeugID, AusstattungID) VALUES(" & lngFahrzeugID & ", " _
         & lngAusstattungID & ")"
     db.Execute strSQL, dbFailOnError
     Me!sfmFahrzeugeAusstattungenPreisschild.Form.Requery
     Call AusstattungslisteFuellen
End Sub

Listing 1: Anpassung der Prozedur für das Vergrößern und Verkleinern

Diese Prozedur ermittelt zunächst die Werte für die FahrzeugID und die AusstattungID für die Kombination aus Fahrzeug und Ausstattung, die zur Tabelle tblFahrzeugeAusstattungen hinzugefügt werden soll. Dann stellt sie eine SQL-Abfrage wie die folgende zusammen und arbeitet dabei die FahrzeugID und die AusstattungID ein:

INSERT INTO tblFahrzeugeAusstattungen(FahrzeugID, AusstattungID) VALUES(1, 3)

Danach führt sie diese Abfrage mit der Execute-Methode des Database-Objekts für die aktuelle Datenbank aus. Anschließend aktualisiert sie das Unterformular, sodass dieses nun die neu hinzugefügte Ausstattung anzeigt. Außerdem ruft es die Prozedur AusstattungslisteFuellen auf, damit das Listenfeld mit den neuen Daten aktualisiert wird.

Das Ergebnis eines Doppelklicks auf einen Eintrag im Listenfeld sehen wir in Bild 15. Allerdings wurde noch keine Kategorie angelegt und auch das Feld Auf Preisschild wurde noch nicht aktualisiert. Hier haben wir nun folgende Möglichkeiten:

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