Assistent für Domänenfunktionen

Domänenfunktionen wie DomWert (DLookup), DomMax (DMax) oder DomAnzahl (DCount) sind praktische Helfer, wenn es um das schnelle Zugreifen auf verschiedene Informationen einer Datenbank geht. Wir können damit den Wert eines oder mehrerer Felder aus einem Datensatz mit einem bestimmten Kriterium ermitteln, die Gesamtanzahl von Datensätzen einer Tabelle mit oder ohne Kriterium oder auch Minimal- oder Maximalwerte. Der Assistent, den wir in diesem Beitrag vorstellen, hilft dabei auf verschiedene Arten. Die erste ist, dass Sie ihn einfach aufrufen können, um schnell die Werte für eine Domänenfunktion einzutippen und das Ergebnis auszulesen. Aber der Assistent kann noch mehr: Sie können ihn auch von Eigenschaftsfeldern heraus aufrufen, um die gewählte Domänenfunktion direkt dort einzutragen.

Damit Sie sich direkt einen Eindruck davon machen können, wie der fertige Assistent aussieht, schauen Sie sich Bild 1 an. Hier finden Sie im oberen Bereich die Eingabefelder für die Parameter der Domänenfunktionen. Wir haben die Reihenfolge im Vergleich zur Funktion etwas umgestellt, sodass die Auswahl der Domäne der erste Schritt ist. Das hat den Hintergrund, dass wir nach der Auswahl der Tabelle oder Abfrage, aus der die zu ermittelnden Daten stammen sollen, die darin enthaltenen Felder per Kontextmenü zum Einfügen in die folgenden beiden Textfelder bereithalten wollen.

Bild 1: Der Domänenfunktions-Assistent in Aktion

Nachdem wir beispielsweise eine Tabelle wie tblAutoren ausgewählt haben, liefert ein Rechtsklick auf eines der Textfelder für den Ausdruck oder das Kriterium die Liste der Felder dieser Tabelle (siehe Bild 2).

Bild 2: Auswahl der Felder per Kontextmenü

Der untere Bereich enthält jeweils eine Zeile für alle zwölf verfügbaren Domänenfunktionen. Hier sehen wir zuerst die deutsche und in Klammern die englische Bezeichnung, daneben folgt das Ergebnis für die Domänenfunktion mit den oben angegebenen Parametern.

Rechts davon finden wir den aktuellen Ausdruck für den Aufruf dieser Domänenfunktion, der die oben angegebenen Parameter berücksichtigt. Mit den Schaltflächen rechts daneben können wir die aktuelle Funktion in die Zwischenablage kopieren.

Da der Assistent die Domänenfunktionen in der deutschen Fassung (für die Benutzeroberfläche der deutschen Version von Access) sowie in der englischen Fassung (für VBA) bereitstellen soll, haben wir im oberen Bereich noch eine Option hinzugefügt, mit der wir wählen können, ob die Funktion als Ausdruck oder als VBA-Funktion angezeigt werden soll.

Programmierung des Formulars

In der Entwurfsansicht sieht das Formular wie in Bild 3 aus. Die oberen drei Steuerelemente heißen cboDomaene, txtAusdruck und txtKriterium. Das Kombinationsfeld cboDomaene wird beim Laden des Formulars durch das Ereignis aus Listing 1 gefüllt. Hier öffnen wir mit OpenRecordset ein Recordset auf Basis der Tabelle MSysObjects. Diese enthält alle Objekte einer Access-Datenbank. Wir benötigen nur die mit den Typen 1, 4, 5 und 6. 1, 4 und 6 liefern die verschiedenen Tabellenarten (lokale Tabelle, verknüpfte Tabelle und ODBC-Tabelle) und 5 die Abfragen. Außerdem wollen wir Systemtabellen und ähnliche Objekte ausschließen, die nicht vom Benutzer angelegt wurden und legen dazu entsprechende Kriterien fest.

Bild 3: Entwurfsansicht des Formulars frmDomaenenfunktionen

