Home > Artikel > Ausgabe 2/2018 > Lookup-Daten löschen

Lookup-Daten löschen

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).

Wenn Sie mit Lookup-Daten arbeiten und dem Benutzer erlauben, Daten in die Lookup-Tabelle wie etwa eine Tabelle zum Verwalten von Kategorien einzugeben, sollten Sie auch eine Löschen-Funktion für diese Daten bereitstellen. Benutzer sind nämlich schnell übereifrig und legen ähnliche und somit unter Umständen redundante Daten an. Wenn dann fleißig Datensätzen den redundanten Lookupwerten zugeordnet wurden, ist guter Rat teuer: Wie die Dubletten entfernen, und was geschieht mit den bereits verknüpfte Daten? Dieser Artikel bringt Lichts ins Dunkel.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1802_LookupLoeschen.accdb.

Ausgangslage

Wir beschäftigen uns in diesem Artikel mit den beiden Tabellen tblArtikel und tblKategorien (siehe Bild 1). Zu jedem Artikel kann der Benutzer eine Kategorie auswählen. Um das flexibel zu gestalten, soll der Benutzer eigene Kategorien eingeben können – und zwar, indem er diese einfach in das Kombinationsfeld zur Auswahl der Kategorien einträgt und eine kurze Bestätigung nach Eingabe eines neuen Eintrags bestätigt. Dabei geschieht es dann, dass der Benutzer eine neue Kategorie anlegt – wie etwa Bier –, aber übersieht, dass es ja bereits eine Kategorie namens Getränke gibt, in die der neue Artikel Pilsener Quellbräu aufgenommen werden kann. In diesem Fall soll der Benutzer später seinen Fehler korrigieren können, indem er die falsch angelegte Kategorie wieder löscht. Sollten jedoch bereits Datensätze der Tabelle tblArtikel mit den zu löschenden Datensätzen aus der Tabelle tblKategorien angelegt sein, gelingt dies nicht so einfach: Wir haben nämlich referenzielle Integrität für die Beziehung zwischen den beiden Tabellen definiert, und zwar ohne Löschweitergabe. Das heißt, dass wir einen Datensatz aus der Tabelle tblKategorien, der mit mindestens einem Datensatz der Tabelle tblArtikel verknüpft ist, nicht einfach löschen können. Oder andersherum formuliert: Wir können nur leere Kategorien löschen.

Tabellen der Beispieldatenbank

Bild 1: Tabellen der Beispieldatenbank

Damit hat der Benutzer eine Aufgabe vor der Brust: Er muss nämlich alle Datensätze der Tabelle tblArtikel ausfindig machen, die mit der zu löschenden Kategorie verknüpft sind und diese mit einer anderen Kategorie verknüpfen und kann erst dann die überflüssige Kategorie löschen. Dies wollen wir im folgenden etwas einfacher gestalten.

Beispielformular

Ausgangspunkt dafür ist das Formular tblArtikeldetails, das im Entwurf wie in Bild 2 aussieht und über die Eigenschaft Datenherkunft an die Tabelle tblArtikel gebunden wird. Da das Fremdschlüsselfeld KategorieID gleich im Tabellenentwurf als Nachschlagefeld ausgelegt wurde, erscheint es nach dem Hineinziehen der Felder aus der Feldliste in den Detailbereich gleich als Kombinationsfeld (übrigens genau wie das Feld LieferantID).

Entwurf des Formulars

Bild 2: Entwurf des Formulars

Wenn wir dann allerdings in die Formularansicht wechseln, erhalten wir die Fehlermeldung aus Bild 3. Kein Wunder: Der Eintrag ist in der Liste nicht bekannt.

Im Standardzustand bewirkt das Einfügen einer neuen Kategorie diesen Fehler.

Bild 3: Im Standardzustand bewirkt das Einfügen einer neuen Kategorie diesen Fehler.

Neuen Eintrag hinzufügen

