Home > Artikel > Ausgabe 6/2017 > Suche in Lookupfeldern

Suche in Lookupfeldern

  PDF ansehen

  Download PDF und Beispieldatenbank

Die Suche nach Datensätzen mit bestimmten Werten in Text- oder Zahlenfeldern haben wir in Acces [basics] bereits bearbeitet. Aber was ist, wenn es um Kombinationsfelder geht, welche die Daten aus einer Lookup-Tabelle anzeigen? So wie im einfachsten Fall bei den Anreden einer Kundentabelle? Dann werden Sie in der Regel erstmal nicht an die Eingabe eines Suchetextes denken, sondern dem Benutzer die Möglichkeit geben wollen, den gesuchten Datensatz auch im Suchfeld per Kombinationsfeld auszuwählen. Dieser Artikel zeigt, wie das gelingt.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1706_Lookupsuche.accdb.

Artikel nach Kategorie durchsuchen

Die Datenblattansicht von Access bietet seit einigen Access-Versionen die Möglichkeit, über den Pfeil nach unten ein Menü zu öffnen, mit dem Sie alle Einträge des aktuellen Feldes anhaken und damit die Suche nach Datensätzen mit diesem Inhalt im entsprechenden Feld starten können (siehe Bild 1).

Eingebaute Suche nach einem Eintrag

Bild 1: Eingebaute Suche nach einem Eintrag

Das Ergebnis entspricht dann wie in Bild 2 genau den Erwartungen. Was aber nun, wenn wir diese Suche nicht anbieten können oder wollen und eine eigene Suche implementieren müssen, die so ähnlich arbeitet – und in diesem Fall zumindest ein Kombinationsfeld anbieten soll, um die passenden Datensätze zu ermitteln?

Suchergebnis mit eingebauter Suche

Bild 2: Suchergebnis mit eingebauter Suche

Dann benötigen wir zunächst ein Formular, welches das Datenblatt mit den zu durchsuchenden Datensätzen in einem Unterformular anzeigt. Dazu erstellen Sie zunächst ein Unterformular namens sfmLookupsuche und weisen der Eigenschaft Datenherkunft die Tabelle tblArtikel zu.

Ziehen Sie dann alle Felder dieser Tabelle aus der Feldliste in den Detailbereich des Formularentwurfs und stellen Sie noch die Eigenschaft Standardansicht auf den Wert Datenblatt ein. Speichern und schließen Sie das Formular nun.

Legen Sie ein neues Formulars namens frmLookupsuche an, welches als Hauptformular dienen soll. Ziehen Sie das Unterformular sfmLookupsuche aus dem Navigationsbereich in den Detailbereich des neuen Formulars. Stellen Sie die Eigenschaften Navigationsschaltflächen, Datensatzmarkierer, Bildlaufleisten und Trennlinien des Hauptformulars auf Nein und die Eigenschaft Automatisch zentrieren auf Ja ein. Fügen Sie über dem Unterformular schließlich noch das Kombinationsfeld hinzu, mit dem wir die Suchbegriffe festlegen wollen (siehe Bild 3). Das Kombinationsfeld nennen wir cboSucheLieferant.

Suchformular mit Kombinationsfeld

Bild 3: Suchformular mit Kombinationsfeld

Filterausdruck ermitteln

Wenn Sie wissen wollen, welchen Filterausdruck Access automatisch einstellt, wenn Sie mit der eingebauten Filtermöglichkeit arbeiten wollen, welche wir eingangs vorgestellt haben, brauchen Sie nur das Formular zu öffnen und den Filter festzulegen.

Nun öffnen Sie den VBA-Editor und geben im Direktbereich einen Ausdruck ein, der den aktuellen Wert der Filter-Eigenschaft des Unterformulars ausgibt:

Forms(0).sfmLookupsuche.form.filter

Die Ausgabe lautet wie folgt:

([Lookup_LieferantID].[Firma]="Bigfoot Breweries")

Was ist nun [Lookup_LieferantID].[Firma] für ein Ausdruck? Wir konnten es nicht herausfinden. Selbst wenn wir den Code des Formulars mit der folgenden Methode in eine Textdatei exportiert haben, konnten wir diese Zeichenfolge nicht finden:

SaveAsText acForm, "sfmLookupsuche", CurrentProject.Path & "\form.txt"

Fest steht jedenfalls, dass Access wohl hier irgendwie auf ein Recordset zugreift, das sich hinter dem Kombinationsfeld zur Auswahl des Lieferanten verbirgt und diesen als Feld im Filterausdruck einsetzt.

Kombinationsfeld mit Daten füllen

