Microsoft Access zeichnet sich im allgemeinen durch Benutzerfreundlichkeit aus. Heute bin ich allerdings auf ein Problem gestossen, dessen Lösung unerwartet kompliziert war.
Die Aufgabenstellung war eine gängige: Ich habe eine Tabelle, in der Schüler/innen in Kurse eingeteilt werden, eine typische Zwischentabelle für m:n-Relationen also. Im Eingabeformular möchte man Schüler/innen und Kurse mit Hilfe von Dropdown-Feldern auswählen. Das gleiche Formular dient auch für das Durchblättern alter Datensätze. Wenn man mit der Dateneingabe neu anfängt, ist dies kein Problem. Ebenfalls kein Problem ist es, wenn man alte Datensätze – sowohl nicht mehr vorhandene Schüler/innen wie vergangene Kurse – einfach löscht. Ein Problem hat man nur dann, wenn man die alten Datensätze behalten möchte. Dann nämlich werden die Listen in den Dropdowns mit jeder neuen Periode immer länger und unübersichtlicher.
Das gewünschte Verhalten ist eigentlich klar: Wenn ich mit dem Formular einen neuen Datensatz erstelle, dann möchte ich nur die aktuellen Daten in den Dropdowns sehen.
Beim Browsen durch die alten Datensätze müssen dagegen auch die inaktiven Datensätze sichtbar sein.
Es gibt eine Lösung für diese Aufgabenstellung. Allerdings kann man sie nicht zusammenklicken, sondern man benötigt ein paar Zeilen VBA und etwas SQL.
Achtung, dies ist kein Tutorial für Anfänger/innen: Dieser Artikel richtet sich an Leute, die mindestens Grundkenntnisse in Datenbankdesign, SQL und dem Erstellen von Formularen in Access haben. Grundlegende Schritte, z.B. wie man zwischen verschiedenen Ansichten und Objekten in Access wechselt, Tabellen in der Entwurfsansicht ändert, den Nachschlageassistenten verwendet oder in die Entwicklungsumgebung kommt, werden hier nicht erklärt.
1. Schritt: Datendesign
Damit man die gewünschte Unterscheidung machen kann, muss man sich zuerst auf der Ebene der Daten überlegen, wie man aktuelle und nicht aktuelle Datensätze unterscheiden kann.
Bei den Schüler/innen könnte man dies mit einem Datumsfeld wie z.B. Austrittsdatum machen. In meinem Fall genügte allerdings ein einfaches Ja/Nein-Feld namens „Inaktiv“.
Bei den Kursen gab es in meinem Fall tatsächlich bereits ein verwendbares Datumsfeld, nämlich KursAnfang. Allerdings können die Kurse an verschiedenen Daten desselben Jahres beginnen. Deshalb interessiert mich nur das Jahr von KursAnfang. Mit der Funktion Jahr bzw. in VBA Year lässt sich dieses aus dem Datum herausholen. Aktuelle Kurse sind somit jene, bei denen das Jahr in KursAnfang dem letzten vorhandenen Jahr aller Kurs-Datensätzen entspricht.
Wichtig ist auch, dass man bereits auf der Ebene des Tabellendesigns für beide Felder mit Hilfe des Nachschlage-Assistenten Dropdowns erstellt, die man mit allen Daten aus den jeweiligen Tabellen erstellt. Möchte man im Dropdown für die SchülerInnen nicht nur Nach- und Vorname anzeigen, sonder auch noch die inaktiven SchülerInnen kennzeichnen, dann ergibt das im Feld Datensatzherkunft im Register Nachschlagen folgendes SQL:
SELECT tbl_sch.ID_sch, [tbl_sch].[sch_name] & " " & [tbl_sch].[sch_vorname]
& IIf([tbl_sch]![inaktiv]," (inaktiv)","") AS Ausdr1
FROM tbl_sch
ORDER BY tbl_sch.inaktiv DESC , tbl_sch.sch_name, tbl_sch.sch_vorname; 2. Schritt: Formular erstellen
Wenn man seine Tabellendropdowns erstellt hat, kann man die Tabelle in der Navigationsleiste auswählen und im Register Erstellen auf Formular klicken. Damit erhält man bereits ein Formular mit Dropdowns. Allerdings sind immer alle Quelldatensätze sichtbar.
Dem sauberen Programmierstil zuliebe habe ich die Namen der zwei Dropdown-Felder gemäss der Namenskonvention mit dem Präfix cbo für Kombobox versehen. Somit habe ich zwei Dropdown-Controls mit den Namen cboID_sch für die Schüler/innen und cboID_kurse für die Kurse.
3. Schritt: Dynamisches Dropdown mit VBA
Nun fehlt noch das VBA, um das Dropdown je nach Formular-Zustand dynamisch abzufüllen. Dabei hatte ich zwei Schwierigkeiten:
Zum ersten war es mir anhand der deutschen Ereignisbezeichnungen nicht sofort klar, an welches Ereignis ich mein VBA eigentlich hängen soll. Das Ereignis muss immer feuern, wenn man entweder das Formular öffnet oder auf einen neuen Datensatz wechselt. Gelandet bin ich beim ersten Ereignis der Formularereignisse, „Beim Anzeigen“ oder im Englischen etwas präziser Form_Current. Man wählt also im Eigenschaftsblatt zuerst den Auswahltyp Formular, dann rechts neben „Beim Anzeigen“ im Dropdown-Feld „[Ereignisprozedur]“, und schliesslich klickt man auf die drei Pünktchen dahinter. Damit landet man automatisch im VBA-Editor im neu erzeugten Methodenrumpf.
Die zweite Schwierigkeit war, zwischen Insert- und Update-Modus des Formulars zu unterscheiden. Ein „On Insert“ oder etwas ähnliches gibt es nicht. Mit Googlen bin ich dann auf das korrekte If-Statement gestossen:
If Me.NewRecord = True Then
Die Daten für die zwei Kombinationsfelder stecken in der Eigenschaft RowSource. Am einfachsten ist es, wenn man sich diese Eigenschaft mit Debug.Print im Direktfenster ausgeben lässt und den ausgegebenen SQL-Code dann in sein VBA übernimmt. Das spart Tipparbeit und vermeidet Tippfehler. Nach dem Kopieren kann man das Debug.Print wieder löschen oder auskommentieren.
Private Sub Form_Current()
Debug.Print (Me.cboID_kurse.RowSource)
Debug.Print (Me.cboID_sch.RowSource)
If Me.NewRecord = True Then
...
Nun muss man im ersten Teil des Ifs nur noch die zusätzliche WHERE-Clause in sein VBA einbauen. Für die Schüler ist dies:
WHERE tbl_sch.inaktiv = False
Für die Kursdaten wird es dagegen etwas komplizierter. Hier wollen wir ja anhand des Feldes KursAnfang nur die Kurse des letzten erfassten Jahres anzeigen. Mit einem IN und einem eingeklammerten SELECT Max(Year(.. lässt sich auch das auf kompakte Weise lösen:
WHERE Year([tbl_kurszeitpunkte].[KursAnfang]) In
(SELECT Max(Year([KursAnfang])) FROM tbl_Kurszeitpunkte)
Damit war ich fast am Ziel. Nur wenn ich beim Testen vom neuen Datensatz zurück auf einen alten wechselte, dann wurde der Wert im Feld cboID_sch beim ersten Mal nicht aktualisiert, das Feld blieb leer. Beim Hin- und Herklicken zwischen Datensätzen kam es dann wieder. Deshalb habe ich beiden Felder im Else-Teil noch ein Requery spendiert.
Der ganze Code sieht nun so aus:
Private Sub Form_Current()
'Debug.Print (Me.cboID_kurse.RowSource)
'Debug.Print (Me.cboID_sch.RowSource)
If Me.NewRecord = True Then
Me.cboID_sch.RowSource = "SELECT tbl_sch.ID_sch, " & _
"[tbl_sch].[sch_name] & "" "" & [tbl_sch].[sch_vorname] & " & _
"IIf([tbl_sch]![inaktiv],"" (inaktiv)"","""") AS GanzerName " & _
"FROM tbl_sch " & _
"WHERE tbl_sch.inaktiv = False " & _
"ORDER BY tbl_sch.sch_name, tbl_sch.sch_vorname;"
Me.cboID_kurse.RowSource = "SELECT [tbl_Kurszeitpunkte].[ID_kurse], " & _
"[tbl_Kurszeitpunkte].[Kurstitel]&"" ""& [tbl_Kurszeitpunkte].[KursAnfang] " & _
"FROM tbl_Kurszeitpunkte " & _
"WHERE Year([tbl_kurszeitpunkte].[KursAnfang]) In " & _
"(SELECT Max(Year([KursAnfang])) FROM tbl_Kurszeitpunkte) " & _
"ORDER BY [Kurstitel], [KursAnfang] DESC;"
Else
Me.cboID_sch.RowSource = "SELECT tbl_sch.ID_sch, [tbl_sch].[sch_name] & "" "" & " & _
"[tbl_sch].[sch_vorname] AS GanzerName " & _
"FROM tbl_sch " & _
"ORDER BY tbl_sch.inaktiv DESC, tbl_sch.sch_name, tbl_sch.sch_vorname;"
Me.cboID_kurse.RowSource = "SELECT [tbl_Kurszeitpunkte].[ID_kurse], " & _
"[tbl_Kurszeitpunkte].[Kurstitel]&"" ""& [tbl_Kurszeitpunkte].[KursAnfang] " & _
"FROM tbl_Kurszeitpunkte " & _
"ORDER BY [KursAnfang] DESC, [Kurstitel];"
Me.cboID_sch.Requery
Me.cboID_kurse.Requery
End If
End SubPS: Leider kann ich den Code aktuell nicht mit dem Syntax-Highlighter anzeigen, denn der hat zur Zeit einen Bug, der die korrekte Anzeige von Ampersands verhindert.
Download des Artikels als PDF File