Das ändern wir durch die Ereignisprozedur Bei nicht in Liste des Kombinationsfeldes, das wir zuvor in cboKategorieID umbenennen. Die Prozedur sieht wie in Listing 1 aus und liefert mit dem Parameter NewData den Wert des vom Benutzer neu hinzugefügten Eintrags. Die Prozedur fragt den Benutzer, ob die angegebene Kategorie neu in die Liste der Kategorien aufgenommen werden soll. Falls ja, führt sie eine INSERT INTO-Abfrage aus, welche einen entsprechenden neuen Datensatz zur Tabelle tblKategorien hinzufügt. In diesem Fall stellen wir den Wert des Rückgabeparameters Response auf acDataErrAdded ein, was Access mitteilt, dass ein neuer Eintrag hinzugefügt wurde.

Private Sub cboKategorieID_NotInList(NewData As String, Response As Integer)

     Dim intResult As VbMsgBoxResult

     Dim db As DAO.Database

     intResult = MsgBox("Möchten Sie die Kategorie '" & NewData & "' hinzufügen?", vbYesNo, "Neue Kategorie")

     If intResult = vbYes Then

         Set db = CurrentDb

         db.Execute "INSERT INTO tblKategorien(Kategoriename) VALUES('" & NewData & "')", dbFailOnError

         Response = acDataErrAdded

     End If

End Sub

Listing 1: Hinzufügen einer neuen Kategorie zur Tabelle tblKategorien

Danach haben wir also eine neue Kategorie, die gegebenenfalls überflüssig ist, weil sie schon durch eine andere Kategorie abgedeckt wird, und der Benutzer fügt fleißig neue Artikel hinzu und weist sie dieser Kategorie zu. Nun benötigen wir Elemente für die Benutzeroberfläche, mit denen wir die Einträge der Tabelle tblKategorien verwalten können und dies es auch erlauben, den Artikeln mit einer zu löschenden Kategorie auf einfache Weise die richtige Kategorie zuzuweisen. Wir aber erledigen wir das auf effiziente Weise?

Die übersichtlichste Methode ist die Anzeige eines neuen Formulars, dass die zu löschende Kategorie samt der verknüpfte Datensätze der Tabelle tblArtikel anzeigt. Außerdem sollte dieses Formular noch die Möglichkeit bieten, vor dem Löschen eine alternative Kategorie für die betroffenen Artikel auszuwählen – und das gegebenenfalls noch nach vorheriger Selektion der Artikel, um diese auch mehreren verschiedenen Kategorien zuordnen zu können.

Aufruf des Formulars zum Bearbeiten der Kategorien

Damit der Benutzer dieses Formular aufrufen kann, fügen wir dem Formular frmArtikeldetails eine Schaltfläche neben dem Kombinationsfeld cboKategorieID hinzu (siehe Bild 4). Die Schaltfläche heißt cmdKategorieBearbeiten und soll die folgende Ereignisprozedur auslösen:

Schaltfläche zum Bearbeiten der Kategorien

Bild 4: Schaltfläche zum Bearbeiten der Kategorien

Private Sub cmdKategorieBearbeiten_Click()

DoCmd.OpenForm _

"frmKategorienZuweisen", _

WindowMode:=acDialog, _

OpenArgs:=Me!cboKategorieID

End Sub

Die Prozedur öffnet das Formular frmKategorienZuweisen als modalen Dialog und übergibt die ID der aktuell im Kombinationsfeld cboKategorieID ausgewählten Kategorie als Öffnungsargument an das geöffnete Formular.

Formular zum Zuordnen und Löschen von Kategorien

Das Formular, mit dem wir überflüssige Kategorien loswerden wollen, heißt frmKategorienZuweisen und sieht im Entwurf wie in Bild 5 aus. Das obere Kombinationsfeld soll die Kategorie anzeigen, die im Formular frmArtikeldetails ausgewählt war, als der Benutzer auf die Schaltfläche mit den drei Punkten geklickt hat.

Formular zum Zuweisen und Löschen von Kategorien

Bild 5: Formular zum Zuweisen und Löschen von Kategorien

Dazu weisen wir dem Kombinationsfeld als Datensatzherkunft zunächst eine Abfrage auf Basis der Tabelle tblKategorien zu, die wie folgt lautet:

SELECT KategorieID, Kategoriename FROM tblKategorien ORDER BY Kategoriename;