Wie auch immer: Wir können uns nicht auf irgendwelche nirgends offensichtlich definierten Feldnamen beziehen, wenn wir programmieren, und füllen zunächst unser Kombinationsfeld mit den Daten, die wir zur Suche nach den Lieferanten auswählen wollen.

Dazu stellen wir die Eigenschaft Datensatzherkunft des Kombinationsfeldes auf die Abfrage aus Bild 4 ein. Damit es das Primärschlüsselfeld als gebundene Spalte verwendet und diese aber ausblendet, stellen sie die Eigenschaft Spaltenanzahl auf den Wert 2 und die Eigenschaft Spaltenbreiten auf den Wert 0cm ein. Das Zwischenergebnis sieht dann wie in Bild 5 aus.

Das Kombinationsfeld im Einsatz

Bild 4: Das Kombinationsfeld im Einsatz

Abfrage als Datensatzherkunft für das Such-Kombinationsfeld

Bild 5: Abfrage als Datensatzherkunft für das Such-Kombinationsfeld

Nun müssen wir noch eine Ereignisprozedur hinzufügen, die nach der Auswahl eines der Einträge des Kombinationsfeldes ausgelöst wird und die Daten des Datenblatts entsprechend des Filterkriteriums filtert.

Dazu stellen Sie für die Eigenschaft Nach Aktualisierung des Kombinationsfeldes den Wert [Ereignisprozedur] ein und klicken auf die Schaltfläche mit den drei Punkten (...) neben der Eigenschaft.

Dies öffnet den VBA-Editor und zeigt gleich die neue, leere Prozedur cboSucheLieferanten_AfterUpdate an (siehe Bild 6). Diese füllen wir nun wie folgt:

Neue Ereignisprozedur im VBA-Editor

Bild 6: Neue Ereignisprozedur im VBA-Editor

Private Sub cboSucheLieferant_AfterUpdate()

Dim lngLieferantID As Long

Dim strSQL As String

lngLieferantID = Nz(Me!cboSucheLieferant, 0)

If Not lngLieferantID = 0 Then

strSQL = "LieferantID = " & lngLieferantID

Me!sfmLookupsuche.Form.Filter = strSQL

Me!sfmLookupsuche.Form.FilterOn = True

Else

Me!sfmLookupsuche.Form.Filter = ""

End If

End Sub

Diese Prozedur deklariert zunächst zwei Variablen namens lngLieferantID und strSQL. Die Variable lngLieferantID wird gleich mit dem aktuellen Wert des Feldes cboSuche gefüllt. Allerdings kann es vorkommen, dass der Benutzer das Kombinationsfeld geleert hat, und dann liefert dieses den Wert Null. Dieser kann nicht in einer Long-Variablen gespeichert werden, also sorgen wir mit der Nz-Funktion dafür, dass die in diesem Fall der Wert 0 zurückgegeben und in der Variablen lngLieferantID gespeichert wird.

Danach prüfen wir in einer If...Then-Bedingung, ob lngLieferantID einen Wert ungleich 0 enthält, was bedeuten würde, dass der Benutzer einen der vorhandenen Einträge ausgewählt hat.

In diesem Fall stellen wir in der Variablen strSQL einen SQL-Ausdruck zusammen, den wir später als Filterkriterium nutzen wollen. Der Ausdruck besteht aus dem Feldnamen LieferantID, dem Gleichheitszeichen sowie dem Vergleichswert aus der Variablen lngLieferantID.

Dieser wird als Wert der Eigenschaft Filter des Unterformulars eingestellt. Hier ist wichtig, dass Sie nicht das Unterformular-Steuerelement referenzieren (Me!sfmLookupsuche), sondern das darin enthaltene Formular, das wir mit der Form-Eigenschaft des Unterformular-Steuerelements referenzieren (Me!sfmLookupsuche.Form).

Damit der Filter aktiviert wird, müssen wir auch noch die Eigenschaft FilterOn auf den Wert True einstellen. Das Ergebnis sieht dann beispielsweise wie in Bild 7 aus.

Erfolgte Auswahl im Kombinationsfeld

Bild 7: Erfolgte Auswahl im Kombinationsfeld

Wenn der Benutzer das Kombinationsfeld geleert hat, dann soll das Unterformular wieder alle Datensätze anzeigen. Praktisch bedeutet dies, dass das Kombinationsfeld den Wert Null liefert, den wir als 0 in lngLieferantID speichern. Dies bewirkt die Ausführung des Else-Teils der If...Then-Bedingung. Die einzige Anweisung dort stellt die Eigenschaft Filter wieder auf eine leere Zeichenkette ein (""). Dadurch erscheinen alle Einträge wieder im Unterformular.