Private Sub Form_Load()
     Dim db As DAO.Database, rstDomaene As DAO.Recordset
     Dim strDomaenen As String, strAusdruck As String
     Dim bolHinzufuegen As Boolean
     Dim qdf As DAO.QueryDef, tdf As DAO.TableDef
     Set db = CurrentDb
     Set rstDomaene = db.OpenRecordset("SELECT Name, Type FROM MSysObjects WHERE Type IN (1,4,5,6) AND NOT Name " _
         & "LIKE ''f_*'' AND NOT Name LIKE ''MSys*'' AND NOT Name LIKE ''USys*'' AND NOT Name LIKE ''~*'' ORDER BY Name", _
         dbOpenDynaset)
     Do While Not rstDomaene.EOF
         Select Case rstDomaene!Type
             Case 1, 4, 6
                 bolHinzufuegen = True
             Case 5
                 Set qdf = db.QueryDefs(rstDomaene!Name)
                 On Error Resume Next
                 If qdf.Parameters.Count = 0 Then
                     bolHinzufuegen = True
                 Else
                     bolHinzufuegen = False
                 End If
                 If Not Err.Number = 0 Then
                     bolHinzufuegen = False
                 End If
                 On Error GoTo 0
         End Select
         If bolHinzufuegen Then
             If Len(strAusdruck) = 0 Then
                 Select Case rstDomaene!Type
                     Case 1, 4, 6
                         Set tdf = db.TableDefs(rstDomaene.Fields(0))
                         strAusdruck = tdf.Fields(0).Name
                     Case 5
                         Set qdf = db.QueryDefs(rstDomaene.Fields(0))
                         strAusdruck = qdf.Fields(0).Name
                 End Select
             End If
             strDomaenen = strDomaenen & """" & rstDomaene!Name & """;"
         End If
         rstDomaene.MoveNext
     Loop
     Me!cboDomaene.RowSource = strDomaenen
     Me!cboDomaene = Me.cboDomaene.ItemData(0)
     Me!txtAusdruck = "[" & strAusdruck & "]"
     Me!txtKriterium = ""
     Me!ogrZiel = 1
     UpdateControls Nz(Me!txtAusdruck, ""), Nz(Me!cboDomaene, ""), Nz(Me!txtKriterium, "")
End Sub

Listing 1: Prozedur, die beim Laden des Formulars aufgerufen wird

Das Kombinationsfeld cboDomaene soll eine Wertliste als Datensatzherkunft erhalten, deshalb stellen wir die Eigenschaft Herkunftsart auf Wertliste ein. Um den Rest kümmern sich die folgenden Zeilen der Prozedur Form_Load. Hier durchlaufen wir alle Datensätze des Recordsets. In einer Select Case-Bedingung prüfen wir den Typ, also ob es sich um eine Tabelle oder um eine Abfrage handelt. Warum ist das nötig? Weil es vorkommen kann, dass eine Abfrage Parameter enthält. In diesem Fall können wir nicht per Domänenfunktion darauf zugreifen, daher wollen wir solche Einträge direkt auslassen. Um festzulegen, ob eine Domäne Parameter enthält, verwenden wir die Variable bolHinzufuegen. Diese wird bei Tabellen immer auf True eingestellt. Bei einer Abfrage erstellt die Prozedur ein QueryDef-Objekt und weist diesem die aktuell untersuchte Abfrage zu. Für dieses können wir mit der Eigenschaft Parameters.Count ermitteln, ob die Abfrage Parameter enthält. Ist das nicht der Fall, stellen wir bolHinzufuegen auf True ein, sonst auf False.

Diesen Vorgang haben wir auch noch bei deaktivierter eingebauter Fehlerbehandlung ausgeführt, da dieser Zugriff bei einigen weiteren Abfragen zu Fehlern führte. Ist dies der Fall, stellen wir ebenfalls bolHinzufuegen auf False ein.

Hat bolHinzufuegen danach den Wert True, können wir die Tabelle oder Abfrage zur Liste der Domänen in strDomaenen hinzufügen. Der Variablen strAusdruck weisen wir den Namen des ersten Feldes der Tabelle oder Abfrage zu, denn wir wollen das Kombinationsfeld mit dem ersten Eintrag vorbelegen und das Textfeld txtAusdruck mit dem ersten Feld dieser Tabelle oder Abfrage – so sieht der Benutzer direkt Beispiele für die anzugebenden Werte. Den Namen des ersten Feldes lesen wir wieder in einer Select Case-Bedingung ein, in der wir testen, ob es sich um eine Tabelle oder Abfrage handelt. Abhängig davon öffnen wir ein TableDef– oder QueryDef-Objekt, dem wir dann den Namen des ersten Feldes entnehmen.