Außerdem stellen wir die Eigenschaft Spaltenanzahl auf den Wert 2 und Spaltenbreiten auf 0cm ein, damit nur die zweite Spalte mit der Bezeichnung der Kategorie im Kombinationsfeld angezeigt wird.

Damit die Kategorie, deren KategorieID als Öffnungsargument des Formulars übergeben wurde, im Kombinationsfeld erscheint, bestücken wir die durch das Ereignis Beim Laden des Formulars ausgelöste Ereignisprozedur wie folgt:

Private Sub Form_Load()

Me!cboAktuelleKategorieID = Me.OpenArgs

End Sub

Da das Formular nicht an eine Datenherkunft gebunden wird, stellen wir die Eigenschaften Navigationsleiste, Datensatzmarkierer, Trennlinien und Bildlaufleisten auf den Wert Nein und Automatisch zentrieren auf Ja ein. Wenn Sie das Formular nun für eine der Kategorien öffnen, sollte dies wie in Bild 6 aussehen.

Formular mit der zu bearbeitenden Kategorie

Bild 6: Formular mit der zu bearbeitenden Kategorie

Das zweite Kombinationsfeld statten wir fast mit der gleichen Datensatzherkunft wie das erste Kombinationsfeld aus – mit einem Unterschied: Die im ersten Kombinationsfeld angezeigte Kategorie soll dort nicht erhalten sein. Es macht ja auch keinen Sinn, die Artikel einer Kategorie der gleichen Kategorie zuzuweisen.

Da wir aber zur Entwurfszeit noch nicht wissen, welcher Datensatz im ersten Kombinationsfeld ausgewählt wird, stellen wir die Datensatzherkunft des zweiten Kombinationsfeldes auch erst beim Laden des Formulars ein. Dazu erweitern wir die Prozedur Form_Load wie in Listing 2.

Private Sub Form_Load()

     Me!cboAktuelleKategorieID = Me.OpenArgs

     Me!cboZielkategorieID.RowSource = "SELECT KategorieID, Kategoriename FROM tblKategorien WHERE KategorieID NOT IN (" _

         & Me.OpenArgs & ") ORDER BY Kategoriename"

     Me!cboZielkategorieID = Me.cboZielkategorieID.ItemData(0)

End Sub

Listing 2: Aktionen beim Laden des Formulars frmKategorienZuweisen

Die erste Anweisung kennen Sie bereits, die zweite stellt eine SQL-Anweisung zusammen, die alle Datensätze der Tabelle tblKategorien außer der mit Me.OpenArgs gelieferten Kategorie enthält. Für die Kategorie mit der ID 9 sieht die Abfrag beispielsweise so aus:

SELECT KategorieID, Kategoriename

FROM tblKategorien

WHERE KategorieID NOT IN (9)

ORDER BY Kategoriename

Die dritte Anweisung stellt das Kombinationsfeld cboZielkategorieID auf den ersten Eintrag der Datensatzherkunft ein. Auch für das Kombinationsfeld cboZielkategorieID stellen wie die Eigenschaften Spaltenanzahl auf 2 und Spaltenbreiten auf 0cm ein.

Füllen des Listenfeldes

Nun wollen wir noch das Listenfeld namens lstZugeordneteArtikel mit den Datensätzen der Tabelle tblArtikel bestücken, die aktuell mit der im ersten Kombinationsfeld cboAktuelleKategorieID angezeigten Kategorie verknüpft sind. Das erledigen wir initial ebenfalls in der Prozedur Form_Load. Das Listenfeld soll nur die Artikelnamen anzeigen, also stellen wir die Eigenschaft Spaltenanzahl auf 2 und die Eigenschaft Spaltenbreiten auf 0cm ein. Der Prozedur Form_Load fügen wir am Ende die folgende Zeile hinzu:

Me!lstZugeordneteArtikel.RowSource = "SELECT ArtikelID, Artikelname FROM tblArtikel WHERE KategorieID = " & Me.OpenArgs & " ORDER BY Artikelname"

Wenn wir das Formular nun über die Schaltfläche cmdKategorieBearbeiten des Formulars frmArtikeldetails öffnen, sieht dieses wie in Bild 7 aus.

Alle notwendigen Daten sind nun verfügbar.