Nach mehreren Kombinationsfeldern filtern

Da wir schon eine Tabelle mit zwei Lookup-Feldern haben, fügen wir noch ein zweites Kombinationsfeld hinzu, mit dem wir nach einem zweiten Kriterium filtern können.

Dieses Steuer­element platzieren wir wie in Bild 8 und nennen es cboLookupKategorie. Als Datensatzherkunft stellen wir die folgende Abfrage ein:

Zweites Such-Kombinationsfeld

Bild 8: Zweites Such-Kombinationsfeld

SELECT tblKategorien.KategorieID,

tblKategorien.Kategoriename

FROM tblKategorien

ORDER BY tblKategorien.Kategoriename;

Für die beiden Eigenschaften Spaltenanzahl und Spaltenbreiten legen wir wieder die Werte 2 und 0cm fest.

Für dieses Kombinationsfeld können wir eine ähnliche Ereignisprozedur hinterlegen wir für das vorherige Steuerlement:

Private Sub cboSucheKategorie_AfterUpdate()

Dim lngKategorieID As Long

Dim strSQL As String

lngKategorieID = Nz(Me!cboSucheKategorie, 0)

If Not lngKategorieID = 0 Then

strSQL = "KategorieID = " & lngKategorieID

Me!sfmLookupsuche.Form.Filter = strSQL

Me!sfmLookupsuche.Form.FilterOn = True

Else

Me!sfmLookupsuche.Form.Filter = ""

End If

End Sub

Das funktioniert sogar, aber bemerken Sie den Fehler? Genau: Der zuvor mit dem ersten Kombinationsfeld festgelegte Filter für das Lookupfeld LieferantID wird einfach überschrieben (siehe Bild 9).

Suche nach Kategorien

Bild 9: Suche nach Kategorien

Also müssen wir den Code noch etwas aufbohren. Da wir immer prüfen müssen, ob eines von beiden oder auch beide Kombinationsfelder einen Filterausdruck enthalten, liegt es nahe, die entscheidende Prozedur auszulagern und diese von den Nach Aktualisierung-Ereignissen der beiden Kombinationsfelder aus aufzurufen (siehe Bild 10). Diese versehen wir dann zunächst jeweils mit der folgenden Anweisung:

Filter nach zwei Lookup-Feldern gleichzeitig

Bild 10: Filter nach zwei Lookup-Feldern gleichzeitig

Call LookupSuche

Damit wird die Prozedur LookupSuche immer dann aufgerufen, wenn der Benutzer die Auswahl in einem der beiden Kombinationsfelder ändert. Die Prozedur LookupSuche finden Sie in Listing 1.

Private Sub cboSucheKategorie_AfterUpdate()

     Call LookupSuche

End Sub

Private Sub cboSucheLieferant_AfterUpdate()

     Call LookupSuche

End Sub

Private Sub LookupSuche()

     Dim lngKategorieID As Long

     Dim lngLieferantID As Long

     Dim strSQL As String

     lngKategorieID = Nz(Me!cboSucheKategorie, 0)

     lngLieferantID = Nz(Me!cboSucheLieferant, 0)

     If Not lngKategorieID = 0 Then

         strSQL = strSQL & " AND KategorieID = " & lngKategorieID

     End If

     If Not lngLieferantID = 0 Then

         strSQL = strSQL & " AND LieferantID = " & lngLieferantID

     End If

     If Not Len(strSQL) = 0 Then

         strSQL = Mid(strSQL, 5)

         Me!sfmLookupsuche.Form.Filter = strSQL

         Me!sfmLookupsuche.Form.FilterOn = True

     Else

         Me!sfmLookupsuche.Form.Filter = ""

     End If

End Sub

Listing 1: Filtern nach zwei verschiedenen Lookup-Feldern

Sie deklariert die bereits bekannten Variablen und füllt die Variablen lngKategorieID und LieferantID mit den Werten aus den beiden Kombinationsfeldern cboSucheKategorie und cboSucheLieferant, immer unter Berücksichtigung eventuell vorhandener Null-Werte. Dann prüft sie zunächst, ob lngKategorieID nicht 0 ist und fügt in diesem Fall den Ausdruck AND KategorieID = plus den Wert von lngKategorieID zur Variablen strSQL hinzu.

Das Gleiche erledigt sie für die Variable LieferantID, wobei hier der Ausdruck AND KategorieID = plus dem Wert von lngKategorieID lautet.

Warum erhalten beide Elemente vorneweg den Operator AND? Das wäre doch nur beim zweiten notwendig gewesen. Allerdings sind wir so sicher, dass der Ausdruck mit einem AND eingeleitet wird und können, sofern strSQL nicht leer ist, einfach die ersten fünf Zeichen abschneiden, da wir natürlich kein führendes AND benötigen.

