![\"Tabellen,](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_001.png\")
<\/p>\nKunden nach bestellten Produkten kann jeder filtern, der sich ein wenig mit dem Abfrageentwurf beschäftigt hat. Etwas aufwendiger ist es schon, ein Formular zu erstellen, dass verschiedene Möglichkeiten zum Filtern von Kunden nach den bestellten Produkten bietet. Hier wollen wir beispielsweise ein Produkt auswählen, sodass direkt alle Kunden in einer Liste angezeigt werden, die dieses Produkt bestellt haben. Oder wir gehen noch einen Schritt weiter und wollen Kunden anzeigen, die mindestens eines von mehreren Produkten geordert haben. Um dann vielleicht noch solche Kunden auszuschließen, die bereits ein bestimmtes anderes Produkt besitzen. Also auf ins Abenteuer!<\/b><\/p>\nWarum Kunden nach Produkten filtern?<\/h2>\n
Bevor wir uns an die Arbeit dieses recht aufwendigen Unterfangens machen, wollen wir uns überlegen, wozu wir das Ergebnis überhaupt nutzen können. Mir als Shopbetreiber fällt da direkt ein, Kunden, die ein bestimmtes Produkt erworben haben, über eine neue Version dieses Produkts zu informieren. Oder man möchte dem Kunden mitteilen, dass eine Lizenz ausläuft, damit er diese verlängern kann. Vielleicht wollen wir auch einfach Kunden, die Produkt A bestellt haben, eine Empfehlung zu dem dazu passenden Produkt B geben. Dabei wollen wir dann natürlich nur Kunden anschreiben, die Produkt B noch nicht bestellt haben. Sie sehen: Es lohnt sich, die Kunden nach den bestellten Produkten selektieren zu können.<\/p>\n
Als Basis für die Ermittlung von Kunden nach den bestellten Produkten verwenden wir die Beispieldatenbank, die wir bereits in der Beitragsreihe zur Rechnungsverwaltung vorgestellt haben. Den ersten Teil dieser Beitragsreihe finden Sie übrigens unter dem Titel Rechnungsverwaltung: Datenmodell <\/b>(www.access-im-unternehmen.de\/1385<\/b>). <\/p>\n
Der relevante Teil des Datenmodells dieser Datenbank ist in Bild 1 abgebildet.<\/p>\n
<\/p>\n
Bild 1: Tabellen, die am Filtern von Kunden nach Produkten beteiligt sind<\/span><\/b><\/p>\n Wir wollen mit dem Formular zum Filtern von Kunden nach bestellten Produkten möglichst flexibel arbeiten können.<\/p>\n Also schauen wir uns zunächst an, welche Filtermöglichkeiten wir abbilden wollen:<\/p>\n Die ersten beiden Filtermöglichkeiten sollen außerdem per Und-Verknüpfung mit den letzten beiden Möglichkeiten kombiniert werden.<\/p>\n Wir wollen zwei Listenfelder im Formular anzeigen, die unterschiedliche Produkte enthalten:<\/p>\n Um die beiden Listenfelder zu füllen, bieten wir ein drittes Listenfeld an, das alle Produkte enthält. Aus diesem wählen wir dann die Produkte aus, die den beiden zuvor genannten Listenfeldern hinzugefügt werden sollen.<\/p>\n Um aus diesem Listenfeld komfortabel die gewünschten Produkte auswählen zu können, wollen wir über diesem Listenfeld noch ein Textfeld einfügen, mit dem wir die angezeigten Produkte nach dem eingegebenen Text filtern können.<\/p>\n Unter den Listenfeldern zur Auswahl der zu filternden Produkte fügen wir schließlich noch ein Unterformular ein, das die Kunden anzeigt, welche die den Listenfeldern hinzugefügten Produkte entweder bestellt haben – oder auch nicht.<\/p>\n Als Erstes legen wir das Unterformular sfmKundenNachProduktenFiltern<\/b> an. Dieses soll die Kunden anzeigen, die den im Hauptformular festgelegten Kriterien entsprechen. Beim Öffnen des Formulars jedoch soll es zunächst alle Kunden anzeigen. Deshalb stellen wir die Eigenschaft Datensatzquelle<\/b> auf eine Abfrage ein, welche die Tabelle tblKunden <\/b>als Datenherkunft verwendet und davon die Felder KundeID<\/b>, AnredeID<\/b>, Nachname<\/b>, Vorname <\/b>und EMail <\/b>anzeigt, sortiert nach den Feldern Nachname <\/b>und Vorname<\/b> (siehe Bild 2).<\/p>\n Bild 2: Abfrage für das Unterformular<\/span><\/b><\/p>\n Anschließend ziehen wir alle Felder dieser Abfrage aus der Feldliste in den Entwurf des Formulars (siehe Bild 3).<\/p>\n Bild 3: Entwurf des Unterformulars<\/span><\/b><\/p>\n Damit dieses Formular seine Daten in der Datenblattansicht anzeigt, stellen wir seine Eigenschaft Standardansicht <\/b>auf den Wert Datenblatt <\/b>ein. Nun schließen und speichern wir das Unterformular.<\/p>\n Dann legen wir ein weiteres neues Formular namens frmKundenNachProduktenFiltern <\/b>an und fügen diesem folgende Steuerelemente hinzu:<\/p>\n Außerdem ziehen wir aus dem Navigationsbereich das Formular sfmKundenNachProduktenFiltern <\/b>in das Hauptformular. Die Steuerelemente ordnen wir dabei wie in Bild 4 an.<\/p>\n Bild 4: Anordnung der Steuerelemente im Hauptformular<\/span><\/b><\/p>\n Das Listenfeld lstAlleProdukte <\/b>soll alle Produkte anzeigen mit Ausnahme derer, die bereits zu einem der Listenfelder lstBestellteProdukte <\/b>oder lstNichtBestellteProdukte <\/b>hinzugefügt wurden.<\/p>\n Das Listenfeld lstBestellteProdukte <\/b>soll alle Produkte anzeigen, die nach Markierung im Listenfeld lstAlleProdukte <\/b>mit der Schaltfläche cmdZuBestelltenProdukten <\/b>hinzugefügt wurden.<\/p>\n Das Listenfeld lstNichtBestellteProdukte <\/b>soll analog alle Produkte anzeigen, die nach Markierung im Listenfeld lstAlleProdukte <\/b>mit der Schaltfläche cmdZuNichtBestelltenProdukten <\/b>hinzugefügt wurden.<\/p>\n Nun müssen wir allerdings noch festlegen, wie wir die Produkte markieren, die in einem der beiden Listenfelder lstBestellteProdukte <\/b>oder lstNichtBestellteProdukte <\/b>angezeigt werden.<\/p>\n Wir könnten die beiden Listenfelder einfach mit dem Wert Wertliste <\/b>für die Eigenschaft Herkunftsart <\/b>ausstatten und die Produkte als String-Liste der Eigenschaft Datensatzherkunft <\/b>hinzufügen. Wenn wir die Produkte in diesen beiden Listen allerdings jederzeit nach dem Produktnamen sortiert anzeigen wollen, haben wir mehr Programmieraufwand. Und da wir in Access arbeiten, legen wir schnell zwei Tabellen namens tblBestellteProdukte <\/b>und tblNichtBestellteProdukte <\/b>an. Die erste sieht in der Entwurfsansicht wie in Bild 5 aus und enthält lediglich ein Zahlenfeld zum Speichern der ProduktID<\/b>-Werte, nach denen die Kunden gefiltert werden sollen. Dieses Feld haben wir als Primärschlüsselfeld definiert, jedoch logischerweise nicht mit dem Datentyp Autowert<\/b>. Die zweite unterscheidet sich nur durch den Namen von der ersten Tabelle. Tipp zum Zeit sparen: Einfach die erste Tabelle kopieren und unter dem Namen tblNichtBestellteProdukte <\/b>speichern.<\/p>\n Bild 5: Entwurf der Tabelle tblBestellteProdukte<\/span><\/b><\/p>\n Die Datensatzherkunft der beiden rechten Listenfelder können wir nun bereits einstellen. Die für das Listenfeld lstBestellteProdukte <\/b>gestalten wir wie in Bild 6. Sie soll alle Datensätze der Tabelle tblProdukte <\/b>anzeigen, deren Wert im Feld ProduktID <\/b>in der Tabelle tblBestellteProdukte <\/b>gespeichert ist. Nachdem Sie die beiden Tabellen zum Abfrageentwurf hinzugefügt haben, müssen Sie die Beziehung zwischen den Feldern ID <\/b>der Tabelle tblProdukte <\/b>und ProduktID <\/b>der Tabelle tblBestellteProdukte <\/b>manuell hinzufügen. Diese Beziehung wollen wir nicht im Datenbankfenster festlegen.<\/p>\n Bild 6: Datensatzherkunft des Listenfeldes lstBestellteProdukte<\/span><\/b><\/p>\n Die Datensatzherkunft für das Listenfeld lstNichtBestellteProdukte <\/b>erstellen wir analog, diesmal verwenden wir jedoch die Tabelle tblNichtBestellteProdukte <\/b>als zweite Tabelle.<\/p>\n Für alle drei Listenfelder nehmen wir nun die Format-Einstellungen vor. Hier legen wir für die Eigenschaft Spaltenanzahl <\/b>den Wert 2 <\/b>und für Spaltenbreiten <\/b>den Wert 0cm <\/b>fest. So zeigen die Listenfelder immer nur die Produktbezeichnung an, aber nicht den Wert der gebundenen Spalte ProduktID<\/b>.<\/p>\n Der Name dieses Listenfeldes ist eigentlich irreführend, denn es zeigt nur direkt nach dem Öffnen des Formulars alle Produkte an. Sobald der Benutzer über das Textfeld txtProduktfilter<\/b> einen Filter eingegeben hat oder eines der Produkte in eines der rechten Listenfelder verschoben hat, zeigt es nicht mehr alle Produkte an.<\/p>\n Es soll dann nur noch die Produkte anzeigen, die nach dem Filtern und dem Entfernen der bereits in einem der übrigen Listenfeldern enthaltenen Produkte übrig bleiben.<\/p>\n Zuerst aber soll dieses Listenfeld einfach alle Produkte anzeigen, die in der Tabelle tblProdukte <\/b>enthalten sind – und zwar nach dem Alphabet sortiert. Dazu weisen wir der Eigenschaft Datensatzherkunft <\/b>des Listenfeldes die Abfrage aus Bild 7 zu.<\/p>\n Bild 7: Datensatzherkunft des Listenfeldes lstAlleProdukte<\/span><\/b><\/p>\n Die enthaltenen Daten sollen anschließend nach verschiedenen Aktionen aktualisiert werden. Bei den Aktionen handelt es sich um die folgenden:<\/p>\n Deshalb rufen wir von all den durch die oben beschriebenen Aktionen Prozeduren auf, welche die Datensatzherkunft des Listenfeldes lstAlleProdukte <\/b>aktualisiert (und später auch die Liste der Kunden).<\/p>\n Diese Prozeduren sollen ProdukteAktualisieren<\/b> und KundenAktualisieren <\/b>heißen.<\/p>\n Die Prozeduren ProdukteAktualisieren<\/b> und KundenAktualisieren <\/b>sollen den im Textfeld txtProduktfilter <\/b>eingegebenen Suchbegriff berücksichtigen und zusätzlich über die in die beiden Tabellen tblBestellteProdukte <\/b>und tblNichtBestellteProdukte <\/b>gespeicherten Einträge die noch verfügbaren Produkte im Listenfeld lstAlleProdukte <\/b>anzeigen. Schließlich soll noch die Anzeige der Kunden passend zu den in den beiden Tabellen gespeicherten Daten aktualisiert werden.<\/p>\n <\/p>\n Da diese Prozeduren sowohl durch das Ereignis Bei Änderung <\/b>des Textfeldes txtProduktfilter <\/b>ausgelöst werden kann als auch durch das Hinzufügen oder Entfernen von Einträgen zu den Tabellen tblBestellteProdukte <\/b>und tblNichtBestellteProdukte<\/b>, haben wir ein kleines Problem: Beim Aufruf über das Textfeld können wir auf den aktuellen Inhalt des Textfeldes nur über die Eigenschaft Text <\/b>des Textfeldes zugreifen. Beim Aufruf über eines der anderen Ereignisse müssen wir die Value<\/b>-Eigenschaft des Textfeldes verwenden.<\/p>\n Wir können das auf verschiedene Arten lösen. Eine ist, den Wert des Textfeldes jeweils in einer Variablen zu speichern, auf die wir dann von der Prozedur ProdukteAktualisieren <\/b>aus zugreifen. Die zweite ist, dass wir den Inhalt der Variablen Text <\/b>beim Aufruf vom Textfeld aus als Parameter übergeben und beim Aufruf von den Schaltflächen beziehungsweise Listenfeldern den Wert über die Eigenschaft Value <\/b>ermitteln und dann ebenfalls per Parameter übergeben.<\/p>\n Da Variablen unter Umständen ihren Wert verlieren können, nutzen wir die letztere Variante.<\/p>\n Die Eingabe beziehungsweise Änderung des Inhalts des Textfeldes txtProduktfilter <\/b>soll die im Listenfeld lstAlleProdukte <\/b>enthaltenen Datensätze filtern. Dazu hinterlegen wir für die Ereigniseigenschaft Bei Änderung <\/b>die folgende Prozedur:<\/p>\n Diese ruft lediglich eine weitere Prozedur namens ProdukteAktualisieren <\/b>auf und übergibt dieser den aktuellen Inhalt des Textfeldes. Diese Prozedur beschreiben wir weiter unten. Erst schauen wir uns weitere Aufrufe dieser Prozedur an.<\/p>\n Wenn der Benutzer wie in Bild 8 einen der Einträge des Listenfeldes lstAlleProdukte <\/b>markiert und dann auf die Schaltfläche cmdZuBestelltenProdukten <\/b>klickt, löst dies das Ereignis Beim Klicken <\/b>der Schaltfläche aus.<\/p>\n Bild 8: Hinzufügen eines Produkts zu den bestellten Produkten<\/span><\/b><\/p>\n Die Prozedur gestalten wir wie in Listing 1. Sie prüft zunächst, ob überhaupt ein Eintrag im Listenfeld lstAlleProdukte <\/b>markiert ist. Falls ja, erstellt sie eine Referenz auf das aktuelle Database<\/b>-Objekt und führt damit eine INSERT INTO<\/b>-Aktionsabfrage aus.<\/p>\n Listing 1: Produkt zum Listenfeld der als Kriterien einzuschließenden Produkte hinzufügen<\/span><\/b><\/p>\n Diese soll den Primärschlüsselwert des aktuell im Listenfeld markierten Eintrags als neuen Datensatz in die Tabelle tblBestellteProdukte <\/b>einfügen. Anschließend erfolgt hier ebenfalls ein Aufruf der bereits weiter oben erwähnten Prozedur ProdukteAktualisieren<\/b>, diesmal mit dem über die Eigenschaft Value <\/b>des Textfeldes txtProduktfilter <\/b>ermitteln Feldwert. Dass dieses Feld einen NULL<\/b>-Wert enthalten kann, fangen wir mit der Nz<\/b>-Funktion ab und übergeben in diesem Fall eine leere Zeichenkette.<\/p>\n Danach folgt noch ein Aufruf der Prozedur KundenAktualisieren<\/b>. Diese erledigt den größten Teil der Arbeit, denn sie stellt die Datensatzquelle für das Datenblatt im Unterformular zusammen. Mehr dazu weiter unten.<\/p>\n Die Prozedur, die durch die weiter unten befindliche Schaltfläche namens cmdZuNichtBestelltenProdukten <\/b>ausgelöst wird, funktioniert recht ähnlich.<\/p>\n Sie verwendet allerdings eine andere INSERT INTO<\/b>-Anweisung und fügt mit dieser den entsprechenden Datensatz zur Tabelle tblNichtBestellteProdukte <\/b>ein (siehe Listing 2).<\/p>\n Listing 2: Produkt zum Listenfeld der auszuschließenden Produkte hinzufügen<\/span><\/b><\/p>\n Auch diese Prozedur ruft anschließend die beiden Prozeduren ProdukteAktualisieren <\/b>und KundenAktualisieren <\/b>auf.<\/p>\n Die nächste Prozedur wird ausgelöst, wenn der Benutzer einen Doppelklick auf einen der Einträge des Listenfeldes lstBestellteProdukte <\/b>ausführt (siehe Bild 9).<\/p>\n Bild 9: Doppelklick auf das Listenfeld lstBestellteProdukte<\/span><\/b><\/p>\n Dies führt zum Aufruf der Prozedur aus Listing 3.<\/p>\n Listing 3: Produkt aus dem Listenfeld der als Kriterien einzuschließenden Produkte entfernen<\/span><\/b><\/p>\n Die Prozedur prüft wiederum, ob der Benutzer nicht auf eine leere Stelle im Listenfeld geklickt hat, und zwar mit der IsNull<\/b>-Funktion.<\/p>\n Ist lstBestellteProdukte <\/b>nicht NULL<\/b>, sorgt die Prozedur dafür, dass der angeklickte Eintrag aus der Tabelle tblBestellteProdukte <\/b>gelöscht wird.<\/p>\n Dazu nutzt sie die Execute<\/b>-Methode des aktuellen Database<\/b>-Objekts und übergibt dieser eine entsprechende DELETE<\/b>-Anweisung.<\/p>\n Anschließend folgen die obligatorischen Aufrufe der Prozeduren ProdukteAktualisieren <\/b>und KundenAktualisieren<\/b>.<\/p>\n Schließlich fehlt noch die Prozedur, die durch einen Doppelklick auf das Listenfeld lstNichtBestellteProdukte <\/b>ausgelöst wird (siehe Listing 4).<\/p>\n Listing 4: Produkt aus dem Listenfeld der als Kriterien auszuschließenden Produkte entfernen<\/span><\/b><\/p>\n Diese führt ähnliche Anweisungen wie die zuvor beschriebene Prozedur aus, entfernt aber einen Datensatz aus der Tabelle tblNichtBestellteProdukte<\/b>.<\/p>\n Damit kommen wir zur Prozedur ProdukteAktualisieren<\/b> (siehe Listing 5). Diese wird immer aufgerufen, wenn wir eine der bisherigen fünf Prozeduren per Ereignis ausgelöst haben – also durch das Ändern des Produktfilters oder durch Hinzufügen oder Entfernen eines Produkts aus einem der beiden Listenfelder lstBestellteProdukte <\/b>oder lstNichtBestellteProdukte<\/b>.<\/p>\n Listing 5: Produkte in der Liste aller nicht zugeordneten Produkte aktualisieren<\/span><\/b><\/p>\n Die Prozedur nimmt mit dem Parameter strProduktfilter <\/b>die im Textfeld txtProduktfilter <\/b>enthaltene Zeichenkette als Filter entgegen.<\/p>\n In einer Stringvariablen namens strSQL <\/b>stellt sie zunächst die Basisabfrage zusammen:<\/p>\n Dann prüft sie, ob der Parameter strProduktfilter<\/b>, als der Wert aus dem Textfeld txtProduktfilter<\/b>, eine Zeichenkette mit mindestens einem Zeichen enthält.<\/p>\n Ist das der Fall, fügt sie einer weiteren Stringvariablen namens strWhere <\/b>ein erstes Kriterium hinzu, das beispielsweise bei dem Wert Access <\/b>im Textfeld txtProduktfilter <\/b>wie folgt aussieht:<\/p>\n Dann fügt sie dieser Zeichenkette noch zwei weitere Kriterien hinzu.<\/p>\n Das erste schließt alle Produkte aus, deren Primärschlüsselwerte in der Tabelle tblBestellteProdukte <\/b>gespeichert sind:<\/p>\n Das nächste Kriterium erledigt das Gleiche für die Einträge der Tabelle tblNichtBestellteProdukte<\/b>:<\/p>\n Danach entfernt sie, da strWhere <\/b>nun auf jeden Fall mit einem Leerzeichen und der Zeichenkette AND <\/b>beginnt, die ersten vier Zeichen aus strWhere <\/b>und hängt den verbleibenden Inhalt von strWhere <\/b>neben dem Schlüsselwort WHERE <\/b>an den Inhalt von strSQL <\/b>an. Damit entsteht nun eine Abfrage wie die folgende:<\/p>\n Diesen Ausdruck weist die Prozedur nun dem Listenfeld lstAlleProdukte <\/b>zu. Außerdem aktualisiert sie die Listenfelder lstBestellteProdukte <\/b>und lstNichtBestellteProdukte<\/b>.<\/p>\n Damit zeigt das Listenfeld lstAlleProdukte <\/b>nur noch diejenigen Datensätze an, die nicht durch den Produktfilter aus dem Textfeld txtProduktfilter <\/b>ausgeschlossen sind oder in einem der beiden Listenfelder lstBestellteProdukte <\/b>oder lstNichtBestellteProdukte <\/b>angezeigt werden.<\/p>\n Die weitaus kompliziertere Aufgabe ist das Zusammenstellen der Datensatzquelle für das Unterformular sfmKundenNachProduktenFiltern<\/b>. Diese Aufgabe erledigt die Prozedur KundenAktualisieren<\/b>, die beim Hinzufügen oder Entfernen eines der Produkte aus den beiden Listenfeldern lstBestellteProdukte <\/b>oder lstNichtBestellteProdukte <\/b>aufgerufen wird (siehe Listing 6).<\/p>\n Listing 6: Kunden nach den in den Steuerelementen festgelegten Kriterien filtern<\/span><\/b><\/p>\n Die Prozedur schreibt zuerst den ersten Teil der als Datensatzquelle zu verwendenden Abfrage in die Variable strSQL<\/b>. Sie lautet:<\/p>\n Danach prüft sie, ob das Listenfeld lstBestellteProdukte <\/b>mindestens einen Eintrag enthält. Ist das der Fall, fügt sie zu einer bis dahin noch leeren Zeichenkette in der Variablen strWhere <\/b>einen Ausdruck hinzu, der wie folgt lautet:<\/p>\n Der Ausdruck enthält einen Kriteriumausdruck, der alle Primärschlüsselwerte derjenigen Datensätze der Tabelle tblBestellungen <\/b>ermittelt, die mit einem Datensatz der Tabelle tblBestellpositionen verknüpft <\/b>sind, der im Feld ProduktID <\/b>eines der Produkte enthält, die im Listenfeld lstBestellteProdukte <\/b>beziehungsweise der Tabelle tblBestellteProdukte <\/b>gespeichert sind. Letztere holen wir wieder als Vergleichswert eines Vergleichsausdrucks mit dem IN<\/b>-Operator.<\/p>\n Anschließend wird es etwas spannender. Während wir mit dem zuvor definierten Kriterium lediglich prüfen, ob der Kunde irgendeines der Produkte aus dem Listenfeld lstBestellteProdukte <\/b>geordert hat, müssen wir nun sicherstellen, dass er keines der auszuschließenden Produkte gekauft hat. Dazu reicht es nicht aus, diese wie zuvor mit einer IN<\/b>-Klausel zu prüfen – wir müssen nun alle auszuschließenden Produkte untersuchen.<\/p>\n Dazu stellen wir eine ganze Reihe von Ausdrücken zusammen, die alle per AND<\/b>-Schlüsselwort verknüpft werden. Dazu erstellt die Prozedur ein Recordset auf Basis der Tabelle tblNichtBestellteProdukte<\/b>.<\/p>\n Die enthaltenen Datensätze durchläuft die Prozedur anschließend in einer Do While<\/b>-Schleife. Darin fügt sie für jeden Datensatz ein neues Kriterium zur Zeichenkette aus strWhere <\/b>hinzu.<\/p>\n Hier stellen wir also nun für jedes auszuschließende Produkt ein Kriterium zusammen, das die KundeID<\/b>-Werte der Tabelle tblBestellungen <\/b>liefert, denen über die Tabelle tblBestellpositionen <\/b>eine der auszuschließenden ProduktID<\/b>-Werte zugewiesen wurde. Und diese sollen mit der übergeordneten Abfrage genau nicht geliefert werden, also negieren wir die Bedingung mit ID NOT IN (…)<\/b> – hier am Beispiel der ProduktID <\/b>mit dem Wert 123<\/b>:<\/p>\n Nach dem durchlaufen aller Datensätze haben wir das Kriterium in strWhere <\/b>zusammengestellt und können das führende AND<\/b>-Schlüsselwort durch WHERE <\/b>ersetzen. Außerdem hängen wir den gesamten Ausdruck an die Abfrage aus strSQL <\/b>an.<\/p>\n Schließlich weisen wir die resultierende Abfrage der Eigenschaft RecordSource <\/b>des Formulars im Unterformular-Steuerelement zu.<\/p>\n Danach können wir den Filter ausprobieren. Um alle Kunden zu finden, die zwar das Magazin Access im Unternehmen <\/b>abonniert, aber noch keines der im unteren Listenfeld aufgeführten Bücher gekauft haben, stellen wir die Listenfelder wie in Bild 10 ein und erhalten das entsprechende Ergebnis.<\/p>\n Bild 10: Gefilterte Datensätze<\/span><\/b><\/p>\n Die Inhalte der beiden Listenfelder lstBestellteProdukte <\/b>und lstNichtBestellteProdukte <\/b>werden in den Tabellen tblBestellteProdukte <\/b>und tblNichtBestellteProdukte <\/b>gespeichert.<\/p>\n Wir müssen uns noch überlegen, ob wir die so gespeicherten Kriterien beibehalten wollen, wenn der Benutzer das Formular schließt. So können wir die zuletzt getroffenen Kriterien beim Öffnen des Formulars wiederherstellen. Alternativ leeren wir die Kriterien mit jedem Schließen-Vorgang.<\/p>\n Wir entscheiden uns für eine Lösung, die das schnelle leeren der Kriterien-Listenfelder erlaubt und mit der wir, falls der Benutzer die Kriterien nicht löscht, die Kriterien beim Öffnen wiederherstellen könnten.<\/p>\n Damit die nicht gelöschten Kriterien beim nächsten Öffnen des Formulars wiederhergestellt werden, brauchen wir nur beim Öffnen die beiden Prozeduren ProdukteAktualisieren <\/b>und KundenAktualisieren <\/b>aufzurufen. Das erledigen wir in der durch das Ereignis Beim Laden <\/b>ausgelösten Ereignisprozedur. Der Prozedur ProdukteAktualisieren <\/b>übergeben wir eine leere Zeichenkette, da das Textfeld txtProduktfilter <\/b>zu diesem Zeitpunkt ohnehin noch leer ist:<\/p>\n Fehlt noch die Schaltfläche, mit welcher der Benutzer die beiden Listenfeldern lstBestellteProdukte <\/b>und lstNichtBestellteProdukte <\/b>leeren kann.<\/p>\n Diese erstellt wieder eine Referenz auf das aktuelle Database<\/b>-Objekt und nutzt dieses, um mit der Execute<\/b>-Methode zwei DELETE<\/b>-Abfragen zum Leeren der Tabellen tblBestellteProdukte <\/b>und tblNichtBestellteProdukte <\/b>abzusetzen. Außerdem leert sie das Textfeld txtProduktfilter <\/b>und aktualisiert anschließend per Aufruf der beiden Prozeduren ProdukteAktualisieren <\/b>und KundenAktualisieren <\/b>die Inhalte der Listenfelder und des Unterformulars:<\/p>\n Das Hauptformular zeigt aktuell noch Datensatzmarkierer et cetera an. Daher stellen wir die Eigenschaften Datensatzmarkierer<\/b>, Navigationsschaltflächen<\/b>, Bildlaufleisten <\/b>und Trennlinien <\/b>auf den Wert Nein <\/b>ein sowie Automatisch zentrieren <\/b>auf den Wert Ja<\/b>.<\/p>\n Gegebenenfalls stellt man noch den Wert der beiden Eigenschaften Horizontaler Anker <\/b>und Vertikaler Anker <\/b>auf den Wert Beide <\/b>ein, damit das Unterformular beim Vergrößern des Hauptformulars ebenfalls vergrößert wird.<\/p>\n Mit der hier vorgestellten Lösung können Sie die Kunden herausfinden, die kein, ein oder mehrere Produkte bestellt haben und kein, ein oder mehrere Produkte noch nicht bestellt haben. Damit lassen sich gezielt Listen von Kunden ermitteln, denen man ein bestimmtes Produkt vorstellen möchte.<\/p>\n Eine denkbare Erweiterung ist eine Schaltfläche, mit der sich die Daten in eine neue Tabelle exportieren lassen oder auch in eine .csv<\/b>-Datei. Von dort aus kann man die Daten dann weiterverarbeiten.<\/p>\n Enthaltene Beispieldateien:<\/p>\n KundenNachBestelltenProdukten.accdb<\/p>\nGeplante Funktionen für die Lösung<\/h2>\n
\n
Einfache Auswahl der Produkte, nach denen gefiltert werden soll<\/h2>\n
\n
Unterformular zur Anzeige der Kunden<\/h2>\n
![\"Abfrage](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_002.png\")
<\/p>\n![\"Entwurf](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_003.png\")
<\/p>\nHauptformular zum Filtern der Kunden<\/h2>\n
\n
![\"Anordnung](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_004.png\")
<\/p>\nListenfelder mit Daten füllen<\/h2>\n
Tabellen zum Speichern der ein- und auszuschließenden Produkte<\/h2>\n
![\"Entwurf](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_005.png\")
<\/p>\nDatensatzherkunft der Listenfelder lstBestellteProdukte und lstNichtBestellteProdukte<\/h2>\n
![\"Datensatzherkunft](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_006.png\")
<\/p>\nWeitere Einstellungen für die Listenfelder<\/h2>\n
Daten des Listenfeldes lstAlleProdukte<\/h2>\n
![\"Datensatzherkunft](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_007.png\")
<\/p>\n\n
Prozeduren zum Aktualisieren von Produkten und Kunden<\/h2>\n
Nach Produktname filtern<\/h2>\n
Private Sub <\/span>txtProduktfilter_Change()\r\n ProdukteAktualisieren Me!txtProduktfilter.Text\r\nEnd Sub<\/span><\/pre>\nHinzufügen von Produkten als einschließende Kriterien für den Kundenfilter<\/h2>\n
![\"Hinzufügen](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_008.png\")
<\/p>\nPrivate Sub <\/span>cmdZuBestelltenProdukten_Click()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n If <\/span>Not<\/span> IsNull(Me!lstAlleProdukte) Then<\/span>\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"INSERT INTO tblBestellteProdukte(ProduktID) VALUES(\" & Me!lstAlleProdukte & \")\", dbFailOnError\r\n ProdukteAktualisieren Nz(Me!txtProduktfilter)\r\n KundenAktualisieren\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\nHinzufügen von Produkten als ausschließende Kriterien für den Kundenfilter<\/h2>\n
Private Sub <\/span>cmdZuNichtBestelltenProdukten_Click()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n If <\/span>Not<\/span> IsNull(Me!lstAlleProdukte) Then<\/span>\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"INSERT INTO tblNichtBestellteProdukte(ProduktID) VALUES(\" & Me!lstAlleProdukte & \")\", dbFailOnError\r\n ProdukteAktualisieren Nz(Me!txtProduktfilter)\r\n KundenAktualisieren\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\nEntfernen von Produkten als einschließende Kriterien für den Kundenfilter<\/h2>\n
![\"Doppelklick](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_009.png\")
<\/p>\nPrivate Sub <\/span>lstBestellteProdukte_DblClick(Cancel As Integer<\/span>)\r\n Dim <\/span>db As <\/span>DAO.Database\r\n If <\/span>Not<\/span> IsNull(Me!lstBestellteProdukte) Then<\/span>\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"DELETE FROM tblBestellteProdukte WHERE ProduktID = \" & Me!lstBestellteProdukte, dbFailOnError\r\n ProdukteAktualisieren Nz(Me!txtProduktfilter)\r\n KundenAktualisieren\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\nEntfernen von Produkten als ausschließende Kriterien für den Kundenfilter<\/h2>\n
Private Sub <\/span>lstNichtBestellteProdukte_DblClick(Cancel As Integer<\/span>)\r\n Dim <\/span>db As <\/span>DAO.Database\r\n If <\/span>Not<\/span> IsNull(Me!lstNichtBestellteProdukte) Then<\/span>\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"DELETE FROM tblNichtBestellteProdukte WHERE ProduktID = \" & Me!lstNichtBestellteProdukte, dbFailOnError\r\n ProdukteAktualisieren Nz(Me!txtProduktfilter)\r\n KundenAktualisieren\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\nAktualisieren der Produkte im Listenfeld lstAlleProdukte<\/h2>\n
Private Sub <\/span>ProdukteAktualisieren(strProduktfilter As String<\/span>)\r\n Dim <\/span>strSQL As String<\/span>\r\n strSQL = \"SELECT ID, Produktbezeichnung FROM tblProdukte\" _\r\n & \" WHERE ID NOT IN (SELECT ProduktID FROM tblBestellteProdukte)\" _\r\n & \" AND ID NOT IN (SELECT ProduktID FROM tblNichtBestellteProdukte)\"\r\n If <\/span>Len<\/span>(strProduktfilter) > 0 Then<\/span>\r\n strSQL = strSQL & \" AND Produktbezeichnung LIKE ''*\" & strProduktfilter & \"*''\"\r\n End If<\/span>\r\n Debug.Print<\/span> strSQL\r\n Me!lstAlleProdukte.RowSource = strSQL\r\n Me!lstBestellteProdukte.Requery\r\n Me!lstNichtBestellteProdukte.Requery\r\nEnd Sub<\/span><\/pre>\nSELECT ID, Produktbezeichnung FROM tblProdukte<\/pre>\n
AND Produktbezeichnung LIKE ''*Access*''<\/pre>\n
AND ID NOT IN (SELECT ProduktID FROM tblBestellteProdukte)<\/pre>\n
AND ID NOT IN (SELECT ProduktID FROM tblNichtBestellteProdukte)<\/pre>\n
SELECT ID, Produktbezeichnung FROM tblProdukte WHERE Produktbezeichnung LIKE ''*Access*'' AND ID NOT IN (SELECT ProduktID FROM tblBestellteProdukte) AND ID NOT IN (SELECT ProduktID FROM tblNichtBestellteProdukte)<\/pre>\n
Aktualisieren des Unterformulars zur Anzeige der Kunden<\/h2>\n
Private Sub <\/span>KundenAktualisieren()\r\n Dim <\/span>strSQL As String<\/span>\r\n Dim <\/span>strWhere As String<\/span>\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rst As <\/span>DAO.Recordset\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rst = db.OpenRecordset(\"SELECT ProduktID FROM tblNichtBestellteProdukte\", dbOpenDynaset)\r\n strSQL = \"SELECT DISTINCT ID, AnredeID, Nachname, Vorname, EMail FROM tblKunden\"\r\n If <\/span>Not<\/span> Me!lstBestellteProdukte.ListCount = 0 Then<\/span>\r\n strWhere = \" AND ID IN (SELECT tblBestellungen.KundeID FROM tblBestellungen INNER JOIN tblBestellpositionen \" _\r\n & \"ON tblBestellungen.ID = tblBestellpositionen.BestellungID WHERE tblBestellpositionen.ProduktID IN \" _\r\n & \"(SELECT ProduktID FROM tblBestellteProdukte))\"\r\n End If<\/span>\r\n Do While<\/span> Not<\/span> rst.EOF\r\n strWhere = strWhere & \" AND ID NOT IN (SELECT tblBestellungen.KundeID FROM tblBestellungen \" _\r\n & \"INNER JOIN tblBestellpositionen ON tblBestellungen.ID = tblBestellpositionen.BestellungID \" _\r\n & \"WHERE tblBestellpositionen.ProduktID = \" & rst!ProduktID & \")\"\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n If <\/span>Not<\/span> Len<\/span>(strWhere) = 0 Then<\/span>\r\n strWhere = Mid<\/span>(strWhere, 5)\r\n strSQL = strSQL & \" WHERE \" & strWhere\r\n End If<\/span>\r\n Me!sfmKundenNachProduktenFiltern.Form.RecordSource = strSQL\r\nEnd Sub<\/span><\/pre>\nSELECT DISTINCT ID, AnredeID, Nachname, Vorname, EMail FROM tblKunden<\/pre>\n
AND ID IN (\r\n SELECT tblBestellungen.KundeID \r\n FROM tblBestellungen \r\n INNER JOIN tblBestellpositionen \r\n ON tblBestellungen.ID = tblBestellpositionen.BestellungID \r\n WHERE tblBestellpositionen.ProduktID IN (\r\n SELECT ProduktID FROM tblBestellteProdukte\r\n )\r\n)<\/pre>\n
Kriterium für nicht bestellte Produkte zusammenstellen<\/h2>\n
AND ID NOT IN (\r\n SELECT tblBestellungen.KundeID \r\n FROM tblBestellungen \r\n INNER JOIN tblBestellpositionen \r\n ON tblBestellungen.ID = tblBestellpositionen.BestellungID \r\n WHERE tblBestellpositionen.ProduktID = 123\r\n)<\/pre>\n
Filter ausprobieren<\/h2>\n
![\"Gefilterte](\"..\/fileadmin\/_temp_\/2022_06\/pic_1408_010.png\")
<\/p>\nKriterien löschen oder behalten?<\/h2>\n
Kriterien und Auswahl beim Öffnen wiederherstellen<\/h2>\n
Private Sub <\/span>Form_Load()\r\n ProdukteAktualisieren \"\"\r\n KundenAktualisieren\r\nEnd Sub<\/span><\/pre>\nAuswahl in den Listenfeldern aufheben<\/h2>\n
Private Sub <\/span>cmdAllesZuruecksetzen_Click()\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"DELETE FROM tblBestellteProdukte\", dbFailOnError\r\n db.Execute \"DELETE FROM tblNichtBestellteProdukte\", dbFailOnError\r\n Me!txtProduktfilter = \"\"\r\n ProdukteAktualisieren \"\"\r\n KundenAktualisieren\r\nEnd Sub<\/span><\/pre>\nLetzte Arbeiten<\/h2>\n
Zusammenfassung und Ausblick<\/h2>\n
Downloads zu diesem Beitrag<\/h2>\n