Bild 7: Alle notwendigen Daten sind nun verfügbar.

Daten aktualisieren

Nun könnte es sein, dass der Benutzer noch andere Kategorien als die aktuell im oberen Kombinationsfeld dargestellte entfernen möchte. Dann müssen wir auch das zweite Kombinationsfeld und das Listenfeld an die neue Auswahl anpassen. Also legen wir eine Prozedur an, die durch das Ereignis Nach Aktualisierung des Kombinationsfeldes cboAktuelleKategorieID ausgelöst wird. Nach dem Aufruf dieser Prozedur sollte das Formular dann etwa wie in Bild 8 aussehen.

Formular nach dem Wechsel zu einer anderen Kategorie

Bild 8: Formular nach dem Wechsel zu einer anderen Kategorie

Die Prozedur ermittelt zunächst den Primärschlüsselwert der im ersten Kombinationsfeld angezeigten Kategorie und speichert diese in der Variablen lngKategorie. Dann führt sie drei Anweisungen aus, die den hinteren drei Anweisungen der Ereignisprozedur Form_Load sehr ähneln.

Der einzige Unterschied ist, dass hier kein Bezug auf die mit dem Öffnungsargument übermittelte Kategorie mehr genommen wird, sondern auf die durch den Benutzer im Kombinationsfeld cboAktuelleKategorieID ausgewählte Kategorie. Dementsprechend zeigt das zweite Kombinationsfeld nun alle Datensätze der Tabelle tblKategorien an, die nicht im Kombinationsfeld cboAktuelleKategorieID ausgewählt sind und das Listenfeld liefert nun alle Artikel, die zu der aktuell im Kombinationsfeld cboAktuelleKategorieID angezeigten Kategorie gehören (siehe Listing 3).

Private Sub cboAktuelleKategorieID_AfterUpdate()

     Dim lngAktuelleKategorieID As Long

     lngAktuelleKategorieID = Me!cboAktuelleKategorieID

     Me!cboZielkategorieID.RowSource = "SELECT KategorieID, Kategoriename FROM tblKategorien WHERE KategorieID NOT IN (" _

         & lngAktuelleKategorieID & ") ORDER BY Kategoriename"

     Me!cboZielkategorieID = Me!cboZielkategorieID.ItemData(0)

     Me!lstZugeordneteArtikel.RowSource = "SELECT ArtikelID, Artikelname FROM tblArtikel WHERE KategorieID = " _

         & lngAktuelleKategorieID & " ORDER BY Artikelname"

End Sub

Listing 3: Aktualisierung des ersten Kombinationsfeldes

Bevor wir weitergehen, prüfen wir, ob wir nicht schon dieser Stelle eine Vereinfachung des Codes vornehmen können. Das ist in der Tat der Fall: Die drei hinteren Zeilen der Prozeduren Form_Load und cboAktuelleKategorie_AfterUpdate unterscheiden sich nur durch einen Parameter.

Also vereinfachen wir diesen Code, indem wir die drei Zeilen in eine neue Prozedur packen, diese mit einem Parameter versehen und einen Aufruf dieser Prozedur in die ursprünglichen Routinen einbauen. Die Prozedur SteuerelementeAktualisieren sieht nun wie in Listing 4 aus. Damit können wir die Prozedur Form_Load wie folgt anpassen:

Private Sub SteuerelementeAktualisieren(lngAktuelleKategorieID As Long)

     Me!cboZielkategorieID.RowSource = "SELECT KategorieID, Kategoriename FROM tblKategorien WHERE KategorieID NOT IN (" _

         & lngAktuelleKategorieID & ") ORDER BY Kategoriename"

     Me!cboZielkategorieID = Me!cboZielkategorieID.ItemData(0)

     Me!lstZugeordneteArtikel.RowSource = "SELECT ArtikelID, Artikelname FROM tblArtikel WHERE KategorieID = " _

         & lngAktuelleKategorieID & " ORDER BY Artikelname"

End Sub

Listing 4: Ausgelagerte Anweisungen zur Aktualisierung der Steuerelemente

Private Sub Form_Load()

Me!cboAktuelleKategorieID = Me.OpenArgs

SteuerelementeAktualisieren Me.OpenArgs

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!