Nach dem Durchlaufen aller Recordsets stellen wir die Eigenschaft Datensatzherkunft (RowSource) auf die Liste aus strDomaenen ein und setzen den Wert des Kombinationsfeldes auf den ersten verfügbaren Eintrag.

Dann füllen wir das Textfeld txtAusdruck mit dem Wert aus strAusdruck, also dem ersten Feld der gewählten Tabelle oder Abfrage. txtKriterium leeren wir hingegen. Schließlich stellen wir noch die Optionsgruppe zum Auswählen der auszugebenden Ausdrücke auf 1 ein, damit zunächst die deutschen Versionen der Domänenfunktionen erscheinen.

Danach folgt Arbeit für das Add-In – wir rufen die Prozedur UpdateControls auf, welche die übrigen Steuerelemente des Formulars aktualisiert. Dabei übergeben wir die Werte der drei Steuerelemente cboDomaene, txtAusdruck und txtKriterien als Parameter.

Aktualisieren der Steuerelemente

Die Prozedur UpdateControls erwartet die Werte der drei Steuerelemente cboDomaene, txtAusdruck und txtKriterien als Parameter. Sie füllt zunächst die Textfelder mit den Ergebnissen der jeweiligen Domänenfunktion (siehe Listing 2). Dies erledigen wir jeweils unter Deaktivierung der eingebauten Fehlerbehandlung. Somit verhindern wir eine bei einem Fehler ausgegebene Fehlermeldung von Access selbst und können stattdessen einfach den Wert #Fehler in das jeweilige Textfeld schreiben – genau so, wie es auch bei einer als Ausdruck verwendeten Domänenfunktion geschehen würde. Der Ablauf sieht für alle zwölf Domänenfunktionen ähnlich aus:

Private Sub UpdateControls(strAusdruck As String, strDomaene As String, strKriterium As String)
     On Error Resume Next
     Me!txtDCount = DCount(strAusdruck, strDomaene, strKriterium)
     If Not Err.Number = 0 Then
         Me!txtDCount = "#Fehler"
     End If
     ...
     Me!txtDLookup = DLookup(strAusdruck, strDomaene, strKriterium)
     If Not Err.Number = 0 Then
         Me!txtDLookup = "#Fehler"
     End If
     On Error GoTo 0
     Select Case ogrZiel
         Case 1
             Me!txtDCountCall = "=DomAnzahl(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
             Me!txtDFirstCall = "=DomErsterWert(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
             Me!txtDLastCall = "=DomLetzterWert(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
             Me!txtDMaxCall = "=DomMax(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
             Me!txtDMinCall = "=DomMin(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
             Me!txtDAvgCall = "=DomMittelwert(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
             ...
             Me!txtDLookupCall = "=DomWert(""" & strAusdruck & """;""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """;""" & strKriterium) & """)"
         Case 2
             Me!txtDCountCall = "DCount(""" & strAusdruck & """,""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
             Me!txtDFirstCall = "DFirst(""" & strAusdruck & """,""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
             Me!txtDLastCall = "DLast(""" & strAusdruck & """,""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
             Me!txtDMaxCall = "DMax(""" & strAusdruck & """,""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
             Me!txtDMinCall = "DMin(""" & strAusdruck & """,""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
             Me!txtDAvgCall = "DAvg(""" & strAusdruck & """,""" & strDomaene & _
                 IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
             ...
             Me!txtDLookupCall = "DLookup(""" & strAusdruck & """,""" & strDomaene _
                 & IIf(Len(strKriterium) = 0, "", """,""" & strKriterium) & """)"
     End Select
End Sub

[

Listing 2: Prozedur, welche die Parameter in Ergebnisse umwandelt

Me!txtDCount = DCount(strAusdruck, strDomaene, strKriterium)
If Not Err.Number = 0 Then
     Me!txtDCount = "#Fehler"
End If

Anschließend folgt eine Select Case-Bedingung, die prüft, ob der deutsche Ausdruck oder die englische VBA-Funktion ausgegeben werden soll. Der erste Teil weist jeweils dem rechten Textfeld die Funktion zu. Diese setzen wir aus dem Funktionsaufruf und den in Klammern eingefassten Parametern zusammen, die wir den drei Steuerelementen cboDomaene, txtAusdruck und txtKriterium entnehmen – hier am Beispiel der Funktion DomAnzahl (DCount):

Me!txtDCountCall = "DomAnzahl(" & strAusdruck & ";" _
     & strDomaene & IIf(Len(strKriterium) = 0, "", ";" _
     & strKriterium) & ")"

Für die VBA-Funktion sieht der zusammengestellte Ausdruck nur wenig anders aus.

Die beiden Unterschiede sind die englische statt der deutschen Bezeichnung und die Verwendung des Kommas statt des Semikolons als Trennzeichen:

Me!txtDCountCall = "DCount(" & strAusdruck & "," _
     & strDomaene & IIf(Len(strKriterium) = 0, "", "," _
     & strKriterium) & ")"

Nach dem Laden sehen die Werte der drei oberen Steuerelemente wie in Bild 4 aus.

Bild 4: Anzeige nach dem Laden des Assistenten

Aktualisieren der Funktionen bei Eingabe

Nach dem Initialisieren zeigt das Formular also die erste Abfrage oder Tabelle nach alphabetischer Sortierung an sowie die Ergebnisse der einzelnen Domänenfunktionen samt Definition.

Die Ergebnisse und Definitionen sollen nun direkt bei Änderung durch den Benutzer aktualisiert werden, unmittelbar nach der Eingabe eines jeden Zeichens. Dazu nutzen wir das Ereignis Bei Änderung der verschiedenen Steuerelemente.

Für das Textfeld txtAusdruck fügen wir folgende Prozedur hinzu, welche wieder die Prozedur UpdateControls aufruft. Um den aktuell im Textfeld angezeigten Text mit dem ersten Parameter zu übergeben, verwenden wir dessen Text-Eigenschaft. Anderenfalls würde nämlich nur der gespeicherte Wert übergeben, aber wir wollen ja den aktuell eingegebenen Wert:

Private Sub txtAusdruck_Change()
     UpdateControls Me!txtAusdruck.Text, _
         Nz(Me!cboDomaene, ""), Nz(Me!txtKriterium, "")
End Sub

Eine ähnliche Ereignisprozedur hinterlegen wir für das Textfeld txtKriterium. Der Unterschied ist, dass wir hier die Text-Eigenschaft von txtKriterium als dritten Wert übergeben und den Wert von txtAusdruck als ersten:

Private Sub txtKriterium_Change()
     UpdateCalls Nz(Me!txtAusdruck, ""), _
         Nz(Me!cboDomaene, ""), Me!txtKriterium.Text
End Sub

Für das Kombinationsfeld zur Eingabe der Tabelle oder Abfrage hinterlegen wir gleich zwei Ereignisprozeduren.

Die erste wird ebenfalls bei Änderungen am enthaltenen Text ausgelöst:

Private Sub cboDomaene_Change()
     UpdateControls Me!txtAusdruck, Me!cboDomaene.Text, _
         Nz(Me!txtKriterium, "")
End Sub

Die zweite wird aufgerufen, wenn der Benutzer einen neuen Eintrag im Kombinationsfeld auswählt. Sie ist etwas umfangreicher, da wir hier erneut auch das erste Feld dieser Tabelle oder Abfrage auslesen, um dieses direkt im Textfeld txtAusdruck anzuzeigen (siehe Listing 3).

Private Sub cboDomaene_AfterUpdate()
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim tdf As DAO.TableDef
     Dim rstDomaene As DAO.Recordset
     Dim strAusdruck As String
     Set db = CurrentDb
     Set rstDomaene = db.OpenRecordset("SELECT Name, Type FROM MSysObjects WHERE Name = ''" & Me!cboDomaene & "''", _
         dbOpenDynaset)
     Select Case rstDomaene!Type
         Case 1, 4, 6
             Set tdf = db.TableDefs(rstDomaene.Fields(0))
             strAusdruck = tdf.Fields(0).Name
         Case 5
             Set qdf = db.QueryDefs(rstDomaene.Fields(0))
             strAusdruck = qdf.Fields(0).Name
     End Select
     Me!txtAusdruck = strAusdruck
     UpdateControls Me!txtAusdruck, Nz(Me!cboDomaene, ""), Nz(Me!txtKriterium, "")
End Sub

Listing 3: Prozedur, die nach dem Auswählen einer neuen Tabelle oder Abfrage ausgewählt wird

Schließlich aktualisieren wir den Inhalt noch, wenn der Benutzer zwischen den Zielen Ausdruck und VBA wechselt:

Private Sub ogrZiel_AfterUpdate()
     UpdateControls Nz(Me!txtAusdruck, ""), _
         Nz(Me!cboDomaene, ""), Nz(Me!txtKriterium, "")
End Sub

Felder per Kontextmenü einfügen

Wenn man schon einen Assistenten zum Zusammenstellen von Domänenfunktionen nutzt, hat man vielleicht nicht alle Feldnamen, die man im Ausdruck und im Kriterium einsetzen möchte, im Kopf und möchte diese auch nicht nachschlagen.

Also fügen wir noch eine Funktion hinzu, mit der wir ein Kontextmenü anzeigen können, wenn der Benutzer mit der rechten Maustaste in eines der Textfelder txtAusdruck oder txtKriterium klickt.

Dazu nutzen wir das Ereignis Bei Maustaste ab der beiden Textfelder, die wir mit dem Aufruf der gleichen Funktion füllen. Dabei übergeben wir als ersten Parameter den Wert des Parameters Button und als zweiten den Namen des aufrufenden Textfeldes. Der Parameter Button enthält einen Wert, der angibt, welche Maustaste der Benutzer angeklickt hat:

Private Sub txtAusdruck_MouseDown(Button As Integer, _
         Shift As Integer, X As Single, Y As Single)
     Kontextmenue Button, "txtAusdruck"
End Sub
Private Sub txtKriterium_MouseDown(Button As Integer, _
         Shift As Integer, X As Single, Y As Single)
     Kontextmenue Button, "txtKriterium"
End Sub

Die hier aufgerufene Prozedur Kontextmenue erwartet genau diese beiden Parameter (siehe Listing 4).

Public Sub Kontextmenue(Button As Integer, strSteuerelement As String)
     Dim cbr As CommandBar
     Dim cbb As CommandBarButton
     Dim db As DAO.Database
     Dim fld As DAO.Field
     Dim tdf As DAO.TableDef
     Dim qdf As DAO.QueryDef
     Dim strDomaene As String
     Dim lngSelStart As Long
     Dim lngSelLength As Long
     Dim lngIndex As Long
     If Button = acRightButton Then
         strDomaene = Nz(Me!cboDomaene, "")
         Set db = CurrentDb
         lngSelStart = Me.Controls(strSteuerelement).SelStart
         lngSelLength = Me.Controls(strSteuerelement).SelLength
         If db.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type IN (1,4,6) AND Name = ''" _
                 & strDomaene & "''").RecordCount = 1 Then
             Set tdf = db.TableDefs(strDomaene)
             Set cbr = CommandBars("Form View Control")
             cbr.Reset
             For Each fld In tdf.Fields
                 lngIndex = lngIndex + 1
                 Set cbb = cbr.Controls.Add(msoControlButton, , , lngIndex, True)
                 With cbb
                     .Caption = "[" & fld.Name & "]"
                     .OnAction = "=FeldEinfuegen(""" & strSteuerelement & """, ""[" & fld.Name & "]"", " & lngSelStart _
                         & ", " & lngSelLength & ")"
                 End With
             Next fld
         ElseIf db.OpenRecordset("SELECT Name FROM MSysObjects WHERE Type IN (5) AND Name = ''" & strDomaene _
                 & "''").RecordCount = 1 Then
             Set qdf = db.QueryDefs(strDomaene)
             Set cbr = CommandBars("Form View Control")
             cbr.Reset
             For Each fld In qdf.Fields
                 lngIndex = lngIndex + 1
                 Set cbb = cbr.Controls.Add(msoControlButton, , , lngIndex, True)
                 With cbb
                     .Caption = "[" & fld.Name & "]"
                     .OnAction = "=FeldEinfuegen(""" & strSteuerelement & """, ""[" & fld.Name & "]"", " & lngSelStart _
                         & ", " & lngSelLength & ")"
                 End With
             Next fld
         End If
     End If
End Sub

Listing 4: Prozedur zum Anzeigen von Kontextmenü-Einträgen

Hier arbeiten wir mit dem CommandBars-Objekt, wozu wir einen Verweis auf die Bibliothek Microsoft Office 16.0 Object Library benötigen. Diesen fügen wir mit dem Verweise-Dialog (siehe Bild 5) hinzu.

Bild 5: Verweis auf die Office-Bibliothek

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo