Dieser Artikel ist Teil des Magazins 'Access im Unternehmen', Ausgabe 1.
Filter speichern und wieder abrufen
Der Benutzer einer Access-Datenbank will sich nicht damit beschäftigen, Where-Ausdrücke zu definieren, mit denen er die Daten seiner Datenbank filtern kann. Er braucht aussagekräftige Bezeichnungen, mit denen er die gewünschten Daten erhält – am besten mit nicht viel mehr als einem Mausklick. Das ist eine schöne Aufgabe: Also statten wir unsere Anwendung mit einer solchen Möglichkeit aus, die der Benutzer sogar noch mit selbst angewendeten Filterkriterien anreichern kann. Und das, ohne auch nur einen Hauch von SQL zu beherrschen.
Ausgangsdatenbank
Die Beispieldatenbank enthält eine Tabelle tblKunden und eine Tabelle tblAnreden, wobei die Tabelle tblKunden über die Fremdschlüsselfelder LieferAnrede und RechnungAnrede mit der Tabelle tblAnreden verknüpft ist. Zusätzlich haben wir eine Abfrage namens qryKunden angelegt, welche die beiden Tabellen tblKunden und tblAnreden zusammenfasst. Dabei haben wir in dieser Abfrage die beiden Felder LieferAnredeID und RechnungAnredeID der Tabelle tblKunden weggelassen und dafür die Bezeichnung der Anreden aus der Tabelle tblAnreden hinzugefügt. Diese Abfrage dient als Datenherkunft eines Unterformulars namens sfmKundenfilter. Die Felder haben wir vollständig in den Detailbereich der Entwurfsansicht gezogen. Damit das Unterformular die Daten in der Datenblattansicht anzeigt, stellen Sie die Eigenschaft Standardansicht auf diesen Wert ein. Das Unterformular haben wir in ein Hauptformular namens frmKundenfilter gezogen, das neben diesem Unterformular natürlich noch die Steuerelemente zum Anwenden der Filter enthalten soll. Dieser Aufbau sieht wie in Bild 1 aus.
Bild 1: Basisausstattung des Formulars frmKundenfilter samt Unterformular sfmKundenfilter
Ziel der Lösung
Das Ziel ist nun, dem Hauptformular ein Kombinationsfeld hinzuzufügen, mit dem der Benutzer einen vordefinierten Filter auswählen kann. Hat er diesen ausgewählt, soll der Filter direkt auf die Daten im Unterformular angewendet werden. Das bedeutet, dass wir die Filter auch an irgendeiner Stelle vordefinieren und auch abrufbar machen müssen. Was bietet sich da besser an als eine Tabelle?
Tabelle zum Speichern der Filter
Die Tabelle, in der wir die Filterkriterien speichern wollen, heißt tblFormularfilter und ist wie in der Entwurfsansicht aus Bild 2 aufgebaut. Neben dem Primärschlüsselfeld enthält die Tabelle drei Felder:
- Bezeichnung: Bezeichnung des Filters, wie er auch im Kombinationsfeld des Formulars zur Auswahl angeboten werden soll
- Filter: Filterkriterium im SQL-Format
- Formularname: Name des Formulars, für das der Filter vorgesehen ist. Durch dieses Feld ermöglichen wir den Einsatz der Lösung in mehreren Formularen.
Da jede Bezeichnung nur einmal für jedes Formular verwendet werden soll, legen wir außerdem noch einen eindeutigen Index über die beiden Felder Bezeichnung und Formularname fest (s. Bild 3).
Um gleich mit dieser Tabelle arbeiten zu können, geben wir ein paar Beispiele für Filterkriterien in die Datenblattansicht der Tabelle ein (s. Bild 4).
Bild 4: Die Tabelle tblFormularfilter mit einigen Beispieldatensätzen
Einbau des Filterkombinationsfeldes
Nun wollen wir das Kombinationsfeld zum Filtern der Daten des Unterformulars nach einem der vordefinierten Filter hinzufügen. Dazu legen Sie ein einfaches Kombinationsfeld im oberen Bereich des Formulars an und nennen es cboFormularfilter. Tragen Sie für die Eigenschaft Datensatzherkunft des Kombinationsfeldes die Abfrage tblFormularfilter ein. Stellen Sie für die Eigenschaft Spaltenanzahl den Wert 3 und für die Eigenschaft Spaltenbreiten den Wert 0cm;;0cm ein (s. Bild 5). Was bedeuten diese Werte? Als Datensatzherkunft verwenden wir die Tabelle tblFormularfilter.
Wir benötigen das erste Feld als gebundene Spalte, das zweite als anzuzeigenden Wert und das dritte enthält das Filterkriterium, das wir auslesen wollen, wenn der Benutzer einen der Einträge ausgewählt hat. Also teilen wir dem Kombinationsfeld drei Spalten zu, von denen die erste und die dritte die Breite 0cm aufweisen sollen – also quasi ausgeblendet sind. Nur die mittlere Spalte soll erscheinen, und das in voller Breite. Daher finden Sie zwischen den beiden Semikola keine explizite Breitenangabe.
Nach Formular filtern
Nun haben wir allerdings in der Tabelle tblFormularfilter bereits ein Feld namens Formularname hinterlegt, mit dem wir festlegen wollen, für welches Formular der jeweilige Filter verwendet werden kann. Wir können ja durchaus ein Kundenformular, ein Artikelformular oder auch ein Lieferantenformular mit einem solchen Filter ausstatten.
Wie aber filtern wir die Datensatzherkunft eines Kombinationsfeldes nach dem Namen des Formulars, in dem es angezeigt wird? Dies erledigen wir am einfachsten, indem wir die Datensatzherkunft beim Laden des Formulars einstellen. Die dazu notwendige Ereignisprozedur wird durch das Ereignis Beim Laden ausgelöst und sieht wie folgt aus:
Private Sub Form_Load()
Me!cboFormularfilter.RowSource = _
"SELECT * FROM tblFormularfilter WHERE Formularname = '" & Me.Name & "'"
End Sub
Danach finden wir im Kombinationsfeld bereits alle gewünschten Einträge vor (s. Bild 6).
Filterfunktion hinzufügen
Nun fehlt als Grundausstattung noch eine Funktion, die den in der Tabelle gespeicherten Filterausdruck nach der Auswahl eines der Einträge des Kombinationsfeldes auf die Daten des Unterformulars anwendet. Diese Prozedur erstellen wir für das Ereignis Nach Aktualisierung des Kombinationsfeldes. Sie sieht wie folgt aus:
Private Sub cboFormularfilter_AfterUpdate()
Dim strFilter As String
strFilter = Me!cboFormularfilter.Column(2)
Me!sfmKundenfilter.Form.Filter = strFilter
Me!sfmKundenfilter.Form.FilterOn = True
End Sub
Die Prozedur ermittelt mit der Eigenschaft Column(2) den Wert der dritten Spalte des ausgewählten Eintrags des Kombinationsfeldes und trägt diesen in die Variable strFilter ein (zur Erläuterung: der Index der Spalten im Kombinationsfeld startet mit 0). Dann weist sie diesen Ausdruck der Eigenschaft Filter des Form-Objekts des Unterformular-Steuerelements zu und stellt FilterOn auf den Wert True ein. Das Ergebnis sieht dann beispielsweise wie in Bild 7 aus.
Filter leeren
Nun fehlt noch eine einfache Funktion, mit welcher der Benutzer den Filter leeren und wieder alle Datensätze im Unterformular einblenden kann. Dazu haben wir zwei Möglichkeiten: Entweder wir fügen eine Schaltfläche hinzu, welche den Filter wieder entfernt, oder wir legen im Kombinationsfeld einen Eintrag an, der etwa den Text
Die Schaltfläche fügen Sie am besten direkt neben dem Kombinationsfeld ein und legen für das Ereignis Beim Klicken die folgende Ereignisprozedur an:
Private Sub cmdFilterLeeren_Click()
Me!cboFormularfilter = Null
Me!sfmKundenfilter.Form.Filter = ""
End Sub
Eigene Filter definieren
Nun kommt der interessante Teil: Sie wollen ja nicht immer, wenn der Benutzer ein neues Filterkriterium wünscht, zur Tastatur greifen und dem Benutzer anschließend eine neue Version der Datenbank zusenden. Es wäre doch toll, wenn der Benutzer selbst seine Filterkriterien zusammenstellen könnte. Den Grundstein dazu haben wir ja bereits gelegt, indem wir eine Tabelle zum Speichern der Filterkriterien angelegt haben – diese lädt natürlich dazu ein, weitere Kriterien hinzuzufügen.
Nun könnten Sie dem Benutzer grundlegende SQL-Kenntnisse vermitteln, damit er die Abfragekriterien selbst anhand der Felder und der gewünschten Vergleichswerte zusammenstellt. Ab einer gewissen Komplexität kommt der Benutzer auch sicher nicht um diesen Schritt herum. Aber für viele einfache Kriterien kann der Benutzer sich mit den Mitteln der Benutzeroberfläche behelfen: Dabei filtert er einfach selbst die Daten in dem Formular, indem er etwa auf den Pfeil nach unten im Spaltenkopf klickt, dann beispielsweise für ein Textfeld den Eintrag Textfilter|Enthält... auswählt und den gewünschten Vergleichswert in das nun erscheinende Feld eingibt (s. Bild 9).
Was die Wenigsten wissen: Das dadurch angewendete Filterkriterium wird ebenfalls in die Eigenschaft Filter des jeweiligen Formulars eingetragen. Sie können dieses also etwa über den Direktbereich ausgeben lassen – zum Beispiel wie in Bild 10.
Das können wir uns natürlich zunutze machen und das Filterkriterium speichern, wenn der Benutzer das gewünschte Ergebnis erhalten soll. Dazu fügen wir eine weitere Schaltfläche mit der Beschriftung Filter speichern hinzu. Diese erhält den Namen cmdFilterSpeichern und die Prozedur aus Listing 1 für die Ereigniseigenschaft Beim Klicken. Die Prozedur ermittelt zunächst den aktuellen Filterausdruck und speichert diesen in der Variablen strFilter.
Private Sub cmdFilterSpeichern_Click()
Dim strBezeichnung As String
Dim strFilter As String
Dim db As DAO.Database
Dim strFormularname As String
Dim bolGespeichert As Boolean
Dim lngFilterID As Long
strFilter = Me!sfmKundenfilter.Form.Filter
If Len(strFilter) = 0 Then
MsgBox "Es ist kein Filter aktiv."
Exit Sub
End If
bolGespeichert = True
strBezeichnung = InputBox("Geben Sie eine Bezeichnung für den Filter ein:", "Filter speichern") strFormularname = Me.Name
If Not Len(strBezeichnung) = 0 Then
Set db = CurrentDb
On Error Resume Next
db.Execute "INSERT INTO tblFormularfilter(Bezeichnung, Filter, Formularname) VALUES('" & strBezeichnung _ & "', '" & strFilter & "', '" & strFormularname & "')", dbFailOnError
If Err.Number = 3022 Then
If MsgBox("Es gibt bereits einen Filter mit dieser Bezeichnung. Überschreiben?", vbYesNo, _ "Filter überschreiben") = vbYes Then
db.Execute "UPDATE tblFormularfilter SET Filter = '" & strFilter & "' WHERE Bezeichnung = '" _
& strBezeichnung & "' AND Formularname = '" & strFormularname & "'", dbFailOnError
Else
bolGespeichert = False
End If
End If
If bolGespeichert Then
lngFilterID = DLookup("FilterID", "tblFormularfilter", "Bezeichnung = '" & strBezeichnung _ & "' AND Formularname = '" & strFormularname & "'")
Me!cboFormularfilter.Requery
Me!cboFormularfilter = lngFilterID
End If
End If
End Sub
Listing 1: Speichern eines benutzerdefinierten Filters
Hat strFilter danach die Länge 0, war offensichtlich kein Filter aktiviert und die Prozedur wird mit einer entsprechenden Meldung abgebrochen. Eine Variable namens bolGespeichert wird auf True eingestellt – hat diese am Ende der Prozedur auch noch diesen Wert, wurde erfolgreich gespeichert und es können die zur Anpassung der Benutzeroberfläche nötigen Schritte durchgeführt werden.
Nun fragt die Prozedur per InputBox die Bezeichnung für den neuen Filter ab und speichert diese in der Variablen strBezeichnung. Außerdem landet noch der Name des aktuellen Formulars in der Variablen strFormularname. Hat der Benutzer keinen Wert in die InputBox eingegeben oder die Abbrechen-Schaltfläche betätigt, ist die Länge der Zeichenkette in der Variablen strBezeichnung gleich 0. In diesem Fall endet die Prozedur ebenfalls. Anderenfalls holt die Prozedur einen Verweis auf das aktuelle Database-Objekt in die Variable db und deaktiviert die Fehlerbehandlung. Dies hat den Sinn, dass beim nachfolgenden Versuch, die aktuelle Kombination aus Bezeichnung und Formularname samt Filterausdruck in der Tabelle tblFormularfilter zu speichern, ein Fehler auftreten könnte – und zwar, wenn bereits ein Filter mit der angegebenen Bezeichnung für dieses Formular gespeichert wurde. In diesem Fall liefert Err.Number in der folgenden If...Then-Bedingung den Wert 3022, was zum Ausführen des Then-Teils dieser Bedingung führt. Hier fragt die Prozedur den Benutzer, ob er den bereits vorhandenen Filter mit dieser Bezeichnung überschreiben möchte. Falls ja, führt die Prozedur per Execute-Methode eine entsprechende UPDATE-Anweisung aus und aktualisiert den Datensatz mit dem neuen Filterausdruck. Wünscht der Benutzer dies nicht, wurde nichts geändert und bolGespeichert erhält den Wert False.
Hat bolGespeichert hier den Wert True, wollen wir noch dafür sorgen, dass der frisch angelegte (oder geänderte) Filterausdruck auch gleich im Kombinationsfeld angezeigt wird. Dazu ermitteln wir per DLookup-Funktion den Primärschlüsselwert zu dem Datensatz mit der Bezeichnung und dem Formularnamen und legen diesen in der Variablen lngFilterID ab. Danach aktualisieren wir die Datensatzherkunft des Kombinationsfeldes und stellen dieses auf die soeben ermittelte ID ein – wie etwa in Bild 11 zu sehen.
Filter verwalten
Mit der Zeit sammeln sich so einige Filter in der Tabelle strFormularfilter an (s. Bild 12). Diese soll der Benutzer natürlich auch verwalten können – allein, um nicht mehr benötigte Filter auszusortieren oder Filter umzubenennen und so neu zu strukturieren.
Dazu fügen wir dem Formular frmKundenfilter eine weitere Schaltfläche namens cmdFilterBearbeiten hinzu, welche die folgende Ereignisprozedur auslöst:
Private Sub cmdFilterBearbeiten_Click()
DoCmd.OpenForm "frmFormularfilter", OpenArgs:=Nz(Me.cboFormularfilter, 0)
End Sub
Der Entwurf des Formulars frmFormularfilter sieht wie in Bild 13 aus. Er besteht aus einem Hauptformular mit zwei Textfeldern namens txtSucheBezeichnung und txtSucheFilter, in die der Benutzer Filterausdrücke für die Felder Bezeichnung und Filter eingeben kann.
Außerdem finden Sie dort eine Schaltfläche namens cmdFilterLeeren, welche die zuvor genannten Textfelder leert und auch den Filter für das Unterformular zurücksetzt.
Das Unterformular heißt sfmFormularfilter und verwendet die Tabelle tblFormularfilter als Datenherkunft. Es zeigt alle Felder der Tabelle in der Datenblattansicht an.
Die beiden Textfelder zum Filtern der Datensätze des Unterformulars erwarten die Eingabe des Benutzers und filtern das Unterformular nach jedem getippten Zeichen neu, damit der Benutzer immer direkt das aktuelle Ergebnis sieht.
Zu diesem Zweck finden Sie im Kopf des Klassenmoduls zu diesem Formular die Deklaration zweier Variablen zum Aufnehmen des jeweils aktuellen Textes der beiden Textfelder:
Dim strSucheBezeichnung As String
Dim strSucheFilter As String
Wenn der Benutzer nun etwa ein Zeichen in das Textfeld txtSucheBezeichnung eintippt, löst er das Ereignis Bei Änderung aus. Dafür haben wir die Ereignisprozedur txtSucheBezeichnung_Change hinterlegt, die wie folgt aussieht:
Private Sub txtSucheBezeichnung_Change()
strSucheBezeichnung = Me!txtSucheBezeichnung.Text
Suchen
End Sub
Hier ermitteln wir über die Eigenschaft Text des Textfeldes txtSucheBezeichnung den aktuellen Inhalt dieses Textfeldes und tragen ihn in die Variable strSucheBezeichnung ein. Warum Text und nicht einfach Value? Weil Value sich erst nach dem Abschließen der Eingabe etwa durch Verlassen des Textfeldes ändert und wir so nicht nach der Eingabe eines jeden Zeichens neu filtern können. Nach dem Speichern des Vergleichswertes ruft die Prozedur die Routine Suchen auf, die wir gleich im Anschluss erläutern.
Zuvor noch ein Blick auf die entsprechende Ereignisprozedur für das Textfeld txtSucheFilter. Diese ist analog aufgebaut wie txtBezeichnungFilter_Change und speichert den aktuellen Text des Textfeldes in der Variablen strSucheFilter:
Private Sub txtSucheFilter_Change()
strSucheFilter = Me!txtSucheFilter.Text
Suchen
End Sub
Warum aber speichern wir die Inhalte der Text-Eigenschaft jeweils in lokalen Variablen, bevor wir die Suchen-Funktion aufrufen? Wir könnten doch auch von der Suchen-Funktion auf die entsprechenden Werte der Textfelder zugreifen? Leider nein: Auf die Text-Eigenschaft kann man nur zugreifen, wenn das jeweilige Textfeld gerade den Fokus besitzt. Wir müssten also in der Routine zum Suchen erst noch den Fokus verschieben, was zu unnötigen Komplikationen führt. Da speichern wir die aktuellen Werte doch lieber schnell in zwei Variablen.
Beide Prozeduren rufen danach die Routine Suchen auf, die wir in Listing 2 abgebildet haben. Diese prüft zunächst, ob strSucheBezeichnung eine Zeichenkette mit mehr als 0 Zeichen enthält. In diesem Fall fügt sie zur Variablen strSuchausdruck einen Teil hinzu, der beispielsweise so aussieht:
Private Sub Suchen()
Dim strSuchausdruck As String
If Len(strSucheBezeichnung) > 0 Then
strSuchausdruck = strSuchausdruck & " AND Bezeichnung LIKE '*" & strSucheBezeichnung & "*'"
End If
If Len(strSucheFilter) > 0 Then
strSuchausdruck = strSuchausdruck & " AND Filter LIKE '*" & strSucheFilter & "*'"
End If
If Len(strSuchausdruck) > 0 Then
strSuchausdruck = Mid(strSuchausdruck, 6)
Me!sfmFormularfilter.Form.Filter = strSuchausdruck
Me!sfmFormularfilter.Form.FilterOn = True
Else
Me!sfmFormularfilter.Form.Filter = ""
End If
End Sub
Listing 2: Durchsuchen der Filter nach verschiedenen Kriterien
AND Bezeichnung LIKE '*Liefer*'
Das Gleiche geschieht danach für die String-Variable strSucheFilter. Wenn beide Textfelder bereits einen Wert enthalten, sind auch die beiden String-Variablen gefüllt, wodurch dann zwei Ausdrücke in der Variablen strSuchausdruck zusammengeführt werden.
Hat strSuchausdruck danach also einen Inhalt mit der Länge größer 0, wird das führende AND vorn im Suchausdruck mit der Mid-Funktion abgeschnitten. Dann landet der Suchausdruck in der Eigenschaft Filter des Form-Objekts des Unterformular-Steuerelements und der Filter wird mit FilterOn = True aktiviert. Sollte keine der beiden Variablen strSucheBezeichnung und strSucheFilter gefüllt sein, wird der Filter mit Filter = "" deaktiviert.
Das Ergebnis sieht dann etwa wie in Bild 14 aus.
Nun haben wir auch diesem Formular eine Schaltfläche namens cmdFilterLeeren hinzugefügt, welche beim Anklicken die folgende Prozedur auslösen soll:
Private Sub cmdFilterLeeren_Click()
Me!txtSucheBezeichnung = Null
Me!txtSucheFilter = Null
strSucheBezeichnung = ""
strSucheFilter = ""
Suchen
End Sub
Damit werden zunächst die beiden Textfelder txtSucheBezeichnung und txtSucheFilter geleert und danach die entsprechenden String-Variablen. Der anschließende Aufruf der Suchen-Routine sorgt dann dafür, dass das Unterformular wieder alle Datensätze anzeigt.
Filter für alle oder nur für Formulare?
Wir müssen nun noch entscheiden, ob das Formular zur Verwaltung der Formularfilter immer alle Datensätze für die aktuelle Datenbank anzeigen soll oder ob der Benutzer nur die Datensätze sehen soll, die sich auf das Formular beziehen, von dem aus er das Formlar zur Filterverwaltung aufgerufen hat. Letzteres scheint logischer zu sein, denn wieso sollte man Filter verwalten, die sich auf mehrere Formulare beziehen?
Also stellen wir das Formular so ein, dass das Unterformular gleich zu Beginn nur die Filter zum aktuellen Formular anzeigt. Dazu leeren wir einfach die Eigenschaft Datenherkunft des Formulars und weisen beim Laden des Formulars eine entsprechend gefilterte Datenherkunft zu. Warum auf diesem Wege und nicht durch Anpassen des Filterausdrucks? Weil wir dann mit den anderen per Textfeld zu setzenden Filtern durcheinander kommen.
Also verwenden wir beim Laden des Formulars die Ereignisprozedur aus Listing 3. Diese ermittelt mit Screen.ActiveForm.Name den Namen des aufrufenden Formulars und stellt eine Recordsource zusammen, welche die Tabelle tblFormularfilter nach diesem Formular filtert. Dann nutzen wir noch die beim Aufruf des Formulars per Öffnungsargument übergebene ID des aktuell im Kombinationsfeld zur Auswahl des Filters ausgewählten Filter. Sofern ein solcher markiert ist, wird der Datensatzzeiger des Unterformulars gleich auf diesen Eintrag eingestellt.
Private Sub Form_Load()
Dim lngFilterID As String
Dim strRecordsource As String
Dim strFormularname As String
lngFilterID = Me.OpenArgs
strFormularname = Screen.ActiveForm.Name
strRecordsource = "SELECT * FROM tblFormularfilter WHERE Formularname = '" & strFormularname & "'"
Me!sfmFormularfilter.Form.RecordSource = strRecordsource
If Not lngFilterID = 0 Then
Me!sfmFormularfilter.Form.Recordset.FindFirst "FilterID = " & lngFilterID
End If
End Sub
Listing 3: Beim Laden der Filterverwaltung
Der Benutzer kann seine Filterausdrücke nun komfortabel mit diesem Formular bearbeiten, neue Filter hinzufügen und nicht mehr benötigte Filter löschen.