Dementsprechend untersucht die Prozedur den Inhalt von strSQL auch nur darauf, ob dieser leer ist. Fall nicht, entfernt die Mid-Funktion die ersten fünf Zeichen und die Filter-Eigenschaft wird auf den Ausdruck aus strSQL eingestellt. Anderenfalls landet eine leere Zeichenkette in der Filter-Eigenschaft, was den Filter aufhebt.

Erweiterung: Nur vorhandene Werte anzeigen

Wir können noch eine interessante Erweiterung liefern. Wenn Sie sich die Situation in Bild 11 ansehen, sehen Sie, dass beim einem ausgewählten Filter für das Feld Kategorie (hier Getreideprodukte) nur noch wenige verschiedene Einträge im Feld Lieferant angezeigt werden. Das Kombinationsfeld zum Filtern zeigt jedoch noch alle Einträge der Datensatzherkunft an. Wie wäre es denn, wenn wir die Datensatzherkunft der beiden Kombinationsfelder dynamisch so einschränken könnten, dass diese nur die jeweils verfügbaren Einträge liefern?

Obwohl nur ein paar Lieferanten in der Datenblattansicht erscheinen, bietet das Kombinationsfeld alle zur Auswahl an.

Bild 11: Obwohl nur ein paar Lieferanten in der Datenblattansicht erscheinen, bietet das Kombinationsfeld alle zur Auswahl an.

Bei genauer Betrachtung scheint dies jedoch keine gute Idee, da man sonst zur Einstellung eines anderen Filters immer erst das Kombinationsfeld leeren müsste, außerdem wird die Wechselwirkung zwischen den beiden Kombinationsfeldern und den aktuell angezeigten Datensätzen wohl etwas zu unruhig – vor allem, wenn dann später noch andere Filter hinzukommen.

Weitere Suchkriterien hinzufügen

Normalerweise werden Sie nicht nur Kombinationsfelder als Suchkriterien verwenden, sondern gegebenenfalls auch noch Textfelder oder Kontrollkästchen einbeziehen. Wir zeigen beispielhaft, wie die Suche mit einem zusätzlichen Textfeld für die Suche nach dem Artikelnamen zusammenarbeitet. Dieses fügen wir unter dem Namen txtSucheArtikelname wie in Bild 12 zum Hauptformular hinzu. Auch für diese legen wir wieder eine Ereignisprozedur an, die durch das Ereignis Nach Aktualisierung ausgelöst wird. Diese ruft wiederum die Prozedur LookupSuche auf:

Hinzufügen eines Textfeldes für die Suche nach dem Dateinamen

Bild 12: Hinzufügen eines Textfeldes für die Suche nach dem Dateinamen

Private Sub txtSucheArtikelname_AfterUpdate()

Call LookupSuche

End Sub

Die Prozedur LookupSuche erweitern wir an einigen Stellen. Wir fügen die folgenden Zeilen hinzu:

Private Sub LookupSuche()

...

Dim strArtikelname As String

...

strArtikelname = Nz(Me!txtSucheArtikelname, "")

...

If Not Len(strArtikelname) = 0 Then

strSQL = strSQL & " AND Artikelname LIKE '" _

& strArtikelname & "'"

End If

...

End Sub

Als Erstes benötigen wir eine Variable zum Speichern des Vergleichsausdrucks für das Feld Artikelname namens strArtikelname. Dieses füllen wir dann aus dem Textfeld txtSucheArtikelname, wobei wir diesmal im Falle des Wertes Null eine leere Zeichenkette verwenden. Schließlich erweitern wir das Kriterium aus der Variablen strSQL um eine weitere Klausel, die diesmal aus AND Artikelname LIKE '...' sowie den in Hochkommata eingefassten Inhalt der Variablen strArtikelname besteht.

Zusammenfassung und Ausblick

Mit den Techniken aus diesem Artikel können Sie per Kombinationsfeld die Werte auswählen, nach denen in einem Datenblatt im Unterformular gesucht werden soll.

Als Erweiterung könnte man nun noch eine Option berücksichtigen, bei welcher der Benutzer wie bei der eingebauten Filterfunktion nicht nur einen, sondern gleich mehrere Elemente auswählen kann, also etwa Getränke, Gewürze und Fleischprodukte bei der Kategorie.

Eine weitere Erweiterung wäre die Möglichkeit, auch die Texte innerhalb der per Lookupfeld ausgewählten Werte in der Lookuptabelle zu durchsuchen.

Beides werden wir in folgenden Artikel untersuchen.